JPA: problems with translated tables

Question

I have a problem with JPA (using Spring and Hibernate) when having 2 tables, one of them represents the translation of the other one. My intention is to retrieve the whole information of the registries in one query, but I can't get it: I need to retrieve first the registry of the TABLE_A, and then the associated registry from TABLE_A_TRANSLATION, and then merge them "by hand". Here is an example:

CREATE TABLE TABLE_A (
    ID INTEGER NOT NULL,
    CONSTRAINT PK_A PRIMARY KEY (ID)
);

CREATE TABLE TABLE_A_TRANSLATION (
    ID INTEGER NOT NULL,
    LANG VARCHAR2(2) NOT NULL,
    DESCRIPTION VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_A_TRA PRIMARY KEY (ID, LANG),
    CONSTRAINT FK_A_TRA_A FOREIGN KEY (ID) REFERENCES TABLE_A (ID)
);

The associated JPA Java code, is the following:

@Entity
@Table(name = "table_a")
public class TableA {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @Column(name = "code", nullable = false, unique = false, length = 10)
    private String code;

    private String lang;
    private String description;

    // getters and setters
}

@Entity
@Table(name = "table_a")
public class TableATranslation {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @Id
    @Column(name = "lang", nullable = false, unique = true, length = 2)
    private String lang;

    @Column(name = "description", nullable = false, unique = false, length = 100)
    private String description;

    // getters and setters
}

And when I want to retrieve a TABLE_A element, I do the following (please, avoid talking about syntactic errors, I know that the 'find' method and the Entity classes are not well described, but it's just an example):

public TableA getOne(Integer id, String lang) {
    TableA tableA = entityManager.find(TableA.class, id);
    TableATranslation tableATrans = entityManager.find(TableATranslation.class, ...);

    tableA.lang = tableATrans.lang;
    tableA.description = tableATrans.description;
}

Summarizing, I need to execute 2 requests to the database to have a complete registry. Is it any way to improve it?

Thanks

Answer 1

If I understood you right, you may have several translations for each TABLE_A entity. In this case the best choise us to use @OneTwoMany relationship: one TABLE_A can reference to many TABLE_A_TRANSLATION entities.

To achieve this you need to modify your TABLE_A_TRANSLATION table a little: I suggest you to add a SOURCE_ID field to reference the TABLE_A primary key.

CREATE TABLE TABLE_A_TRANSLATION (
    ID INTEGER NOT NULL,
    SOURCE_ID INTEGER NOT NULL,
    LANG VARCHAR2(2) NOT NULL,
    DESCRIPTION VARCHAR2(100) NOT NULL,
    CONSTRAINT PK_A_TRA PRIMARY KEY (ID),
    CONSTRAINT FK_A_TRA_A FOREIGN KEY (SOURCE_ID) REFERENCES TABLE_A (ID)
);

And then in your entities:

TableATranslation:

@Entity
@Table(name = "table_a_translation") // <- notice that you had a typo here
public class TableATranslation {     //    ("table_a" instead of "table_a_translation")
    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @Column(name = "lang", nullable = false, unique = true, length = 2)
    private String lang;

    @Column(name = "description", nullable = false, unique = false, length = 100)
    private String description;

    @ManyToOne
    private TableA source; // so you can reference the source of this translation

    // getters and setters
}

TableA:

@Entity
@Table(name = "table_a")
public class TableA {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    private Integer id;

    @Column(name = "code")
    private String code;

    @Column(name = "lang")
    private String lang;

    @Column(name = "description")
    private String description;

    @OneToMany(mappedBy = "source")
    private List<TableATranslation> translations;

    // getters and setters
}

Then if you fetch your TableA entity, it will have a list of all associated translations:

TableA t = entityManager.find(TableA.class, 1);
List<TableATranslation> translations = t.getTranslations();

And if you want hibernate to fetch all theese entities using one SQL query, you can specify a FetchMode:

@OneToMany(mappedBy = "source")
@Fetch(FetchMode.JOIN)
private List<TableATranslation> translations;

The generated SQL will look like this:

select ...  from table_a tablea0_ 
left outer join table_a_translation translatio1_ 
on tablea0_.id=translatio1_.source_id where tablea0_.id=?

If you have just one translation for each TableA entity, you could use a @OneToOne relationship similary.