This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "HIBERNATE: Question about union" Watch "HIBERNATE: Question about union" New topic

HIBERNATE: Question about union

Bruce Sparo

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

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

Thomas Whitmore
[ September 11, 2004: Message edited by: Mark Spritzler ]
It is sorta covered in the JavaRanch Style Guide.
subject: HIBERNATE: Question about union
Similar Threads
converting sql union query into java
Alternative for union in Hibernate
bigger value in function MAX(x,y)
example of where exists
<jsp:usebean ... action