JPQL Versus The Criteria API

January 13, 2019

This article compares and contrasts two Java Persistence Architecture (JPA) techniques for building queries. The Java Persistence Query Language (JPQL) is a SQL-like language that expresses queries in Strings. The Criteria API uses Java code with a builder syntax. Generally, I write JPQL, but sometimes the parameters, logic, and String concatenation makes less readable code. In that case, I opt for the Criteria API.

The Query

Two implementations of an AccountEntity lookup will be presented. The lookup findAccountsByName will accept a name parameter that can appear in any of three fields: firstName, lastName, gamertag. The search will be case insensitive and will accept wildcarded values. Additionally, the AccountEntity supports a logical delete whereby a deleted flag is updated rather than a record removed. By default, the lookup will filter logically deleted records.

The AccountEntity class is presented below.

package securitydemo.ejb;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Version;

@Entity
public class AccountEntity {

    @Id @GeneratedValue
    private Long accountId;

    private String firstName;
    private String lastName;

    /**
     * gamertag is a unique account identifier that relates this record to the auth subsystem
     */
    private String gamertag;

    @Version
    private Integer version;

    /**
     * a flag supporting a logical delete
     */
    private Boolean deleted = false;

    public AccountEntity() {
    }

    public AccountEntity(String firstName, String lastName, String gamertag, Integer version) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.gamertag = gamertag;
        this.version = version;
    }

    public Long getAccountId() {
        return accountId;
    }

    public void setAccountId(Long accountId) {
        this.accountId = accountId;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getGamertag() {
        return gamertag;
    }

    public void setGamertag(String gamertag) {
        this.gamertag = gamertag;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public Boolean getDeleted() {
        return deleted;
    }

    public void setDeleted(Boolean deleted) {
        this.deleted = deleted;
    }

    @Override
    public String toString() {
        return "AccountEntity{" +
                "accountId=" + accountId +
                ", firstName='" + firstName + '\'' +
                ", lastName='" + lastName + '\'' +
                ", gamertag='" + gamertag + '\'' +
                ", version=" + version +
                ", deleted=" + deleted +
                '}';
    }
}

JPQL

The JPQL version forms a TypedQuery from a String that's built up in parts. The first part (SELECT + the LIKEs) is conditionally concatenated with a deleted equals clause. Note that the showDeleted parameter is NOT a parameter intended for the query. Rather, if showDeleted is false, the entire clause will be skipped. Finally, a common ORDER BY is concatenated to the result which may or may not included the deleted=:del WHERE expression.

	
@PersistenceContext
private EntityManager em;
	
public List<AccountEntity> findAccountsByName(String name, Boolean showDeleted) {

	String jpql = "SELECT ae FROM AccountEntity ae " +
			"WHERE (upper(ae.firstName) LIKE upper(:fn) OR upper(ae.lastName) " +
			"LIKE upper(:ln) OR upper(ae.gamertag) LIKE upper(:gt)) ";

	if( !showDeleted ) {
		jpql += " AND ae.deleted = :del";  // add a clause to hide deleted=true records
	}

	jpql += " ORDER BY ae.gamertag ASC";

	TypedQuery<AccountEntity> q = em.createQuery(
			jpql,
			AccountEntity.class
	);
	q.setParameter("fn", name);
	q.setParameter("ln", name);
	q.setParameter("gt", name);

	if ( !showDeleted ) {
		q.setParameter( "del", false);  // constant parameterized for demo purposes
	}

	return q.getResultList();
}	
	

With the TypedQuery formed, the parameters are added. The parameter involving "del" is cordoned off with logic since the String "jpql" might not have a deleted parameter to substitute. A resultList is returned.

While this twenty line function isn't unreadable, adding more parameters and logic can become problematic. The JPQL String is fragmented requiring the reader to concatenate them while reading. Or, the debugger needs to be consulting to make sure there aren't errant characters in the JPQL or the concatenated result is still valid. One common problem is missing whitespace (ex, "FROM AccountEntity aeWHERE").

Another problem is that the JPQL becomes disassociated with the setParameter() calls. You have to look in two places to make sure that the showDeleted parameter is handled correctly.

IDEs can help. If you register your persistence.xml with IntelliJ, it will make sure that fields such as "firstName" or "lastName" exist. If I mistakenly put "fname", the IDE warns me. Additionally, keywords are highlighted only if valid. If I replace "SELECT" with "SEELCT", the latter wouldn't be highlighted and would standout alongside the FROM and WHERE keywords.

Despite the shortcomings of JPQL, I use that for most of my queries. Because of the strong SQL heritage of many developers, this is a comfortable technique.

Criteria API

The Criteria API dispenses with the Strings and works exclusively with Java code. In the example below, Predicate objects are formed and linked together with a builder notation rather than String concatenation. Additionally, the queries are type safe because of a Criteria API feature called the Metamodel.

@PersistenceContext
private EntityManager em;

public List<AccountEntity> findAccountsByName(String name, Boolean showDeleted) {

	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<AccountEntity> cq = cb.createQuery(AccountEntity.class);
	Root<AccountEntity> ae = cq.from(AccountEntity.class);

	cq.select(ae);
	cq.orderBy(cb.asc(ae.get(AccountEntity_.gamertag)));

	Predicate names = cb.or(
			cb.like(cb.upper(ae.get("firstName")), name.toUpperCase()),  // not using metamodel for demo
			cb.like(cb.upper(ae.get(AccountEntity_.lastName)), name.toUpperCase()),
			cb.like(cb.upper(ae.get(AccountEntity_.gamertag)), name.toUpperCase())
	);

	if( showDeleted ) {
		cq.where(names);  // no clause involving deleted; both true and false returned
	} else {
		cq.where(
			cb.and(
				names,
				cb.equal(ae.get(AccountEntity_.deleted), false))
		);
   }

	TypedQuery<AccountEntity> q = em.createQuery(cq);

	return q.getResultList();
}

After some setup creating CriteriaBuilder and CriteriaQuery objects, the shared SELECT and ORDER BY are applied with the select() and orderBy() functions. Next, an OR-ed list of LIKE Predicates are formed. These Predicates will be used in all forms of the query. Next, the showDeleted flag is consulted. If all records are to be shown, then the CriteriaQuery will contain only the OR-ed LIKE statements. Otherwise, the CriteriaQuery will have the LIKEs AND-ed to a deleted Predicate.

Metamodel

The Metamodel is a set of classes generated by JPA to complement your @Entity classes. In this case, AccountEntity.java is a class that I created. JPA generated a similarly-named AccountEntity_.java class in the same package. If you're using Hibernate as with the WildFly app server, adding this directive to your Maven project will trigger this code generation automatically.

<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-jpamodelgen</artifactId>
	<version>5.3.0.Final</version>
	<scope>provided</scope>
</dependency>

The first of the three LIKE Predicates does not use the Metamodel. Instead a String "firstName" is used in the CriteriaAPI. While syntactically and functionally valid, it does not provide any assurance that "firstName" is a valid field in AccountEntity. For instance, I can substitute "firstName" for "firstName1" and won't receive an error until I test it. The Metamodel helps this.

The remaining two LIKE Predicates pull from the generated AccountEntity_ object. If they do not specify a valid AccountEntity (no underscore) field, the compiler will throw a warning. Also, code completion is available.

Once the CriteriaQuery is set up, a TypedQuery is produced from the EntityManager and a resultList is returned.

Usually, I write JPQL. However, when there are a lot of parameters or a lot of logic, I opt for the Criteria API. One rule of thumb you might follow is tracking how closely the resulting query follows from the API spec or the UI. If the resulting query is driven from a mixture of checkboxes, tables, and drop-downs, Criteria API is probably the better choice.

---