Localization (i18n) with Spring boot with data in database

Question

I am writing a Spring boot app with Thymeleaf, which should support two languages (or more). I have set up the locale resolver and messages.properties so now my app can translate static messages on locale change.

The problem is, because there is some data in my MySQL database that have text in it (for example, description of a product). I would need this data translated as well.

So, my question is, what is the best approach for this? Duplicate all the entries, have two tables, have two separate app for each language?

You need at least to extract the description in a multilingual table. — Walfrat, Sep 22 '17 at 16:57
Do you need to perform filtered queries by these fields? Order bys? — Laiv, Sep 22 '17 at 19:11
no, nothing specific. just get the list of items, where their text attributes are translated — Miha Jamsek, Sep 22 '17 at 19:29
You could use the already persisted description as a default value and implement all the i18n with Spring's i18n localization. The complexity is at finding out a way to dinamically generate the key you put in the property file, according with each entity and instance — Laiv, Sep 22 '17 at 19:57

score 1 · Accepted Answer · answered Sep 22 '17 at 21:04

I managed to solve the problem on my own:

First, i created entities:

Product (id, name, price, description)

Language (id, lang)

Product_t (id, prod_id, lang_id, name, description)

then I created function on database:

FUNCTION `getLangOrder`(language INTEGER, locale varchar(5)) RETURNS int(11)
BEGIN
    case language 
        when (select id from language where lang=locale) then return 1; 
        when 2 then return 2;
        when 1 then return 3;
        else return 4;
    end case;
RETURN 5;
END

This function orders my translations by given specified locale first (if null it defaults) and if that is missing in order I defined (English(2), Slovenian(1), German(3))

Then in my repository instead of using JPA's default findAll() function I defined my own query:

select p.id
, ifnull((select t.description 
                  from product_t t where t.prod_id = p.id 
                  order by getLangOrder(lang_id, @lang) limit 1)
          , p.description 
  ) as description 
, ifnull((select t.name 
                 from product_t t where t.prod_id = p.proj_id 
                 order by getLangOrder(lang_id, @lang) limit 1)
          , p.name
  ) as name
, p.price 
from product p

where @lang is my passed argument (locale read from cookie)

Which then created list with entities Product, where name and description where translated.

score 0 · Answer 2 · answered Aug 18 '22 at 20:11

0

Or: put a 'message key' in the DB (in the 'name' & 'description') fields.

Then do the translation as usual in your Java/Spring/Thymeleaf.

getMessage(message_key, args, locale)

answered Aug 18 '22 at 20:11

Jack Punt

111
1

Localization (i18n) with Spring boot with data in database

2 Answers2