Java 多语言数据库，默认回退

Multi language database, with default fallback

提问by Luca

I have a question that, I know, has been widely discussed about, but in my opinion, there is one aspect that still needs clarification.

我有一个问题，我知道已经被广泛讨论过，但在我看来，还有一个方面需要澄清。

I am creating a web-application with a multilanguage database, I already found some good-practices articles (such as this) and answers here in stack overflow like this.

我创建一个Web应用程序与数据库的多语言，我已经发现了一些好的做法物品（如 本）和答案在这里像堆栈溢出此。

So I decided to use a main table with the IDs of my items and another table with the translation for each item, let's say, for example

所以我决定使用一个包含我的项目 ID 的主表和另一个包含每个项目翻译的表，例如

Content
ContentTranslation

or

或者

Category
CategoryTranslation

and so on.

等等。

Right now what I'm doing? I just get the items from the database with all the translations and then I iterate over each one to look for the correct translation based on the current user's local, and if I find the correct local I set into the main object that translation for the page to render, otherwise I just get the translation that is flagged as the "default" one.

现在我在做什么？我只是从数据库中获取所有翻译的项目，然后我遍历每个项目以根据当前用户的本地查找正确的翻译，如果我找到正确的本地，我将设置为页面翻译的主要对象渲染，否则我只会得到标记为“默认”的翻译。

With large amounts of objects and translations, though, server response time might grow and even if the user might not notice, I don't want this.

但是，对于大量对象和翻译，服务器响应时间可能会增加，即使用户可能没有注意到，我也不希望这样。

So, is there any good practice for this use case too? For example some specific queries that say "pick the translation with locale "it" but if you don't find it just get the one with the "default" flag set?

那么，这个用例也有什么好的做法吗？例如，一些特定的查询会说“选择带有区域设置”它的翻译，但如果你没有找到它，就获取设置了“默认”标志的那个？

Now for the technology I'm using Spring MVC with Hibernate and JPA (by means of JPARepository).

现在对于技术，我将 Spring MVC 与 Hibernate 和 JPA 一起使用（通过 JPARepository）。

My objects all extend a basic Translatable class that I made this way

我的对象都扩展了我以这种方式制作的基本 Translatable 类

@MappedSuperclass
public abstract class Translatable<T extends Translation> extends BaseDTO {

    private static final long serialVersionUID = 562001309781752460L;

    private String title;

    @OneToMany(fetch=FetchType.EAGER, orphanRemoval=true, cascade=CascadeType.ALL)
    private Set<T> translations = new HashSet<T>();

    @Transient private T currentLocale;

    public void addLocale(T translation, boolean edit) {
        if (!edit)
            getTranslations().add(translation);
    }

    public void remLocale(String locale) {
        T tr = null;
        for (T candidate: getTranslations()) {
            if (candidate.getLocale().equals(locale))
                tr = candidate;
        }

        getTranslations().remove(tr);
    }

    public T getLocaleFromString(String locale) {
        if (locale == null)
            return null;
        for (T trans: translations) {
            if (trans.getLocale().equals(locale))
                return trans;
        }
        return null;
    }

    public T getDefaultLocale() {
        for (T tr: translations) {
            if (tr.isDefaultLocale())
                return tr;
        }
        return null;
    }

    public Set<T> getTranslations() {
        return translations;
    }

    public void setTranslations(Set<T> translations) {
        this.translations = translations;
    }

    public T getCurrentLocale() {
        return currentLocale;
    }

    public void setCurrentLocale(T currentLocale) {
        this.currentLocale = currentLocale;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }
}

So in my controller I iterate over the translations, find the one with the right locale and populate the "currentLocale" property, in my page I just take that and the user gets the correct language as intended.

因此，在我的控制器中，我遍历翻译，找到具有正确语言环境的翻译并填充“currentLocale”属性，在我的页面中，我只需要它，用户就会按预期获得正确的语言。

I hope I've been clear and not messy, but if you need more informations I'll be glad to tell you more.

我希望我已经清楚而不是混乱，但是如果您需要更多信息，我会很乐意告诉您更多信息。

采纳答案by M4N

Some notes upfront:

一些注意事项：

• my answer is more of an addition to my answer to this question, where you added a comment which then led to this question
• in my answer I'm using C# and MS SQL Server (and I'll leave out any OR-mapping specific code)

• 我的回答更多是对我对这个问题的回答的补充，您在其中添加了一条评论，然后导致了这个问题
• 在我的回答中，我使用的是 C# 和 MS SQL Server（我将省略任何 OR 映射特定代码）

---

---

In my applications, I use two different approaches for loading multilingual data, depending on the use case:

在我的应用程序中，我使用两种不同的方法来加载多语言数据，具体取决于用例：

Administration / CRUD

管理/CRUD

In the case where the user is entering data or editing existing data (e.g. a product with its translations) I'm using the same approach as you have shown above in your question, e.g:

在用户输入数据或编辑现有数据（例如带有翻译的产品）的情况下，我使用的方法与您在上面的问题中显示的方法相同，例如：

