How to map this relationship in Hibernate ?

Question

I have 2 tables :

The table Data : id, plus many fields. Some of these fields are 'codes' which reference multilanguage values from the next table, for example country_code, continent_code.

The table Thesaurus which contains the multilanguage codes, with columns: code, code_type, language, text. Code is unique for one code_type but there might be the same code several times with different 'code_type' values.

Example data from this table :

code    code_type    language    text
----------------------------------------------------
USA     CNT          EN          United States
USA     CNT          FR          Etats-Unis
FR      CNT          EN          France
FR      CNT          FR          France
FR      LNG          EN          French
FR      LNG          FR          Français

So the country_code column of the data table might contain 'FR' or 'US', and the language code column might contain 'FR' as well. It is implicit that the country_code column contains a code of type 'CNT' for country, and the language_code column contains a code of type 'LNG' for language.

How can I map this in Hibernate so that I can do something like that in my Java code : (let's assume the current locale of the app is US English)

myData.getCountryCode(currentLocale.getlanguage()); --> returns 'France'
myData.getLanguageCode(currentLocale.getlanguage()); --> returns 'French'

Note that I can not modifiy the DB schema, which I didn't design myself !

Answer 1

You can have list of two different POJOs, which are implementing same abstract class, in your Data class for both country code and language code (with fields code, language and text). To map them you can use "inheritance mapping" with single table strategy and define the code_type column as discriminator column.

    @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
    @DiscriminatorColumn(name = "code_type", discriminatorType = DiscriminatorType.STRING)

You can then query the language code or the country code as follows

    myData.getCountryCode(currentLocale.getlanguage()).getText(); --> returns 'France'
    myData.getLanguageCode(currentLocale.getlanguage()).getText(); --> returns 'French'

And you can define the code, code_type and language columns as unique.

For more information see the hibernate "Mapping inheritance" section here