JPA criteria queries

Querying database with JPQL queries is quite simple. But a main drawback of this solution is its static character. To create dynamic queries, a more adapted solution exists, JPA Criteria.

In one of previous articles, we discovered the static metamodel in JPA. It can be used to generate a dynamic JPA queries, based on JPA's Criteria API. The first chapter of this article will explain the Criteria API. The next one will cover more technical part by describing important interfaces of the API. At the last chapter we'll see how to use Criteria API to construct dynamical queries.

What is JPA Criteria API ?

We can simplify the definition by telling that Criteria API is a programmatic way of preparing JPA database queries. Instead of concatenating Strings, we can use Java's objects and create queries in another manner. This another way has some advantages compared with text queries:

• type-safety - Criteria API is based on static metamodels. Thanks to it, the queries prepared with this API know what type is expected for every parameter. This type-safety avoids to launch the queries with inappropriated parameters. String-based queries don't do that and you can pass every object you want to a parameter:

  @Test
  public void testBadParamBinding() {
    EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("sampleUnit");
    EntityManager entityManager = emFactory.createEntityManager();
  
    // first remove all peans and add a new pea to could execute query constructed with CriteriaBuilder
    Product pea = new Product();
    pea.setName("grapes");
    entityManager.persist(pea);
  
    // check if was added
    Query queryOk = entityManager.createQuery("SELECT p FROM Product p WHERE p.name = 'grapes'");
    Product grapes = (Product) queryOk.getSingleResult();
    assertTrue("Grapes should be found in the database but they weren't", 
      grapes != null);
  
    // prove the "none type-safety" of JPQL queries
    Query query = entityManager.createQuery("SELECT p FROM Product p WHERE p.name = :name")
      .setParameter("name", new ArrayList<String>());
    Product product = null;
    assertTrue("Query object can't be null here", query != null);
    product = (Product) query.getSingleResult();
    assertTrue("Product shouldn't be retreived in the database because of ArrayList<String> parameter", 
      product == null);
  }
  

  As we expected, the test passes. We insert a new product called 'grapes' and check if it was correctly inserted. At the end we execute the same query as previously, but instead of putting "grapes" in the condition, we put an instance of ArrayList<String>. The query won't work. And this kind of errors is very often difficult to find because it's not accompanied with exceptions or 500 pages in front side. So, JPQL queries (previously called String-based queries) aren't type-safe, unlike queries based on Criteria API and static metamodels which fails on compiled time with bad object types.
• syntax error on compile time - in JPQL queries you can simply make a mistake, for example by writing "FOM" instead of "FROM" in SELECT query. This error won't be detected at compile time but only at runtime. With Criteria API this kind of problem is detected at compile time because the technique is based on Java's methods (strict interpretation) and not on freely writen queries. Once again, let's see it in test sample (only JPQL part):

  @Test
  public void testTypoError() {
    EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("sampleUnit");
    EntityManager entityManager = emFactory.createEntityManager();
  
    boolean wasQse = false;
    try {
      Query queryOk = entityManager.createQuery("SELECT FROM Product p WHERE p.name = :name").setParameter("name", "grapes");
      Product grapes = (Product) queryOk.getSingleResult();
    } catch (QuerySyntaxException qse) {
      wasQse = true;
    } catch (Exception e) {
      wasQse = e.getCause().getClass() == QuerySyntaxException.class; 
    }
    assertTrue("Query is bad written (without p between 'SELECT' and 'FROM') and it couldn't be executed, but it was", 
      wasQse);
  }
  

  The query is bad written because a small 'p' letter misses between SELECT and FROM. But the code compiles well and only at execution it fails. It's very probable that this kind of error can, without correct test covering, pass to production environment.
