Hibernate version: 3.1.3

Mapping documents:

<?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="com.metlife.calcs.model.Experience" table="T_RFR_EXPERIENCE_DAWN">
<!-- key def -->
<composite-id name="expPK" class="com.metlife.calcs.model.composite.ExperiencePK">
<key-property name="number" column="XPRN_NUM_TXT" type="string"/>
<key-property name="valDate" column="VAL_DT" type="date"/>
</composite-id>

<!-- relationships -->
<many-to-one name="customer" class="com.metlife.calcs.model.Customer" outer-join="true" insert="false" update="false">
<column name="CUST_NUM_TXT"/>
<column name="VAL_DT"/>
</many-to-one>

<!-- regular properties -->
<property name="name" column="XPRN_NM"/>
<property name="zoneCode" column="ADMN_ZN_CD"/>
<property name="teamCode" column="ADMN_TM_CD"/>
<property name="regionName" column="RGN_NM"/>
<property name="financialResCode" column="FIN_RES_CD"/>
<property name="origEffectiveDate" column="ORIG_EFF_DT"/>
<property name="cancelDate" column="CAN_DT"/>
<property name="asaInd" column="ASA_IND"/>
<property name="prtcpInd" column="PRTCP_IND"/>
<property name="stckInd" column="STCK_IND"/>
</class>
</hibernate-mapping>

<?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="com.metlife.calcs.model.Customer" table="INSCMDB.dbo.T_RFR_CUSTOMER_DAWN">
<!-- key def -->
<composite-id name="custPK" class="com.metlife.calcs.model.composite.CustomerPK">
<key-property name="number" column="CUST_NUM_TXT" type="string"/>
<key-property name="valDate" column="VAL_DT" type="date"/>
</composite-id>

<!-- regular properties -->
<property name="name" column="CUST_NM"/>
<property name="cancelDate" column="CUST_CAN_DT"/>
<!-- property name="effectiveDate" column="CUST_ORIG_EFF_DT"/ -->
</class>
</hibernate-mapping>


<?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="com.metlife.calcs.model.FinancialArrangement" table="T_RFR_FIN_ARNG">
<!-- key def -->
<id name="code" column="FIN_ARNG_CD" type="string"/>

<!-- regular properties -->
<property name="asaInd" column="ASA_IND"/>
<property name="partcpInd" column="PARTCP_IND"/>
<property name="stckInd" column="STCK_IND"/>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

Session hibernateSession = HibernateSessionFactory.currentSession();
Query q = hibernateSession.createQuery
(
"SELECT "+
"e, f.code "+
"FROM "+
"Experience e, FinancialArrangement f "+
"WHERE "+
"e.asaInd=f.asaInd AND e.stckInd=f.stckInd AND e.prtcpInd=f.partcpInd"
);
Iterator i = q.list().iterator();
...
HibernateSessionFactory.closeSession();

Name and version of the database you are using:
MS SQLServer 2000

The generated SQL (show_sql=true):
SELECT TOP 10
experience0_.XPRN_NUM_TXT as col_0_0_, experience0_.VAL_DT as col_0_1_, financiala1_.FIN_ARNG_CD as col_1_0_, experience0_.XPRN_NUM_TXT as XPRN1_0_, experience0_.VAL_DT as VAL2_0_, experience0_.CUST_NUM_TXT as CUST3_0_, experience0_.XPRN_NM as XPRN4_0_, experience0_.ADMN_ZN_CD as ADMN5_0_, experience0_.ADMN_TM_CD as ADMN6_0_, experience0_.RGN_NM as RGN7_0_, experience0_.FIN_RES_CD as FIN8_0_, experience0_.ORIG_EFF_DT as ORIG9_0_, experience0_.CAN_DT as CAN10_0_, experience0_.ASA_IND as ASA11_0_, experience0_.PRTCP_IND as PRTCP12_0_, experience0_.STCK_IND as STCK13_0_, experience0_1_.ECG_NUM as ECG3_1_
FROM
T_RFR_EXPERIENCE_DAWN experience0_ , T_RFR_FIN_ARNG financiala1_
WHERE
experience0_.ASA_IND=financiala1_.ASA_IND AND
experience0_.STCK_IND=financiala1_.STCK_IND AND
experience0_.PRTCP_IND=financiala1_.PARTCP_IND

SELECT
customer0_.CUST_NUM_TXT as CUST1_2_0_, customer0_.VAL_DT as VAL2_2_0_, customer0_.CUST_NM as CUST3_2_0_, customer0_.CUST_CAN_DT as CUST4_2_0_
FROM
T_RFR_CUSTOMER_DAWN customer0_
WHERE
customer0_.CUST_NUM_TXT=? and customer0_.VAL_DT=?



-------------------------------------
I have a SQL query:

SELECT TOP 10
rc.cust_nm, rc.cust_num_txt, xprn_nm, admn_tm_cd, admn_zn_cd, rgn_nm, fin_arng_cd, fin_res_cd,
orig_eff_dt,can_dt,cust_orig_eff_dt,cust_can_dt
FROM
(
(T_RFR_EXPERIENCE_DAWN re INNER JOIN T_RFR_CUSTOMER_DAWN rc ON re.cust_num_txt = rc.cust_num_txt)
INNER JOIN T_RFR_FIN_ARNG fa ON
re.prtcp_ind = fa.partcp_ind AND
re.stck_ind = fa.stck_ind AND
re.asa_ind = fa.asa_ind
)

This gets me the Experience and Customer information, which is a many-to-one relationship. I also get the financial arrangement code which is the inner join happening at the end there.

As per the code snippets above, when I am trying to get the financial arrangement code via the hibernate code, two queries are being performed.

If I take the financial arrangement stuff out of the equation, then I can set up a criteria on Experience.class, and I can see that only a single query with an inner join is being performed to retrieve the Experience and related Customer information.

My question is what do I need to do in order to get hibernate to perform a single query in the background the way I can do manually? Is there some relationship that needs to be mapped or a different kind of query or criteria operation?

Any help would be much appreciated.

Thanks!