Query Roots

The first fundamental concept to revisit is the identification variable used in the FROM clause of JP QL queries to alias declarations that cover entity, embeddable, and other abstract schema types. In JP QL, the identification variable takes on a central importance, as it is the key to tying the different clauses of the query together. But with the Criteria API we represent query components with objects and therefore rarely have aliases with which to concern ourselves. Still, in order to define a FROM clause we need a way to express which abstract schema types we are interested in querying against.

The AbstractQuery interface (parent of CriteriaQuery) provides the from() method to define the abstract schema type that will form the basis for the query. This method accepts an entity type as a parameter and adds a new root to the query. A root in a criteria query corresponds to an identification variable in JP QL, which in turn corresponds to a range variable declaration or join expression. In Listing 9-2, we used the following code to obtain our query root:

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);

The from() method returns an instance of Root corresponding to the entity type. The Root interface is itself extended from the From interface, which exposes functionality for joins. The From interface extends Path, which further extends Expression and then Selection, allowing the root to be used in other parts of the query definition. The role of each of these interfaces will be described in later sections. Calls to the from() method are additive. Each call adds another root to the query, resulting in a Cartesian product when more than one root is defined if no further constraints are applied in the WHERE clause. The following example from Chapter 8 demonstrates multiple query roots, replacing a conventional join with the more traditional SQL approach:

SELECT DISTINCT d
FROM Department d, Employee e
WHERE d = e.department

To convert this query to the Criteria API we need to invoke from() twice, adding both the Department and Employee entities as query roots. The following example demonstrates this approach:

CriteriaQuery<Department> c = cb.createQuery(Department.class);
Root<Department> dept = c.from(Department.class);
Root<Employee> emp = c.from(Employee.class);
c.select(dept)
 .distinct(true)
 .where(cb.equal(dept, emp.get("department")));

Path Expressions

The second fundamental concept to revisit is the path expression. The path expression is the key to the power and flexibility of the JP QL language, and it is likewise a central piece of the Criteria API. We discussed path expressions in detail in Chapter 8 so if you feel you need a refresher we recommend going back to review that section.

We went over query roots in the previous section, and roots are actually just a special type of path expression. Query roots in hand, we can now look at how to obtain and extend path expressions. Consider the following basic JP QL query, which returns all the employees living in New York City:

SELECT e
FROM Employee e
WHERE e.address.city = 'New York'

Thinking in terms of the Criteria API, the query root for this expression is the Employee entity. This query also contains a path expression in the WHERE clause. To represent this path expression using the Criteria API, we would use the following expression:

emp.get("address").get("city")

The emp object in this example corresponds to the query root for Employee. The get() method is derived from the Path interface extended by the Root interface and is equivalent to the dot operator used in JP QL path expressions to navigate along a path. Because the get() method returns a Path object, the method calls can be chained together, avoiding the unnecessary declaration of intermediate local variables. The argument to get() is the name of the attribute we are interested in. Because the result of constructing a path expression is an Expression object that we can use to build conditional expressions, we can then express the complete query as follows:

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp)
 .where(cb.equal(emp.get("address").get("city"), "New York"));

Much like JP QL, path expressions may be used throughout the different clauses of the query definition. With the Criteria API it is necessary to hold onto the root object in a local variable and use it to form path expressions where required. Once again it is worth emphasizing that the from() method of AbstractQuery should never be invoked more than once for each desired root. Invoking it multiple times will result in additional roots being created and a Cartesian product if not careful. Always store the root objects locally and refer to them when necessary.

Selecting Single Expressions

The select() method of the CriteriaQuery interface is used to form the SELECT clause in a Criteria API query definition. All forms of the SELECT clause may be represented via the select() method, although convenience methods also exist to simplify coding. The select() method requires an argument of type Selection, an interface extended by Expression as well as CompoundSelection to handle the case where the result type of a query is a Tuple or array of results.

Thus far, we have been passing in a query root to the select() method, therefore indicating that we want the entity to be the result of the query. We could also supply a single-valued expression such as selecting an attribute from an entity or any compatible scalar expression. The following example demonstrates this approach by selecting the name attribute of the Employee entity:

CriteriaQuery<String> c = cb.createQuery(String.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp.<String>get("name"));

This query will return all employee names, including any duplicates. Duplicate results from a query may be removed by invoking distinct(true) from the AbstractQuery interface. This is identical in behavior to the DISTINCT keyword in a JP QL query.

