Hi,

I am using Hibernate 3.0. I am facing really weird problem. We are using oracle 9i for data store. For some tables composite-key FetchMode.JOIN query works fine but for some others it doesn't. I am including both examples below.

Below are the tables which works fine with composite key
------------------------------------------------------------------

Parent Table: SEG_CODE_TYPE
-------------------------------------
Column Definations: CODE_TYPE_ID varchar2(10) not null
CODE_TYPE_NAME varchar2(80) not null
Primary key: CODE_TYPE_ID


<hibernate-mapping>

<class
name="com.XYZ.kernel.segmentation.SegmentCodeType"
table="SEG_CODE_TYPE"
>

<id
name="id"
type="java.lang.String"
column="CODE_TYPE_ID"
>
<generator class="sequence" >
<param name="sequence">QUERY_ID_SEQ</param>
</generator>
</id>

<property
name="codeTypeName"
type="java.lang.String"
column="CODE_TYPE_NAME"
not-null="true"
/>

<!-- Associations -->

<set name="segCodes" lazy="true">
<key column="CODE_TYPE_ID"/>
<one-to-many class="com.XYZ.kernel.segmentation.SegmentCode"/>
</set>

</class>
</hibernate-mapping>


Child Table: SEG_CODE
---------------------------
Column Definations: CODE_TYPE_ID varchar2(10) not null
CODE_ID varchar2(10) not null
CODE_VALUE varchar2(80) not null
Foreign Key: CODE_TYPE_ID
Composite Key: CODE_TYPE_ID, CODE_ID

<hibernate-mapping>

<class
name="com.XYZ.kernel.segmentation.SegmentCode"
table="SEG_CODE"
>

<composite-id>
<key-property name="id" column="CODE_TYPE_ID"/>
<key-property name="codeId" column="CODE_ID"/>
</composite-id>


<property
name="codeValue"
type="java.lang.String"
column="CODE_VALUE"
not-null="true"
/>

</class>
</hibernate-mapping>

Code For data lookup From SEG_CODE table by help of the primary key CODE_TYPE_ID of the parent table looks like as below.

public java.util.List<SegmentCodeType> findAll (String codeTypeId) {
Criteria crit = getSession().createCriteria(getReferenceClass()).setFetchMode("segCodes", FetchMode.JOIN);

if(codeTypeId != null)
{
crit.add(Restrictions.eq("id", codeTypeId));
}

return crit.list();
}

And above query returns SegmentCodeType objects (one object for as many as row i found from SEG_CODE table) and each object points to the Set of SEG_CODE objects (1 object for one row found from SEG_CODE table).

Here is the query issued by hibernate:
--------------------------------------------
select this_.CODE_TYPE_ID as CODE1_1_, this_.CODE_TYPE_NAME as CODE2_19_1_,
segcodes2_.CODE_TYPE_ID as CODE1_3_, segcodes2_.CODE_ID as CODE2_3_,
segcodes2_.CODE_TYPE_ID as CODE1_0_, segcodes2_.CODE_ID as CODE2_0_,
segcodes2_.CODE_VALUE as CODE3_20_0_
from SEG_CODE_TYPE this_, SEG_CODE segcodes2_
where this_.CODE_TYPE_ID=segcodes2_.CODE_TYPE_ID(+)
and this_.CODE_TYPE_ID= ?


/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Below are the tables which doesn't work with composite key
--------------------------------------------------------------------

Parent Table: QUERY
-------------------------------------
Column Definations: QUERY_ID number(8) not null
QUERY_NAME varchar2(80) not null
Primary key: QUERY_ID

<hibernate-mapping>

<class
name="com.XYZ.kernel.segmentation.Query"
table="QUERY"
>

<id
name="id"
type="java.lang.Integer"
column="QUERY_ID"
>
<generator class="sequence" >
<param name="sequence">QUERY_ID_SEQ</param>
</generator>
</id>

<property
name="name"
type="java.lang.String"
column="QUERY_NAME"
not-null="true"
/>

<set name="queryRules" lazy="true">
<key column="QUERY_ID"/>
<one-to-many class="com.XYZ.kernel.segmentation.QueryRule"/>
</set>

</class>
</hibernate-mapping>


Child Table: QUERY_RULE
---------------------------
Column Definations: QUERY_ID number(8) not null
RULE_NUMBER number(4) not null
ATTRIBUTE_NAME varchar2(80) not null
Foreign Key: QUERY_ID
Composite Key: QUERY_ID, RULE_NUMBER

<hibernate-mapping>

<class
name="com.XYZ.kernel.segmentation.QueryRule"
table="QUERY_RULE">


<composite-id>
<key-property name="id" column="QUERY_ID" />
<key-property name="ruleNo" column="RULE_NUMBER" />
</composite-id>

<property
name="attributeName"
type="java.lang.String"
column="ATTRIBUTE_NAME"
not-null="true"
/>

</class>
</hibernate-mapping>

Code For data lookup From QUERY_RULE table by help of the primary key QUERY_ID of the parent table looks like as below.

public java.util.List<Query> findAll (java.lang.Integer key)
{
Criteria crit = getSession().createCriteria(getReferenceClass()).setFetchMode("queryRules", FetchMode.JOIN);

if(key != null)
{
crit.add(Restrictions.eq("id", key));
}

return crit.list();
}

Unfortunately this returns the Query object but it returns empty set for the QUERY_RULE data which should be stored as FetchMode.JOIN should take care of this problem. I have also tried to change composite key by adding types in it. For Example

Here is the query issued by hibernate:
--------------------------------------------
select this_.QUERY_ID as QUERY1_0_, this_.QUERY_NAME as QUERY2_23_0_
from QUERY this_
where this_.QUERY_ID=?

<composite-id>
<key-property name="id" column="QUERY_ID" type="java.lang.Integer"/>
<key-property name="ruleNo" column="RULE_NUMBER" type="java.lang.Integer"//>
</composite-id>

But that didn't solve problem either. Can you please tell me what's wrong with code?

Yoohoo!!

I figured out problem on my own. Here is the the changes i have made in my method call and it worked fine!!

public java.util.List<Query> findAll (java.lang.Integer key)
{
/* ****** This one was not working ****
Criteria crit = getSession().createCriteria(getReferenceClass()).setFetchMode("queryRules", FetchMode.JOIN);

if(key != null)
{
crit.add(Restrictions.eq("id", key));
}
crit.addOrder(this.getDefaultOrder());
return crit.list();
************************************************/

Transaction tx = getSession().beginTransaction();

Query currentQueryObject = (Query) getSession().get(Query.class, key);
Hibernate.initialize(currentQueryObject.getQueryRules());

tx.commit();
getSession().close();

List result = new ArrayList();
result.add(currentQueryObject);

return result;
}

When ever you use composite key and run into similar problem, you can use above technique to load child table data and it works fine.