I am trying to map a lookup table without a seperate class corresponding to that table. I have a bunch of lookup tables that are simple id and name pairs. For example there is a table called order status defined below

CREATE TABLE OrderStatus (pk_status_id INTERGER PRIMARY KEY, status_value VARCHAR(20));

This table would be populated with values such as: 'Open', 'Closed', 'On Hold', etc. I then have a main 'Order' table that has a foreign key fk_status_id that links the order with a predefined status in the status lookup table. The mapping looks like this

<class name="Order" table="Order">
...
<many-to-one name="Status" class="OrderStatus">
<column name="fk_order_status" />
</many-to-one>
...
</class>

<class name="OrderStatus" table="OrderStatus">
...
<property name="StatusValue" column="status_value" type="String"/>
...
</class>

The problem with this mapping is that it creates a class OrderStatus. I want the StatusValue to be a property of Order and to make the fact that a lookup table is used transparent to the user. I was thinking of something like:

<class name="Order" table="Order">
...
<join table="OrderStatus">
<key column="fk_order_status" />
<property name="StatusValue" column="status_value" type="String"/>
</join>
...
</class>

My question with this is will it lead to duplicate entries in the OrderStatus lookup table. For example if I save two Orders both with a status of 'Open' will it create two rows in the OrderStatus table both with a status_value='Open' and different pk_status_id's. How can I avoid this? Any help would be most appreciated.

I ran into this exact problem today and found your post looking for
my answer. Had to find it myself though :)

Here's my solution: derived property.

In your case it would be something like:

<property name="StatusValue"
formula = "(select o.status_value from OrderStatus o where o.pk_status_id=fk_status_id)"
type = "String"
/>

Unfortunately that doesn't seem to work for me. I tried a formula like you suggested but I get the following MappingException when my application tries to load the mappings:

ERROR com.bearingpoint.orderlog.struts.HibernatePlugin - mapping error
org.hibernate.MappingException: Could not determine type for: String, for columns: [org.hibernate.mapping.Formula( (select location from tbl_Dfas_Location o where o.pk_dfas_location_id=fk_dfas_location_id) )]

My mapping file looks like this:

<class name="com.bearingpoint.orderlog.data.Order" table="tbl_Order">
....
<property name="DfasLocation" formula = "(select location from tbl_Dfas_Location o where o.pk_dfas_location_id=fk_dfas_location_id)" type = "String" />
...
</class>

It says that it cannot determine the type but I explicitly give the type as String so I don't know what to say. I think the formula approach is not going to work, sadly. Let me know if you got it to work.

hi there
I have tried mapping the data from the lookup table in this manner.

i have a tables Author and Language(my lookup table).
my author mapping :
<hibernate-mapping package="test">

<class name="Author" table="Author">
<id name="id" column="Id" type="integer">
<generator class="identity"/>
</id>

<property name="name" column="name" type="string" />
<property name="languageid" insert="true" column="languageid" type="integer" />
<property name="languageidy" insert="false" formula="(select o.langId from language o where o.langId=languageId)" type="integer" />
</class>

</hibernate-mapping>

here languageid is the foreigrn key to the language table used for insertion of records and languageidy is derived used only for the retreival of the matching results.


my language mapping goes like this:


- <class name="Language" table="language" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" batch-size="1" select-before-update="false" optimistic-lock="version">
- <id name="langid" column="langId" type="integer" unsaved-value="null">
<generator class="assigned" />
</id>
<property name="name" column="name" type="string" not-null="false" unique="false" update="true" insert="true" />
</class>
</hibernate-mapping>

tell me ,does this helped you anyways...

Try type="string" (lower case) or fully qualified type="java.lang.String"

snake068, did you ever get a decent reply to this offline? I am facing the exact same issue. A search over the forums reveals that this problem does not seem to be handled in a simple read-the-FAQ boilerplate kind of way.

Cheers,
Laird