Also note the unusual syntax we used to declare that the “name” attribute was of type String. The type of the expression provided to the select() method must be compatible with the result type used to create the CriteriaQuery object. For example, if the CriteriaQuery object was created by invoking createQuery(Project.class) on the CriteriaBuilder interface, then it will be an error to attempt to set an expression resolving to the Employee entity using the select() method. When a method call such as select() uses generic typing in order to enforce a compatibility constraint, the type may be prefixed to the method name in order to qualify it in cases where the type could not otherwise be automatically determined. We need to use that approach in this case because the select() method has been declared as follows:

CriteriaQuery<T> select(Selection<? extends T> selection);

The argument to select() must be a type that is compatible with the result type of the query definition. The get() method returns a Path object, but that Path object is always of type Path<Object> because the compiler cannot infer the correct type based on the attribute name. To declare that the attribute is really of type String, we need to qualify the method invocation accordingly. This syntax has to be used whenever the Path is being passed as an argument for which the parameter has been strongly typed, such as the argument to the select() method and certain CriteriaBuilder expression methods. We have not had to use them so far in our examples because we have been using them in methods like equal(), where the parameter was declared to be of type Expression<?>. Because the type is wildcarded, it is valid to pass in an argument of type Path<Object>. Later in the chapter, we will look at the strongly typed versions of the Criteria API methods that remove this requirement.

Selecting Multiple Expressions

When defining a SELECT clause that involves more than one expression, the Criteria API approach required depends on how the query definition was created. If the result type is Tuple, then a CompoundSelection<Tuple> object must be passed to select(). If the result type is a non-persistent class that will be created using a constructor expression, then the argument must be a CompoundSelection<[T]> object, where [T] is the class type of the non-persistent class. Finally, if the result type is an array of objects, then a CompoundSelection<Object[]> object must be provided. These objects are created with the tuple(), construct() and array() methods of the CriteriaBuilder interface, respectively. The following example demonstrates how to provide multiple expressions to a Tuple query:

CriteriaQuery<Tuple> c= cb.createTupleQuery();
Root<Employee> emp = c.from(Employee.class);
c.select(cb.tuple(emp.get("id"), emp.get("name")));

As a convenience, the multiselect() method of the CriteriaQuery interface may also be used to set the SELECT clause. The multiselect() method will create the appropriate argument type given the result type of the query. This can take three forms depending on how the query definition was created.

The first form is for queries that have Object or Object[] as their result type. The list of expressions that make up each result are simply passed to the multiselect() method.

CriteriaQuery<Object[]> c = cb.createQuery(Object[].class);
Root<Employee> emp = c.from(Employee.class);
c.multiselect(emp.get("id"), emp.get("name"));

Note that, if the query result type is declared as Object instead of Object[], the behavior of multiselect() in this form changes slightly. The result is always an instance of Object, but if multiple arguments are passed into multiselect() then the result must be cast to Object[] in order to access any particular value. If only a single argument is passed into multiselect(), then no array is created and the result may be cast directly from Object to the desired type. In general, it is more convenient to be explicit about the query result type. If you want to work with an array of results, then declaring the query result type to be Object[] avoids casting later and makes the shape of the result more explicit if the query is invoked separately from the code that creates it.

The second form is a close relative of the first form, but for queries that result in Tuple. Again, the list of expressions is passed into the multiselect() call.

CriteriaQuery<Tuple> c = cb.createTupleQuery();
Root<Employee> emp = c.from(Employee.class);
c.multiselect(emp.get("id"), emp.get("name"));

The third and final form is for queries with constructor expressions that result in non-persistent types. The multiselect() method is again invoked with a list of expressions, but it uses the type of the query to figure out and automatically create the appropriate constructor expression, in this case a data transfer object of type EmployeeInfo.

CriteriaQuery<EmployeeInfo> c = cb.createQuery(EmployeeInfo.class);
Root<Employee> emp = c.from(Employee.class);
c.multiselect(emp.get("id"), emp.get("name"));

This is equivalent to the following:

CriteriaQuery<EmployeeInfo> c = cb.createQuery(EmployeeInfo.class);
Root<Employee> emp = c.from(Employee.class);
c.select(cb.construct(EmployeeInfo.class,
                      emp.get("id"),
                      emp.get("name")));

As convenient as the multiselect() method is for constructor expressions, there are still cases where you will need to use the construct() method from the CriteriaBuilder interface. For example, if the result type of the query is Object[] and it also includes a constructor expression for only part of the results, the following would be required:

CriteriaQuery<Object[]> c = cb.createQuery(Object[].class);
Root<Employee> emp = c.from(Employee.class);
c.multiselect(emp.get("id"),
              cb.construct(EmployeeInfo.class,
                           emp.get("id"),
                           emp.get("name")));

Using Aliases

Like JP QL, aliases may also be set on expressions in the SELECT clause, which will then be included in the resulting SQL statement. They are of little use from a programming perspective as we construct the ORDER BY clause through the use of the Selection objects used to construct the SELECT clause.

