Hibernate need to access properties on join table

How do you access fields of a join table without mapping the join table in your model as an entity?

For example:

A Device object has many Accessory objects. An Accessory object can be associted with many Device(s). It is a classical many to many relationship. There are three tables DEVICE, DEVICE_ACCESSORY, and ACCESSORY. However there is one snag, there is an attribute on the join table that we need to access. The attribute determines if the accessory comes standard with the device. We need it.

One way you could do this is to create a third entity, DeviceAccessory object, that represents the relationship between Device and Accessory. Then Device has a 1 to m relationship with DeviceAccessory and Accessory has a 1 to m relationship with DeviceAccessory. This is the most straight forward approach. And if this suits you, you can stop reading now....

The problem with the third enity approach is that it clutters our domain model. The nice thing about Hibernate is you can map a rich domain model to database tables w/o a lot of clutter.

In other words, there is a join table, but should we make the join table a true entity object? We thought not.
We actually found several solutions.

Background Table Info

Here are the tables involved (prototype version):

DEVICE TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(255)
 ID                                        NOT NULL NUMBER

DEVICE_ACCESSORY TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 FK_DEVICE_ID                                       NUMBER
 FK_ACCESSORY_ID                                    NUMBER
 FLAG                                               NUMBER

ACCESSORY TABLE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(255)
 ID                                        NOT NULL NUMBER

The above tables are just stand-ins for the real tables, which are legacy tables (the above are just prototypes). The DEVICE_ACCESSORY table is a join table.

Background Classes Info

Here are the classes that we are mapping.

Device.java

public class Device
    implements Serializable
{
    private Long id;
    private Set accessories;
    private String name;

 .... [getter and setters omitted]

Accessory.java

public class Accessory
    implements Serializable
{
    private Long id;
    private String accessoryName;
    private boolean flag;
 .... [getter and setters omitted]

The device mapping file is not very interesting (yet) and is as follows:

Device.hbm.xml

<class name="Device" table="DEVICE" >
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
 
        <property name="name" column="NAME" type="string" />
 
        <set name="accessories" inverse="false">
            <key column="FK_DEVICE_ID"/>
            <one-to-many class="Accessory"/>
        </set>
    </class>

The interesting mapping is the Accessory mapping as it is the one that maps to two tables, namely ACCESSORY and DEVICE_ACCESSORY to the one class: Accessory.

First try: swing and a miss

At first blush it seemes a <join> tag would do the trick:

Using a join tag with DEVICE_ACCESSORY as the primary table does NOT work

<class name="Accessory" table="DEVICE_ACCESSORY" >
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
  
        <property name="flag" type="boolean"/>
        <many-to-one name="device" column="FK_DEVICE_ID" class="Device" />

        <join table="ACCESSORY" inverse="false" optional="false">
 		<key column="FK_ACCESSORY_ID"/>
 		<property name="accessoryName" column="name" />
        </join>
  	
    </class>

problem

The issues is that the above keeps reversing the ids. The above mapping thinks the FK_ACCESSORY_ID is on the ACCESSORY table, it is not. The FK_ACCESSORY_ID is on the DEVICE_ACCESSORY table. It tried to join the tables on ACCESSORY.FK_ACCESSORY_ID = DEVICE_ACCESSORY.ID when we really want ACCESSORY.ID = DEVICE_ACCESSORY.FK_ACCESSORY_ID.

Setting the column, foreign-key did not help either as follows:

<join table="ACCESSORY" inverse="false" optional="false">
 	<key column="id" foreign-key="FK_ACCESSORY_ID"/>
 	<property name="accessoryName" column="name" />
    </join>

problem

The above did not work it produced this type of join ACCESSORY.ID = DEVICE_ACCESSORY.ID.
We need ACCESSORY.ID = DEVICE_ACCESSORY.FK_ACCESSORY_ID.

Second try... well it works kinda, base hit

The second approach uses a formula and actually works as follows:

Using a formula works

<class name="Accessory" table="DEVICE_ACCESSORY" >
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
  
        <property name="flag" type="boolean"/>
        <property name="accessoryName" formula="(select a.name from accessory a where a.id=deviceacce0_.FK_ACCESSORY_ID)"/>
        <many-to-one name="device" column="FK_DEVICE_ID" class="Device" />

    </class>

problem

The above uses a formula to calculate the accessoryName. The issues is that the real ACCESSORY table has a lot more fields than just name. We want to access them all, and the above is more than a tad inefficient. It would make your DBA flip his wig b/c we would need a lot of subselects. YUCK!

Third try, a double

The third approach works as well. It uses a subselect.

Using a subselect works

<class name="Accessory" table="DEVICE_ACCESSORY" >
        <subselect>
			select 
				da.FK_DEVICE_ID as fk_device_id, 
				da.ID as id, 
				da.flag as flag, 	
				a.name as name 
			from 	DEVICE_ACCESSORY da 
			inner join 
				ACCESSORY a on da.FK_ACCESSORY_ID=a.id        
        </subselect>
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
  
        <property name="flag" type="boolean"/>
        <property name="accessoryName" column="name" insert="false" update="false"/>
        <many-to-one name="device" column="FK_DEVICE_ID" class="Device" />
    </class>

problem can we edit?

This is efficient, but can we edit the data. Luckily we don't have to for this app as we are accessing legacy data in a new applicaiton. The legacy data is updated by another app not the current on we are working on.

If wanted to make the ACCESSORY table editable from Hiberante, we could add another mapping for that if we had troubles with the former approaches (which seems likely).

Fourth try: Three outs!

Based on the first <join> failure, I had an idea, which came to me as I was writing this. What if we make the ACCESSORY the primary table of the Accessory object (not really a stretch if you think about it). You can't just use a formula to access the DEVICE_ACCESSORY since there is not Device in the context.

Here is a working solution, but in this solution we had to change the Device mapping to a many to many. I think this is the best general purpose solution since it allows the Accessory to be editable (not that we need it).

new Device.hbm.xml

<class name="Device" table="DEVICE" >
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
 
        <property name="name" column="NAME" type="string" />
 
        <set name="accessories" inverse="false" table="DEVICE_ACCESSORY"> <!--NOTICE-->
            <key column="FK_DEVICE_ID"/>
            <many-to-many class="Accessory" column="ID"/>
        </set>
    </class>

This mapping seems to work but misses out

<class name="Accessory" table="ACCESSORY" >
        <id name="id" column="ID" type="long">
            <generator class="sequence"/>
        </id>
  
        <property name="accessoryName" type="string" column="name" />

    	<join table="DEVICE_ACCESSORY" >
 			<key column="FK_ACCESSORY_ID"/>
 			<property name="flag" type="boolean" />
 			<property name="key" column="FK_ACCESSORY_ID" 
 			          insert="false" update="false"
 			          access="field"
 			          type="long"/>
  		</join>
    </class>

problem Not really working.

The above is not the correct mapping either. It seems to works. The JUnit test passes and Hibernate does not complain the problem is there is not a one to one mapping between ACCESSORY and DEVICE_ACCESSORY. A DEVICE_ACCESSORY is associated with a single ACCESSORY, but an ACCESORY is a associated with many DEVICE_ACCESSORY rows.

What we need is what we had in the first go round:

The join tag does not have a column attribute. The solution with the subselect seems to work the best so far. And, exposing DeviceAccessory as an entity seems better and better.