Trouble using <loader> element

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

I'm working with legacy data, and trying to get around difficulties by using native sql queries to load an object. Trouble is, I can't figure out how to get Hibernate to *use* the native sql query. I'm working off of manual section 17.5 Custom SQL for loading

My mapping file:

Code:

<hibernate-mapping package="edu.taylor.domain.services.registrar">
   <class name="DeclaredMajor">
      <composite-id>
         <key-many-to-one name="major" class="Major" column="SORLFOS_MAJR_CODE"/>
         <key-many-to-one name="studentRecord" class="StudentRecord" column="SORLFOS_PIDM"/>
      </composite-id>   
      <loader query-ref="loadDeclaredMajor"/>
   </class>
   <sql-query name="loadDeclaredMajor">
      <return alias="dm" class="DeclaredMajor"/>
      select 
          s1.SORLFOS_PIDM as {dm.sorlfos_pidm},
          s1.SORLFOS_MAJR_CODE as {dm.sorlfos_majr_code}
      from SORLFOS s1
      where s1.SORLFOS_PIDM=?
          and SORLFOS_CACT_CODE = 'ACTIVE'
          and (SORLFOS_CSTS_CODE = 'INPROGRESS' OR SORLFOS_CSTS_CODE = 'COMPLETED')
          and (SORLFOS_LFST_CODE = 'MAJOR')
          and s1.sorlfos_term_code = (
              select max(s2.SORLFOS_TERM_CODE) 
              from sorlfos s2 
              where s1.sorlfos_pidm = s2.sorlfos_pidm and s1.sorlfos_majr_code = s2.sorlfos_majr_code)
      group by s1.SORLFOS_PIDM, s1.SORLFOS_MAJR_CODE 
   </sql-query>
</hibernate-mapping>

Which seems right according to the example provided in the manual. I have a mapping for another object which references DeclaredMajor as a many-to-one:

Code:

<hibernate-mapping package="edu.taylor.domain.services.registrar">
   <class name="StudentRecord" table = "SHRLGPA" 
      where="shrlgpa_levl_code = 'UG' and shrlgpa_gpa_type_ind = 'I'">
      <id name="pidm" column="shrlgpa_pidm"/>   <!--  no generator, we always assign this -->
      <property name="institutionalHours" column="SHRLGPA_HOURS_EARNED"/>
      <property name="cumGpa" column="SHRLGPA_GPA"/>
      <bag name="declaredMajors" lazy="true" inverse="true">
         <key column="SORLFOS_PIDM"/>
         <one-to-many class="DeclaredMajor"/>
      </bag>
   </class>
</hibernate-mapping>

When I attempt to access this collection, the following query is generated by hibernate (formatted by me for easy reading):

Code:

select 
  declaredma0_.SORLFOS_PIDM as SORLFOS2_1_, 
  declaredma0_.SORLFOS_MAJR_CODE as SORLFOS1_1_, 
  declaredma0_.SORLFOS_MAJR_CODE as SORLFOS1_0_, 
  declaredma0_.SORLFOS_PIDM as SORLFOS2_0_
from
  DeclaredMajor declaredma0_
where
  declaredma0_.SORLFOS_PIDM=?

Clearly the named query referenced by the <loader> tag is not being used. What have I left out?

I am using Hibernate 3.0.5

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

