10

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
  • 3,006
  • 5
  • 35
  • 73
  • 1
    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

3 Answers3

10

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
  • 11,383
  • 3
  • 34
  • 29
1

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.

Smeki
  • 21
  • 4
-2

Use where and CriteriaBuilder.equal.

em.select(aRoot).where( criteriaBuilder.equal(bJoin.get("IdLanguage"), 22));
K.Nicholas
  • 10,956
  • 4
  • 46
  • 66
  • 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