Aliases are useful when the query has a result type of Tuple. The aliases will be available through the resulting Tuple objects. To set an alias, the alias() method of the Selection interface (parent to Expression) must be invoked. The following example demonstrates this approach:

CriteriaQuery<Tuple> c= cb.createTupleQuery();
Root<Employee> emp = c.from(Employee.class);
c.multiselect(emp.get("id").alias("id"), emp.get("name").alias("fullName"));

This example actually demonstrates two facets of the alias() method. The first is that it returns itself, so it can be invoked as part of the call to select() or multiselect(). The second is, once again, that it returns itself, and is therefore mutating what should be an otherwise immutable object. The alias() method is an exception to the rule that only the query definition interfaces, CriteriaQuery and Subquery, contain mutating operations. Invoking alias() changes the original Selection object and returns it from the method invocation. It is invalid to set the alias of a Selection object more than once.

Making use of the alias when iterating over the query results is as simple as requesting the expression by name. Executing the previous query would allow it to be processed as follows:

TypedQuery<Tuple> q = em.createQuery(c);
for (Tuple t : q.getResultList()) {
    String id = t.get("id", String.class);
    String fullName = t.get("fullName", String.class);
    // ...
}

Inner and Outer Joins

Join expressions are created using the join() method of the From interface, which is extended both by Root, which we covered earlier, and Join, which is the object type returned by creating join expressions. This means that any query root may join, and that joins may chain with one another. The join() method requires a path expression argument and optionally an argument to specify the type of join, JoinType.INNER or JoinType.LEFT, for inner and outer joins respectively.

When joining across a collection type (except for Map, which we will discuss later in this chapter), the join will have two parameterized types: the type of the source and the type of the target. This maintains the type safety on both sides of the join, and makes it clear what types are being joined.

The join() method is additive, so each call results in a new join being created; therefore, the Join instance returned from invoking the method should be retained in a local variable for forming path expressions later. Because Join also extends Path, it behaves like Root objects when defining paths.

In Listing 9-2, we demonstrated an outer join from Employee to Project.

Join<Employee,Project> project = emp.join("projects", JoinType.LEFT);

Had the JoinType.LEFT argument been omitted, the join type would have defaulted to be an inner join. Just as in JP QL, multiple joins may be associated with the same From instance. For example, to navigate across the directs relationship of Employee and then to both the Department and Project entities would require the following, which assumes inner joining:

Join<Employee,Employee> directs = emp.join("directs");
Join<Employee,Project> projects = directs.join("projects");
Join<Employee,Department> dept = directs.join("dept");

Joins may also be cascaded in a single statement. The resulting join will be typed by the source and target of the last join in the statement:

Join<Employee,Project> project = dept.join("employees").join("projects");

Joins across collection relationships that use Map are a special case. JP QL uses the KEY and VALUE keywords to extract the key or value of a Map element for use in other parts of the query. In the Criteria API, these operators are handled by the key() and value() methods of the MapJoin interface. Consider the following example assuming a Map join across the phones relationship of the Employee entity:

SELECT e.name, KEY(p), VALUE(p)
FROM Employee e JOIN e.phones p

To create this query using the Criteria API, we need to capture the result of the join as a MapJoin, in this case using the joinMap() method. The MapJoin object has three type parameters: the source type, key type, and value type. It can look a little more daunting, but makes it explicit what types are involved.

CriteriaQuery<Object> c = cb.createQuery();
Root<Employee> emp = c.from(Employee.class);
MapJoin<Employee,String,Phone> phone = emp.joinMap("phones");
c.multiselect(emp.get("name"), phone.key(), phone.value());

We need to use the joinMap() method in this case because there is no way to overload the join() method to return a Join object or MapJoin object when all we are passing in is the name of the attribute. Collection, Set, and List relationships are likewise handled with the joinCollection(), joinSet(), and joinList() methods for those cases where a specific join interface must be used. The strongly typed version of the join() method, which we will demonstrate later, is able to handle all join types though the single join() call.

Fetch Joins

As with JP QL, the Criteria API supports the fetch join, a query construct that allows data to be prefetched into the persistence context as a side effect of a query that returns a different, but related, entity. The Criteria API builds fetch joins through the use of the fetch() method on the FetchParent interface. It is used instead of join() in cases where fetch semantics are required and accepts the same argument types.

Consider the following example we used in the previous chapter to demonstrate fetch joins of single-valued relationships:

SELECT e
FROM Employee e JOIN FETCH e.address

To re-create this query with the Criteria API, we use the fetch() method.

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
emp.fetch("address");
c.select(emp);

Note that when using the fetch() method the return type is Fetch, not Join. Fetch objects are not paths and may not be extended or referenced anywhere else in the query.

