Custom SQL for loading a collection

madqba2 · **Joined:** Wed Apr 12, 2006 2:11 pm **Posts:** 3

Hello,
I would like to use custom SQL for loading a collection. After a look to the Hibernate reference I need to say that this feature is quite poorly documented, at least for me (I am a newbe). Examples presented there do not show how to use this in general. A also checked "Hibernate in Action" and "Hibernate Quickly" and found no answer.

My mappings are as follows:

Code:

<hibernate-mapping>
    <class name="GeographicLocationGroup" table="LOCATIONGROUP" lazy="false" >
        <composite-id name="id" class="GeographicLocationGroupId">
         <key-property name="label" column="LABEL"/>
         <key-property name="pseudoCity" column="PSEUDOCITY"/>
      </composite-id>
         
      <set name="includedLocations" cascade="persist,delete-orphan" lazy="false" inverse="true">
         <key>
            <column name="LABEL" not-null="true"></column>
            <column name="PSEUDOCITY" not-null="true"></column>
         </key>
         <one-to-many class="GeographicLocation"/>
         <loader query-ref="includedLocationsSql"/>
      </set>
   
      <set name="excludedLocations" cascade="persist,delete-orphan" lazy="false" inverse="true" >
         <key>
              <column name="LABEL" not-null="true"></column>
            <column name="PSEUDOCITY" not-null="true"></column>
         </key>
         <one-to-many class="GeographicLocation"/>
         <loader query-ref="excludedLocationsSql"/>
      </set>
    </class>

   <sql-query name="includedLocationsSql" >
      <load-collection alias="location" role="GeographicLocationGroup.includedLocations"/>
      SELECT {location.*}
      FROM LOCATIONGROUPLIST location
      WHERE INCLEXCL = 'I'
   </sql-query>

   <sql-query name="excludedLocationsSql">
      <load-collection alias="location" role="GeographicLocationGroup.excludedLocations"/>
      SELECT {location.*}
      FROM LOCATIONGROUPLIST location
      WHERE INCLEXCL = 'E'
   </sql-query>
   
</hibernate-mapping>

Code:

<hibernate-mapping>
    <class name="GeographicLocation" table="LOCATIONGROUPLIST" lazy="false">
        <composite-id name="id" class="GeographicLocationId" >
         <key-property name="label" column="LABEL"/>
         <key-property name="pseudoCity" column="PSEUDOCITY"/>
   </composite-id>

   <property name="inclExcl" column="INCLEXCL" not-null="true"/>   
    </class>

</hibernate-mapping>

In fact they contain more properties but I didn't include them to make the thing shorter.
And now the question: the only problem is how to reference the key defined in <set>. I guess that it's a question about how does these key's values map to named parameters in the sql query so that they could be referenced in the 'WHERE' clause.

A hope the question is clear enough:).
Thanks for any help:).

Nikolai_T · **Joined:** Tue Apr 11, 2006 3:00 pm **Posts:** 4

If you are asking "How to pass parameters into an SQL query", here is an example mapping pulled from a working code (DTD and other minor details removed):

Code:

<?xml version="1.0"?>
<hibernate-mapping
   package="com.foo.beans">

    <class name="AccessLevel" >    
    
       <composite-id>                          
          <key-property name="server" />
           <key-property name="securityClass" />           
           <key-property name="accessLevel" />   
       </composite-id>
               
    </class>
    
    <sql-query name="getAccessLevel">
       <return alias="asset" class="AccessLevel" />
   
          SELECT DISTINCT 
         4 as {asset.accessLevel}, 
         B.updsn as {asset.server}, 
         B.upuscl as {asset.securityClass}
       FROM 
         dbo.table_b B,
         dbo.table_a A
       WHERE 
             A.serv_nme = B.updsn
         AND B.usr_id   = :userId
         AND A.appl_nme = :appName 
                 
    </sql-query>   
</hibernate-mapping>

And in the code, the call looks like this:

Code:

Query sqlQuery = session.getNamedQuery("getAccessLevel")
                  .setParameter("appName", applicationName, Hibernate.STRING);

