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