I am re-posting this topic hoping to get a reply. Can anyone help answer my questions?

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:



Why is Hibernate re-querying the database and how can I prevent this?

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=?

If I understand correctly what you are trying to do, you want to load a Business object with all it's employees initialized, and you want Hibernate to load the whole graph in a single query ?



Does it mean that you call setBusinessEmployess() ? I don't understand what you mean by 'ordering' objects. You shouln't try to initialize the graph yourself, upon save this could trigger Hibernate to think that associations have been updated and force useless updates. I think what you're lookin for is 'left join fetch' : look at this query from the docs :



A Cat is retrived from DB with its mate and kittens, in a single query. And you can fetch as deep as you like :



I think you get the idea, fetch is your friend. Use it to initialize the associations ends you will use.

BTW don't (ever !) use



Use query.setXXX() instead. This is far better for performance and security.

I think my basic concern was the mapping is many-to-one/one-to-many with the associative table EmpAuth, so the data comes back in Business --> EmpAuth --> Person order, (e.g. the getBusinessEmployees() returns EmpAuth records with the attached Person) but I want to process the data by Business --> Person --> EmpAuth, so when I call getBusinessEmployees(), I want a Person object returned with its associated EmpAuth. Therefore, I thought I could re-order the data since all objects have been initialized with the first query, but apparently not?

How would left join fetch change this result? Muchas gracias for your input! E

Ok, I think I get what you mean by reorder data, you mean change the associations model !

If you need getBusinessEmployees() sometimes to return a EmpAuth and sometimes to return a Person, then your model is broken.

If you want to iterate business employees, why don't you add a Person attribute to your EmpAuth object, then you could do :

So I get from what you are saying I should either change my association model, or perhaps add another set with a different ordering to the business object. Or as you said, add a Person to the EmpAuth. I could do that... thanks for all your help!! Most appreciated!! E