File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes HIBERNATE: Question about union Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "HIBERNATE: Question about union" Watch "HIBERNATE: Question about union" New topic
Author

HIBERNATE: Question about union

Bruce Sparo
Greenhorn

Joined: Jun 24, 2004
Posts: 9
How do you do a "union" in Hibernate.

I need to make implement the following code in a Hibernate session.


select c.*, p.*
from Cst_Custgroup c, Tps_Program p, Tps_User_Program u
where c.custGroup = p.custGroup
and p.prog_ID = u.prog_ID
and u.tps_ID = 'testuser'
union
select c.*, p.*
from Cst_Custgroup c, Tps_Program p, Tps_User_Custgroup u
where c.custGroup = p.custGroup
and p.custgroup = u.custgroup
and u.tps_ID = 'testuser'
order by 2 asc, 1, 5


If I haven't already said it... THANKS for responding.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Hibernate doesn't support UNIONS. They prefer two round trips. I'm not strictly sure why they don't - I remember reading somewhere in the docs that there are problems with implementing UNIONS in a portable way.

With your example you've got two options. First is to explicitly run the query as SQL (see the Session API for createSQLQuery() ). I suspect if you do it this way you'll eventually run into whatever portablility issues stop Hibernate implementing this is the first place. The other option is to query for you first set, then query for the second and programatically handle the two collections.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Thomas Whitmore
Ranch Hand

Joined: Aug 05, 2004
Posts: 33
Hi Bruce,

(Post removed by Mark Spritzler It was promoting his product. If you are interested you can still click on his link to check out his product. )

Cheers,
Thomas Whitmore
www.powermapjdo.com
[ September 11, 2004: Message edited by: Mark Spritzler ]
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: HIBERNATE: Question about union