Tuesday, November 23, 2004

Hibernate 3 sql-query not working

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 Hibernate3, 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: <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

No comments:

Post a Comment