public class Product
{
    public int ID {get; set;}
    public string SKU {get; set;}
    public IList<ProductTranslation> Translations {get; set;}
}
public class ProductTranslation
{
    public string Language {get; set;}
    public bool IsDefaultLanguage {get; set;}
    public string Title {get; set;}
    public string Description {get; set;}
}

I.e. I'll let the OR-mapper load the product instance(s) with all their translations attached. I then iterate through the translations and pick the ones needed.

即，我将让 OR 映射器加载产品实例及其所有翻译。然后我遍历翻译并选择需要的翻译。

Front-end / read-only

前端/只读

In this case, which is mainly front-end code, where I usually just display information to the user (preferably in the user's language), I'm using a different approach:

在这种情况下，主要是前端代码，我通常只向用户显示信息（最好使用用户的语言），我使用了不同的方法：

First of all, I'm using a different data model which doesn't support/know the notion of multiple translations. Instead it is just the representation of a product in the "best" language for the current user:

首先，我使用的是不同的数据模型，它不支持/不知道多重翻译的概念。相反，它只是为当前用户以“最佳”语言表示产品：

public class Product
{
    public int ID {get; set;}
    public string SKU {get; set;}

    // language-specific properties
    public string Title {get; set;}
    public string Description {get; set;}
}

To load this data, I'm using different queries (or stored procedures). E.g. to load a product with ID @Idin the language @Language, I'd use the following query:

为了加载这些数据，我使用了不同的查询（或存储过程）。例如，要加载带有@Id语言ID 的产品@Language，我将使用以下查询：

SELECT
    p.ID,
    p.SKU,
    -- get title, description from the requested translation,
    -- or fall back to the default if not found:
    ISNULL(tr.Title, def.Title) Title,
    ISNULL(tr.Description, def.Description) Description
  FROM Products p
  -- join requested translation, if available:
  LEFT OUTER JOIN ProductTranslations tr
    ON p.ID = tr.ProductId AND tr.Language = @Language
  -- join default language of the product:
  LEFT OUTER JOIN ProductTranslations def
    ON p.ID = def.ProductId AND def.IsDefaultLanguage = 1
  WHERE p.ID = @Id

This returns the product's title and description in the requested language if a translation for that language exists. If no translation exists, the title and description from the default language will be returned.

如果存在该语言的翻译，则以所请求的语言返回产品的标题和描述。如果不存在翻译，将返回默认语言的标题和描述。

回答by Francis Mathew

Using common shared table for all translatable fields of all tables

对所有表的所有可翻译字段使用公共共享表

In the above approach the translation table is an extension of the parent table. Hence ProductTranslation has all the translatable fields of Product. It is a neat and quick approach and nice one as well.

在上述方法中，转换表是父表的扩展。因此 ProductTranslation 具有 Product 的所有可翻译字段。这是一种简洁快捷的方法，也是一种不错的方法。

But there is one disadvantage (not sure if it can be called disadvantage). If many more tables require translate-able fields, that many new tables are required. From my experience we took a different approach. We created a generic table for translation and a link table to link translations to the translate-able fields of the parent table.

但是有一个缺点（不确定是否可以称为缺点）。如果更多的表需要可翻译的字段，则需要许多新表。根据我的经验，我们采取了不同的方法。我们创建了一个通用的翻译表和一个链接表，将翻译链接到父表的可翻译字段。

So I'm going to use the previous example of Product which has two fields title and description that are translate-able to explain our approach. Also consider another table ProductCategory with fields name and description that also require translations.

因此，我将使用前面的 Product 示例，它有两个字段 title 和 description，可以翻译来解释我们的方法。还可以考虑另一个表 ProductCategory，其字段名称和描述也需要翻译。

Product
(
   ID: Integer
   SKU: String
   titleID: Integer // ID of LocalizableText record corresponding title
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

ProductCategory
(
   ID: Integer
   nameID: Integer // ID of LocalizableText record corresponding name
   descriptionID: Integer // ID of LocalizableText record corresponding description
)

LocalizableText // This is nothing but a link table
{
    ID: Integer
}

Translations //This is where all translations are stored.
{
    ID: Integer
    localizableTextID: Integer
    language: String
    text: String
}

To load this data, I'm using different queries (modified the above). E.g. to load a product with ID @Id in the language @Language, I'd use the following query

为了加载这些数据，我使用了不同的查询（修改了上面的内容）。例如，要在语言 @Language 中加载 ID 为 @Id 的产品，我将使用以下查询

SELECT
    p.ID,
    p.SKU,
    -- get title, description from the requested translation,
    -- or fall back to the default if not found:
    Title.text Title,
    description.text Description
  FROM Products p
  -- join requested translation for title, if available:
  LEFT OUTER JOIN Translations title
    ON p.titleID = title.localizableTextID
       AND title.Language = @Language
  -- join requested translation for description, if available:
  LEFT OUTER JOIN Translations description
    ON p.descriptionID = description.localizableTextID
       AND description.Language = @Language
  WHERE p.ID = @Id

This query is based on the assumption that individual fields of Product does not have a default translation

此查询基于产品的各个字段没有默认翻译的假设

Similar query can be used to fetch records from ProductCategory

类似的查询可用于从 ProductCategory 中获取记录