NOT EXISTS subquery with JPA 2.0 CriteriaBuilder

Dec 13, 2010   #hibernate  #java  #jpa 

Assume you have a data model where you have a n:1 relationship from addresses to persons. The relationship is only navigable from addresses to persons (just as an example, please don’t think twice if this is good data modelling here).

Now you want to find all persons that do not have any addresses. In SQL, you would use a RIGHT JOIN in this case.

In JPA 2.0 the implementation of RIGHT JOINS is optional for the persistence provider. The very popular ORM Hibernate does not support RIGHT JOIN operations. In order to complete the task described above, you have to use a subquery. Here’s how to do it:

CriteriaBuilder criteriaBuilder = mEntityManager.getCriteriaBuilder();

CriteriaQuery query = criteriaBuilder.createQuery(Person.class);
Root person = query.from(Person.class);
query.select(person);

Subquery subquery = query.subquery(Address.class);
Root subRootEntity = subquery.from(Address.class);
subquery.select(subRootEntity);

Predicate correlatePredicate = criteriaBuilder.equal(subRootEntity.get("Person"), person);
subquery.where(correlatePredicate);
query.where(criteriaBuilder.not(criteriaBuilder.exists(subquery)));

TypedQuery typedQuery = mEntityManager.createQuery(query);
List unreferencedPersons = typedQuery.getResultList();