I have 4 tables and I would like to retrieve data from all 4 using eager fetching. I am using the Criteria API, so that eager fetching can be performed dynamically. The entity classes and mapping files are found below. Two tables, Applications and Accounts, are many to many relationships with a link table called AccountApps. They have an inverse relationship, so there is a set of Applications in the Accounts class and vice versa. The third table is Versions which has a many to one relationship with Applications. This is also inverse, so Applications has a one to many relationship with Versions. The fourth table is Machines. This table has a many to one relationship with Accounts and is also an inverse relationship.

So, basically, what I have is this: Versions is a child of Applications and Machines is a child of Accounts. Accounts is being treated as the parent table to Applications.

Here is the code I'm using to retrieve the date:



Here is the problem that I have. The SQL generated seems to retrieve the correct data, but a second query is being generated when the Application collection is accessed. Both queries are documented below. The frist query retrieves the data I need, but all the data does not seem to be retained, thus making the second query necessary. I read an article that stated that using the createCriteria() method from another criteria will not supply you with the child data, so what I am seeing seems to be consistent. So, my question is this: What, if anything, can be done to "tell" Hibernate to hold onto all the data retrieved with the first query and eliminate the second query altogether?

Hibernate version: 3.2.5

Mapping documents:
Application mapping file:

<hibernate-mapping>
<class catalog="deployment" name="com.ecrsoft.daoBroker.pojo.Application" table="applications">
<id name="id" type="java.lang.Integer">
<column name="id"/>
<generator class="identity"/>
</id>
<property name="appName" type="string">
<column length="50" name="appName" not-null="true"/>
</property>
<property name="releaseLevel" type="integer">
<column name="releaseLevel" not-null="false"/>
</property>
<set inverse="true" name="accounts" table="AccountApps">
<key column="appID"/>
<many-to-many class="com.ecrsoft.daoBroker.pojo.Account" column="accountID"/>
</set>
<set inverse="true" name="versions" table="Versions">
<key column="appID"/>
<one-to-many class="com.ecrsoft.daoBroker.pojo.Version"/>
<filter condition="trail = :trail" name="trailFilter"/>
<filter condition="trail = :trail and releaseNum = :releaseNum" name="releaseFilter"/>
<filter condition="active = :active" name="activeFilter"/>
<filter condition="trail = :trail and active = :active" name="activeTrailFilter"/>
<filter condition="trail = :trail and releaseNum = :releaseNum and active = :active" name="activeReleaseFilter"/>
</set>
<filter condition="appName = :appName" name="GetAppByNameFilter"/>
<filter condition="appName = :appName and releaseLevel = :releaseLevel" name="GetAppByNameReleaseLevelFilter"/>
<filter condition="releaseLevel = :releaseLevel" name="GetAppByReleaseLevelFilter"/>
</class>
<filter-def name="GetAppByNameFilter">
<filter-param name="appName" type="string"/>
</filter-def>
<filter-def name="GetAppByNameReleaseLevelFilter">
<filter-param name="appName" type="string"/>
<filter-param name="releaseLevel" type="integer"/>
</filter-def>
<filter-def name="GetAppByReleaseLevelFilter">
<filter-param name="releaseLevel" type="integer"/>
</filter-def>
<filter-def name="trailFilter">
<filter-param name="trail" type="string"/>
</filter-def>
<filter-def name="releaseFilter">
<filter-param name="trail" type="string"/>
<filter-param name="releaseNum" type="integer"/>
</filter-def>
<filter-def name="activeFilter">
<filter-param name="active" type="byte"/>
</filter-def>
<filter-def name="activeTrailFilter">
<filter-param name="trail" type="string"/>
<filter-param name="active" type="byte"/>
</filter-def>
<filter-def name="activeReleaseFilter">
<filter-param name="trail" type="string"/>
<filter-param name="releaseNum" type="integer"/>
<filter-param name="active" type="byte"/>
</filter-def>
</hibernate-mapping>

Version Mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 13, 2008 10:13:53 AM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
<class catalog="deployment" name="com.ecrsoft.daoBroker.pojo.Version" table="versions">
<id name="id" type="java.lang.Integer">
<column name="id"/>
<generator class="identity"/>
</id>
<property name="major" type="int">
<column name="major" not-null="false"/>
</property>
<property name="minor" type="int">
<column name="minor" not-null="false"/>
</property>
<property name="revision" type="int">
<column name="revision" not-null="false"/>
</property>
<property name="appBuild" type="int">
<column name="appBuild" not-null="false"/>
</property>
<property name="pkgBuild" type="int">
<column name="pkgBuild" not-null="false"/>
</property>
<property name="appPatch" type="string">
<column length="3" name="appPatch" not-null="false"/>
</property>
<property name="appPatchBuild" type="int">
<column name="appPatchBuild" not-null="false"/>
</property>
<property name="appPatchPkgBuild" type="int">
<column name="appPatchPkgBuild" not-null="false"/>
</property>
<property name="pkgPatch" type="string">
<column length="3" name="pkgPatch" not-null="false"/>
</property>
<property name="pkgPatchBuild" type="int">
<column name="pkgPatchBuild" not-null="false"/>
</property>
<property name="trail" type="string">
<column length="45" name="trail" not-null="true"/>
</property>
<property name="releaseNum" type="integer">
<column name="releaseNum" not-null="true"/>
</property>
<property name="active" type="byte">
<column name="active" not-null="true"/>
</property>
<many-to-one class="com.ecrsoft.daoBroker.pojo.Application" column="appID" name="app"/>
</class>
</hibernate-mapping>

