I have two mappings that relate to two tables... as follows:

Report Request..

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

<hibernate-mapping>

<class name="cis.persistent.ReportRequest" table="RPT_RQST" lazy="false">
<id name="objectID" column="OBJ_ID" type="java.lang.Long">
<generator class="native"/>
</id>
<property name="userObjectID" column="USR_OBJ_ID"
type="java.lang.Long"/>
<property name="startDate" column="STRT_TM" type="timestamp"/>
<property name="endDate" column="END_TM" type="timestamp"/>
<property name="requestDate" column="RQST_TM" type="timestamp"/>
<property name="deleteDate" column="DEL_TM" type="timestamp"/>
<property name="description" column="DSCR" type="string"/>
<property name="directoryName" column="DIR_NM" type="string"/>
<property name="fileName" column="FILE_NM" type="string"/>
<property name="downloaded" column="DWNLD_FLG" type="boolean"/>
<property name="reportStatusLK" column="RPT_STUS_LKP_ID"
type="java.lang.Integer"/>
<property name="reportTypeLK" column="RPT_TYP_LKP_ID"
type="java.lang.Integer"/>
<property name="redactionInfoByLK" column="RDCT_INFO_BY_LKP_ID"
type="java.lang.Integer"/>
<property name="useImageReceived" column="USE_IMG_RCVD_FLG" type="boolean"/>
<property name="imageReceived" column="IMG_RCVD_FLG" type="boolean"/>
<property name="requestNumber" column="RQST_NUM" type="string"/>
<bag name="countyList" inverse="true" lazy="false"
cascade="all-delete-orphan">
<key column="RPT_RQST_OBJ_ID"/>
<one-to-many class="cis.persistent.ReportRequestCounty"/>
</bag>
<bag name="userRoleList" inverse="true" lazy="false"
cascade="all-delete-orphan">
<key column="RPT_RQST_OBJ_ID"/>
<one-to-many class="cis.persistent.ReportRequestUserRole"/>
</bag>
<bag name="userStatusList" inverse="true" lazy="false"
cascade="all-delete-orphan">
<key column="RPT_RQST_OBJ_ID"/>
<one-to-many class="cis.persistent.ReportRequestUserStatus"/>
</bag>
<bag name="redactionStatusList" inverse="true" lazy="false"
cascade="all-delete-orphan">
<key column="RPT_RQST_OBJ_ID"/>
<one-to-many class="cis.persistent.ReportRequestRedactionStatus"/>
</bag>
</class>
<query name="cis.dataaccess.ReportRequestDAO.findByUserObjectID">
<![CDATA[
from cis.persistent.ReportRequest as request
where request.userObjectID = ?
order by request.requestDate desc]]>
</query>
</hibernate-mapping>

and ReportRequestCounty...

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

<hibernate-mapping>

<class name="cis.persistent.ReportRequestCounty" table="RPT_RQST_CNTY" lazy="false">
<id name="objectID" column="OBJ_ID" type="java.lang.Long">
<generator class="native"/>
</id>
<many-to-one name="reportRequest" column="RPT_RQST_OBJ_ID"
not-null="true" class="cis.persistent.ReportRequest"/>
<property name="countyLK" column="CNTY_LKP_ID"
type="java.lang.Integer"/>
</class>

</hibernate-mapping>

The idea is that the reportrequestcounty table keeps track of which county any given report was created for.... In the database the relationship of these two tables is on RPT_RQST_OBJ_ID relates to the identity OBJ_ID in report request. So the goal is to be able to use the criteria API to get a collection of all report requests for a given county...

One point of confusion that I have is that the actual relationship in the database is 1 to 1. There is one entry in report_request_county for every entry in report_quest. I am under the impression that a join is required. The existing code utilizes the paging of the criteria api extensively, hence want to avoid
moving to an HQL query. Here is an outline of the code that we have....

public static List<ReportRequestDO> findDailyReportRequestDOs(
String aReportTypeName, Collection<Integer> aCountyIDList,
boolean aTodaysReportsOnly, int aStartItem, int aNumberOfItems,
String aSortByField, boolean anAscending)
throws PersistenceException {
List<ReportRequestDO> doList = new ArrayList<ReportRequestDO>();
try {
startSession();
CriteriaQuery query = GlobalQueryFactory.getInstance()
.newCriteriaQuery(ReportRequest.class);


logger.info("Tom @ - CreateCriteria");
query.getCriteria().createCriteria("countyList");


buildCommonCriteria(aReportTypeName, aCountyIDList,
aTodaysReportsOnly, query);

if (aStartItem >= 0) {
query.setFirstResult(aStartItem);
}
if (aNumberOfItems > 0) {
query.setMaxResults(aNumberOfItems);
}

String sortBy = getSortColumnName(aSortByField);
if (anAscending) {
query.addOrder(Order.asc(sortBy));
} else {
query.addOrder(Order.desc(sortBy));
}
Collection<ReportRequest> results = query.execute();

logger.info("Tom @ query" + aCountyIDList.toString());

if (results != null && !results.isEmpty()) {
logger.info("daily " + aReportTypeName + " report size: "
+ results.size());
for (Iterator<ReportRequest> iter = results.iterator(); iter
.hasNext();) {
ReportRequest element = (ReportRequest) iter.next();
ReportRequestDO overviewDO = new ReportRequestDO(element);
doList.add(overviewDO);
}
}
}
catch (SessionException se) {
throw new PersistenceException("Unable to open Hibernate Session.",
se);
}
catch (QueryException qe) {
throw new PersistenceException(
"Unable to findDailyReportRequestDOs() for "
+ aReportTypeName + ".", qe);
}
finally {
try {
endSession();
logger.debug("findDailyReportRequestDOs() for "
+ aReportTypeName + ": ThreadSessionHolder.get(): "
+ ThreadSessionHolder.get());
}
catch (Exception e) {
throw new PersistenceException(
"Unable to end Hibernate Session.", e);
}
}
if (doList.isEmpty()) {
doList = null;
}
return doList;
}

