-->
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
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
crazeinc
 Post subject: How would I write this HSQL query?
PostPosted: Thu Jan 27, 2005 4:10 pm 
Beginner
Beginner

Joined: Sun Jan 09, 2005 5:16 pm
Posts: 20
I have this mapping:

Code:
<hibernate-mapping>
    <class name="org.appfuse.model.Category" table="CATEGORY">
        <id name="id" type="long" column="CATEGORY_ID">
            <generator class="increment"/>
        </id>
      <property name="name" type="string" column="CATEGORY_NAME" not-null="true"/>
      <list name="parents" table="CATEGORY_STRUCTURE">
         <key column="CATEGORY_ID"/>
         <index column="POSITION"/>
         <composite-element class="org.appfuse.model.CategoryStructure">
            <many-to-one name="parent" class="org.appfuse.model.Category" column="PARENT_ID"/>
         </composite-element>
      </list>
    </class>
</hibernate-mapping>


This works well for grabbing the parents of a category, but I also need to grab the children as well. The SQL code I'd use to do this would look something like:
Code:
SELECT CATEGORY_ID FROM CATEGORY_STRUCTURE WHERE PARENT_ID=fooId;


The only problem is I'm having trouble figuring out how to write this query in HSQL. Could somebody point me in the right direction?

Thanks,
PJ
Top
 Profile  
 
egervari
 Post subject:
PostPosted: Thu Jan 27, 2005 4:38 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Code:
select category
from Category parent
   inner join parent.childrenCategories category
where parent.id = :parentId


or

Code:
select category
from Category category
where category.parent.id = :parentId


You should change your mapping to reflect these queries since your organization is kind of backwards.
Top
 Profile  
 
crazeinc
 Post subject:
PostPosted: Thu Jan 27, 2005 5:27 pm 
Beginner
Beginner

Joined: Sun Jan 09, 2005 5:16 pm
Posts: 20
egervari wrote:
Code:
select category
from Category parent
   inner join parent.childrenCategories category
where parent.id = :parentId


or

Code:
select category
from Category category
where category.parent.id = :parentId


You should change your mapping to reflect these queries since your organization is kind of backwards.


You mean to map children to a parent Id instead of what I have now? Wouldn't that just create the same issue when I wanted the parents of a child?
Top
 Profile  
 
egervari
 Post subject:
PostPosted: Thu Jan 27, 2005 5:40 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Then do both a children and parent mapping on the same object, which still allows you to do:

Code:
select child
from Category parent
   inner join parent.children child
where parent.id = :parentId


or

Code:
select child
from Category child, Category parent
where parent.id = :parentId
   and parent in elements(child.parents)
Top
 Profile  
 
egervari
 Post subject:
PostPosted: Thu Jan 27, 2005 5:43 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
or

Code:
select child
from Category child
   inner join child.parents parent
where parent.id = :parentId


There are lots of ways to do this stuff. Read the manual :P
Top
 Profile  
 
crazeinc
 Post subject:
PostPosted: Thu Jan 27, 2005 5:47 pm 
Beginner
Beginner

Joined: Sun Jan 09, 2005 5:16 pm
Posts: 20
You mean something like this? I'm kinda confused with your code now
Code:
<hibernate-mapping>
    <class name="org.appfuse.model.Category" table="CATEGORY">
        <id name="id" type="long" column="CATEGORY_ID">
            <generator class="increment"/>
        </id>
      <property name="name" type="string" column="CATEGORY_NAME" not-null="true"/>
      <list name="parents" table="CATEGORY_PARENTS">
         <key column="CATEGORY_ID"/>
         <index column="POSITION"/>
         <many-to-one name="parent" class="org.appfuse.model.Category" column="PARENT_ID"/>
      </list>
      <list name="children" table="CATEGORY_CHILDREN">
         <key column="CATEGORY_ID"/>
         <index column="POSITION"/>
         <many-to-one name="child" class="org.appfuse.model.Category" column="CHILD_ID"/>
      </list>
    </class>
</hibernate-mapping>
[/code]
Top
 Profile  
 
egervari
 Post subject:
PostPosted: Thu Jan 27, 2005 6:31 pm 
Senior
Senior

Joined: Sun Oct 26, 2003 5:05 am
Posts: 139
Sure. Any of my queries would work against that. Some of my queries would have worked with your old mapping as well.

Make sure you set those collections to lazy. Learn about fetch joining.
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
  [ 7 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.