Hibernate Union alternatives

Question

What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view table.

The other option is to use plain jdbc, but this way I would loose all my example/criteria queries goodies, as well as the hibernate mapping validation that hibernate performs against the tables/columns.

Answer 1

You could use id in (select id from ...) or id in (select id from ...)

e.g. instead of non-working

from Person p where p.name="Joe" union from Person p join p.children c where c.name="Joe" 

you could do

from Person p    where p.id in (select p1.id from Person p1 where p1.name="Joe")      or p.id in (select p2.id from Person p2 join p2.children c where c.name="Joe"); 

At least using MySQL, you will run into performance problems with it later, though. It's sometimes easier to do a poor man's join on two queries instead:

// use set for uniqueness Set<Person> people = new HashSet<Person>((List<Person>) query1.list()); people.addAll((List<Person>) query2.list()); return new ArrayList<Person>(people); 

It's often better to do two simple queries than one complex one.

EDIT:

to give an example, here is the EXPLAIN output of the resulting MySQL query from the subselect solution:

mysql> explain    select p.* from PERSON p      where p.id in (select p1.id from PERSON p1 where p1.name = "Joe")        or p.id in (select p2.id from PERSON p2          join CHILDREN c on p2.id = c.parent where c.name="Joe") \G *************************** 1. row ***************************            id: 1   select_type: PRIMARY         table: a          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 247554         Extra: Using where *************************** 2. row ***************************            id: 3   select_type: DEPENDENT SUBQUERY         table: NULL          type: NULL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: NULL         Extra: Impossible WHERE noticed after reading const tables *************************** 3. row ***************************            id: 2   select_type: DEPENDENT SUBQUERY         table: a1          type: unique_subquery possible_keys: PRIMARY,name,sortname           key: PRIMARY       key_len: 4           ref: func          rows: 1         Extra: Using where 3 rows in set (0.00 sec) 

Most importantly, 1. row doesn't use any index and considers 200k+ rows. Bad! Execution of this query took 0.7s wheres both subqueries are in the milliseconds.

Answer 2

Use VIEW. The same classes can be mapped to different tables/views using entity name, so you won't even have much of a duplication. Being there, done that, works OK.

Plain JDBC has another hidden problem: it's unaware of Hibernate session cache, so if something got cached till the end of the transaction and not flushed from Hibernate session, JDBC query won't find it. Could be very puzzling sometimes.