• code clarity in conditioning queries - some queries are conditioned by external decisions. For example, imagine a form used to launch advanced search on your site. JPQL query illustrating user choices can be difficult to understand and the bugs can be introduced in some search scenarios (see previous point of this list). The same query generated with Criteria API is more clear and the syntax problems (maybe without association ones) are detected at compile time. You can see the complexity of JPQL dynamic query on below sample:

  Date addedDate = getAddedDate(request);
  int priceFrom = getPriceFrom(request);
  int priceTo = getPriceTo(request);
  String category = getCategory(request);
  String name = getName(request);
  
  String query = "SELECT p ";
  if (categories.size() > 0) {
          query += " , c";
  }
  query += " FROM Product p";
  if (!category.equals("")) {
          query += " JOIN Category c ";
  }
  boolean addAnd = false;
  query += " WHERE ";
  if (!name.equals("")) {
    query += " p.name = :name";
    addAnd = true;
  }
  if (priceFrom > 0) {
    if (addAnd) {
      query += " AND ";
    }
    query += " p.price >= :priceFrom";
    addAnd = true;
  }
  if (priceTo > 0) {
    if (addAnd) {
      query += " AND ";
    }
    query += " p.price <= :priceTo";
    addAnd = true;
  }
  if (!category.equals("")) {
    if (addAnd) {
      query += " AND ";
    }
    query += " c.name = :categoryName";
    addAnd = true;
  }
  
  Query queryObj = entityManager.createQuery(query);
  
  // bind parameters
  if (!name.equals("")) {
    queryObj.setParameter("name", name);
  }
  if (priceFrom > 0) {
    queryObj.setParameter("priceFrom", priceFrom);
  }
  if (priceTo > 0) {
    queryObj.setParameter("priceTo", priceTo);
  }
  if (!category.equals("")) {
    queryObj.setParameter("categoryName", category);
  }
  

  This query is pretty long. In additionally, at the end we repeat some checks made at the begin. Criteria API isn't a miraculous solution, but it can allow to write the same query in more simple way. We'll see it after.

But they are not only advantages of using Criteria queries. The drawbacks exist too. Firstly, they can't be pushed in parse cache. Secondly, in the most of cases, Criteria queries are also verbose. Imagine the case of a simple SELECT query which we can write in one String expression. To do the same, we will need several lines in Criteria API.

Components of JPA Criteria API

JPA Criteria queries look like object graph where each part represents more specific part of the query. For example, the root will represent the table used in FROM clause while the other nodes will represent associated tables. A keyword linked with Criteria JPA's components appeared here. It's root, and more precisely, javax.persistence.criteria.Root implementations. As we can read in the JavaDoc, Root references always an entity and points to FROM part of query.

Another important component of Criteria API is javax.persistence.criteria.CriteriaBuilder. The implementations of this interface are used to generated the queries, add conditions (as SQL's WHERE, AND, ==) or orderings (SQL's ASC or DESC for ORDER BY clauses) to queries.

Interfaces used to define queries are javax.persistence.criteria.AbstractQuery and javax.persistence.criteria.CriteriaQuery, where the first one is a superinterface for the second. By implementing them, we can create an object that can be used as a parameter for EntityManager's createQuery() method. The implementation of CriteriaQuery has to have a set of root entities. It means that you can't execute a query which doesn't generate a Root object. If you try to do that, following exception should be returned:

java.lang.IllegalStateException: No criteria query roots were specified
  at org.hibernate.ejb.criteria.CriteriaQueryImpl.validate(CriteriaQueryImpl.java:303)
  at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:146)
  at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:587)
  at com.waitingforcode.jpa.test.CriteriaTest.test(CriteriaTest.java:143)

Code samples with JPA Criteria API

After this introduction we can finally pass to code samples. We'll focus here on basic cases as selecting from a single table. In our sample, we'll extend previously saw Product entity by adding complementary fields: price, added date and color. The sample test code will show how to construct a simple query based on Criteria API and how to create a more complicated dynamic query, based on user choice. Let's start by the first one:

@Test
public void simpleSelect() {
  EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("sampleUnit");
  EntityManager entityManager = emFactory.createEntityManager();
  Product product = new Product();
  product.setName("radish");
  entityManager.persist(product);
  
  // check if was correctly inserted
  assertTrue("Radish wasn't correctly inserted to the database", 
    product.getId() > 0);
  
  // make simple query with Criteria API
  CriteriaBuilder builder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class);
  Root<Product> criteriaRadish = criteriaQuery.from(Product.class);
  criteriaQuery.where(builder.equal(criteriaRadish.get(Product_.name), "radish"));
  TypedQuery<Product> typedQuery = entityManager.createQuery(criteriaQuery);
  Product radishDb = typedQuery.getSingleResult();
  assertTrue("Pea should be found in the database but it wasn't", radishDb != null);
  assertTrue("Retreived product should be a 'radish' but was '"+radishDb.getName()+"'", 
    radishDb.getName().equals("radish"));
}

This case is quite simple. At the begin, we prepare the ground by deleting all products called "radish" and by inserting only one with this name. After we check if the product was added into database. Next part concerns our Criteria problematic. Depart point for the Criteria queries is CriteriaBuilder which creates a CriteriaQuery. After that, CriteriaQuery's instance is used to generate the root element of the graph. In our case, the root is the single present element. After we call an where clause by using Product's static metamodel. Thanks to it, we avoid to type errors in the query. The last step consists on create TypedQuery instance by invoking createQuery method and getting the product through standard getSingleResult method.

