Criteria API

The Java Persistence Criteria API is used to define dynamic queries through the construction of object-based
query definition objects, rather than use of the string-based approach of JPQL. The criteria API allows dynamic queries to be built problematically offering better integration with the Java language than a string-based 4th GL approach.

The Criteria API has two modes, the type-restricted mode, and the non-typed mode. The type-restricted mode uses a set of JPA meta-model generated class to define the query-able attributes of a class. The non-typed mode uses strings to reference attributes of a class.

The criteria API is only for dynamic queries, and cannot be used in meta-data or named queries. Criteria queries are dynamic queries, so are not as performant as static named queries, or even dynamic parametrized JPQL which benefit from EclipseLink's parse cache.

CriteriaBuilder

CriteriaBuilder is the main interface into the Criteria API. A CriteriaBuilder is obtained from an EntityManager or an EntityManagerFactory using the getCriteriaBuilder() API. CriteriaBuilder is used to construct CriteriaQuery objects and their expressions. The Criteria API currently only supports select queries.

CriteriaBuilder defines API to create CriteriaQuery objects:

createQuery() - Creates a CriteriaQuery.

createQuery(Class) - Creates a CriteriaQuery using generics to avoid casting the result class.

CriteriaBuilder also defines all supported comparison operations and functions used for defining the query's clauses.

CriteriaQuery

CriteriaQuery defines a database select query. A CriteriaQuery models all of the clauses of a JPQL select query.
Elements from one CriteriaQuery cannot be used in other CriteriaQuerys. A CriteriaQuery is used with the EntityManagercreateQuery() API to create a JPA Query.

CriteriaQuery defines the following clauses and options:

distinct(boolean) - Defines if the query should filter duplicate results (defaults to false). If a join to a collection relationship is used, distinct should be used to avoid duplicate results.

from(Class) - Defines and returns an element in the query's from clause for the entity class. At least one from element is required for the query to be valid.

from(EntityType) - Defines and returns an element in the query's from clause for the meta-model entity type. At least one from element is required for the query to be valid.

select(Selection) - Defines the query's select clause. If not set, the first root will be selected by default.

Selection

A Selection define what is selected by a query. A Selection can be any object expression, attribute expression, function, sub-select, constructor or aggregation function. An alias can be defined for a Selection using the alias() API.

Aggregation functions

Aggregation functions can include summary information on a set of objects. These functions can be used to return a single result, or can be used with a groupBy to return multiple results.

Aggregate functions are defined on CriteriaBuilder and include:

max(Expression) - Return the maximum value for all of the results. Used for numeric types.

greatest(Expression) - Return the maximum value for all of the results. Used for non-numeric types.

min(Expression) - Return the minimum value for all of the results. Used for numeric types.

least(Expression) - Return the minimum value for all of the results. Used for non-numeric types.

avg(Expression) - Return the mean average of all of the results. A Double is returned.

sum(Expression) - Return the sum of all of the results.

sumAsLong(Expression) - Return the sum of all of the results. A Long is returned.

sumAsDouble(Expression) - Return the sum of all of the results. A Double is returned.

count(Expression) - Return the count of all of the results. null values are not counted. A Long is returned.

countDistinct(Expression) - Return the count of all of the distinct results. null values are not counted. A Long is returned.

Constructors

The construct operator on CriteriaBuilder can be used with a class and values to return data objects from a criteria query. These will not be managed objects, and the class must define a constructor that matches the arguments and types. Constructor queries can be used to select partial data or reporting data on objects, and get back a class instance instead of an object array or tuple.

From

The query from clause defines what is being queried. The from clause is defined using the from API on CriteriaQuery. A Root object is return from from, which represent the object in the context of the query. A Root also implements From, and Path. From defines a variable in the from clause, and allows joins. Path defines any attribute value and allows traversal to nested attributes.

Root e = cq.from(Employee.class);

Criteria queries allow for multiple root level objects. Caution should be used when doing this, as it can result in Cartesian products of the two table. The where clause should ensure the two objects are joined in some way.

Join

A join operation can be used on a From object to obtain a relationship to use in the query. join does not mean the relationships will be fetched, to also fetch the related objects in the result use the fetch operation instead.

The join operation can be used with OneToOne, ManyToOne, OneToMany, ManyToMany and ElementColleciton mappings. When used with a collection relationship you can join the same relationship multiple times to query multiple independent values.

Fetch

The fetch operation can be used on a From object to fetch the related objects in a single query. This avoids additional queries for each of the object's relationships, and ensures that the relationships have been fetched if they were LAZY. EclipseLink also supports batch fetching through query hints.