Collection-valued fetch joins are also supported and use similar syntax. In the following example, we demonstrate how to fetch the Phone entities associated with each Employee, using an outer join to prevent Employee entities from being skipped if they don’t have any associated Phone entities. We use the distinct() setting to remove any duplicates.

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
emp.fetch("phones", JoinType.LEFT);
c.select(emp)
 .distinct(true);

Predicates

In Listing 9-2, we passed an array of Predicate objects to the and() method. This has the behavior of combining all of the expressions with the AND operator. Equivalent behavior for the OR operator exists via the or() method. One shortcut that works for AND operators is to pass all of the expressions as arguments to the where() method. Passing multiple arguments to where() implicitly combines the expressions using AND operator semantics.

The Criteria API also offers a different style of building AND and OR expressions for those who wish to build things incrementally rather than as a list. The conjunction() and disjunction() methods of the CriteriaBuilder interface create Predicate objects that always resolve to true and false respectively. Once obtained, these primitive predicates can then be combined with other predicates to build up nested conditional expressions in a tree-like fashion. Listing 9-3 rewrites the predication construction portion of the example from Listing 9-2 using the conjunction() method. Note how each conditional statement is combined with its predecessor using an and() call.

Listing 9-3.  Predicate Construction Using Conjunction

Predicate criteria = cb.conjunction();
if (name != null) {
    ParameterExpression<String> p =
        cb.parameter(String.class, "name");
    criteria = cb.and(criteria, cb.equal(emp.get("name"), p));
}
if (deptName != null) {
    ParameterExpression<String> p =
        cb.parameter(String.class, "dept");
    criteria = cb.and(criteria,
                      cb.equal(emp.get("dept").get("name"), p));
}
if (projectName != null) {
    ParameterExpression<String> p =
        cb.parameter(String.class, "project");
    criteria = cb.and(criteria, cb.equal(project.get("name"), p));
}
if (city != null) {
    ParameterExpression<String> p =
        cb.parameter(String.class, "city");
    criteria = cb.and(criteria,
                      cb.equal(emp.get("address").get("city"), p));
}

if (criteria.getExpressions().size() == 0) {
    throw new RuntimeException("no criteria");
}

With respect to other predicate concerns, in Table 9-2 it should be noted that there are two sets of methods available for relative comparisons. For example, there is greaterThan() and gt(). The two-letter forms are specific to numeric values and are strongly typed to work with number types. The long forms must be used for all other cases.

Literals

Literal values may require special handling when expressed with the Criteria API. In all the cases encountered so far, methods are overloaded to work with both Expression objects and Java literals. However, there may be some cases where only an Expression object is accepted (in cases where it is assumed you would never pass in a literal value or when any of a number of types would be acceptable). If you encounter this situation then, to use these expressions with Java literals, the literals must be wrapped using the literal() method. NULL literals are created from the nullLiteral() method, which accepts a class parameter and produces a typed version of NULL to match the passed-in class. This is necessary to extend the strong typing of the API to NULL values.

Parameters

Parameter handling for Criteria API queries is different from JP QL. Whereas JP QL strings simply prefix string names with a colon to denote a parameter alias, this technique will not work in the Criteria API. Instead, we must explicitly create a ParameterExpression of the correct type that can be used in conditional expressions. This is achieved through the parameter() method of the CriteriaBuilder interface. This method requires a class type (to set the type of the ParameterExpression object) and an optional name for use with named parameters. Listing 9-4 demonstrates this method.

Listing 9-4.  Creating Parameter Expressions

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp);
ParameterExpression<String> deptName =
    cb.parameter(String.class, "deptName");
c.where(cb.equal(emp.get("dept").get("name"), deptName));

If the parameter will not be reused in other parts of the query, it can be embedded directly in the predicate expression to make the overall query definition more concise. The following code revises the Listing 9-4 to use this technique:

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp)
 .where(cb.equal(emp.get("dept").get("name"),
                 cb.parameter(String.class, "deptName")));

Subqueries

The AbstractQuery interface provides the subquery() method for creation of subqueries. Subqueries may be correlated (meaning that they reference a root, path, or join from the parent query) or non-correlated. The Criteria API supports both correlated and non-correlated subqueries, again using query roots to tie the various clauses and expressions together. The argument to subquery() is a class instance representing the result type of the subquery. The return value is an instance of Subquery, which is itself an extension of AbstractQuery. With the exception of restricted methods for constructing clauses, the Subquery instance is a complete query definition like CriteriaQuery that may be used to create both simple and complex queries.

