Hibernate version: 2.17


In my database I have a table LOCATION to store Locations:

CREATE TABLE LOCATION (
location_id int, /* Primary Key */
location_name varchar(20) )

Now the description of locations are stored in a different way w.r.t. different languages. For that I have 3 tables :

First a LANGUAGE table to store languages:

CREATE TABLE LANGUAGE (
language_id int, /* Primary Key */
language_name varchar(20) )

Then a TABLE table to store tables whose description will be stored:

CREATE TABLE TABLE (
table_id int, /* Primary Key */
table_name varchar(50),
table_keycolumn_name varchar(50) )

And lastly a TRANSLATION table to store descriptions:

CREATE TABLE TRANSLATION (
translation_id int, /* Primary Key */
language_id int, /* Foreign Key to LANGUAGE */
table_id int, /* Foreign Key to TABLE */
key_value int,
description varchar(100) )


For example this way the data is tored :

location_id location_name
--------------------------------
101 Miss
102 Bram

language_id language_name
----------------------------------
1 English
2 French

table_id table_name table_keycolumn_name
----------------------------------------------------------------
1 LOCATION location_id
2 DEPARTMENT department_id

translation_id language_id table_id key_value description
----------------------------------------------------------------------------
1 1 1 101 Mississauga
2 2 1 101 Mississauga (fr)
3 1 1 102 Brampton
4 2 1 102 Brampton (fr)


There is no foreign key relationship in this type of model between a code table (in this case LOCATION) and the translation table.

what I would like is to build the mapping file of LOCATION in such a way that when I try to retrieve a LOCATION, it can also retrieve its respective descriptions (for all languages) in the same SQL statement. Is it possible? If yes then how?



Thanks


JY

Here are the mapping files:

Location.hbm.xml
===========
<hibernate-mapping>
<class
name="com.jy.model.Location"
table="LOCATION"
>
<id
name="locationId"
type="java.lang.Integer"
column="location_id"
>
<generator class="assigned" />
</id>
<property
name="locationName"
type="java.lang.String"
column="location_name"
not-null="true"
length="50"
/>
</class>
</hibernate-mapping>


Language.hbm.xml
=============
<hibernate-mapping>
<class
name="com.jy.model.Language"
table="LANGUAGE"
>
<id
name="languageId"
type="java.lang.Integer"
column="language_id"
>
<generator class="assigned" />
</id>
<property
name="languageName"
type="java.lang.String"
column="language_name"
not-null="true"
length="50"
/>
</class>
</hibernate-mapping>


Table.hbm.xml
==========
<hibernate-mapping>
<class
name="com.jy.model.Table"
table="TABLE"
>
<id
name="tableId"
type="java.lang.Integer"
column="table_id"
>
<generator class="assigned" />
</id>
<property
name="tableName"
type="java.lang.String"
column="table_name"
not-null="true"
length="50"
/>
<property
name="tableKeyColumnName"
type="java.lang.String"
column="table_keycolumn_name"
not-null="true"
length="50"
/>
</class>
</hibernate-mapping>


Translation.hbm.xml
==============
<hibernate-mapping>
<class
name="com.jy.model.Translation"
table="TRANSLATION"
>
<id
name="translationId"
type="java.lang.Integer"
column="translation_id"
>
<generator class="assigned" />
</id>
<property
name="keyValue"
type="java.lang.Integer"
column="key_value"
not-null="true"
length="5"
/>
<property
name="description"
type="java.lang.String"
column="description"
length="100"
/>

<!-- many-to-one association to Language -->
<many-to-one
name="language"
class="com.jy.model.Language"
not-null="true"
>
<column name="language_id" />
</many-to-one>

<!-- many-to-one association to Table -->
<many-to-one
name="table"
class="com.jy.model.Table"
not-null="true"
>
<column name="table_id" />
</many-to-one>

</class>
</hibernate-mapping>



I was thinking it would be nice to have a java.util.Map in Location class where the key is the language_id and the value is the description. When a Location is retrieved then the respective description for each language are also retrieved in this Map:

Map languages = new HashMap();

// getter and setter
public Map getLanguages() {
return languages;
}
public void setLanguages(Map languages) {
this.languages= languages;
}

// Convenience method to get the description
public String getDescription( Integer languageId ) {
return (String)this.getLanguages().get(languageId);
}



My question is how to make the changes in the Location mapping file so that the Map also gets populated.

Thanks


JY