Sorry to have to post, but this is my first Hibernate project, and I am not understanding something about Hibernate. I hope you can help.

I started out with a LazyInitializationException, so I have implemented the openSessionWithView pattern using ThreadLocal so that the session is not closed until the view is rendered. I originally closed the session in the dao layer.

I am doing a 4-table join, which I know will return a limited number of rows (usually less than 10). I am using a Hibernate HQL query to get the Business, Person, EmpRole and EmpAuth records for a single business. It does appear from the show-sql that Hibernate is doing a 4-table join, and then from the log – appears to create all the appropriate objects (Business, EmpRole, Person, EmpAuth).
The mapping files are mapped thusly:

• Business --> EmpRole --> Person
• EmpRole --> EmpAuth

EmpRole is the association between Business and Person. A Person will have only one Association with a single business, but could have associations with 0-M Businesses. After retrieving the first result, I get back an Object[] with a Business, EmpRole, Person, EmpAuth in each row. I order these classes to Person --> EmpRole --> EmpAuth, place the re-ordered collection in the businessEmployees set of a selected Business object, and return the business from the dao layer. During the ordering process, Hibernate re-queries the database for both empRole and empAuth for each Person I have retrieved.

When I reach the view, the order is Business --> EmpRole (e.g. the mapped relationships). I assume this is happening because of 1) the mapping 2) the session is still open? I need to display the data in Business --> Person order which would be the logical association from the user perspective. I did read the Hibernate in Action book, and in Section 6.3 it recommends that mapping a one-to-many/many-to-one relationship is more extensible than mapping a many-to-many relationship; all my associations must be bi-directional. All tables have additional properties (e.g. no tables are strictly associative with PK/FK only).

Questions:

1. Why is Hibernate re-querying the database and how can I prevent this?
2. How can I keep the object order I set up in the dao layer? Perhaps ordering needs to be done in a different layer, but I was unsure whether any manual re-ordering would be maintained while the session is open.

Most appreciative for any help.

Regards. e

Hibernate version:
3.? I think 0 with JDK 1.4.2

Mapping documents:

<class name="Business" table="business" >
<id name="busOid" column="BUS_OID"
unsaved-value="0">
<generator class="identity">
</generator>
</id>
...(properties removed for clarity)…
<set name="businessEmployees" inverse="true" cascade="save-update">
<key column="BUS_OID"/>
<one-to-many class="EmpRole" />
</set>
</class>

<class name="EmpRole" table="Emp_Role" >
<composite-id name="ID" class="common.model.EmpRoleID"
unsaved-value="undefined">
<key-property name="personNum" type="integer" column="PERSON_NUM" />
<key-property name="busOid" type="integer" column="BUS_OID" />
</composite-id>
...(properties removed for clarity)...
<many-to-one name="business" class="Business" column="BUS_OID" not-null="true" insert="false" update="false"/>
<many-to-one name="person" class="Person" column="PERSON_NUM" not-null="true" insert="false" update="false"/>
<set name="empAuth" inverse="true" cascade="save-update">
<key>
<column name="PERS_NUM" not-null="true"/>
<column name="BUS_OID" not-null="true"/>
</key>
<one-to-many class="EmpAuth" />
</set>
</class>

<class name="EmpAuth" table="EMP_AUTH" >
<composite-id name="ID" class="common.model.EmpAuthID"
unsaved-value="undefined">
<key-property name="personNum" type="integer" column="PERSON_NUM" />
<key-property name="appCd" type="string" column="APP_CD" />
<key-property name="busOid" type="integer" column="BUS_OID" />
</composite-id>
...(properties removed for clarity)...
<many-to-one name="ARole" class="EmpRole" not-null="true" insert="false" update="false">
<column name="BUS_OID" not-null="true"/>
<column name="PERS_NUM" not-null="true"/>
</many-to-one>
</class>

<class name="Person" table="PERSON" >
<id name="personNum" column="PERSON_NUM" unsaved-value="0">
</id>
...(properties removed for clarity)...
<set name="ARoles" inverse="true" cascade="save-update">
<key column="PERSON_NUM"/>
<one-to-many class="EmpRole" />
</set>
</class>

Code between sessionFactory.openSession() and session.close():
logger.info(" starting getBusinessByIDNumWithEmployees with " + uniqueIdNumber);
Business business = null;
Session session = null;
Iterator iter = null;
try {
logger.debug("SessionFactory located - begin lookup for " + uniqueIdNumber);
String query = "from Business bus inner join bus.businessEmployees mr inner join mr.empAuth as empauth inner join mr.person as person where bus.busIdNum = '" + uniqueIdNumber + "'";
session = this.getSession();
Query q = session.createQuery(query);
iter = q.list().iterator();
business = this.mapBusiness(iter);
}
catch (Exception e) {
e.printStackTrace();
throw new HibernateDAOException(e.getClass().getName() + " - " + e.getMessage())
}
finally{
return business;
}

Name and version of the database you are using:
DB2 8.1 or higher

The generated SQL (show_sql=true):

