Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.15

Mapping documents:

Code:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
    <class name="Table1Data" table="table1">
            <composite-id name="table1Id" class="Table1DataIdentifier">
                        <key-property name="tableId" column="table1_id" type="long" />
                        <key-property name="startDate" column="start_date" type="timestamp" />
            </composite-id>
        <property name="acctNumber" column="acct_numbr" not-null="true" />
        <property name="totalApproved" column="total_approved" type="int" />
        <property name="lastUpdatedDate" column="last_updated_dat" type="timestamp" />

        <set name="table2Set" lazy="true" >
            <key>
                        <column name="table1_id" />
                        <column name="start_date" />
            </key>
            <one-to-many class="Table2Data" />
        </set>
    </class>
</hibernate-mapping>

Mapping 2:
=========

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
    <class name="Table2Data" table="table2">
            <id name="table2Id" column="table2_id" type="long" >
                        <generator class="native" />
            </id>
        <property name="firstName" column="fname" />
        <property name="lastName" column="lname" />
        <property name="acctNumber" column="acct_number" not-null="true" unique="true" />
        <property name="table1Id" column="table1_id" type="long" not-null="true" />
        <property name="startDate" column="start_date" type="timestamp" not-null="true" />
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

String query = "select sp from Table1Data sp join fetch sp.table2Set ref where sp.acctNumber = ? ";

Object[] params = {accountNumber};
List list =  myDAO.get(query, params);
Iterator it = list.iterator();
while(it.hasNext()) {
      Table1Data td = (Table1Data)it.next();
      Set table2Set = td.getTable2Set();
}

Full stack trace of any exception that occurs: none

Name and version of the database you are using: Oracle 9i

When I try to execute this HQL, it correctly produce the final SQL joining TABLE1 and TABLE2. but when I try to access table2Set in my java code as : it execute another SQL, querying only TABLE2.

If I change my PK in TABLE1 from composite-id to normal id with only table1Id then above code does not produce extra SQL to get TABLE2 data. Why is that? Why having composite-id cause this extra SQL generation even though, I am fetching eagerly with HQL?

Second problem is, in above extra SQL execution to fetch TABLE2 data, it uses start_date captured from TABLE1 to compare with TABLE2.start_date and that fails. I have read many posting related to date comparison issue in Hibernate timestamp vs. java.util.Date but they are all related to java level comparison. None were having any information about how to compare dates at database level.

So, please advise what am I doing wrong here. What is the correct way to eagerly fetch Table2Set without additional query?

Thanks in advance and sorry for such a long email, but I thought I should put as much information as I could so that I can get correct help.



[/code]