Account mapping:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 13, 2008 10:13:53 AM by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
<class catalog="deployment" name="com.ecrsoft.daoBroker.pojo.Account" table="accounts">
<id name="id" type="java.lang.Integer">
<column name="id"/>
<generator class="identity"/>
</id>
<property name="name" type="string">
<column length="45" name="name" not-null="true"/>
</property>
<set inverse="true" name="apps" table="AccountApps">
<key column="accountID"/>
<many-to-many class="com.ecrsoft.daoBroker.pojo.Application" column="appID">
<filter condition="appName = :appName" name="AppNameFilter"/>
<filter condition="releaseLevel = :releaseLevel" name="ReleaseLevelFilter"/>
</many-to-many>
</set>
<set inverse="true" name="machines" table="Machines">
<key column="accountID"/>
<one-to-many class="com.ecrsoft.daoBroker.pojo.Machine"/>
</set>
<filter condition="name = :name" name="GetByNameFilter"/>
</class>
<filter-def name="GetByNameFilter">
<filter-param name="name" type="string"/>
</filter-def>
<filter-def name="AppNameFilter">
<filter-param name="appName" type="string"/>
</filter-def>
<filter-def name="ReleaseLevelFilter">
<filter-param name="releaseLevel" type="integer"/>
</filter-def>
</hibernate-mapping>

<hibernate-mapping>
<class catalog="deployment" name="com.ecrsoft.daoBroker.pojo.Machine" table="machines">
<id name="id" type="java.lang.Integer">
<column name="id"/>
<generator class="identity"/>
</id>
<property name="machineName" type="string">
<column length="20" name="machineName" not-null="true"/>
</property>
<many-to-one class="com.ecrsoft.daoBroker.pojo.Account" column="accountID" name="account"/>
</class>
</hibernate-mapping>

Name and version of the database you are using: Sybase ASA 8

The generated SQL (show_sql=true):
Hibernate: select this_.id as id0_3_, this_.name as name0_3_, apps3_.accountID as accountID5_, applicatio1_.id as appID5_, applicatio1_.id as id6_0_, applicatio1_.appName as appName6_0_, applicatio1_.releaseLevel as releaseL3_6_0_, versions5_.appID as appID5_, versions5_.id as id5_, versions5_.id as id2_1_, versions5_.major as major2_1_, versions5_.minor as minor2_1_, versions5_.revision as revision2_1_, versions5_.appBuild as appBuild2_1_, versions5_.pkgBuild as pkgBuild2_1_, versions5_.appPatch as appPatch2_1_, versions5_.appPatchBuild as appPatch8_2_1_, versions5_.appPatchPkgBuild as appPatch9_2_1_, versions5_.pkgPatch as pkgPatch2_1_, versions5_.pkgPatchBuild as pkgPatc11_2_1_, versions5_.trail as trail2_1_, versions5_.releaseNum as releaseNum2_1_, versions5_.active as active2_1_, versions5_.appID as appID2_1_, machines6_.accountID as accountID6_, machines6_.id as id6_, machines6_.id as id4_2_, machines6_.machineName as machineN2_4_2_, machines6_.accountID as accountID4_2_ from deployment.accounts this_ inner join AccountApps apps3_ on this_.id=apps3_.accountID inner join deployment.applications applicatio1_ on apps3_.appID=applicatio1_.id and applicatio1_.releaseLevel = ? left outer join deployment.versions versions5_ on applicatio1_.id=versions5_.appID and versions5_.active = ? left outer join deployment.machines machines6_ on this_.id=machines6_.accountID where this_.name=?

Hibernate: select apps0_.accountID as accountID1_, apps0_.appID as appID1_, applicatio1_.id as id6_0_, applicatio1_.appName as appName6_0_, applicatio1_.releaseLevel as releaseL3_6_0_ from AccountApps apps0_ left outer join deployment.applications applicatio1_ on apps0_.appID=applicatio1_.id where applicatio1_.releaseLevel = ? and apps0_.accountID=?

Hi,
I am having the same issue. Can you please let me know if you have found an answer to do this.

Criteria criteria = session.createCriteria
(PanelApplication.class, "panelApplication")
.setFetchMode("reviewers", FetchMode.JOIN)
.createCriteria("reviewers")
.setFetchMode("reviewerScore", FetchMode.JOIN)

But when I try to access
panelApplication.getReviewers() it fires the select statements again.