Java Persistence Query Language
All of our examples that obtain entities from the database so far have conveniently assumed that the primary key for the entity is known ahead of time. We all know that frequently this is not the case. Whenever we need to search for an entity by a field other than the entity's primary key, we must use the Java Persistence Query Language (JPQL).
JPQL is a SQL-like language used for retrieving, updating, and deleting entities in a database. The following example illustrates how to use JPQL to retrieve a subset of states from the US_STATES table in the CUSTOMERDB database:
package net.ensode.javaee8book.jpql.namedbean;
import java.util.List;
import javax.enterprise.context.RequestScoped;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import net.ensode.javaee8book.jpql.entity.UsState;
@Named
@RequestScoped
public class SelectQueryDemoBean {
@PersistenceContext
private EntityManager entityManager;
private Stream<UsState> matchingStatesStream;
private List<UsState> matchingStatesList;
public String findStates() {
String retVal = "confirmation";
try {
Query query = entityManager
.createQuery(
"SELECT s FROM UsState s WHERE s.usStateNm "
+ "LIKE :name");
query.setParameter("name", "New%");
matchingStatesStream = query.getResultStream();
if (matchingStatesStream != null) {
matchingStatesList =
matchingStatesStream.collect(Collectors.toList());
}
} catch (Exception e) {
retVal = "error";
e.printStackTrace();
}
return retVal;
}
public List<UsState> getMatchingStatesList() {
return matchingStatesList;
}
public void setMatchingStatesList(List<UsState> matchingStatesList) {
this.matchingStatesList = matchingStatesList;
}
}
The preceding code invokes the EntityManager.createQuery() method, passing a String containing a JPQL query as a parameter. This method returns an instance of javax.persistence.Query. The query retrieves all UsState entities whose name starts with the word New.
As can be seen in the preceding code, JPQL is similar to SQL; however, there are some differences that may confuse readers with some knowledge of SQL. The equivalent SQL code for the query in the code would be:
SELECT * from US_STATES s where s.US_STATE_NM like 'New%'
The first difference between JPQL and SQL is that in JPQL we always reference entity names, wherein SQL table names are referenced. The s after the entity name in the JPQL query is an alias for the entity. Table aliases are optional in SQL, but entity aliases are required in JPQL. Keeping these differences in mind, the JPQL query should now be a lot less confusing.
The :name in the query is a named parameter; named parameters are meant to be substituted with actual values. This is done by invoking the setParameter() method in the instance of javax.persistence.Query returned by the call to EntityManager.createQuery(). A JPQL query can have multiple named parameters.
To actually run the query and retrieve the entities from the database, the getResultList() method must be invoked in the instance of javax.persistence.Query obtained from EntityManager.createQuery(). This method returns an instance of a class implementing the java.util.List interface; this list contains the entities matching the query criteria. If no entities match the criteria, then an empty list is returned.
If we are certain that the query will return exactly one entity, then the getSingleResult() method may be alternatively called on Query ; this method returns an Object that must be cast to the appropriate entity.
The preceding example uses the LIKE operator to find entities whose names start with the word "New". This is accomplished by substituting the query's named parameter with the value "New%". The percent sign at the end of the parameter value means that any number of characters after the word "New" will match the expression. The percent sign can be used anywhere in the parameter value, for example, a value of "%Dakota" would match any entities whose name end in "Dakota", a value of "A%a" would match any states whose name starts with a capital "A" and end with a lowercase "a". There can be more than one % sign in a parameter value. The underscore sign, (_), can be used to match a single character; all the rules for the % sign apply to the underscore as well.
In addition to the LIKE operator, there are other operators that can be used to retrieve entities from the database:
- The = operator will retrieve entities whose field at the left of the operator exactly match as the value to the right of the operator
- The > operator will retrieve entities whose field at the left of the operator is greater than the value to the right of the operator
- The < operator will retrieve entities whose field at the left of the operator is less than the value to the right of the operator
- The >= operator will retrieve entities whose field at the left of the operator is greater than or equal to the value to the right of the operator
- The <= operator will retrieve entities whose field at the left of the operator is less than or equal to the value to the right of the operator
All of the preceding operators work the same way as the equivalent operators in SQL. Just like in SQL, these operators can be combined with the AND and OR operators. Conditions combined with the AND operator match if both conditions are true; conditions combined with the OR operator match if at least one of the conditions is true.
If we intend to use a query many times, it can be stored in a named query. Named queries can be defined by decorating the relevant entity class with the @NamedQuery annotation. This annotation has two elements: a name element used to set the name of the query, and a query element defining the query itself. To execute a named query, the createNamedQuery() method must be invoked in an instance of EntityManager. This method takes a String containing the query name as its sole parameter, and returns an instance of javax.persistence.Query.
In addition to retrieving entities, JPQL can be used to modify or delete entities. However, entity modification and deletion can be done programmatically via the EntityManager interface; doing so results in code that tends to be more readable than when using JPQL. Because of this, we will not cover entity modification and deletion via JPQL. Readers interested in writing JPQL queries to modify and delete entities, as well as readers wishing to know more about JPQL, are encouraged to review the Java Persistence 2.2 specification. This specification can be downloaded at http://jcp.org/en/jsr/detail?id=338.