/**
* Build the common criteria items.
*
* @param aReportTypeName String
* @param aCountyIDList Collection<Integer>
* @param aTodaysReportsOnly boolean
* @param aQuery CriteriaQuery
*/
private static void buildCommonCriteria(String aReportTypeName,
Collection<Integer> aCountyIDList, boolean aTodaysReportsOnly,
CriteriaQuery aQuery) {

Integer reportTypeLK = CLRISCache.getLookupObjectID(
REPORT_TYPE_LOOKUP, aReportTypeName);
aQuery.add(Criterion.eq(REPORT_TYPE_LK, reportTypeLK));
aQuery.add(Criterion.isNull("userObjectID"));


SearchService.buildCollectionCriterion(aQuery, "countyList", aCountyIDList);


if (aTodaysReportsOnly) {
// last 24 hours report.
Date startDate = Utilities.convertToStartOfDay(new Date());
Date endDate = Utilities.convertToEndOfDay(new Date());
aQuery.add(Criterion.between("requestDate", startDate, endDate));
}
}
}

-=======================================================================

I was expecting a join to occur because of the Alias criteria i setup..... here is the error that I get....


edStatements: 1, globally: 1)
2011-01-25 10:54:13,613 DEBUG [http-0.0.0.0-8080-Processor25] [org.hibernate.jdbc.AbstractBatcher] closing statement
2011-01-25 10:54:13,613 DEBUG [http-0.0.0.0-8080-Processor25] [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
2011-01-25 10:54:13,613 DEBUG [http-0.0.0.0-8080-Processor25] [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2011-01-25 10:54:13,614 DEBUG [http-0.0.0.0-8080-Processor25] [org.hibernate.util.JDBCExceptionReporter] could not execute query [select this_.OBJ_ID as OBJ1_74_0_, this_.USR_OBJ_ID as USR2_74_0_, this_.STRT_TM as STRT3_74_0_, this_.END_TM as END4_74_0_, this_.RQST_TM as RQST5_74_0_, this_.DEL_TM as DEL6_74_0_, this_.DSCR as DSCR74_0_, this_.DIR_NM as DIR8_74_0_, this_.FILE_NM as FILE9_74_0_, this_.DWNLD_FLG as DWNLD10_74_0_, this_.RPT_STUS_LKP_ID as RPT11_74_0_, this_.RPT_TYP_LKP_ID as RPT12_74_0_, this_.RDCT_INFO_BY_LKP_ID as RDCT13_74_0_, this_.USE_IMG_RCVD_FLG as USE14_74_0_, this_.IMG_RCVD_FLG as IMG15_74_0_, this_.RQST_NUM as RQST16_74_0_ from RPT_RQST this_ where this_.RPT_TYP_LKP_ID=? and this_.USR_OBJ_ID is null and this_.OBJ_ID in (?)]
com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07001, SQLERRMC=null, DRIVER=3.57.86
at com.ibm.db2.jcc.am.bd.a(bd.java:679)
at com.ibm.db2.jcc.am.bd.a(bd.java:60)
at com.ibm.db2.jcc.am.bd.a(bd.java:127)
at com.ibm.db2.jcc.am.km.c(km.java:2506)
at com.ibm.db2.jcc.am.km.a(km.java:1990)
at com.ibm.db2.jcc.t4.db.n(db.java:801)
at com.ibm.db2.jcc.t4.db.i(db.java:258)
at com.ibm.db2.jcc.t4.db.c(db.java:53)
at com.ibm.db2.jcc.t4.t.c(t.java:44)
at com.ibm.db2.jcc.t4.sb.i(sb.java:147)
at com.ibm.db2.jcc.am.km.gb(km.java:1985)
at com.ibm.db2.jcc.am.lm.b(lm.java:3576)
at com.ibm.db2.jcc.am.lm.Vb(lm.java:654)
at com.ibm.db2.jcc.am.lm.executeQuery(lm.java:628)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:314)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
at org.hibernate.loader.Loader.doQuery(Loader.java:718)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
at org.hiber

Any advice would be greatly appreciated !!!