Hibernate 3 sql-query not working

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

I have had a problem using order by on one of my mappings.

So as an attempted solution I have tried upgrading the application to Hib3, and tried to use the sql-query, so that I could use my own sql statement to load the object.

For some reason it is not working as hibernate is still generating its own SQL and ignoring the fact that I have specified loader in the class mapping.

Is this a bug or am I doing something wrong. The documentation seems very precise on how to use this, so any help would be most appreciated.

Failing this I may have to switch over to Ibatis so that I can get complete control of the SQL, as im pretty stuck at the moment, and all im trying to do is retrieve shopping baskets in description order?

Hibernate version:
Hibernate 3.0 alpha

Mapping documents:

Code:

<hibernate-mapping package="com.bertrams.bertweb.domain.order">
   
    <class name="Basket" table="BBSHBM">
      <composite-id name="id" class="BasketKey">
        <key-property name="account" type="long" column="SBACCT"/>
        <key-property name="branch" type="long" column="SBBRAN"/>
        <key-property name="uuid" type="com.bertrams.bertweb.dao.hibernate.TrimmedString" column="SBUUID"/>
      </composite-id>
                                   
        <property name="description" type="com.bertrams.bertweb.dao.hibernate.TrimmedString">
           <column name="SBDESC" sql-type="char(50)" not-null="true"/>
        </property>
                                  
        <property name="type" type="com.bertrams.bertweb.dao.hibernate.TrimmedString">
           <column name="SBTYPE" sql-type="char(20)" not-null="true"/>
        </property>

      <map name="basketItems" inverse="true" lazy="false" cascade="all"> 
            <key>
               <column name="SGACCT"/>
               <column name="SGBRAN"/>
               <column name="SGUUID"/>
            </key>
         <composite-index class="BasketItemIndex">
            <key-property name="seqn" type="long" column="SGSEQN"/>
            <key-property name="isbn" type="com.bertrams.bertweb.dao.hibernate.TrimmedString" column="SGISBN"/>
         </composite-index>
           <one-to-many class="BasketItem"/>
       </map>
       [b]<loader query-ref="basketLoader"/>[/b]
    </class>
    
[b]
    <sql-query name="basketLoader">
       <return alias="b" class="Basket"/>
          SELECT 
             {b}.SBACCT AS {b.id.account},
             {b}.SBBRAN AS {b.id.branch},
             {b}.SBUUID AS {b.id.uuid},
             {b}.SBTYPE AS {b.type},
             {b}.SBTYPEID AS {b.typeId},
             {b}.SBSTAT AS {b.status},
             {b}.SBCDAT AS {b.created},
             {b}.SBCUSR AS {b.createdBy},
             {b}.SBCPGM AS {b.createdByPgm},
             {b}.SBMDAT AS {b.modified},
             {b}.SBMUSR AS {b.modifiedBy},
             {b}.SBMPGM AS {b.modifiedByPgm}
          FROM BBSHBM {b} 
          WHERE {b}.SBACCT=? and {b}.SBBRAN=? AND {b}.SBSTAT='OPN' ORDER BY SBDESC
   </sql-query>
[/b]

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Customer customer = (Customer)currentSession().get(Customer.class, ck);

Full stack trace of any exception that occurs:

Name and version of the database you are using:
db2/400 v5r1

The generated SQL (show_sql=true):
Hibernate: select baskets0_.SBACCT as SBACCT__, baskets0_.SBBRAN as SBBRAN__, baskets0_.SBUUID as SBUUID__, baskets0_.SBACCT as SBACCT0_, baskets0_.SBBRAN as SBBRAN0_, baskets0_.SBUUID as SBUUID0_, baskets0_.SBDESC as SBDESC3_0_, baskets0_.SBTYPE as SBTYPE3_0_, baskets0_.SBTYPEID as SBTYPEID3_0_, baskets0_.SBSTAT as SBSTAT3_0_, baskets0_.SBCDAT as SBCDAT3_0_, baskets0_.SBCUSR as SBCUSR3_0_, baskets0_.SBCPGM as SBCPGM3_0_, baskets0_.SBMDAT as SBMDAT3_0_, baskets0_.SBMUSR as SBMUSR3_0_, baskets0_.SBMPGM as SBMPGM3_0_ from BBSHBM baskets0_ where baskets0_.SBACCT=? and baskets0_.SBBRAN=? and baskets0_.SBSTAT='OPN'

Debug level Hibernate log excerpt:

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

I had same problem. Did you find resolution?

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

No,
sorry mate,
as you can see no one bothered to help with this problem, so I gave up and moved to Ibatis.

ju · **Joined:** Thu Sep 02, 2004 4:54 am **Posts:** 24

Hi,
first you need to use the getNamedQuery("basketLoader") from the Session API to use the basketLoader query.
Second, your query needs to return all the table fields in order to create the java object (the basket in our case)
=> use {b.*} or enumerate all fields.

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

I thought that was what the loader keyword was supposed to do

<loader query-ref="basketLoader"/>

The object should be loaded automagically with the defined statement when it is requested from hibernate

ju · **Joined:** Thu Sep 02, 2004 4:54 am **Posts:** 24

that's right but your query may returns many result... and it doesn't fit with the get method specification:
- the get method takes only one parameter for the loader query and you got 2.
- it only returns one object so the 'order by' is theoritically useless but I understand what you want to do.

