How to use the JPA Criteria API in a subquery on an ElementCollection

Imagine the following entities:

class Category {
    private String softKey;

class Item {
    private Collection<String> categorySoftKeys;

If we now would want to load only the non-empty categories, our JPQL would look like this:

EntityManager em = getEntityManager();
List<Category> categories = em.createQuery("select c " + 
    "from Category c " + 
    "where softKey in (select i.categorySoftKeys from Item i)", Category.class)

How do we do that in Hibernate using the JPA criteria API?

// Create the outer query for categories
EntityManager em = getEntityManager();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Category> query = builder.createQuery(Category.class);
Root<Category> from = query.from(Category.class);

// Create the subquery for items using a category
Subquery<String> subquery = query.subquery(String.class);
Root<Item> subfrom = subquery.from(Item.class);;

// Apply the subquery to the query

// Load the categories
TypedQuery<Category> q = em.createQuery(query);
List<Category> categories = q.getResultList();

Both solutions return the same result. Note that the first one is way less code, while the second one can properly react to if and provide (partial) compile time validation when used in conjunction with the hibernate metamodel generator.

The key to making this work using the criteria API is to understand the difference between Root#get() and Root#join(). The former one will just create a new path, while the later one performs an inner join. The scenario above would not work if you would have used:

In this case hibernate generates invalid SQL.

Copyright ©, 2002-2020.