I've done some poking around in the code, and I'm not encouraged. In BasicEntityPersister.java, the constructor initializes a variable 'loaderName'. I have verified in the debugger that this is initialized correctly for the DeclaredMajor class. However, the only reference I can find elsewhere in the code that uses the query loader created from that name is in load(Serializable, Object, LockMode, SessionImplementation), which is (presumably, I haven't looked) only called when you do a session.get() or a session.load().

If that's the only context in which native sql can be used to load a persistant object, it's use is fairly limitted, is it not?

I can get away with doing session.get()s to grab these objects for now, but does anyone have suggestions for me?

pksiv · **Posted:** Fri Aug 26, 2005 4:30 pm

matt_mcgill wrote:

...
...

If that's the only context in which native sql can be used to load a persistant object, it's use is fairly limitted, is it not?

I can get away with doing session.get()s to grab these objects for now, but does anyone have suggestions for me?

In what additional context would you expect that query to be used ?

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

Having stared at the documentation longer and thought about what I need to do, it appears that using a native sql named query and a <loader> for a collection will do what I'm trying to do. Unfortunately, <loader> for collections is on the TODO list as far as documentation goes. Has anyone else figured this out, or could the dev team clue me in?

Here's my new scenario:
I have two classes, StudentRecord and Major. StudentRecord has the 'many' end of a many-to-one association with Major, a bag named 'declaredMajors'. I need to load that collection with some native SQL, because of the way it's stored. Here's the mapping for Major:

Code:

<hibernate-mapping package="edu.taylor.domain.services.registrar">
   <class name="Major" table="STVMAJR" 
      where="STVMAJR_VALID_MAJOR_IND = 'Y'">
      <id name="code" column="STVMAJR_CODE"/>
      <property name="description" column="STVMAJR_DESC"/>
   </class>
</hibernate-mapping>

And here's the mapping for StudentRecord:

Code:

<hibernate-mapping package="edu.taylor.domain.services.registrar">
   <class name="StudentRecord" table = "SHRLGPA" 
      where="shrlgpa_levl_code = 'UG' and shrlgpa_gpa_type_ind = 'I'">
      <id name="pidm" column="shrlgpa_pidm"/>   <!--  no generator, we always assign this -->
      <property name="institutionalHours" column="SHRLGPA_HOURS_EARNED"/>
      <property name="cumGpa" column="SHRLGPA_GPA"/>
      <bag name="declaredMajors" lazy="true" inverse="true">
         <key column="SORLFOS_PIDM"/>
         <one-to-many class="Major"/>
         <loader query-ref="loadDeclaredMajors"/>
      </bag>
   </class>
   <sql-query name="loadDeclaredMajors">
      <load-collection alias="dm" role="StudentRecord.declaredMajors"/>
      select 
          s1.SORLFOS_MAJR_CODE as {dm.code},
          maj.STVMAJR_DESC as {dm.description}
      from
          SORLFOS s1, STVMAJR maj
      where s1.SORLFOS_PIDM=:id and
          s1.SORLFOS_CACT_CODE = 'ACTIVE'
          and (s1.SORLFOS_CSTS_CODE = 'INPROGRESS' OR SORLFOS_CSTS_CODE = 'COMPLETED')
          and (s1.SORLFOS_LFST_CODE = 'MAJOR')
          and s1.SORLFOS_MAJR_CODE = maj.STVMAJR_CODE
          and s1.sorlfos_term_code = (
              select max(s2.SORLFOS_TERM_CODE) 
              from sorlfos s2 
              where s1.sorlfos_pidm = s2.sorlfos_pidm and s1.sorlfos_majr_code = s2.sorlfos_majr_code)
      group by s1.SORLFOS_PIDM, s1.SORLFOS_MAJR_CODE, maj.STVMAJR_DESC   </sql-query>
</hibernate-mapping>

And here's the query that Hibernate runs:

Code:

select 
          s1.SORLFOS_MAJR_CODE as dm.SORLFOS_PIDM as SORLFOS3_0__, dm.STVMAJR_CODE as STVMAJR1_0__, STVMAJR1_0_,
          maj.STVMAJR_DESC as dm.SORLFOS_PIDM as SORLFOS3_0__, dm.STVMAJR_CODE as STVMAJR1_0__, STVMAJR2_3_0_
      from
          SORLFOS s1, STVMAJR maj
      where s1.SORLFOS_PIDM=? and
          s1.SORLFOS_CACT_CODE = 'ACTIVE'
          and (s1.SORLFOS_CSTS_CODE = 'INPROGRESS' OR SORLFOS_CSTS_CODE = 'COMPLETED')
          and (s1.SORLFOS_LFST_CODE = 'MAJOR')
          and s1.SORLFOS_MAJR_CODE = maj.STVMAJR_CODE
          and s1.sorlfos_term_code = (
              select max(s2.SORLFOS_TERM_CODE) 
              from sorlfos s2 
              where s1.sorlfos_pidm = s2.sorlfos_pidm and s1.sorlfos_majr_code = s2.sorlfos_majr_code)
      group by s1.SORLFOS_PIDM, s1.SORLFOS_MAJR_CODE, maj.STVMAJR_DESC

Clearly I'm doing something completely wrong - but since the documentation is incomplete, I'm not sure where to go from here.

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

pksiv wrote:

matt_mcgill wrote:

...
...

If that's the only context in which native sql can be used to load a persistant object, it's use is fairly limitted, is it not?

I can get away with doing session.get()s to grab these objects for now, but does anyone have suggestions for me?

In what additional context would you expect that query to be used ?

When loading associations, for example. But as is implied by your question, my original query and the mechanism I was using can't really _be_ used in any other way, can they? Hence the use of <loader> for collections. I still haven't figured them out yet, though. <=)

gavin · **Posted:** Fri Aug 26, 2005 5:06 pm

You have to put the <loader> tag in a collection mapping, of course.

You should work of the HB 3.1 documentation if you need full documentation of this.

matt_mcgill · **Joined:** Wed Jul 13, 2005 2:18 pm **Posts:** 44

I had actually gotten that far, the <bag> in my last post did have a <loader> in it. It was the nuances of the <sql-query> that I didn't understand at first. I figured it out though, just had to ponder some more and do a little trial/error.

I shall certainly take a look at the 3.1 docs - I've browsed around in the CVS a bit and I see the source xml, but I didn't notice a built version. Is there somewhere I can grab the built docs, or do I need to build them myself?

sameet · **Joined:** Fri Aug 13, 2004 3:07 pm **Posts:** 44

matt_mcgill wrote:

I had actually gotten that far, the <bag> in my last post did have a <loader> in it. It was the nuances of the <sql-query> that I didn't understand at first. I figured it out though, just had to ponder some more and do a little trial/error.

I shall certainly take a look at the 3.1 docs - I've browsed around in the CVS a bit and I see the source xml, but I didn't notice a built version. Is there somewhere I can grab the built docs, or do I need to build them myself?

i am having an issue where it is not really loading the collection. i.e. at the end of the call to the set property returns an empty collection even though the hibernate debug logs show that the entity is being returned and populated.