I have a bidirectional many-to-many (using join table) relationship between employee and location where employee can be associated with more than one location while a location have only one employee.
In the code snippet below if I read the location count (readLocationCountDB) before loading locations (readLocationsDB) the entire process takes 4 secs while if I read the locations first then the process takes over 15 secs. Also during the reading of counts it attempts to read employee info whose debug statements are included inthe debug output area.
In the former case when locations have not been loaded this debug output is not generated. So appears that if the locations and associated employees have been loaded first it is attempting to reload employee info during count.
Any pointers as to what might I be doing incorrectly?
Hibernate version:
3.0.5
Mapping documents:
Code:
<class name="org.tigr.antware.spacedb.Employee" table="employee">
<id name="id" column="id">
<generator class="identity"/>
</id>
<property name="lastName" column="last_name"/>
<property name="firstName" column="first_name"/>
<property name="nickName" column="nick_name"/>
<property name="email"/>
<property name="department"/>
<property name="supervisorId" column="sup_emp_num"/>
<set name="locations" table="emp_location">
<key column="emp_id"/>
<many-to-many column="loc_id"
unique="true"
class="org.tigr.antware.spacedb.Location"/>
</set>
<set name="employees" inverse="true" order-by="last_name asc, first_name asc">
<key column="sup_emp_num"/>
<one-to-many class="org.tigr.antware.spacedb.Employee"/>
</set>
</class>
Code:
<class name="org.tigr.antware.spacedb.Location" table="location" lazy="false">
<id name="id" column="id">
<generator class="identity"/>
</id>
<property name="bldg"/>
<property name="room"/>
<property name="floor"/>
<property name="spot"/>
<property name="type"/>
<join table="emp_location"
optional="true"
inverse="true">
<key column="loc_id" unique="true"/>
<many-to-one name="employee"
column="emp_id"
class="org.tigr.antware.spacedb.Employee"
not-null="true"/>
</join>
</class>
Code:
Session session = HibernateUtil.currentSession();
String [] types = new String [] {"C", "L", "O", "U"};
String [] states = new String [] {"empty", "occupied"};
[quote]
readLocationsDB(session, null, null);
for (int i = 0; i < types.length; i++) {
readLocationCountDB(session, types[i], null);
for (int j = 0; j < states.length; j++) {
readLocationCountDB(session, types[i], states[j]);
}
}
[/quote]
HibernateUtil.closeSession();
HibernateUtil.sessionFactory.close();
Code:
public static int readLocationCountDB (Session session, String type, String state) {
int locCount = 0;
Criteria locCriteria = session.createCriteria(Location.class);
ProjectionList projectionList = Projections.projectionList();
locCriteria.setProjection(projectionList);
projectionList.add( Projections.rowCount() );
if ((type != null) && (type.length() > 0)) {
locCriteria.add(Expression.eq("type", type));
}
if (state != null && state.length() > 0) {
if (state.equals("empty")) {
locCriteria.add(Expression.isNull("employee"));
}
else if (state.equals("occupied")) {
locCriteria.add(Expression.isNotNull("employee"));
}
}
locCount = ((Integer) locCriteria.list().get(0)).intValue();
return locCount;
}
Code:
public static List readLocationsDB (Session session, String type, String state) {
List locations = null;
Criteria locCriteria = session.createCriteria(Location.class);
locCriteria.addOrder(Order.asc("bldg"))
.addOrder(Order.asc("floor"))
.addOrder(Order.asc("room"))
.addOrder(Order.asc("type"))
.addOrder(Order.asc("spot"));
if ((type != null) && (type.length() > 0)) {
locCriteria.add(Expression.eq("type", type));
}
if (state != null && state.length() > 0) {
if (state.equals("empty")) {
locCriteria.add(Expression.isNull("employee"));
}
else if (state.equals("occupied")) {
locCriteria.add(Expression.isNotNull("employee"));
}
}
locations = locCriteria.list();
return locations;
}
Full stack trace of any exception that occurs:
Name and version of the database you are using:
Sybase ASE 15.0
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
DEBUG SpaceDBUtils readLocationCountDB:757 - Retrieving location count with criteria bldg 'null' floor 'null' state 'null' and type 'C'
DEBUG AbstractFlushingEventListener flushEverythingToExecutions:52 - flushing session
DEBUG AbstractFlushingEventListener prepareEntityFlushes:102 - processing flush-time cascades
DEBUG AbstractFlushingEventListener prepareCollectionFlushes:150 - dirty checking collections
DEBUG AbstractFlushingEventListener flushEntities:167 - Flushing entities and processing referenced collections
FINER Location getEmployee:219 - Attempting to read employee associated with location: 1/1/102/A
DEBUG SessionImpl immediateLoad:639 - initializing proxy: [org.tigr.antware.spacedb.Employee#4044]
DEBUG DefaultLoadEventListener doLoad:290 - attempting to resolve: [org.tigr.antware.spacedb.Employee#4044]
DEBUG DefaultLoadEventListener doLoad:326 - object not resolved in any cache: [org.tigr.antware.spacedb.Employee#4044]
DEBUG BasicEntityPersister load:2467 - Materializing entity: [org.tigr.antware.spacedb.Employee#4044]
DEBUG Loader loadEntity:1340 - loading entity: [org.tigr.antware.spacedb.Employee#4044]
DEBUG AbstractBatcher logOpenPreparedStatement:290 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG SQL log:324 - select employee0_.id as id0_, employee0_.last_name as last2_0_0_, employee0_.first_name as first3_0_0_, employee0_.nick_name as nick4_0_0_, employee0_.email as email0_0_, employee0_.department as department0_0_, employee0_.sup_emp_num as sup7_0_0_ from employee employee0_ where employee0_.id=?
DEBUG AbstractBatcher getPreparedStatement:378 - preparing statement
DEBUG AbstractBatcher logOpenResults:306 - about to open ResultSet (open ResultSets: 0, globally: 0)
DEBUG Loader doQuery:405 - processing result set
DEBUG Loader doQuery:410 - result set row: 0
DEBUG Loader getRow:828 - result row: EntityKey[org.tigr.antware.spacedb.Employee#4044]
DEBUG Loader loadFromResultSet:978 - Initializing object from ResultSet: [org.tigr.antware.spacedb.Employee#4044]
DEBUG BasicEntityPersister hydrate:1651 - Hydrating entity: [org.tigr.antware.spacedb.Employee#4044]
DEBUG Loader doQuery:429 - done processing result set (1 rows)
DEBUG AbstractBatcher logCloseResults:313 - about to close ResultSet (open ResultSets: 1, globally: 1)
DEBUG AbstractBatcher logClosePreparedStatement:298 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG AbstractBatcher closePreparedStatement:416 - closing statement
DEBUG Loader initializeEntitiesAndCollections:528 - total objects hydrated: 1
DEBUG TwoPhaseLoad initializeEntity:96 - resolving associations for [org.tigr.antware.spacedb.Employee#4044]
DEBUG CollectionLoadContext getLoadingCollection:141 - creating collection wrapper:[org.tigr.antware.spacedb.Employee.locations#4044]
DEBUG CollectionLoadContext getLoadingCollection:141 - creating collection wrapper:[org.tigr.antware.spacedb.Employee.employees#4044]
DEBUG TwoPhaseLoad initializeEntity:167 - done materializing entity [org.tigr.antware.spacedb.Employee#4044]
DEBUG PersistenceContext initializeNonLazyCollections:789 - initializing non-lazy collections
DEBUG Loader loadEntity:1368 - done entity load
FINER Location getEmployee:222 - employee is: Khouri, Hoda[quote][/quote]
Login
FAQ
Search