Caution should be used in using a Fetch in the where clause as it can affect the data returned for the resulting object's relationships. Objects should normally always have the same data, no matter how they were queried, this is important for caching and consistency. This is only an issue if the alias is used in the where clause on a collection relationship to filter the related objects that will be fetched. This should not be done, but is sometimes desirable, in which case the query should ensure it has been set to BYPASS the cache.

JoinType

By default join and fetch are INNER joins. This means that results that do not have the relationship will be filtered from the query results. To avoid this, a join can be defined as an OUTER join using the LEFT JoinType as an argument to the join or fetch operation.

Order

The query order by clause defines how the query results will be ordered. The order by clause is defined using the orderBy API on CriteriaQuery. Only Order objects can be passed to orderBy, and are obtained from CriteriaBuilder using the asc or desc API.

// Order by the last and first names.
Root e = cq.from(Employee.class);
cq.orderBy(cb.desc(e.get("lastName")), cb.asc(e.get("firstName")));

// Order by the last name, ignoring case.
Root e = cq.from(Employee.class);
cq.orderBy(cb.asc(cb.upper(e.get("lastName"))));

// Order by the address object (orders by its id).
Root e = cq.from(Employee.class);
cq.orderBy(cb.asc(e.get("address")));

Group By

The query group by clause allows for summary information to be computed on a set of objects. group by is normally used in conjunction with aggregation functions. The group by clause is defined using the groupBy API on CriteriaQuery with any valid Expression object.

Where

The where clause is normally the main part of the query as it defines the conditions (predicates) that filter what is returned. The where clause is defined using the where API on CriteriaQuery with any Predicate objects. A Predicate is obtained using a comparison operation, or a logical operation on CriteriaBuilder. The isNull, isNotNull, and in operations can also be called on Expression. The not operation can also be called on Predicate

Parameters

Parameters can be defined using the parameter API on CriteriaBuilder. JPA defines named parameters, and positional parameters. For named parameters the parameter type and name are specified.
For positional parameters only the parameter type is specified. Positional parameters start at position 1 not 0.

Functions

Several database functions are supported by the Criteria API. All supported functions are defined on CriteriaBuilder.
Some functions may not be supported by some databases, if they are not SQL compliant, and offer no equivalent function.

the index of the string within the string, optionally starting at a start index

cb.locate("-", e.<String>get("lastName"))

lower

convert the string value to lower case

cb.lower(e.<String>get("lastName"))

mod

computes the remainder of dividing the first integer by the second

cb.mod(e.<Integer>get("hoursWorked"), 8)

nullif

returns null if the first argument to equal to the second argument, otherwise returns the first argument

cb.nullif(e.<Number>get("salary"), 0)

sqrt

computes the square root of the number

cb.sqrt(e.<Number>get("salary"))

substring

the substring from the string, starting at the index, optionally with the substring size

cb.substring(e.<String>get("lastName"), 0, 2)

trim

trims leading, trailing, or both spaces or optional trim character from the string

cb.trim(TrimSpec.TRAILING, e.<String>get("lastName"))

cb.trim(e.<String>get("lastName"))

cb.trim(TrimSpec.LEADING, '-', e.<String>get("lastName"))

upper

convert the string value to upper case

cb.upper(e.<String>get("lastName"))

Special Functions

JPQL defines several special functions that are not database functions, but have special meaning in JPQL. These include INDEX, KEY, SIZE, IS EMPTY, TYPE, FUNCTION and TREAT. EclipseLink (as of 2.4) also defines several special functions FUNC, OPERATOR, SQL and COLUMN.

JPQL special functions

Function

Description

Example

INDEX

the index of the ordered List element, only supported with @OrderColumn

SELECT l FROM Employee e JOIN e.toDoList l WHEREINDEX(l)=1

KEY

the key of the Map element

SELECT p FROM Employee e JOIN e.priorities p WHEREKEY(p)='high'

SIZE

the size of the collection relationships, this evaluates to a sub-select

SELECT e FROM Employee e WHERESIZE(e.managedEmployees)<2

IS EMPTY

evaluates to true if the collection relationship is empty, this evaluates to a sub-select

EclipseLink Extensions (EQL)

EclipseLink provides many extensions to the standard JPA JPQL. These extensions provide access to additional database features many of which are part of the SQL standard, provide access to native database features and functions, and provide access to EclipseLink specific features. EclipseLink JPQL extensions are referred to as the EclipseLink Query Language (EQL).

EclipseLink's JPQL extensions include:

Less restrictions than JPQL, allows sub-selects and functions within operations such as LIKE, IN, ORDER BY, constructors, functions etc.