Native SQL query trying to resolve associations.

harshalx · **Posted:** Thu Mar 18, 2010 12:01 pm

Hello

I am trying to execute the following Native named SQL query.

Code:

    <resultset name="otwcontactResult">
      <return alias="otwh" entity-name="DealOTWContactHistory"/>
      <return-join alias="individual" property="otwh.individual"/> 
      <return-join alias="requestor" property="otwh.requestor"/>
      <return-join alias="approver" property="otwh.approver"/>
    </resultset>
<sql-query name="getHistoricalOTWContacts" resultset-ref="otwcontactResult">
    SELECT {otwh.*},
           {individual.*},
           {requestor.*},
           {approver.*}  
           FROM 
               DEAL_OTW_CONTACT_HISTORY otwh
               LEFT OUTER JOIN PERSON_HISTORY individual
                    ON otwh.PERSON_KEY = individual.PERSON_KEY
                    AND individual.START_TIMESTAMP &lt;= :asOfDate
                    AND individual.END_TIMESTAMP &gt; :asOfDate
               LEFT OUTER JOIN PERSON_HISTORY requestor
                    ON otwh.REQUESTOR = requestor.PERSON_KEY
                    AND requestor.START_TIMESTAMP &lt;= :asOfDate
                    AND requestor.END_TIMESTAMP &gt; :asOfDate
               LEFT OUTER JOIN PERSON_HISTORY approver
                    ON otwh.APPROVER = approver.PERSON_KEY
                    AND approver.START_TIMESTAMP &lt;= :asOfDate
                    AND approver.END_TIMESTAMP &gt; :asOfDate                                    
           WHERE
                otwh.DEAL_KEY = :dealKey
                AND otwh.START_TIMESTAMP &lt;= :asOfDate
                AND otwh.END_TIMESTAMP &gt; :asOfDate         
  </sql-query>  

I am expecting that an unmanaged DealOTWContactHistory object will be returned. Firstly, I'd like to ask, is this expection correct?

The following is the code which executes this query:

Code:

        Session session = getCurrentSession();
        session.enableFilter("asOfDateFilter").setParameter("asOfDate", asOfDate);
       Query qry = session.getNamedQuery("getHistoricalOTWContacts");
        qry.setInteger("dealKey", dealKey);
        qry.setParameter("asOfDate", asOfDate);        
        List<DealOTWContact> dcs = qry.list();

When the query executes, Hibernate tries to resolve the associations for the DealOTWContactHistory object and in turn fires a query to resolve PersonHistory#876 association. Why is an association being resolved when this is supposed to return an unmanaged entity?

Can somebody please help me understand this or point out where my understanding might be going wrong?

Quote:

