8

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?

gvdm
  • 2,614
  • 4
  • 32
  • 64
  • what is mappedB – charu joshi Jan 29 '19 at 11:42
  • Hi @charu joshi. It's the attribute name. See the [javadoc](https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/From.html#join(java.lang.String,%20javax.persistence.criteria.JoinType)) – gvdm Jan 29 '19 at 14:23

2 Answers2

9

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));
Neil Stockton
  • 10,922
  • 3
  • 30
  • 28
-2

Use where and CriteriaBuilder.equal.

em.select(aRoot).where( criteriaBuilder.equal(bJoin.get("IdLanguage"), 22));
K.Nicholas
  • 9,789
  • 4
  • 40
  • 57
  • Hello @Nicholas. Does the EM's Where work after the Join? My problem is that the condition must be run as a Join condition, not as a where contition – gvdm Apr 04 '16 at 15:53
  • Should do, I've used this plenty. It knows the join because you are using `bJoin` in the where clause. – K.Nicholas Apr 04 '16 at 15:55
  • This is semantically different to specifying the predicates in the join condition. – Leukipp Jul 03 '19 at 22:19