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

Use VIEW. The same classes can be mapped to different tables/views using entuty 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 Hib session, JDBC query won't find it. Could be very puzzling sometimes.

Answer 2

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.

Answer 3

A view is a better approach but since hql typically returns a List or Set... you can do list_1.addAll(list_2). Totally sucks compared to a union but should work.

Answer 4

I too have been through this pain - if the query is dynamically generated (e.g. Hibernate Criteria) then I couldn't find a practical way to do it.

The good news for me was that I was only investigating union to solve a performance problem when using an 'or' in an Oracle database.

The solution Patrick posted (combining the results programmatically using a set) while ugly (especially since I wanted to do results paging as well) was adequate for me.

Answer 5

Generally - i have implemented some fixes for hibernate to support unions in subselect for criteria logic. I'll add it to hibernate's forum today...

Answer 6

Perhaps I had a more straight-forward problem to solve. My 'for instance' was in JPA with Hibernate as the JPA provider.

I split the three selects (two in a second case) into multiple select and combined the collections returned myself, effectively replacing a 'union all'.

Answer 7

You could us 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 the 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.