madqba2 · **Joined:** Wed Apr 12, 2006 2:11 pm **Posts:** 3

Nikolai_T,
The question is about using custom sql code to load collection automaticly when parent class is loaded. So I don't write any code to run the query and I don't pass any parameters. I know how to use named parameters in general, but not in this particular case. I just need to make use of values from parent object but I have no idea how to pass them or reference them from the query.

merkkila · **Joined:** Fri Jul 15, 2005 10:02 am **Posts:** 12

I think hibernate is smart enough to know you have a composite key and that you need 2 parameters to determine a unique record. Try something like this

Code:

<sql-query name="includedLocationsSql" >
      <load-collection alias="location" role="GeographicLocationGroup.includedLocations"/>
      SELECT {location.*}
      FROM LOCATIONGROUPLIST location
      WHERE INCLEXCL = 'I' AND LABEL = ? AND PSEUDOCITY = ?
   </sql-query> 

Not sure if this works, but give it a try. Also I believe the order of the fields in the query need to match the order in which they are declared as keys.

madqba2 · **Joined:** Wed Apr 12, 2006 2:11 pm **Posts:** 3

Well, this doesn't work. This approach always gives:

Code:

java.sql.SQLException: Invalid column name

But at least someone has finally understood my question ;-).

max · **Posted:** Sat Apr 15, 2006 9:27 am

you are probably missing a key or index column.

compare the sql to what hibernate would normally generate and see what columns it wants.

garpinc · **Joined:** Thu Sep 09, 2004 6:46 pm **Posts:** 96

I have a table on which for each row I need a collection of rows from the same table but eliminating the row itself joining by 2 of the columns of the primary key. I've tried just about everything but have been unable to get it to work.

<class name="MemberProgram">....
<composite-id name="id" class="MyIdClass">
<key-property name="id1" type="long">
<column name="MY_ID1"/>
</key-property>
<key-many-to-one name="member" class="MyMemberClass">
<column name="MEMBER_ID" />
</key-many-to-one>
<key-many-to-one name="program" class="MyProgramClass">
<column name="PROGRAM_ID"/>
</key-many-to-one>
</composite-id>

<set name="childPrograms">
<key>
<column name="MY_ID1"/>
<column name="MEMBER_ID"/>
<column name="PROGRAM_ID"/>
</key>
<one-to-many class="MemberProgram" />
<loader query-ref="childProgramsQuery"/>

</set>

<sql-query name="childProgramsQuery">
<load-collection alias="mp" role="MemberProgram.childPrograms"/>
SELECT {mp.*}
FROM MEMBER_PROGRAM mp
WHERE
MEMBER_ID = :id.member.member_id
and PROGRAM_ID = :id.program.program_id
</sql-query>

Can't figure out how to pass the memberId or the programId or the memberProgramId

gcollin · **Joined:** Fri Jun 06, 2008 4:01 am **Posts:** 1

For the readers having the same problem, I finally managed to do this custom load-collections with composite-ids

Using Hibernate 3.2.6, this was almost right:

Code:

<sql-query name="includedLocationsSql" >
      <load-collection alias="location" role="GeographicLocationGroup.includedLocations"/>
      SELECT {location.*}
      FROM LOCATIONGROUPLIST location
      WHERE INCLEXCL = 'I' AND LABEL = ? AND PSEUDOCITY = ?
   </sql-query> 

Hibernate will replace each ? with a property value of the composite key of the object.

The user experienced a "COLUMN NOT FOUND', only because it did not prefix it's where clauses with the table name. So to make this thing work, you should have:

Code:

<sql-query name="includedLocationsSql" >
      <load-collection alias="location" role="GeographicLocationGroup.includedLocations"/>
      SELECT {location.*}
      FROM LOCATIONGROUPLIST location
      WHERE location.INCLEXCL = 'I' AND location.LABEL = ? AND location.PSEUDOCITY = ?
   </sql-query> 

Hope this helps,

Gerard COLLIN