-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 
  Previous topic | Next topic 
Author Message
alankstewart
 Post subject: Need help with HQL query
PostPosted: Mon Mar 20, 2006 5:50 pm 
Beginner
Beginner

Joined: Sat Mar 04, 2006 1:07 am
Posts: 27
3.1

I have a SQL query and would like to translate it into HQL:

SELECT lu.VALUE FROM LOOKUP lu JOIN LOOKUP_CATEGORY lc ON lu.LOOKUP_ID = lc.LOOKUP_CAT_ID WHERE lc.LOOKUP_CAT_VALUE = 'xxxx'

The mapping documents for the Lookup and LookupCategory classes are as follows:

Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="true" default-lazy="false">
   <class name="au.com.woolworths.sdcard.domain.Lookup" table="LOOKUP">
      <id name="id" column="LOOKUP_ID" type="long">
         <generator class="sequence">
            <param name="sequence">LOOKUP_SEQ</param>
         </generator>
      </id>
      <version name="version" column="ROW_VER_NO" />
      <property name="description" column="LOOKUP_DESC" type="string" />
      <property name="value" column="LOOKUP_VALUE" type="string" />
      <property name="readOnly" column="READ_ONLY_FL" type="true_false" />
      <property name="sortOrder" column="SRT_ORDER_NO" type="string" />
      <property name="createdBy" column="CRE_USR_ID" type="string" />
      <property name="createdDate" column="CRE_DTETM" type="timestamp" />
      <property name="modifiedBy" column="UPD_USR_ID" type="string" />
      <property name="modifiedDate" column="UPD_DTETM" type="timestamp" />
   </class>
   <query name="findLookup">
      <![CDATA[from Lookup as lu where lu.id = ?]]>
   </query>
   <query name="findAllLookups">
      <![CDATA[from Lookup as lu]]>
   </query>
</hibernate-mapping>


Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="true" default-lazy="false">
   <class name="au.com.woolworths.sdcard.domain.LookupCategory" table="LOOKUP_CAT">
      <id name="id" column="LOOKUP_CAT_ID" type="long">
         <generator class="sequence">
            <param name="sequence">LOOKUP_CAT_SEQ</param>
         </generator>
      </id>
      <version name="version" column="ROW_VER_NO" />
      <property name="value" column="LOOKUP_CAT_VALUE" type="string" />
      <property name="description" column="LOOKUP_CAT_DESC" type="string" />
      <property name="readOnly" column="READ_ONLY_FL" type="true_false" />
      <property name="createdBy" column="CRE_USR_ID" type="string" />
      <property name="createdDate" column="CRE_DTETM" type="timestamp" />
      <property name="modifiedBy" column="UPD_USR_ID" type="string" />
      <property name="modifiedDate" column="UPD_DTETM" type="timestamp" />
      <set name="lookupValues" inverse="false" cascade="all-delete-orphan">
         <key column="LOOKUP_CAT_ID" />
         <one-to-many class="au.com.woolworths.sdcard.domain.Lookup" />
      </set>
   </class>
   <query name="findLookupCat">
      <![CDATA[from LookupCategory as cat where cat.id = ?]]>
   </query>
   <query name="findAllLookupCat">
      <![CDATA[from LookupCategory as cat]]>
   </query>
</hibernate-mapping>


Thanks
Alan
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Mon Mar 20, 2006 6:05 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
You really want to join on two unrelated sequences? Are you sure that one of those primary keys shouldn't use the foreign generator instead of the sequence generator?

When the two class aren't related in their mappings, you have to use cross joins:

Code:
select l
from Lookup l, LookupCategory lc
where l.id = lc.id and lc.value = :Val
As you can imagine, this is not desirable. You'd be better off changing your mapping to use the associations and the "foreign" id generator.
Top
 Profile  
 
alankstewart
 Post subject:
PostPosted: Mon Mar 20, 2006 6:14 pm 
Beginner
Beginner

Joined: Sat Mar 04, 2006 1:07 am
Posts: 27
Thanks tenwit for your very quick response.

Without considering the Hibernate mappings, the LOOKUP table has a foreign key (LOOKUP_CAT_ID) that references the LOOKUP_CAT table's primary key (LOOKUP_CAT_ID). LOOKUP_CAT has categories and LOOKUP has the lookup values for each of the categories.

The LookupCategory class has a Set of "Lookup" objects and I intended it to be a uni-directional one-to-many association from LookupCategory to Lookup. Can you you please give me an example of the mapping documents that you would typically use?
Thanks
Alan
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Mon Mar 20, 2006 7:06 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Perhaps I've been confused by what generator="sequence" does: I use SQLServer, and that doesn't support the sequence generator. I've always assumed that sequence = identity; however, the example in section 6.4 of the ref docs supports your mapping, with one minor caveat: add not-null="true" in the key of the <set> mapping, so that hibernate knows not to insert children (Lookup) before parents (LookupCategory).

Given this, the cross join is no longer necessary and you don't need to change you mapping (beyond adding that not-null="true"). Your HQL query now becomes
Code:
select lu from LookupCategory lc
join lc.lookupValues lu
where lc.value = :Val
Top
 Profile  
 
alankstewart
 Post subject:
PostPosted: Mon Mar 20, 2006 7:32 pm 
Beginner
Beginner

Joined: Sat Mar 04, 2006 1:07 am
Posts: 27
Thanks tenwit - your code works perfectly!
Really appreciated.
Regards
Alan
Top
 Profile  
 
tenwit
 Post subject:
PostPosted: Mon Mar 20, 2006 7:38 pm 
Expert
Expert

Joined: Thu Dec 23, 2004 9:08 pm
Posts: 2008
Don't forget to click "Yes" on my message then :)
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 6 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.