select busine0_.BUS_OID as BUS1_0_, businessem1_.PERSON_NUM as PERSON1_1_, businessem1_.BUS_OID as BUS2_1_, empauth2_.PERSON_NUM as PERSON1_2_, empauth2_.APP_CD as APP2_2_, empauth2_.BUS_OID as BUS3_2_, person3_.PERSON_NUM as PERSON1_3_, busine0_.UPDT_TS as UPDT2_4_0_, busine0_.BUS_ID_NUM as BUS3_4_0_, busine0_.BUS_ID_TYPE_CD as BUS4_4_0_, busine0_.BUS_NAME as BUS5_4_0_, busine0_.ADDR_1 as ADDR6_4_0_, busine0_.ADDR_2 as ADDR7_4_0_, busine0_.CITY as CITY4_0_, busine0_.ST as ST4_0_, busine0_.ZIP_CD_9 as ZIP10_4_0_, busine0_.PHONE_NUM as PHONE11_4_0_, busine0_.FAX_NUM as FAX12_4_0_, busine0_.CREATE_DT as CREATE13_4_0_, busine0_.AUTH_STAT_CD as AUTH14_4_0_, busine0_.AUTH_RSN_CD as AUTH15_4_0_, busine0_.RANDOM_ID_CD as RANDOM16_4_0_, busine0_.BUS_ROLE_CD as BUS17_4_0_, busine0_.UNIQUE_ID_NUM as 18_4_0_, busine0_.PROV_TYPE_CD as PROV19_4_0_, busine0_.UPDT_ID as UPDT20_4_0_, busine0_.BUS_DESC as BUS21_4_0_, busine0_.SEQ_NO as SEQ22_4_0_, businessem1_.UPDT_TS as UPDT3_5_1_, businessem1_.ROLE_CD as ROLE4_5_1_, businessem1_.PHONE_NUM as PHONE5_5_1_, businessem1_.PHONE_EXT_NUM as PHONE6_5_1_, businessem1_.EMAIL_ADDR as EMAIL7_5_1_, businessem1_.CREATE_DT as CREATE8_5_1_, businessem1_.AUTH_STAT_CD as AUTH9_5_1_, businessem1_.AUTH_RSN_CD as AUTH10_5_1_, businessem1_.UPDT_ID as UPDT11_5_1_, businessem1_.BUS_OID as BUS2_5_1_, businessem1_.PERSON_NUM as PERSON1_5_1_, empauth2_.UPDT_TS as UPDT4_6_2_, empauth2_.AUTH_LVL_CD as AUTH5_6_2_, empauth2_.ROLE_CD as ROLE6_6_2_, empauth2_.CREATE_DT as CREATE7_6_2_, empauth2_.UPDT_ID as UPDT8_6_2_, empauth2_.BUS_OID as BUS3_6_2_, empauth2_.PERSON_NUM as PERSON1_6_2_, person3_.UPDT_TS as UPDT2_7_3_, person3_.FULL_NAME as FULL3_7_3_, person3_.SALUTATION as SALUTATION7_3_, person3_.CREATE_DT as CREATE5_7_3_, person3_.AUTH_STAT_CD as AUTH6_7_3_, person3_.AUTH_RSN_CD as AUTH7_7_3_, person3_.SEC_AGMT_IND as SEC8_7_3_, person3_.ERR_CNT as ERR9_7_3_, person3_.LAST_ACCD_DT as LAST10_7_3_, person3_.UPDT_ID as UPDT11_7_3_, person3_.LAST_NAME as LAST12_7_3_ from aiddb2.business busine0_ inner join aiddb2.EMP_ROLE businessem1_ on busine0_.BUS_OID=businessem1_.BUS_OID inner join aiddb2.EMP_AUTH empauth2_ on businessem1_.PERSON_NUM=empauth2_.PERSON_NUM and businessem1_.BUS_OID=empauth2_.BUS_OID inner join aiddb2.PERSON person3_ on businessem1_.PERSON_NUM=person3_.PERSON_NUM where busine0_.UNIQUE_ID_NUM=<busIdNum>

select empauth0_.PERSON_NUM as PERSON1_1_, empauth0_.BUS_OID as BUS3_1_, empauth0_.APP_CD as APP2_1_, empauth0_.PERSON_NUM as PERSON1_0_, empauth0_.APP_CD as APP2_0_, empauth0_.BUS_OID as BUS3_0_, empauth0_.UPDT_TS as UPDT4_6_0_, empauth0_.AUTH_LVL_CD as AUTH5_6_0_, empauth0_.ROLE_CD as ROLE6_6_0_, empauth0_.CREATE_DT as CREATE7_6_0_, empauth0_.UPDT_ID as UPDT8_6_0_, empauth0_.BUS_OID as BUS3_6_0_, empauth0_.PERSON_NUM as PERSON1_6_0_ from aiddb2.EMP_AUTH empauth0_ where empauth0_.PERSON_NUM=? and empauth0_.BUS_OID=?

select emproles0_.PERSON_NUM as PERSON1_1_, emproles0_.BUS_OID as BUS2_1_, emproles0_.PERSON_NUM as PERSON1_0_, emproles0_.BUS_OID as BUS2_0_, emproles0_.UPDT_TS as UPDT3_5_0_, emproles0_.ROLE_CD as ROLE4_5_0_, emproles0_.PHONE_NUM as PHONE5_5_0_, emproles0_.PHONE_EXT_NUM as PHONE6_5_0_, emproles0_.EMAIL_ADDR as EMAIL7_5_0_, emproles0_.CREATE_DT as CREATE8_5_0_, emproles0_.AUTH_STAT_CD as AUTH9_5_0_, emproles0_.AUTH_RSN_CD as AUTH10_5_0_, emproles0_.UPDT_ID as UPDT11_5_0_, emproles0_.BUS_OID as BUS2_5_0_, emproles0_.PERSON_NUM as PERSON1_5_0_ from aiddb2.EMP_ROLE emproles0_ where emproles0_.PERSON_NUM=?