union of two hibernate criteria? Possible?

mr_bungalow · **Joined:** Thu Dec 14, 2006 12:23 pm **Posts:** 6

Hibernate version: 3.1.2

Name and version of the database you are using: Oracle 10G

Hey there. I've exhausted all of my resources trying to figure this out. We have an application that allows a user to search for a specific set of users. The only problem here is that we are allowing the user to search two tables that do not have relationships, even though the criteria entered is the same.

Code:

listing table
----------------
listing_id 
name
status
department
<other rows>

message_group table
---------------
group_number
group_name
<other rows>

When this was written in straight sql we would simply do

Code:

select listing_id, name, status, department
from listing where listing_id like :l_id_IN and name like :name_IN
UNION ALL
select group_number as listing_id, group_name as name, null as status, null as department 
from message_group

Is it possible to do something like this using Criteria queries? I love the abstraction from the database that Criteria provides (not to mention the speed using projections, etc).

I've created two different criteria and am using a result transformer to populate a custom DTO.

thanks in advance!

mr_bungalow · **Joined:** Thu Dec 14, 2006 12:23 pm **Posts:** 6

of course, hibernate does not support unions, but is this possible using a subquery?

halconGT · **Posted:** Thu Dec 14, 2006 1:31 pm

Hi, my two cents, it already was considered:
http://forum.hibernate.org/viewtopic.php?t=968111

I hope this helps