JPA Database structure for internationalisation

java, jpa

Solution

OK, I think I have it. It looks like a simplified version of the first link in my question will work, just using a ManyToOne relationship to a Localised entity (with a different joinColumn for each text element in your main entity) and a simple ElementCollection for the Map within that Localised entity. I coded a slightly different example than my question, with just one entity (Category), having two text elements that need multiple entries for each locale (name and description).

Note this was done against Eclipselink 2.4 going to MySQL.

Two notes about this approach - as you can see in the first link, using ElementCollection forces a separate table to be created, which results in two tables for the translatable strings - one just holds the ID (Locaised) that is the FK in the main one (Localised_strings) that holds all the Map info. The name Localised_strings is the automatic/default name - you can use another one with the @CollectionTable annotation. Overall, this isn't ideal from a DB point of view but not the end of the world.

Second is that, at least for my combination of Eclipselink and MySQL, persisting to a single (auto generated) column table gives an error :( So i've added in a dummy column w a default value in the entity, this is purely to overcome that issue.

import java.io.Serializable;
import java.lang.Long;
import java.lang.String;
import java.util.HashMap;
import java.util.Map;

import javax.persistence.*;


@Entity

public class Category implements Serializable {

@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
private Long id;
@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="NAME_ID")
private Localised nameStrings = new Localised();

@ManyToOne(cascade=CascadeType.ALL)
@JoinColumn(name="DESCRIPTION_ID")
private Localised descriptionStrings = new Localised();

private static final long serialVersionUID = 1L;

public Category() {

    super();
}  

public Category(String locale, String name, String description){
    this.nameStrings.addString(locale, name);
    this.descriptionStrings.addString(locale, description);
}
public Long getId() {
    return this.id;
}

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

public String getName(String locale) {
    return this.nameStrings.getString(locale);
}

public void setName(String locale, String name) {
    this.nameStrings.addString(locale, name);
}
public String getDescription(String locale) {
    return this.descriptionStrings.getString(locale);
}

public void setDescription(String locale, String description) {
    this.descriptionStrings.addString(locale, description);
}

}




import java.util.HashMap;
import java.util.Map;

import javax.persistence.ElementCollection;
import javax.persistence.Embeddable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Localised {

    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private int dummy = 0;
    @ElementCollection
    private Map<String,String> strings = new HashMap<String, String>();

    //private String locale;    
    //private String text;

    public Localised() {}

    public Localised(Map<String, String> map) {
        this.strings = map;
    }

    public void addString(String locale, String text) {
        strings.put(locale, text);
    }

    public String getString(String locale) {
        String returnValue = strings.get(locale);
        return (returnValue != null ? returnValue : null);
    }

}

So these generate tables as follows :-

CREATE TABLE LOCALISED (ID INTEGER AUTO_INCREMENT NOT NULL, DUMMY INTEGER, PRIMARY KEY (ID))
CREATE TABLE CATEGORY (ID BIGINT AUTO_INCREMENT NOT NULL, DESCRIPTION_ID INTEGER, NAME_ID INTEGER, PRIMARY KEY (ID))
CREATE TABLE Localised_STRINGS (Localised_ID INTEGER, STRINGS VARCHAR(255), STRINGS_KEY VARCHAR(255))
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_DESCRIPTION_ID FOREIGN KEY (DESCRIPTION_ID) REFERENCES LOCALISED (ID)
ALTER TABLE CATEGORY ADD CONSTRAINT FK_CATEGORY_NAME_ID FOREIGN KEY (NAME_ID) REFERENCES LOCALISED (ID)
ALTER TABLE Localised_STRINGS ADD CONSTRAINT FK_Localised_STRINGS_Localised_ID FOREIGN KEY (Localised_ID) REFERENCES LOCALISED (ID)

A Main to test it...

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class Main {
  static EntityManagerFactory emf = Persistence.createEntityManagerFactory("javaNetPU");
  static EntityManager em = emf.createEntityManager();

  public static void main(String[] a) throws Exception {
    em.getTransaction().begin();


    Category category = new Category();

    em.persist(category);

    category.setName("EN", "Business");
    category.setDescription("EN", "This is the business category");


    category.setName("FR", "La Business");
    category.setDescription("FR", "Ici es la Business");

    em.flush();

    System.out.println(category.getDescription("EN"));
    System.out.println(category.getName("FR"));


    Category c2 = new Category();
    em.persist(c2);

    c2.setDescription("EN", "Second Description");
    c2.setName("EN", "Second Name");

    c2.setDescription("DE", "Zwei  Description");
    c2.setName("DE", "Zwei  Name");

    em.flush();


    //em.remove(category);


    em.getTransaction().commit();
    em.close();
    emf.close();

  }
}

This produces output :-

This is the business category
La Business

and the following table entries :-

Category
"ID"    "DESCRIPTION_ID"    "NAME_ID"
"1"         "1"                 "2"
"2"         "3"                 "4"

Localised
"ID"    "DUMMY"
"1"         "0"
"2"         "0"
"3"         "0"
"4"         "0"

Localised_strings

"Localised_ID"  "STRINGS"                        "STRINGS_KEY"
"1"                 "Ici es la Business"                 "FR"
"1"                 "This is the business category"      "EN"
"2"                 "La Business"                        "FR"
"2"                 "Business"                       "EN"
"3"                 "Second Description"                 "EN"
"3"                 "Zwei  Description"              "DE"
"4"                 "Second Name"                        "EN"
"4"                 "Zwei  Name"                         "DE"

Uncommenting the em.remove correctly deletes both the Category and it's associated Locaised/Localised_strings entries.

Hope that all helps someone in the future.

Problem

Original source

Related problems

• Internationalization with Hibernate