To demonstrate subquery usage, let’s look at a more significant example, modifying Listing 9-2 to use subqueries instead of the distinct() method to eliminate duplicates. According to the data model shown in Figure 8-1, the Employee entity has relationships with four other entities: single-valued relationships with Department and Address, and collection-valued relationships with Phone and Project. Whenever we join across a collection-valued relationship, we have the potential to return duplicate rows; therefore, we need to change the criteria expression for Project to use a subquery. Listing 9-5 shows the code fragment required to make this change.

Listing 9-5.  Modified Employee Search With Subqueries

@Stateless
public class SearchService {
    @PersistenceContext(unitName="EmployeeHR")
    EntityManager em;

    public List<Employee> findEmployees(String name, String deptName,
                                        String projectName, String city) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
        Root<Employee> emp = c.from(Employee.class);
        c.select(emp);

        // ...

        if (projectName != null) {
            Subquery<Employee> sq = c.subquery(Employee.class);
            Root<Project> project = sq.from(Project.class);
            Join<Project,Employee> sqEmp = project.join("employees");
            sq.select(sqEmp)
              .where(cb.equal(project.get("name"),
                              cb.parameter(String.class, "project")));
            criteria.add(cb.in(emp).value(sq));
        }

        // ...
}

Listing 9-5 contains a couple of significant changes to the example first presented in Listing 9-2. First, the distinct() method call has been removed as well as the join to the Project entity. We have also introduced a new non-correlated subquery against Project. Because the subquery from Listing 9-5 declares its own root and does not reference anything from the parent query, it runs independently and is therefore non-correlated. The equivalent JP QL query with only Project criteria would be:

SELECT e
FROM Employee e
WHERE e IN (SELECT emp
              FROM Project p JOIN p.employees emp
              WHERE p.name = :project)

Whenever we write queries that use subqueries, there is often more than one way to achieve a particular result. For example, we could rewrite the previous example to use EXISTS instead of IN and shift the conditional expression into the WHERE clause of the subquery.

if (projectName != null) {
    Subquery<Project> sq = c.subquery(Project.class);
    Root<Project> project = sq.from(Project.class);
    Join<Project,Employee> sqEmp = project.join("employees");
    sq.select(project)
      .where(cb.equal(sqEmp, emp),
             cb.equal(project.get("name"),
                      cb.parameter(String.class,"project")));
    criteria.add(cb.exists(sq));
}

By referencing the Employee root from the parent query in the WHERE clause of the subquery, we now have a correlated subquery. This time the query takes the following form in JP QL:

SELECT e
FROM Employee e
WHERE EXISTS (SELECT p
              FROM Project p JOIN p.employees emp
              WHERE emp = e AND
                    p.name = :name)

We can still take this example further and reduce the search space for the subquery by moving the reference to the Employee root to the FROM clause of the subquery and joining directly to the list of projects specific to that employee. In JP QL, we would write this as follows:

SELECT e
FROM Employee e
WHERE EXISTS (SELECT p
              FROM e.projects p
              WHERE p.name = :name)

In order to re-create this query using the Criteria API, we are confronted with a dilemma. We need to base the query on the Root object from the parent query but the from() method only accepts a persistent class type. The solution is the correlate() method from the Subquery interface. It performs a similar function to the from() method of the AbstractQuery interface, but does so with Root and Join objects from the parent query. The following example demonstrates how to use correlate() in this case:

if (projectName != null) {
    Subquery<Project> sq = c.subquery(Project.class);
    Root<Employee> sqEmp = sq.correlate(emp);
    Join<Employee,Project> project = sqEmp.join("projects");
    sq.select(project)
      .where(cb.equal(project.get("name"),
                      cb.parameter(String.class,"project")));
    criteria.add(cb.exists(sq));
}

Before we leave subqueries in the Criteria API, there is one more corner case with correlated subqueries to explore: referencing a join expression from the parent query in the FROM clause of a subquery. Consider the following example that returns projects containing managers with direct reports earning an average salary higher than a user-defined threshold:

SELECT p
FROM Project p JOIN p.employees e
WHERE TYPE(p) = DesignProject AND
      e.directs IS NOT EMPTY AND
      (SELECT AVG(d.salary)
       FROM e.directs d) >= :value

When creating the Criteria API query definition for this query, we must correlate the employees attribute of Project and then join it to the direct reports in order to calculate the average salary. This example also demonstrates the use of the type() method of the Path interface in order to do a polymorphic comparison of types:

CriteriaQuery<Project> c = cb.createQuery(Project.class);
Root<Project> project = c.from(Project.class);
Join<Project,Employee> emp = project.join("employees");
Subquery<Number> sq = c.subquery(Number.class);
Join<Project,Employee> sqEmp = sq.correlate(emp);
Join<Employee,Employee> directs = sqEmp.join("directs");
c.select(project)
 .where(cb.equal(project.type(), DesignProject.class),
        cb.isNotEmpty(emp.<Collection>get("directs")),
        cb.ge(sq.select(cb.avg(directs.get("salary"))),
                  cb.parameter(Number.class, "value")));

