JPA Data: optional values

When you need to query your model by optional values, JPA Data mostly fails. It works in a very narrow case, as described below, when the optional values are in the project of the class you’re searching for.

Let’s take the following simple model of a book. For the sake of simplification a Book can have only one author.

The actual JPA entities are as follows:

The Book entity:

// Imports left out for brevity.
@Entity
public class Book {
  @Id
  @GeneratedValue
  private int id;

  private String title;

  private int price;

  @OneToOne(cascade = CascadeType.ALL)
  private Author author;

  // Getters and setters left out for brevity.
}

The Author entity:

@Entity
public class Author {
  @Id
  @GeneratedValue
  private int id;

  private String name;
  // Getters and setters left out for brevity.
}

We can search books by the tuple (title, price, author). Where any of the tuple components can be empty.

If we were to write a JPA Data query we wouldn’t be able to pass Optionals as arguments, that’s not permitted. However, we can use null as sentinel and check the argument against null. This means that for each parameter that can be null we need to test whether it is null *or* equal to the actual member of the entity.

public interface BookRepository extends CrudRepository<Book, Integer> {
  @Query("select b from Book b where "
      + "(:price is null or b.price = :price) and "
      + "(:title is null or b.title = :title) and "
      + "(:authorName is null or b.author.name = :authorName)")
  List<Book> findByTitlePriceAuthor(String title, Integer price, String authorName);
}

However, the approach that I advise is to use JPA Typed Queries. That’s a bit more involved than JPA Data repository, but it adds some static typing. Plus it short-circuits parsing the JPA text query to transform it in a… Typed Query, so our code becomes more efficient.

Here’s the same query as below for (title, price, author), but as Typed Query:

public List<Book> findByTypedQuery(Optional<String> title, Optional<Integer> price,
    Optional<String> author) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

  CriteriaQuery<Book> query = criteriaBuilder.createQuery(Book.class);
  Root<Book> root = query.from(Book.class);
  List<Predicate> predicates = new ArrayList<>();
  if (title.isPresent())
    predicates.add(criteriaBuilder.equal(root.get("title"), title.get()));
  if (price.isPresent())
    predicates.add(criteriaBuilder.equal(root.get("price"), price.get()));
  if (author.isPresent())
    predicates.add(criteriaBuilder.equal(root.get("author").get("name"), author.get()));
  query.where(predicates.toArray(new Predicate[predicates.size()]));
  return entityManager.createQuery(query).getResultList();
}

All the code is available on github: https://github.com/ccosmin/jpa-optionalvalues.git.