-->
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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: Self Join help
PostPosted: Tue Jul 01, 2008 3:33 am 
Newbie

Joined: Tue Jul 01, 2008 3:13 am
Posts: 2
Hi I have been trying to implement following query using
Hibernate Criteria APIs but cannot find the way to do it.

Select table.* from Table table where table.field1 in (
select table1.field2 from Table table2 where table2.field3 = value ).

To summarize it is a case of SELF-JOIN.

Help is appreciated.

Thanks....

Regards,

Hemant


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 04, 2008 10:58 am 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
The Hibernate3 Criteria API should make your queries easier, not tougher. :)

What are the names of your classes? Remember, with the Criteria API, we think in terms of objects, not databases.

Here's a sweet little Criteria API tutorial to get you in the mood. Maybe it can help you out:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials


Top
 Profile  
 
 Post subject: Re: Self Join Help
PostPosted: Mon Jul 07, 2008 8:02 pm 
Newbie

Joined: Tue Jul 01, 2008 3:13 am
Posts: 2
Hi! All,

With some research I found the way to implement self join using
criteria API.

The key is to use DetachedCriteria, for the same object and
set the projection for the join field.

The code may looks like as follows;

DetachedCriteria dc = DetachedCriteria.forClass(Table.class);
dc.add(Restrictions.eq("field1", value1);
dc.setProjection(Property.forName("field2"));
Criteria c = l_session.createCriteria(Table.class);

c.add(Property.forName("field2").in(dc));
c.list();


Thanks....

Hemant


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jul 07, 2008 10:16 pm 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
Wow! Great follow up post!

Thanks for the insight, Hemant.

-Cameron McKenzie

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials


Top
 Profile  
 
 Post subject: Re: Self Join help
PostPosted: Tue Aug 18, 2009 11:45 am 
Newbie

Joined: Tue Aug 18, 2009 4:09 am
Posts: 2
That was really good one helped me solving my problem.


Thanks


Top
 Profile  
 
 Post subject: Re: Self Join help
PostPosted: Thu Jan 17, 2013 6:56 pm 
Newbie

Joined: Thu Jan 18, 2007 11:28 am
Posts: 5
I realize this is an old post, but I really need help with returning column from the table in the detached criteria.
Using your example, let's say Table.class has a column called Field3. and you would like to add that column as projection in criteria c, such that:

criteria.add(Property.forName("field2").in(dc));
criteria.setProjection(Projections.add(Projections.property(dc's field3 column)));

Been trying different things and no luck :(

Thanks


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

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.