Unanswered JPA Criteria API Questions

Published: 2020-01-28
Updated: 2020-02-23
Web: https://fritzthecat-blog.blogspot.com/2020/01/unanswered-jpa-criteria-api-questions.html

Some APIs have left a bad taste in us. We changed the order of some calls, and suddenly nothing worked any more. We forgot to use the return of some method, and it unexpectedly threw an exception.

The JPA Criteria API is relatively mature concerning such problems. Nevertheless I would like to capture the answers to some questions that left me with some uncertainty when I started to work with that API.

Questions

1. Do I have to call select(), where(), orderBy(), ... in the same order as an SQL query would require, or can I call them in any order?


2. All these calls return a CriteriaQuery object, do I have to use that return for further actions, or can I use the initial instance for all subsequent calls?


3. When I call select(), where(), orderBy(), ... a second time, would they overwrite any preceding call, or add to them?

Due to unknown communication problems, developers are used to trying out everything by themselves. So did I, here comes my test.

Answers

You find the entities used in this example in my recent article about JOIN-types.

Following is a query that joins two tables and uses select(), where(), orderBy() in an unusual order, and uses the return from last call as final query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

        final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        final CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        final Root<City> city = query.from(City.class);
        final Join<City,House> house = city.join("houses");
        
        final CriteriaQuery<Object[]> completeQuery = query
            .orderBy(cb.asc(city.get("name")))
            .where(cb.like(cb.upper(city.get("name")), "%A%"))
            .multiselect(city.get("name"), house.get("name"));
        // groupBy(), having(), select(), ... all return the same query,
        // so there is no need for capturing the last returned completeQuery!
        
        return entityManager.createQuery(completeQuery).getResultList();

It is not necessary to use the query returned by the calls to CriteriaQuery, like I do here with completeQuery. All methods return the very query instance they were called on, this programming style is called fluent interface.

Also it is not necessary to keep the calls in a specific order. Following query delivers exactly the same result as the first one, although implemented in a different way:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

        final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        final CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        final Root<City> city = query.from(City.class);
        final Join<City,House> house = city.join("houses");
        
        query.multiselect(house.get("name"), city.get("name"));
        query.multiselect(city.get("name"), house.get("name"));
        // this overwrites the preceding multiselect() call
        query.where(cb.like(cb.upper(city.get("name")), "%A%"));
        query.orderBy(cb.asc(city.get("name")));
        
        return entityManager.createQuery(query).getResultList();

In this second example, on line 6, I call multiselect() for selecting several fields instead of an object. On line 7, I call the same method again, but with different parameters. This demonstrates a problem with fluent interface: do subsequent calls replace any preceding call, or do they merge into it, or would they cause an exception? As you can see by the inline comment, they replace. This is also documented in JavaDoc. Thus several calls to the same method of CriteriaQuery do not make sense, because they overwrite each other.

Rules of thumb:

• Get a CriteriaBuilder from EntityManager


• Fetch a CriteriaQuery with a result-type from CriteriaBuilder


• Call from() with a root-type on the CriteriaQuery to get a query-root, used to name attributes in where() and others
  
  
  • Optionally the same with join()


• Then call select() or multiselect(), where(), orderBy(), groupBy(), having(), either one-by-one or in fluent interface style, in any order, but each just once


• Finally wrap the CriteriaQuery into a TypedQuery using the EntityManager, and actually read some result from database.

Conclusion

Key to understanding the API is that calls to CriteriaQuery just build a query, they do not yet read anything from database. Reading is done by the final entityManager.createQuery(criteriaQuery).getResultList() call.

ɔ⃝ Fritz Ritzberger, 2020-01-28