One last thing is that I don't know how the composite-key property works with a given loader sql query...
my advice:
pray for someone of the hibernate team helps you or develop this part in the manual...

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

Ju,

thanks for the input, but it would have been better if one of the hibernate team had responded at the time when I need the help.

And it wasnt a case of them being too busy as I could see other questions getting answered around the same time, mine was just ignored for some reason that I dont understand.

This problem prompted me to try Ibatis, which I found much easier to use, and more flexible.

max · **Posted:** Wed Apr 20, 2005 7:27 am

haven't seen this posting before now (and no its not just because ibatis is now mentioned).

I can't directly see why it is not working since it should - I would tell you to provide a failing test case to jira for this one if it still fails in 3.0.1

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

will do!

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

This works at least in 3.0.1 (I haven't tried the others). See specifically load-collection. It appears the api has changed since a lot of the documentation I've seen out there. There a bunch of TODO documentation things on the documentation page that the hibernate team need to focus on. This is a partial solution for me however because I need level virtual column to be returned in the object. How can I do that?

If in ContactUsTopicHeader I put

Code:

      <property
         name="level"
         column="LEVEL"
         type="integer"
         not-null="false"
         length="22"
      />

The generated select statement puts h.level and this is incorrect. Also even if I get this working would that mean I have to override insert, update and delete for that one column of could I say somehow it only applies for select

Code:

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

<hibernate-mapping package="com.bcbsma.contactus.business.domain">
   <class
      name="ContactUsTopic"
      table="CONTACT_US_TOPIC"
   >
      <id
         column="TOPIC_ID"
         name="id"
         type="integer"
      >
         <generator class="sequence">
              <param name="sequence">CONTACT_US_TOPIC_ID_SEQ</param>
            </generator>
      </id>

      <property
         name="type"
         column="TYPE"
         type="integer"
         not-null="true"
         length="22"
      />
      <property
         name="specialty"
         column="SPECIALTY"
         type="integer"
         not-null="true"
         length="22"
      />
      <property
         name="sortOrder"
         column="SORT_ORDER"
         type="integer"
         not-null="true"
         length="22"
      />
      <property
         name="topicName"
         column="TOPIC_NAME"
         type="string"
         not-null="false"
         length="255"
      />

      <set name="headers" 
           inverse="true" 
           lazy="true"
           cascade="all"
           order-by="SORT_ORDER"
       >
         <key column="TOPIC_ID"/>
         <one-to-many class="ContactUsTopicHeader"/>
         <loader query-ref="contactUsTopicHeaderSql"/>
      </set>
   </class>
   
   <sql-query name="contactUsTopicHeaderSql">
       <load-collection alias="h" role="ContactUsTopic.headers"/>
       SELECT {h.*}
              FROM CONTACT_US_TOPIC_HEADER h          
        START WITH PARENT_TOPIC_HEADER_ID IS NULL AND TOPIC_HEADER_ID=:id
        CONNECT BY PRIOR TOPIC_HEADER_ID=PARENT_TOPIC_HEADER_ID
   </sql-query>   
</hibernate-mapping>

Code:

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


<hibernate-mapping package="com.bcbsma.contactus.business.domain">

   <class
      name="ContactUsTopicHeader"
      table="CONTACT_US_TOPIC_HEADER"
   >
      <id
         name="id"
         type="integer"
         column="TOPIC_HEADER_ID"
      >
         <generator class="sequence">
              <param name="sequence">CONTACT_US_TOPIC_HEADER_ID_SEQ</param>
            </generator>
      </id>

      <property
         name="topicHeader"
         column="TOPIC_HEADER"
         type="string"
         not-null="true"
         length="255"
      />
      <property
         name="sortOrder"
         column="SORT_ORDER"
         type="integer"
         not-null="true"
         length="22"
      />


      <many-to-one
         name="parentTopicHeader"
         column="PARENT_TOPIC_HEADER_ID"
         class="ContactUsTopicHeader"
         not-null="false"
      >
      </many-to-one>
      <many-to-one
         name="topic"
         column="TOPIC_ID"
         class="ContactUsTopic"
         not-null="true"
      >
      </many-to-one>

      <set name="infoLines" 
           inverse="true" 
           lazy="true"
           cascade="all"
           order-by="SORT_ORDER"
       >
         <key column="TOPIC_HEADER_ID"/>
         <one-to-many class="ContactUsTopicInfo"/>
      </set>

   </class>   
   

</hibernate-mapping>

max · **Posted:** Wed Apr 20, 2005 9:52 am

why is it incorrect to put h.level in the select statement ?

What value do you want hibernate to put in that property ?

max · **Posted:** Wed Apr 20, 2005 9:56 am

just saw you wish for having it in select but not other situations.

set insert="false" and update="false" on the property.

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

This is incorrect because LEVEL is no a real database column and oracle will complaint if you put h.level. In a connect by statement level is a virtual column that you can select to determine the depth in the tree. So select level, h.topic_header_id is fine but select h.level, h.topic_header_id isn't..

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

I'm waiting.... Is there a way to do this or is there an issue with hibernate that is somehow going to cause me to move to IBatis :-)

colinwilliams · **Joined:** Mon Nov 22, 2004 11:41 am **Posts:** 8

You see, its not just me!!