• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HIBERNATE: Question about union

 
Bruce Sparo
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Thomas Whitmore
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic