What you need to know before writing a query with the JPA Criteria API and Hibernate

TL;DR : JPA (2.*) does not support the "UNION" sql operation. JPA does not require a persistence provider to support Right outer joins or Right outer fetch joins, to be compliant with its API. Hibernate (5.*) does not support Right outer joins or Right outer fetch joins.

Last week I bumped heads with JPA (2.2) and Hibernate (5.4). I had to write a search query on some tangled datastructures.  I tend to write queries out on paper first, for clarity. Here are some takeaways, while I wasted some scrap paper on the iterations.

State of the UNION in JPA

I ended up with a union of 3 queries. The moment that I wanted to get dirty with JPA to create a CriteriaQuery I hit the first snag. JPA 2.0 does not support the sql "UNION" operation.

One does not simply join with JPA and Hibernate

The next step was to rewrite the set of unions to a set of joins. 5 (+ 2 shared) Left joins and 4 right outer joins on I had one single unified query. I quickly glanced in my IDE over the JoinType.class and saw that RIGHT outer join was a possible JoinType. Little did I know...

I wrote a set of integration tests for one left join, and a right join and prepared database entities to be loaded on startup of each test. TDD for the win!

The first left join passed with flying colors. Then I naively wrote the right join, all syntactically correct and hit the play button... COMPUTER SAYS NO! (NotSupportedException, Right join is not supported...).

Turns out: JPA does not require a persistence provider to support Right outer joins or Right outer fetch joins, to be compliant with its API. Hibernate (the target persistence provider) does not support Right outer joins or Right outer fetch joins.

Just one possible solution

So you can forget about unions and right joins. The magic formula was a cross join on results from subqueries, or two separate queries.

I ended up with something like this:

SELECT A.id 
FROM A 
WHERE A.id IN (Sub1 OR Sub2 Or Sub3);