春季数据国际化最佳实践

问题描述

我有一个spring mvc项目，其中包含spring数据，jpa和hibernate.我有一个多语言数据库.我设计了数据库和实体.我正在寻找一种按语言查询我的表的最佳实践.我必须编写自定义的jpa查询，还是有查询表的通用方法.

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.

数据库:

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实体:

LocalizedString Entity:

@Embeddable
public class LocalizedString {

    private String lang;
    private String text;

    //Constructors and getter setters...
}

MultilingualString实体:

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;
    }
}

类别实体:

@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);
    }

}

类别存储库:

public interface CategoryRepository extends JpaRepository<Category, Long>{

}

如何将语言参数传递给CategoryRepository并获取该语言的特殊数据?

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

推荐答案

请注意，国际化的最佳实践，特别是在数据库设计中，是独立于spring数据的.

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

特别是因为数据库模式中具有多语言，我在2010年设计了一些东西，试图与可用标准保持一致，例如用于表示语言的ISO 639 -例如，其中的en，de，es，fr，...等语言的众所周知的短名称已在其中进行了标准化.

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.

对于其他人感兴趣的情况，我也将 ISO 3166 用于国家/地区代码以及 ISO 4217 中的货币代码.

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.

因为图片说了上千个单词，所以这是我的数据库设计的一部分屏幕截图(我称其为EarthDB-并有一天会成为OpenSource):

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):

正如您所看到的，语言表使用ISO 639-3代码作为主键(您不需要整数，因为使用iso代码在访问其他表时也更加直观)，并且具有一些更多ISO639有助于完善.

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.

然后language_names表保存以(希望)所有语言编写的所有语言的名称-因此，您可以简单地获取以自己的语言编写的所有语言名称，也可以简单地以英语，德语等编写语言.

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.

因此，该概念始终是为您的基本数据提供一个表，该表仅具有语言独立值.在此基础数据旁边，还有一个用于表语言依赖性文本的表，该表在基础表和语言表之间充当一种M:N表.

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.

因此，您具有基于iso标准的国际化引用完整性;-)

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

创建语言和language_names表的最后但并非最不重要的sql代码:

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.';

这篇关于春季数据国际化最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！