In Expressions

Unlike other operators, the IN operator requires some special handling in the Criteria API. The in() method of the CriteriaBuilder interface only accepts a single argument, the single-valued expression that will be tested against the values of the IN expression. In order to set the values of the IN expression, we must use the CriteriaBuilder.In object returned from the in() method. Consider the following JP QL query:

SELECT e
FROM Employee e
WHERE e.address.state IN ('NY', 'CA')

To convert this query to the Criteria API, we must invoke the value() method of the CriteriaBuilder.In interface to set the state identifiers we are interested in querying, like so:

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp)
 .where(cb.in(emp.get("address")
                 .get("state")).value("NY").value("CA"));

Note the chained invocation of the value() method in order to set multiple values into the IN expression. The argument to in() is the expression to search for against the list of values provided via the value() method.

In cases where there are a large number of value() calls to chain together that are all of the same type, the Expression interface offers a shortcut for creating IN expressions. The in() methods of this interface allow one or more values to be set in a single call.

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp)
 .where(emp.get("address")
           .get("state").in("NY","CA"));

In this case, the call to in() is suffixed to the expression rather than prefixed as was the case in the previous example. Note the difference in argument type between the CriteriaBuilder and Expression interface versions of in(). The Expression version of in() accepts the values to be searched, not the expression to search for. The in() method of the CriteriaBuilder interface allows more typing options, but for the most part it is largely a case of personal preference when deciding which approach to use.

IN expressions that use subqueries are written using a similar approach. For a more complex example, in the previous chapter, we demonstrated a JP QL query using an IN expression in which the department of an employee is tested against a list generated from a subquery. The example is reproduced here.

SELECT e
FROM Employee e
WHERE e.department IN
  (SELECT DISTINCT d
   FROM Department d JOIN d.employees de JOIN de.project p
   WHERE p.name LIKE 'QA%')

We can convert this example to the Criteria API as shown in Listing 9-6.

Listing 9-6.  IN Expression Using a Subquery

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
Subquery<Department> sq = c.subquery(Department.class);
Root<Department> dept = sq.from(Department.class);
Join<Employee,Project> project =
    dept.join("employees").join("projects");
sq.select(dept.<Integer>get("id"))
  .distinct(true)
  .where(cb.like(project.<String>get("name"), "QA%"));
c.select(emp)
 .where(cb.in(emp.get("dept").get("id")).value(sq));

The subquery is created separately and then passed into the value() method as the expression to search for the Department entity. This example also demonstrates using an attribute expression as a value in the search list.

Case Expressions

Like the IN expression, building CASE expressions with the Criteria API requires the use of a helper interface. In this example we will convert the examples used in Chapter 8 to the Criteria API, demonstrating general and simple case expressions, as well as COALESCE.

We will begin with the general form of the CASE expression, the most powerful but also the most complex.

SELECT p.name,
       CASE WHEN TYPE(p) = DesignProject THEN 'Development'
            WHEN TYPE(p) = QualityProject THEN 'QA'
            ELSE 'Non-Development'
       END
FROM Project p
WHERE p.employees IS NOT EMPTY

The selectCase() method of the CriteriaBuilder interface is used to create the CASE expression. For the general form it takes no arguments and returns a CriteriaBuilder.Case object that we may use to add the conditional expressions to the CASE statement. The following example demonstrates this approach:

CriteriaQuery<Object[]> c = cb.createQuery(Object[].class);
Root<Project> project = c.from(Project.class);
c.multiselect(project.get("name"),
         cb.selectCase()
           .when(cb.equal(project.type(), DesignProject.class),
                 "Development")
           .when(cb.equal(project.type(), QualityProject.class),
                 "QA")
           .otherwise("Non-Development"))
 .where(cb.isNotEmpty(project.<List<Employee>>get("employees")));

The when() and otherwise() methods correspond to the WHEN and ELSE keywords from JP QL. Unfortunately, “else” is already a keyword in Java so “otherwise” must be used as a substitute.

The next example simplifies the previous example down to the simple form of the case statement.

SELECT p.name,
       CASE TYPE(p)
            WHEN DesignProject THEN 'Development'
            WHEN QualityProject THEN 'QA'
            ELSE 'Non-Development'
       END
FROM Project p
WHERE p.employees IS NOT EMPTY

In this case, we pass the primary expression to be tested to the selectCase() method and use the when() and otherwise() methods of the CriteriaBuilder.SimpleCase interface. Rather than a predicate or boolean expression, these methods now accept single-valued expressions that are compared to the base expression of the CASE statement.

