Is it possible to perform a One-To-Many foreign-key JOIN with criteria-builder?

Question

Assume the following two database models:

Owner: id, name

Pet: id, name, owner

where Pet.owner is a foreign key to the Owner's id

---

Can you query all users with a name similar to Denise that have pets with a name similar to Bella, using the criteria builder join?

The SQL query would look similar to:

SELECT o FROM Owner o JOIN Pet p ON o.id = p.owner
WHERE o.name LIKE 'Denise' AND p.name LIKE 'Bella'

Sorting options will also be used, so it is only possible to use the criteria queries, instead of creating a typed query directly (and setting parameters).

Answer 1

For sure it is. You can do with Criteria API everything you can do with JPQL.

Read a tutorial: https://www.objectdb.com/java/jpa/query/criteria

Your query will look like this:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();

CriteriaQuery<Owner> criteriaQuery = criteriaBuilder.createQuery(Owner.class);

Root<Pet> pet = criteriaQuery.from(Pet.class);

Join<Pet, Owner> owner = pet.join(Pet_.owner);
criteriaQuery
        .select(owner)
        .where(criteriaBuilder
            .and(
                 criteriaBuilder.like(owner.get(Owner_.name), "Denise"), 
                 criteriaBuilder.like(pet.get(Pet_.name), "Bella")));

List<Owner> resultList = em.createQuery(criteriaQuery).getResultList();