Hibernate Union alternatives

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"unionfrom 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 uniquenessSet<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: ALLpossible_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: NULLpossible_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_subquerypossible_keys: PRIMARY,name,sortname          key: PRIMARY      key_len: 4          ref: func         rows: 1        Extra: Using where3 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.

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.

I have to agree with Vladimir. I too looked into using UNION in HQL and couldn't find a way around it. The odd thing was that I could find (in the Hibernate FAQ) that UNION is unsupported, bug reports pertaining to UNION marked 'fixed', newsgroups of people saying that the statements would be truncated at UNION, and other newsgroups of people reporting it works fine...After a day of mucking with it, I ended up porting my HQL back to plain SQL, but doing it in a View in the database would be a good option. In my case, parts of the query were dynamically generated, so I had to build the SQL in the code instead.