2010-03-18 21:14:40,049 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2010-03-18 21:14:40,049 DEBUG [http-8080-Processor25] o.h.j.ConnectionManager - opening JDBC connection
2010-03-18 21:14:40,049 DEBUG [http-8080-Processor25] org.hibernate.SQL -
SELECT
otwh.OTW_CONTACT_KEY as OTW1_88_0_,
otwh.VERSION as VERSION88_0_,
otwh.DEAL_KEY as DEAL3_88_0_,
otwh.PERSON_KEY as PERSON4_88_0_,
otwh.OTW_CONTACT_TYPE_KEY as OTW5_88_0_,
otwh.START_DATE as START6_88_0_,
otwh.END_DATE as END7_88_0_,
otwh.APPROVER as APPROVER88_0_,
otwh.REQUESTOR as REQUESTOR88_0_,
otwh.INACTIVATED_DATE as INACTIV10_88_0_,
otwh.CROSSOVER_DATE as CROSSOVER11_88_0_,
otwh.STATUS_CODE as STATUS12_88_0_,
otwh.LIST_INDEX as LIST13_88_0_,
otwh.CREATE_DATE as CREATE14_88_0_,
otwh.CREATE_BY as CREATE15_88_0_,
otwh.UPDATE_DATE as UPDATE16_88_0_,
otwh.UPDATE_BY as UPDATE17_88_0_,
otwh.START_TIMESTAMP as START18_88_0_,
otwh.END_TIMESTAMP as END19_88_0_,
individual.PERSON_KEY as PERSON1_103_1_,
individual.EMPLOYEE_ID as EMPLOYEE2_103_1_,
individual.KERBEROS_ID as KERBEROS3_103_1_,
individual.FIRST_NAME as FIRST4_103_1_,
individual.MIDDLE_NAME as MIDDLE5_103_1_,
individual.LAST_NAME as LAST6_103_1_,
individual.FULL_NAME as FULL7_103_1_,
individual.COMPANY as COMPANY103_1_,
individual.EMAIL as EMAIL103_1_,
individual.OFFICE_PHONE as OFFICE10_103_1_,
individual.CELL_PHONE as CELL11_103_1_,
individual.FAX as FAX103_1_,
individual.DIVISION as DIVISION103_1_,
individual.COST_CENTER as COST14_103_1_,
individual.EXTERNAL as EXTERNAL103_1_,
individual.ACTIVE as ACTIVE103_1_,
individual.CREATE_DATE as CREATE17_103_1_,
individual.CREATE_BY as CREATE18_103_1_,
individual.UPDATE_DATE as UPDATE19_103_1_,
individual.UPDATE_BY as UPDATE20_103_1_,
individual.START_TIMESTAMP as START21_103_1_,
individual.END_TIMESTAMP as END22_103_1_,
requestor.PERSON_KEY as PERSON1_103_2_,
requestor.EMPLOYEE_ID as EMPLOYEE2_103_2_,
requestor.KERBEROS_ID as KERBEROS3_103_2_,
requestor.FIRST_NAME as FIRST4_103_2_,
requestor.MIDDLE_NAME as MIDDLE5_103_2_,
requestor.LAST_NAME as LAST6_103_2_,
requestor.FULL_NAME as FULL7_103_2_,
requestor.COMPANY as COMPANY103_2_,
requestor.EMAIL as EMAIL103_2_,
requestor.OFFICE_PHONE as OFFICE10_103_2_,
requestor.CELL_PHONE as CELL11_103_2_,
requestor.FAX as FAX103_2_,
requestor.DIVISION as DIVISION103_2_,
requestor.COST_CENTER as COST14_103_2_,
requestor.EXTERNAL as EXTERNAL103_2_,
requestor.ACTIVE as ACTIVE103_2_,
requestor.CREATE_DATE as CREATE17_103_2_,
requestor.CREATE_BY as CREATE18_103_2_,
requestor.UPDATE_DATE as UPDATE19_103_2_,
requestor.UPDATE_BY as UPDATE20_103_2_,
requestor.START_TIMESTAMP as START21_103_2_,
requestor.END_TIMESTAMP as END22_103_2_,
approver.PERSON_KEY as PERSON1_103_3_,
approver.EMPLOYEE_ID as EMPLOYEE2_103_3_,
approver.KERBEROS_ID as KERBEROS3_103_3_,
approver.FIRST_NAME as FIRST4_103_3_,
approver.MIDDLE_NAME as MIDDLE5_103_3_,
approver.LAST_NAME as LAST6_103_3_,
approver.FULL_NAME as FULL7_103_3_,
approver.COMPANY as COMPANY103_3_,
approver.EMAIL as EMAIL103_3_,
approver.OFFICE_PHONE as OFFICE10_103_3_,
approver.CELL_PHONE as CELL11_103_3_,
approver.FAX as FAX103_3_,
approver.DIVISION as DIVISION103_3_,
approver.COST_CENTER as COST14_103_3_,
approver.EXTERNAL as EXTERNAL103_3_,
approver.ACTIVE as ACTIVE103_3_,
approver.CREATE_DATE as CREATE17_103_3_,
approver.CREATE_BY as CREATE18_103_3_,
approver.UPDATE_DATE as UPDATE19_103_3_,
approver.UPDATE_BY as UPDATE20_103_3_,
approver.START_TIMESTAMP as START21_103_3_,
approver.END_TIMESTAMP as END22_103_3_
FROM
DEAL_OTW_CONTACT_HISTORY otwh
LEFT OUTER JOIN
PERSON_HISTORY individual
ON otwh.PERSON_KEY = individual.PERSON_KEY
AND individual.START_TIMESTAMP <= ?
AND individual.END_TIMESTAMP > ?
LEFT OUTER JOIN
PERSON_HISTORY requestor
ON otwh.REQUESTOR = requestor.PERSON_KEY
AND requestor.START_TIMESTAMP <= ?
AND requestor.END_TIMESTAMP > ?
LEFT OUTER JOIN
PERSON_HISTORY approver
ON otwh.APPROVER = approver.PERSON_KEY
AND approver.START_TIMESTAMP <= ?
AND approver.END_TIMESTAMP > ?
WHERE
otwh.DEAL_KEY = ?
AND otwh.START_TIMESTAMP <= ?
AND otwh.END_TIMESTAMP > ?
2010-03-18 21:14:40,049 TRACE [http-8080-Processor25] o.h.j.AbstractBatcher - preparing statement
2010-03-18 21:14:40,049 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [1]
2010-03-18 21:14:40,049 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 1
2010-03-18 21:14:40,065 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [2]
2010-03-18 21:14:40,065 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 2
2010-03-18 21:14:40,081 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [3]
2010-03-18 21:14:40,081 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 3
2010-03-18 21:14:40,081 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [4]
2010-03-18 21:14:40,081 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 4
2010-03-18 21:14:40,096 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [5]
2010-03-18 21:14:40,096 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 5
2010-03-18 21:14:40,096 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [6]
2010-03-18 21:14:40,096 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 6
2010-03-18 21:14:40,096 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [8]
2010-03-18 21:14:40,096 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 8
2010-03-18 21:14:40,096 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() Tue Mar 16 18:09:35 IST 2010 -> asOfDate [9]
2010-03-18 21:14:40,096 TRACE [http-8080-Processor25] o.h.t.TimestampType - binding '2010-03-16 18:09:35' to parameter: 9
2010-03-18 21:14:40,096 DEBUG [http-8080-Processor25] o.h.loader.Loader - bindNamedParameters() 4987 -> dealKey [7]
2010-03-18 21:14:40,096 TRACE [http-8080-Processor25] o.h.type.IntegerType - binding '4987' to parameter: 7
2010-03-18 21:14:40,768 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.loader.Loader - processing result set
2010-03-18 21:14:40,768 DEBUG [http-8080-Processor25] o.h.loader.Loader - result set row: 0
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '713' as column: OTW1_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning null as column: PERSON1_103_1_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning null as column: PERSON1_103_2_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning null as column: PERSON1_103_3_
2010-03-18 21:14:40,768 DEBUG [http-8080-Processor25] o.h.loader.Loader - result row: EntityKey[DealOTWContactHistory#713], null, null, null
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.loader.Loader - Initializing object from ResultSet: [DealOTWContactHistory#713]
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.p.e.AbstractEntityPersister - Hydrating entity: [DealOTWContactHistory#713]
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '0' as column: VERSION88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '4987' as column: DEAL3_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '618' as column: PERSON4_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '1' as column: OTW5_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning null as column: START6_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning null as column: END7_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '876' as column: APPROVER88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '650' as column: REQUESTOR88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning null as column: INACTIV10_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning '1970-01-01 05:30:00' as column: CROSSOVER11_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.StringType - returning 'OTW' as column: STATUS12_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.IntegerType - returning '0' as column: LIST13_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning '2010-03-16 10:24:25' as column: CREATE14_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.StringType - returning 'vaidhars' as column: CREATE15_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning '2010-03-16 10:24:57' as column: UPDATE16_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.type.StringType - returning 'vaidhars' as column: UPDATE17_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning '2010-03-16 10:24:57' as column: START18_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.t.TimestampType - returning '9999-01-01 00:00:00' as column: END19_88_0_
2010-03-18 21:14:40,768 TRACE [http-8080-Processor25] o.h.e.TwoPhaseLoad - Version: 0
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.loader.Loader - done processing result set (1 rows)
2010-03-18 21:14:40,940 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
2010-03-18 21:14:40,940 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.j.AbstractBatcher - closing statement
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.loader.Loader - total objects hydrated: 1
2010-03-18 21:14:40,940 DEBUG [http-8080-Processor25] o.h.e.TwoPhaseLoad - resolving associations for [DealOTWContactHistory#713]
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - loading entity: [DealHistory#4987]
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - creating new proxy for entity
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - loading entity: [PersonHistory#618]
2010-03-18 21:14:40,940 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - creating new proxy for entity
2010-03-18 21:14:40,956 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - loading entity: [PersonHistory#876]
2010-03-18 21:14:40,956 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - attempting to resolve: [PersonHistory#876]
2010-03-18 21:14:40,956 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - object not resolved in any cache: [PersonHistory#876]
2010-03-18 21:14:40,956 TRACE [http-8080-Processor25] o.h.p.e.AbstractEntityPersister - Fetching entity: [PersonHistory#876]
2010-03-18 21:14:40,956 DEBUG [http-8080-Processor25] o.h.l.e.EntityLoader - Static select for entity PersonHistory: select personhist0_.PERSON_KEY as PERSON1_103_0_, personhist0_.EMPLOYEE_ID as EMPLOYEE2_103_0_, personhist0_.KERBEROS_ID as KERBEROS3_103_0_, personhist0_.FIRST_NAME as FIRST4_103_0_, personhist0_.MIDDLE_NAME as MIDDLE5_103_0_, personhist0_.LAST_NAME as LAST6_103_0_, personhist0_.FULL_NAME as FULL7_103_0_, personhist0_.COMPANY as COMPANY103_0_, personhist0_.EMAIL as EMAIL103_0_, personhist0_.OFFICE_PHONE as OFFICE10_103_0_, personhist0_.CELL_PHONE as CELL11_103_0_, personhist0_.FAX as FAX103_0_, personhist0_.DIVISION as DIVISION103_0_, personhist0_.COST_CENTER as COST14_103_0_, personhist0_.EXTERNAL as EXTERNAL103_0_, personhist0_.ACTIVE as ACTIVE103_0_, personhist0_.CREATE_DATE as CREATE17_103_0_, personhist0_.CREATE_BY as CREATE18_103_0_, personhist0_.UPDATE_DATE as UPDATE19_103_0_, personhist0_.UPDATE_BY as UPDATE20_103_0_, personhist0_.START_TIMESTAMP as START21_103_0_, personhist0_.END_TIMESTAMP as END22_103_0_ from PERSON_HISTORY personhist0_ where personhist0_.PERSON_KEY=?
2010-03-18 21:14:40,956 DEBUG [http-8080-Processor25] o.h.loader.Loader - loading entity: [PersonHistory#876]
2010-03-18 21:14:40,956 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2010-03-18 21:14:40,956 DEBUG [http-8080-Processor25] org.hibernate.SQL -
select
personhist0_.PERSON_KEY as PERSON1_103_0_,
personhist0_.EMPLOYEE_ID as EMPLOYEE2_103_0_,
personhist0_.KERBEROS_ID as KERBEROS3_103_0_,
personhist0_.FIRST_NAME as FIRST4_103_0_,
personhist0_.MIDDLE_NAME as MIDDLE5_103_0_,
personhist0_.LAST_NAME as LAST6_103_0_,
personhist0_.FULL_NAME as FULL7_103_0_,
personhist0_.COMPANY as COMPANY103_0_,
personhist0_.EMAIL as EMAIL103_0_,
personhist0_.OFFICE_PHONE as OFFICE10_103_0_,
personhist0_.CELL_PHONE as CELL11_103_0_,
personhist0_.FAX as FAX103_0_,
personhist0_.DIVISION as DIVISION103_0_,
personhist0_.COST_CENTER as COST14_103_0_,
personhist0_.EXTERNAL as EXTERNAL103_0_,
personhist0_.ACTIVE as ACTIVE103_0_,
personhist0_.CREATE_DATE as CREATE17_103_0_,
personhist0_.CREATE_BY as CREATE18_103_0_,
personhist0_.UPDATE_DATE as UPDATE19_103_0_,
personhist0_.UPDATE_BY as UPDATE20_103_0_,
personhist0_.START_TIMESTAMP as START21_103_0_,
personhist0_.END_TIMESTAMP as END22_103_0_
from
PERSON_HISTORY personhist0_
where
personhist0_.PERSON_KEY=?
2010-03-18 21:14:40,971 TRACE [http-8080-Processor25] o.h.j.AbstractBatcher - preparing statement
2010-03-18 21:14:40,971 TRACE [http-8080-Processor25] o.h.type.IntegerType - binding '876' to parameter: 1
2010-03-18 21:14:41,159 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to open ResultSet (open ResultSets: 0, globally: 0)
2010-03-18 21:14:41,159 TRACE [http-8080-Processor25] o.h.loader.Loader - processing result set
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.loader.Loader - done processing result set (0 rows)
2010-03-18 21:14:41,331 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to close ResultSet (open ResultSets: 1, globally: 1)
2010-03-18 21:14:41,331 DEBUG [http-8080-Processor25] o.h.j.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.j.AbstractBatcher - closing statement
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.loader.Loader - total objects hydrated: 0
2010-03-18 21:14:41,331 DEBUG [http-8080-Processor25] o.h.loader.Loader - done entity load
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - loading entity: [PersonHistory#650]
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.e.d.DefaultLoadEventListener - creating new proxy for entity
2010-03-18 21:14:41,331 ERROR [http-8080-Processor25] o.h.p.BasicPropertyAccessor - IllegalArgumentException in class: com.ms.itlnc.dms.data.DealOTWContact, setter method of property: crossOverDate
2010-03-18 21:14:41,331 ERROR [http-8080-Processor25] o.h.p.BasicPropertyAccessor - expected type: java.lang.Long, actual value: java.sql.Timestamp
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.jdbc.JDBCContext - after autocommit
2010-03-18 21:14:41,331 DEBUG [http-8080-Processor25] o.h.j.ConnectionManager - transaction completed on session with on_close connection release mode; be sure to close the session to release JDBC resources!
2010-03-18 21:14:41,331 TRACE [http-8080-Processor25] o.h.impl.SessionImpl - after transaction completion