Alex Rivera | Logout

JPA Database structure for internationalisation

Asked 2012-11-17T00:11:34.840
15

I am trying to get a JPA implementation of a simple approach to internationalisation. I want to have a table of translated strings that I can reference in multiple fields in multiple tables. So all text occurrences in all tables will be replaced by a reference to the translated strings table. In combination with a language id, this would give a unique row in the translated strings table for that particular field. For example, consider a schema that has entities Course and Module as follows :-

Course int course_id, int name, int description

Module int module_id, int name

The course.name, course.description and module.name are all referencing the id field of the translated strings table :-

TranslatedString int id, String lang, String content

That all seems simple enough. I get one table for all strings that could be internationalised and that table is used across all the other tables.

How might I do this in JPA, using eclipselink 2.4?

I've looked at embedded ElementCollection, ala this... JPA 2.0: Mapping a Map - it isn't exactly what i'm after cos it looks like it is relating the translated strings table to the pk of the owning table. This means I can only have one translatable string field per entity (unless I add new join columns into the translatable strings table, which defeats the point, its the opposite of what I am trying to do). I'm also not clear on how this would work across entites, presumably the id of each entity would have to use a database wide sequence to ensure uniqueness of the translatable strings table.

BTW, I tried the example as laid out in that link and it didn't work for me - as soon as the entity had a localizedString map added, persisting it caused the client side to bomb but no obvious error on the server side and nothing persisted in the DB

java jpa
Edit
Report

1 Answer

9

(I'm Henno who replied to hwellman's blog.) My initial approach was very similar to your approach and it does the job. It meets the requirement that any field from any entity can reference a localized String Map with a general database table that does not have to reference other more concrete tables. Indeed I also use it for multiple fields in our Product entity (name, description, details). I also had the "problem" that JPA generated a table with only a primary key column and a table for the values that referenced this id. With OpenJPA I had no need for a dummy column:

public class StringI18N {

    @OneToMany(mappedBy = "parent", cascade = ALL, fetch = EAGER, orphanRemoval = true)
    @MapKey(name = "locale")
    private Map<Locale, StringI18NSingleValue> strings = new HashMap<Locale, StringI18NSingleValue();
...

OpenJPA simply stores Locale as a String. Because we don't really need an extra entity StringI18NSingleValue so I think your mapping using @ElementCollection is a bit more elegant.

There is an issue you have to be aware of though: do you allow sharing a Localised with multiple entities, and how do you prevent orphaned Localised entities when the owning entity is removed? Simply using cascade all is not sufficient. I decided to see a Localised as much as possible as a "value object" and not allow it to be shared with other entities so that we don't have to think about multiple references to the same Localised and we can safely use orphan removal. So my Localised fields are mapped like:

@OneToOne(cascade = ALL, orphanRemoval = true)

Depending on my use case I also use fetch = EAGER/LAZY and optional = false or true. When using optional = false I use @JoinColumn(nullable=false) so OpenJPA generates a not null constraint on the join column.

Whenever I do need to copy a Localized to another entity, I do not use the same reference but I create a new Localized instanc

answered 2012-11-21T09:33:54.567

Your Answer