JBoss Hibernate Reference - 7.6. More complex association mappings

7.6. More complex association mappings

More complex association joins are extremely rare. Hibernate makes it possible to handle more complex situations using SQL fragments embedded in the mapping document. For example, if a table with historical account information data defines accountNumber, effectiveEndDate and effectiveStartDate columns, mapped as follows:

<properties name="currentAccountKey">
    <property name="accountNumber" type="string" not-null="true"/>
    <property name="currentAccount" type="boolean">
        <formula>case when effectiveEndDate is null then 1 else 0 end</formula>
    </property>
</properties>
<property name="effectiveEndDate" type="date"/>
<property name="effectiveStateDate" type="date" not-null="true"/>

Then we can map an association to the current instance (the one with null effectiveEndDate) using:

<many-to-one name="currentAccountInfo" 
        property-ref="currentAccountKey"
        class="AccountInfo">
    <column name="accountNumber"/>
    <formula>'1'</formula>
</many-to-one>

In a more complex example, imagine that the association between Employee and Organization is maintained in an Employment table full of historical employment data. Then an association to the employee's most recent employer (the one with the most recent startDate) might be mapped this way:

<join>
    <key column="employeeId"/>
    <subselect>
        select employeeId, orgId 
        from Employments 
        group by orgId 
        having startDate = max(startDate)
    </subselect>
    <many-to-one name="mostRecentEmployer" 
            class="Organization" 
            column="orgId"/>
</join>

You can get quite creative with this functionality, but it is usually more practical to handle these kinds of cases using HQL or a criteria query.