Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: SQL to HQL (Having issue in Left Outer Join)
PostPosted: Tue Aug 18, 2009 11:37 am 
Newbie

Joined: Mon Feb 02, 2009 12:23 pm
Posts: 2
SQL :
Code:
select TA.*,TB.* from tableA TA // Line 1 
left outer join (select * from tableB where CB1 = 35236 ) TB //Line 2 
on TA.CA=TB.CB // Line 3


Line 1 is easy to convert in HQL.
Line 3 doesn't need to be converted to HQL.
How can I convert Line 2?

In other words, I want A X B where X denotes left outer join. There is a direct relationship between A and B. But I want to apply filter on B table and then I want A X B(filtered).

Please follow Cross Post for more info.


Top
 Profile  
 
 Post subject: Re: SQL to HQL (Having issue in Left Outer Join)
PostPosted: Wed Aug 19, 2009 11:28 am 
Newbie

Joined: Mon Feb 02, 2009 12:23 pm
Posts: 2
Any body?


Top
 Profile  
 
 Post subject: Re: SQL to HQL (Having issue in Left Outer Join)
PostPosted: Wed Aug 19, 2009 5:11 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hello Vishal,
after looking at the cross post have no idea if this can be of any help.. I was browsing some other post and was wondering if this would help you anyways below is my two cents.
Have you tried to define a filter on the mapping file itself? here is the linky:-
http://docs.jboss.org/hibernate/stable/ ... lters.html

Long story short the article mentions to create a filter in your case on the Table B and enable filter and then execute your HQL query.
THIS MAY NOT DO THE LEFT OUTER JOIN THOU.
<class name="myClass" ...>
...
<filter name="myFilter" condition=":myFilterParam = MY_FILTERED_COLUMN"/>
</class>

enable the filter and then run the query.

Session session = ...;
session.enableFilter("myFilter").setParameter("myFilterParam", 35236);
List results = session.createQuery(<<your HQL>>)
.list();

Hope this helps,
Srilatha.


Top
 Profile  
 
 Post subject: Re: SQL to HQL (Having issue in Left Outer Join)
PostPosted: Sat Nov 28, 2009 12:01 pm 
Newbie

Joined: Sat Nov 28, 2009 11:24 am
Posts: 1
hi, I think I'm doing the same as what's suggested here. Filter enabled on a one-to-many collection myCollectionField of ObjectA, then outer join. Then I did
With Criterion API:
session.createCriteria(ObjectA.class)
.createAlias("myCollectionField", "collB", CriteriaSpecification.LEFT_JOIN)
.add(Restrictions.in("id", myIds))
.setFetchMode("collB" , FetchMode.JOIN)
.list();
Then I tried with HQL:
session.createQuery("select objA, collB from ObjectA objA" +
" left outer join objA.myCollectionField collB where objA.id in (:idList)")
.setParameterList("idList", myIds)
.list();

In both cases, without Filter enabled, I get an outer-join. But with filter enabled, i get an (effectively inner-join) Statement.

select ... from OBJECTA_TABLE objA, OBJECTB_TABLE collB where objA.ID=collB.objA_ID(+)
and (collB filter condition)
and (objA.ID in ( ? , ?))


This as mentioned is like an inner-join, because the filter condition ccurs in the where clause instead of the sub-select as required by Vishal.

So if anyone knows another way to achieve that, I'll be happy to know it.
thanks.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 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.