The previous example was quite normal. Now we'll try to implement a more dynamic query, based on user choices. But before writing test case, we need to upgrade Product's entity and its static model by adding price, added date and color fields:

// Product.java
@Entity
@Table(name="products")
public class Product {

  private Integer id;
  private String name;
  private String color;
  private double price;
  private Date addedDate;
  
  @Id
  @GeneratedValue(strategy = IDENTITY)
  @Column(name="id")
  public Integer getId() {
    return this.id;
  }
  
  @Column(name="name")
  @NotEmpty
  public String getName() {
    return this.name;
  }

  @Column(name="color")
  public String getColor() {
    return this.color;
  }
  
  @Column(name="price")
  public double getPrice() {
    return this.price;
  }
  
  @Column(name="added_date")
  @Temporal(TIMESTAMP)
  public Date getAddedDate() {
    return this.addedDate;
  }
  
  public void setId(Integer id) {
    this.id = id;
  }
  
  public void setName(String name) {
    this.name = name;
  }

  public void setColor(String color) {
    this.color = color;
  }

  public void setPrice(double price) {
    this.price = price;
  }

  public void setAddedDate(Date addedDate) {
    this.addedDate = addedDate;
  }

  @Override
  public String toString() {
    return "Product {"+this.name+"}";
  }
}

// Product_.java
@StaticMetamodel(Product.class)
public class Product_ {
  public static volatile SingularAttribute<Product, Integer> id;
  public static volatile SingularAttribute<Product, String> name;
  public static volatile SingularAttribute<Product, String> color;
  public static volatile SingularAttribute<Product, Double> price;
  public static volatile SingularAttribute<Product, Date> addedDate; 
}

We won't discuss about previous code. It's a simple entity with even simpler static model. Instead, we'll see how to construct a multi-criteria query for them:

@Test
public void dynamicSelect() {
  EntityManagerFactory emFactory = Persistence.createEntityManagerFactory("sampleUnit");
  assertTrue("Entity manager factory can't be null", emFactory != null);
  EntityManager entityManager = emFactory.createEntityManager();
  // add coffee
  Product coffee = new Product();
  coffee.setName("coffee");
  coffee.setAddedDate(new Date());
  coffee.setPrice(40.5d);
  coffee.setColor("black");
  entityManager.persist(coffee);
  // add tea
  Product tea = new Product();
  tea.setName("tea");
  tea.setAddedDate(new Date());
  tea.setPrice(45.5d);
  tea.setColor("brown");
  entityManager.persist(tea);
  // add milk
  Product milk = new Product();
  milk.setName("milk");
  milk.setAddedDate(new Date());
  milk.setPrice(31.5d);
  milk.setColor("white");
  entityManager.persist(milk);
  
  // construct criteria query based on given configuration
  double priceFrom = 30d;
  double priceTo = 50d;
  String color = null;
  String name = null;
  String likeName = "e";
  Date addedDate = null;  
  
  CriteriaBuilder builder = entityManager.getCriteriaBuilder();
  CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class);
  Root<Product> criteriaProducts = criteriaQuery.from(Product.class);
  List<Predicate> conditions = new ArrayList<Predicate>();
  if (priceFrom > 0) {
    conditions.add(builder.ge(criteriaProducts.get(Product_.price), priceFrom));
  }
  if (priceTo > 0) {
    conditions.add(builder.le(criteriaProducts.get(Product_.price), priceTo));
  }
  if (color != null && !color.equals("")) {
    conditions.add(builder.equal(criteriaProducts.get(Product_.color), color));
  }
  if (name != null && !name.equals("")) {
    conditions.add(builder.equal(criteriaProducts.get(Product_.name), name));
  }
  else if (likeName != null && !likeName.equals("")) {
    conditions.add(builder.like(criteriaProducts.get(Product_.name), "%"+likeName+"%"));
  }
  if (addedDate != null) {
    conditions.add(builder.equal(criteriaProducts.get(Product_.addedDate), addedDate));
  }
  
  // get results
  criteriaQuery.where(builder.and(conditions.toArray(new Predicate[conditions.size()])));
  TypedQuery<Product> typedQuery = entityManager.createQuery(criteriaQuery);
  List<Product> products = typedQuery.getResultList();
  assertTrue("Any product found in the database", products != null);
  assertTrue("The query should find 2 products but "+products.size() + " were found instead ("+products+")", 
    products.size() == 2);
}