Spring data internationalization best practice

Question

I have a spring mvc project with spring data, jpa and hibernate. I have a multilanguage database. I designed my database and entity. I am looking for a best practice to query my tables by language. Do I have to write custom jpa query, or is there a generic way to query my tables.

If I have a mistake on db or entity design, please warn me.

Database:

CREATE TABLE translation (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id));

CREATE TABLE translation_text (
  translation_id BIGINT UNSIGNED NOT NULL,
  lang VARCHAR(2),
  text VARCHAR(1000));

ALTER TABLE translation_text
ADD FOREIGN KEY (translation_id)
REFERENCES translation(id);

CREATE TABLE category (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_name BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id));

ALTER TABLE category
ADD FOREIGN KEY (category_name)
REFERENCES translation(id);

LocalizedString Entity:

@Embeddable
public class LocalizedString {

    private String lang;
    private String text;

    //Constructors and getter setters...
}

MultilingualString Entity:

@Entity
@Table(name = "translation")
public class MultilingualString {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private long id;

    @ElementCollection(fetch = FetchType.EAGER)
    @MapKeyColumn(name = "lang_key")
    @CollectionTable(name = "translation_text", joinColumns = @JoinColumn(name = "translation_id"))
    private final Map<String, LocalizedString> map = new HashMap<String, LocalizedString>();

    public MultilingualString() {
    }

    public MultilingualString(String lang, String text) {
        addText(lang, text);
    }

    public void addText(String lang, String text) {
        map.put(lang, new LocalizedString(lang, text));
    }

    public String getText(String lang) {
        if (map.containsKey(lang)) {
            return map.get(lang).getText();
        }
        return null;
    }

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }
}

Category Entity:

@Entity
@Table(name = "category")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Category extends BaseEntity<Long> implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="category_name")
    private final MultilingualString categoryName = new MultilingualString();


    public Category(String lang, String categoryName) {
        this.categoryName.addText(lang, categoryName);
    }
    public Category() {
        super();
    }

    @Override
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCategoryName(String lang) {
        return this.categoryName.getText(lang);
    }

    public void setCategoryName(String lang, String categoryName) {
        this.categoryName.addText(lang, categoryName);
    }

}

Category Repository:

public interface CategoryRepository extends JpaRepository<Category, Long>{

}

How can I pass a language parameter to CategoryRepository and get that language spesific data?

Answer 1

Please note that internationalization best practice, especially in database design is independend of spring data.

Especially for having multilanguages in database schemas I have designed something back in 2010 that tries to keep up with available standards as the ISO 639 for representing languages - for example the well know short names for languages like en, de, es, fr, ... are standardized in there.

For the case that it is interesting to someone else I also have used ISO 3166 for the country codes, as well as ISO 4217 for currency codes.

Because a pictures says more than thousand words here is a part screenshot of my database design (that I call EarthDB - and will become OpenSource some day):

As you can simply see the languages table uses the ISO 639-3 code as primary key (you don't need an integer for this, because using the iso code is much more intuitive also when visiting other tables) and has some more ISO639 things for completness.

The language_names table then holds the names from all languages written in (hopefully) all languages - so you could simply get all language names written in their own language or simply in english, german, etc.

The concept how to use this base in more general could then be seen when haveing an eye on the countries and their country_names (written in all kind of languages). The same then goes for the currencies and their currency_names and all other kind of things you might need to have internationalization for.

So the concept is always to have a table for your base data that has only language independend values. beside this base data you have a table for the language dependend text that acts as a kind of M:N table between your base table and the languages table.

So you have referential integrity for your internationalization base on iso standards ;-)

Last but not least the sql code to create the languages and language_names tables:

create table LANGUAGES
(
   LANG_ISO639_3        char(3) not null comment 'ISO639 Part 3',
   LAN_LANG_ISO639_3    char(3) comment 'ISO639 Part 3',
   LANG_ISO639_2T       char(3) comment 'ISO 639 Part 2 terminology',
   LANG_ISO639_2B       char(3) comment 'ISO 639 Part 2 bibliographic',
   LANG_ISO639_1        char(2) comment 'ISO 639 Part 1',
   LANG_SCOPE           char(1) not null comment 'Scope of denotation:

     Individual languages
     Macrolanguages
     Special situations

     Collections of languages
     Dialects
     Reserved for local use

     http://www.sil.org/iso639-3/scope.asp
     ',
   LANG_TYPE            char(1) not null comment 'Type of language:

            Living languages
            Extinct languages
            Ancient languages
            Historic languages
            Constructed languages

            http://www.sil.org/iso639-3/types.asp',
   primary key (LANG_ISO639_3)
);

alter table LANGUAGES comment 'ISO 639 table http://www.sil.org/iso639-3/codes';


create table LANGUAGE_NAMES
(
   LANG_ISO639_3        char(3) not null comment 'ISO639 Part 3',
   LAN_LANG_ISO639_3    char(3) not null comment 'ISO639 Part 3',
   LNAMES_NAME          national varchar(64) not null comment 'Language name',
   LNAMES_INAME         national varchar(64) comment 'Inverted language name',
   primary key (LANG_ISO639_3, LAN_LANG_ISO639_3)
);

alter table LANGUAGE_NAMES comment 'ISO 639 language names written in given language.';