EclipseLink UNION in FROM

22 Views Asked by At

I want to create a UNION in a FROM expression using EclipseLink. For simplicity I have an over-trivial example that actually can better be expressed by a WHERE clause.

select
  *
from 
  (
    select * from person where id < 10
    union
    select * from person where id > 20
  )
order by
  id

I know that JPA 2 does not support a UNION in a FROM clause. According to this EclipseLink supports UNION. And here are some examples how to do it in (extended) JPQL.

SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2

Furthermore I found some more examples how to to mix standard JPA queries with EclipseLink extensions like SELECT e FROM Employee e WHERE e.lastName REGEXP '^Dr\.*'. There is also an example how to mix Criteria API with these extensions.

JpaCriteriaBuilder cb = (JpaCriteriaBuilder)em.getCriteriaBuilder();
CriteriaQuery query = cb.createQuery(Employee.class);
Root emp = query.from(Employee.class);
query.where(cb.fromExpression(cb.toExpression(emp).get("firstName").regexp("^Dr\.*")));

Because of user specific ordering and filtering I need to create a dynamic query with an API rather than use some fixed JPQL.

I had a look into the JpaCriteriaBuilder class and find it hard to understand what exactly I can do with the few additional methods. While the above example seemed pretty comprehensible, I have no idea how to implement a more complex scenario like the UNION example at the beginning.

I am even not sure if such a statement is possible in the "mixing" of JPA Criteria API and EclipseLinks extensions. I also found an EclipseLink API with classes like ReportQuery, JPAQueryBuilder and ExpressionBuilder. I assume that I need to use these to implement the UNION statement. I struggle finding a decent documentation about this API or maybe some examples that go further than a select * from person where name = 'mary'.

Dose anyone know how to implement the above example in EclipseLink or can point me to a proper documentation?

0

There are 0 best solutions below