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