CriteriaQuery<Object[]> c = cb.createQuery(Object[].class);
Root<Project> project = c.from(Project.class);
c.multiselect(project.get("name"),
         cb.selectCase(project.type())
           .when(DesignProject.class, "Development")
           .when(QualityProject.class, "QA")
           .otherwise("Non-Development"))
 .where(cb.isNotEmpty(project.<List<Employee>>("employees")));

The last example we will cover in this section concerns the JP QL COALESCE expression.

SELECT COALESCE(d.name, d.id)
FROM Department d

Building a COALESCE expression with the Criteria API requires a helper interface like the other examples we have looked at in this section, but it is closer in form to the IN expression than the CASE expressions. Here we invoke the coalesce() method without arguments to get back a CriteriaBuilder.Coalesce object that we then use the value() method of to add values to the COALESCE expression. The following example demonstrates this approach:

CriteriaQuery<Object> c = cb.createQuery();
Root<Department> dept = c.from(Department.class);
c.select(cb.coalesce()
           .value(dept.get("name"))
           .value(dept.get("id")));

Convenience versions of the coalesce() method also exist for the case where only two expressions are being compared.

CriteriaQuery<Object> c = cb.createQuery();
Root<Department> dept = c.from(Department.class);
c.select(cb.coalesce(dept.get("name"),
                     dept.get("id")));

A final note about case expressions is that they are another exception to the rule that the CriteriaBuilder methods are non-mutating. Each when() method causes another conditional expression to be added incrementally to the case expression, and each value() method adds an additional value to the coalesce list.

Function Expressions

Not to be confused with the built-in functions of JP QL, criteria function expressions are the Criteria API equivalent of the FUNCTION keyword in JP QL. They allow native SQL stored functions to be mixed with other Criteria API expressions. They are intended for cases where a limited amount of native SQL is required to satisfy some requirement but you don’t want to convert the entire query to SQL.

Function expressions are created with the function() method of the CriteriaBuilder interface. It requires as arguments the database function name, the expected return type, and a variable list of arguments, if any, that should be passed to the function. The return type is an Expression, so it can be used in many other places within the query. The following example invokes a database function to capitalize the first letter of each word in a department name:

CriteriaQuery<String> c = cb.createQuery(String.class);
Root<Department> dept = c.from(Department.class);
c.select(cb.function("initcap", String.class, dept.get("name")));

As always, developers interested in maximizing the portability of their applications should be careful in using function expressions. Unlike native SQL queries, which are clearly marked, function expressions are a small part of what otherwise looks like a normal portable JPA query that is actually tied to database-specific behavior.

Downcasting

JPA 2.1 introduced support for type downcasting when querying over an entity inheritance hierarchy via the TREAT operation in JP QL. In the Criteria API, this operation is exposed via the treat() method of the CriteriaBuilder interface. It may be used when constructing joins to limit results to a specific subclass or as part of general criteria expressions in order to access state fields from specific subclasses.

The treat() method has been overloaded to return either Join or Path objects depending on the type of arguments. Recall the example in Chapter 8 that demonstrated using treat() to limit an Employee query to only those employees who are working on a QualityProject for which the quality rating is greater than five:

SELECT e FROM Employee e JOIN TREAT(e.projects AS QualityProject) qp

WHERE qp.qualityRating > 5

The criteria equivalent of this query is as follows:

CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
Root<Employee> emp = q.from(Employee.class);
Join<Employee,QualityProject> project = cb.treat(emp.join(emp.get("projects"), QualityProject.class);
q.select(emp)
  .where(cb.gt(project.<Integer>get("qualityRating"), 5));

In this example, treat() accepts a Join object and returns a Join object and can be referenced in the WHERE clause because it is saved in a variable. When used directly in a criteria expression, such as in a WHERE clause, it accepts a Path or a Root object and returns a Path that corresponds to the subclass requested. For example, we could use the treat() method to access the quality rating or the design phase in a query across projects.

CriteriaQuery<Project> q = cb.createQuery(Project.class);
Root<Project> project = q.from(Project.class);
q.select(project)
  .where(cb.or(
                 cb.gt(cb.treat(project, QualityProject.class). <Integer>get("qualityRating"), 5),
                 cb.gt(cb.treat(project, DesignProject.class). <Integer>get("designPhase"), 3)));

Outer Join Criteria

Earlier in the chapter we demonstrated how to use the Criteria API to construct joins between entities. We have also demonstrated how to use the where() method of the AbstractQuery interface and the expression framework of the Criteria API to construct the filtering conditions that will limit the result set. This works fine for inner joins, but as we discussed in the last chapter, outer joins require additional support to establish filtering criteria that still preserves the optionality of the joined entities. Like the where() method of the AbstractQuery interface, the Join interface supports the on() method for specifying outer join criteria that must be evaluated as the result set is being created. This is equivalent to the ON condition of the JP QL JOIN expression. The following is a simple example of using the ON keyword in JP QL:

SELECT e FROM Employee e JOIN e.projects p ON p.name = 'Zooby'

The on() method takes a single predicate object to represent the join condition. The criteria equivalent of the JP QL query above would be expressed as:

CriteriaQuery<Employee> q = cb.createQuery(Employee.class);
Root<Employee> emp = q.from(Employee.class);
Join<Employee,Project> project = emp.join("projects", JoinType.LEFT)
            .on(cb.equal(project.get("name"), "Zooby"));
q.select(emp);

Using the Canonical Metamodel

We are now in the position of being able to leverage the metamodel without actually using the metamodel API. As an example, we can convert the example from the previous section to use the statically generated canonical classes.

CriteriaQuery<Object> c = cb.createQuery();
Root<Employee> emp = c.from(Employee.class);
MapJoin<Employee,String,Phone> phone = emp.join(Employee_.phones);
c.multiselect(emp.get(Employee_.name), phone.key(), phone.value());

This is a much more concise approach to using the metamodel objects than the interfaces we discussed earlier while offering the exact same benefits. Coupled with a development environment that has good code completion features, you may find it more convenient to develop using the canonical metamodel than with the string-based API.

We can convert a more complex example to illustrate using the canonical metamodel. Listing 9-8 converts the example in Listing 9-6, showing an IN expression that uses a subquery, from using the string-based attribute names to using the strongly typed approach.

Listing 9-8.  Strongly Typed Query

CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
Subquery<Department> sq = c.subquery(Department.class);
Root<Department> dept = sq.from(Department.class);
Join<Employee,Project> project =
    dept.join(Department_.employees).join(Employee_.projects);
sq.select(dept.get(Department_.id))
  .distinct(true)
  .where(cb.like(project.get(Project_.name), "QA%"));
c.select(emp)
 .where(cb.in(emp.get(Employee_.dept).get(Department_.id)).value(sq));

Note that there are two main differences between this example and Listing 9-6. First, the use of typed metamodel static fields to indicate entity attributes helps avert typing errors in attribute strings. Second, there is no longer the need to do inlined typing to convert the Path<Object>, returned from the get() method, to a narrower type. The stronger typing solves that problem for us.

All the examples in this chapter can be easily converted to use the canonical metamodel classes by simply changing the string-based attributes to the corresponding static fields of the generated metamodel classes. For example, emp.get("name") can be replaced with emp.get(Employee_.name), and so on.

Generating the Canonical Metamodel

If you choose to use the generated metamodel in your queries, you should be aware of some of the details of the development process in case inconsistency or configuration issues crop up. The canonical metamodel classes will need to be updated or regenerated when certain changes to entities have occurred during development. For example, changing the name of a field or property, or changing its shape, would require an updated canonical metamodel class for that entity.

The generation tools offered by providers may vary widely in function or in operation. Generation may involve reading the persistence.xml file, as well as accessing annotations on entities and XML mapping files to determine what the metamodel classes should look like. Since the specification does not require such tools to even exist, a provider may choose to not support it at all, expecting that if developers want to use the canonical metamodel classes they will handcode them. Most providers do offer some kind of generation tool, though; it’s just a matter of understanding how that vendor-specific tool works. It might run statically as a separate command line tool, or it might use the compiler hook offered in the JDK (starting in Java SE 6) to look at the entities and generate the classes at compile-time. For example, to run the command line mode of the tool shipped with the EclipseLink Reference Implementation, you could set the javac -processor and -proc:only options. These two options indicate the EclipseLink code/annotation processor¹ for the compiler to invoke, and instruct the compiler to call only the processor but not do any actual compilation.

javac -processor org.eclipse.persistence.internal.jpa.modelgen.CanonicalModelProcessor
         -proc:only
         -classpath lib/*.jar;punit
         *.java

The options are on separate lines to make them easier to see. It is assumed that the lib directory contains the necessary EclipseLink JAR and JPA interface JAR, and that the META-INF/persistence.xml is in the punit directory.

Metamodel generation tools will also typically run in an IDE, and there will likely be IDE-specific configuration necessary to direct the incremental compiler to use the tool’s annotation processor. In some IDEs, there must be an additional code/annotation processor JAR to configure. The generated metamodel classes will need to go in a specific directory and be on the build classpath so the criteria queries that reference them can compile. Consult the IDE help files on how annotation processors or APT is supported, as well as the provider documentation on what to configure in order to enable generation in a given IDE.