How to Write a JPA Criteria Query

Step 1: Read the requirements

For each language, find the number of distinct actors who played in films in that language.

Step 2: Write simple CriteriaQuery

CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = qb.createTupleQuery();
Root<Film> filmRoot = q.from(Film.class);
Join<Film, Language> filmJoin = filmRoot.join(Film_.language);
Path<String> languagePath = filmJoin.get(Language_.name);
SetJoin<Film, Actor> actorJoin = filmRoot.join(Film_.actors);
Path<Integer> actorPath = actorJoin.get(Actor_.actorId);
Expression<Long> count = qb.countDistinct(actorPath);
q = q.multiselect(languagePath, count).groupBy(languagePath);
TypedQuery<Tuple> typed = em.createQuery(q);

for (Tuple tuple : typed.getResultList())
    System.out.println(tuple.get(languagePath) 
                  + " (" + tuple.get(count) + " actors)");
		

Step 3: Mentally process

Step 4: Write in jOOQ, instead

for (var rec :
    ctx.select(
          LANGUAGE.NAME, 
          countDistinct(FILM_ACTOR.ACTORS_ACTORID).as("c"))
       .from(LANGUAGE)
       .join(FILM).on(FILM.LANGUAGE_LANGUAGEID.eq(LANGUAGE.LANGUAGEID))
       .join(FILM_ACTOR).on(FILM.FILMID.eq(FILM_ACTOR.FILMS_FILMID))
       .groupBy(LANGUAGE.NAME))
    System.out.println(rec.get(LANGUAGE.NAME) 
                  + " (" + rec.get("c") + " actors)");
		

Step 5: Never look back

The jOOQ Logo