CriteriaBuilder join two tables with a custom condition

Question

I want to write this SQL query

SELECT *  
FROM A
LEFT OUTER JOIN B
ON A.IDRESOURCE=B.IDRESOURCE AND B.IDLANGUAGE=22;

with the JPA Criteria Builder. I wrote the first part of the join simply with:

CriteriaQuery<A> searchQuery = criteriaBuilder.createQuery(A.class);
Root<A> aRoot = searchQuery.from(A.class);
Join<A, B> bJoin= aRoot.join("mappedB", JoinType.LEFT);

but I don't know how to implement the condition B.IDLANGUAGE=22.

Is there any way to accomplish this in Criteria Builder?

Answer 1

Use JPA 2.1 ON to specify it in the JOIN part, which is not the same as in the WHERE

CriteriaQuery<A> searchQuery = criteriaBuilder.createQuery(A.class);
Root<A> aRoot = searchQuery.from(A.class);
Join<A, B> bJoin= aRoot.join("mappedB", JoinType.LEFT);
bJoin.on(criteriaBuilder.equal(bJoin.get("idLanguage"), 22));

Answer 2

Honestly, after spending few days with this problem, we have decided to solve it via database views. So there is a little alternative solution:

1. Create view that joins needed tables together according wanted conditions
2. Create new entity, representing data from this view and annotate it @Immutable, to make it read only
3. Create criteria builder only above this one entity

Also be aware that you entity needs to have UNIQUE Id, so if you use i.e. LEFT JOINs in you view, it might happen that there will be not unique id, and hibernate will have problems with that. So create new Id in you view as compound key, or random generated value.

It is a little workaround solutions, but it worked well for us.