UNION to JPA Query

SQL supports UNION, but JPA 2.0 JPQL does not. Most unions can be done in terms of joins, but some can not, and some are more difficult to express using joins.

EclipseLink supports UNION.


Depending on the case, one could use sub queries, something like:

select e
from Entity e
where e.id in
(
  select e.id
  from Entity2 e2
       join e2.entity e
  where e2.someProperty = 'value'
)
      or e.id in
(
  select e.id
  from Entity3 e3
       join e3.entity e
  where e3.someProperty = 'value2'
)

One thing just comes to my mind (searching for the exact same problem):

Perform two different JPA queries on the same entity mapping and simply add the objects of the second result to the list (or set to avoid duplicates) of the first result.

That way you get the same effect as with a UNION, the difference being that you use two SQL statements instead of one. But actually, I would expect that to perform just as good as issueing one UNION statement.


write native query (set it true , default its false) - ex.

String findQuery = "select xyz from abc union select abc from def"
@Query(value = findQuery, nativeQuery = true)
//method

using EntityManager.createNativeQuery(...); It's allow you use UNION