I have a problem using the the composite id and would like to get some help please.
I am using named queries and want to join 2 tables.
I have created a name query in TableA which is joining to TableB.
The problem I am having is when using an individual key of the composite id, I am unable to get an expected result.
I have run the query via SQL and it returns the expected result.

The tables are as follows:
TableA
Index1 PK
Index2 PK,FK

TableB
Index1 PK


For Table A we have a java class defined to handle the composite key ie ClientOptionsId.
The definition of the hibernate xml defining the table is :
<hibernate-mapping>
<class
name="fullpathname.ClientOptions"
table="TableA"
lazy="false">
<composite-id
name="ClientOptionsId" class="CAClientOptionsId">
<key-property name="IndexPK" type="integer" column="OptionId"/>
<key-property name="IndexPKFK" type="integer" column="EntId"/>
</composite-id>


TableB is defined as:
<hibernate-mapping>
<class
name="fullpathname.Option"
table="TableB"
lazy="false">

<id name="IndexPK" type="integer" column="OptionId" unsaved-value="-1">
<generator class="identity"/>
</id>


1)
The query I have included to TableA is:
<query name="getAllOptionsForClient">
<![CDATA[
from
fullpathname.TableA,
fullpathname.TableB
where TableA.IndexPK = TableB.IndexPK
]]>
</query>

The error I get is :
<could not resolve property: IndexPK of: fullpathname.TableA>

2)
I have also tried the following using the index as defined in the class that defines the composite Id and while this doesn't error out, it also doesn't return an expected result.

<query name="getAllOptionsForClient">
<![CDATA[
from
fullpathname.CAClientOptionsId ,
fullpathname.TableB
where CAClientOptionsId.IndexPK = TableB.IndexPK
]]>
</query>

3)
I have also tried the following.
Merged the two queries above by joining TableA & TableB by IndexPK and joining TableB with the index as defined in the composite Id class.
While this also doesn't error out, it also doesn't return a result as per the previous query.

<query name="getAllOptionsForClient">
<![CDATA[
from
fullpathname.TableA as hco ,
fullpathname.CAClientOptionsId,
fullpathname.TableB as ho
where TableA.IndexPK = TableB.IndexPK
and CAClientOptionsId.IndexPK = TableB.IndexPK
]]>
</query>

4)
Also tried the same query from (3) but joined TableA instead with the index as defined in the composite Id class. This gives the same result as (3).
<query name="getAllOptionsForClient">
<![CDATA[
from
fullpathname.TableA as hco ,
fullpathname.CAClientOptionsId,
fullpathname.TableB as ho
where TableA.IndexPK = TableB.IndexPK
and CAClientOptionsId.IndexPK = TableA.IndexPK
]]>
</query>

Hope I've explained it clearly enough.
Am a little stuck here and up against time so if anybody has an idea ..... would greatly appreciate it.

thanks,

Try:

Thanks anadbn for you reply.
I tried this but still couldn't a result.

To put the problem in its simplest form, I can't even get a result when selecting a field that makes up ppart of the compositeid eg
if the composite id is defined as :

<hibernate-mapping>
<class
name="TableA"
table="HIN_CAClientOptions"
lazy="false">

<composite-id
name="caClientOptionsId"
class="fullpathname.CAClientOptionsId">
<key-property name="IndexPK" type="integer" column="OptionId"/>
<key-property name="IndexPKFK" type="integer" column="EntId"/>
</composite-id>


and I perform the following:
<query name="getAllOptionsForClient">
<![CDATA[
select hco.IndexPK from
fullpathname.TableA as hco
]]>
</query>

I get <could not resolve property: optionId of: com.btfin.wrapcore.gateway.equity.corporateaction.jdv.dao.HINCAClientOptions>

If I add the identity class as follows :
<query name="getAllOptionsForClient">
<![CDATA[
select hco.hcok.IndexPK from
fullpathname.TableA as hco,
fullpathname.CAClientOptionsId as hcok
]]>
</query>


I get no result and yet the table definitely has entries.

thanks,

[quote="sleerd"]
and I perform the following:
<query name="getAllOptionsForClient">
<![CDATA[
select hco.IndexPK from
fullpathname.TableA as hco
]]>
</query>
[quote]

For this use the following query:


The reason you get the "could not resolve property" is because, Hibernate considers IndexPK a property of "CAClientOptionsId" and not TableA directlry.

thanks again. I have tried this and this was how I coded it from the previous post but I still did not get a result.

If I add the identity class as follows :
<query name="getAllOptionsForClient">
<![CDATA[
select hco.hcok.IndexPK from
fullpathname.TableA as hco,
fullpathname.CAClientOptionsId as hcok
]]>
</query>

I get no result and yet the table definitely has entries.

If the reference <fullpathname.CAClientOptionsId as hcok > is left out, it returns a <could not resolve property: CAClientOptionsId of: fullpathname.TableA>