Hi

I am mapping my objects to a legacy database that uses composite keys.

I have hit a problem with a query that seems to be resulting in an "N+1" type problem - i.e., a new query is run for each row returned despite having requested a fetch join.

Here is the HQL:

select
benefit
from
Benefit benefit
inner join fetch benefit.key
inner join fetch benefit.key.lifeAssured
where
benefit.riskStatus.code = 'LS'

This results in an SQL query that retrieves all data in one go (as expected):

SELECT benefit0_.chdrnum AS chdrnum0_,
benefit0_.life AS life0_,
benefit0_.jlife AS jlife0_,
benefit0_.coverage AS coverage0_,
benefit0_.rider AS rider0_,
benefit0_.chdrnum AS chdrnum1_,
benefit0_.life AS life1_,
benefit0_.jlife AS jlife1_,
benefit0_.coverage AS coverage1_,
benefit0_.rider AS rider1_,
lifeassure1_.chdrnum AS chdrnum2_,
lifeassure1_.life AS life2_,
lifeassure1_.jlife AS jlife2_,
benefit0_.sumins AS sumins0_,
benefit0_.statcode AS statcode0_,
benefit0_.crtable AS crtable0_,
benefit0_.sumins AS sumins1_,
benefit0_.statcode AS statcode1_,
benefit0_.crtable AS crtable1_,
lifeassure1_.occup AS occup2_,
lifeassure1_.lifcnum AS lifcnum2_,
DECODE (lifeassure1_.smoking, 'S', 1, 'N', 0, NULL) AS f1_2_
FROM life400_vw_benefit benefit0_,
lifepf lifeassure1_
WHERE benefit0_.chdrnum = lifeassure1_.chdrnum AND
benefit0_.life = lifeassure1_.life AND
benefit0_.jlife = lifeassure1_.jlife AND
((benefit0_.statcode = 'LS'))

However then for each row returned Hibernate issues another query:

SELECT lifeassure0_.chdrnum AS chdrnum0_,
lifeassure0_.life AS life0_,
lifeassure0_.jlife AS jlife0_,
lifeassure0_.occup AS occup0_,
lifeassure0_.lifcnum AS lifcnum0_,
DECODE (lifeassure0_.smoking, 'S', 1, 'N', 0, NULL) AS f1_0_
FROM lifepf lifeassure0_
WHERE lifeassure0_.chdrnum = ? AND
lifeassure0_.life = ? AND
lifeassure0_.jlife = ?

Despite the fact that this is duplicating the data returned by the first query!

Here is the definition of the composite key on Benefit:

<composite-id name="key" class="com.ff.model.life400.core.BenefitKey">
<key-many-to-one name="lifeAssured" class="com.ff.model.life400.core.LifeAssured">
<column name="CHDRNUM"/>
<column name="LIFE"/>
<column name="JLIFE"/>
</key-many-to-one>
<key-property name="coverage" column="COVERAGE"/>
<key-property name="rider" column="RIDER"/>
</composite-id>

And on LifeAssured:

<composite-id name="key" class="com.ff.model.life400.core.LifeAssuredKey">
<key-many-to-one name="contract" class="com.ff.model.life400.core.Contract">
<column name="CHDRNUM"/>
</key-many-to-one>
<key-property name="lifeKey" column="LIFE"/>
<key-property name="jointLifeKey" column="JLIFE"/>
</composite-id>


Interestingly it does not load the contract property of the LifeAssuredKey.

Is this a known problem with key-many-to-one? I found this other similar post:

http://forum.hibernate.org/viewtopic.php?t=930632

Regards,

Niall

Turned out the best solution was to map the composite key just using key-property and then map the many-to-one relationship separately using insert="false" update="false".