Java Persistence/Criteria

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Criteria API[edit | edit source]

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 programmatically 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, see Metamodel. 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 may benefit from a parse cache in some JPA providers.

The Criteria API was added in JPA 2.0.

The Criteria API delete and update support was added in JPA 2.1.

CriteriaBuilder[edit | edit source]

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.
  • createTupleQuery() - Creates a CriteriaQuery that returns map like Tuple objects, instead of object arrays for multiselect queries. See Tuple Queries
  • createCriteriaDelete(Class) - Creates a CriteriaDelete to delete a batch of objects directly on the database (JPA 2.1).
  • createCriteriaUpdate(Class) - Creates a CriteriaUpdate to update a batch of objects directly on the database (JPA 2.1).

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

CriteriaQuery[edit | edit source]

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 EntityManager createQuery() 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.
  • multiselect(Selection...), multiselect(List<Selection>) - Defines a multi-select query.
  • where(Expression), where(Predicate...) - Defines the query's where clause. By default all instances of the class are selected.
  • orderBy(Order...), orderBy(List<Order>) - Defines the query's order clause. By default the results are not ordered.
  • groupBy(Expression...), groupBy(List<Expression>) - Defines the query's group by clause. By default the results are not grouped.
  • having(Expression), having(Predicate...) - Defines the query's having clause. Having allows grouped results to be filtered.
  • subQuery(Class) - Creates a subQuery to be used in one of the other clauses.

The Expressions, Predicates, Order elements are defined using the CriteriaBuilder API and expressions derived from the from Root elements.

CriteriaQuery examples[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Query for a List of objects.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.where(criteriaBuilder.greaterThan(employee.get("salary"), 100000));
Query query = entityManager.createQuery(criteriaQuery);
List<Employee> result = query.getResultList();

// Query for a single object.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.where(criteriaBuilder.equal(employee.get("id"), criteriaBuilder.parameter(Long.class, "id")));
Query query = entityManager.createQuery(criteriaQuery);
query.setParameter("id", id);
Employee result2 = (Employee)query.getSingleResult();

// Query for a single data element.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.max(employee.get("salary")));
Query query = entityManager.createQuery(criteriaQuery);
BigDecimal result3 = (BigDecimal)query.getSingleResult();

// Query for a List of data elements.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(employee.get("firstName"));
Query query = entityManager.createQuery(criteriaQuery);
List<String> result4 = query.getResultList();

// Query for a List of element arrays.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.multiselect(employee.get("firstName"), employee.get("lastName"));
Query query = entityManager.createQuery(criteriaQuery);
List<Object[]> result5 = query.getResultList();

Selection[edit | edit source]

A Selection defines 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[edit | edit source]

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.
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Count the total employees
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.count(employee));
Query query = entityManager.createQuery(criteriaQuery);
Long result = query.getSingleResult();

// Maximum salary
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.max(employee.get("salary"));
Query query = entityManager.createQuery(criteriaQuery);
Long result = query.getSingleResult();

Constructors[edit | edit source]

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.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.construct(EmpReport.class, employee.get("firstName"), employee.get("lastName"), employee.get("salary")));
Query query = entityManager.createQuery(criteriaQuery);
List<EmpReport> result = query.getResultList();

From[edit | edit source]

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 employee = criteriaQuery.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.

// Select the employees and the mailing addresses that have the same address.
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
Root address = criteriaQuery.from(MailingAddress.class);
criteriaQuery.multiselect(employee, address);
criteriaQuery.where(criteriaBuilder.equal(employee.get("address"), address.get("address"));
Query query = entityManager.createQuery(criteriaQuery);
List<Object[]> result = query.getResultList();

Join[edit | edit source]

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.

Root employee = criteriaQuery.from(Employee.class);
Join address = employee.join("address");
criteriaQuery.where(criteriaBuilder.equal(address.get("city"), city);

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

// All employees who work on both projects.
Root employee = criteriaQuery.from(Employee.class);
Join p = employee.join("projects");
Join p2 = employee.join("projects");
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.equal(p.get("name"), projectName1), criteriaBuilder.equal(p2.get("name"), projectName2));

Fetch[edit | edit source]

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.

Root employee = criteriaQuery.from(Employee.class);
Fetch address = employee.fetch("address");
criteriaQuery.select(employee);

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[edit | edit source]

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.

Root employee = criteriaQuery.from(Employee.class);
Join address = employee.join("address", JoinType.LEFT);
criteriaQuery.order(address.get("city"));

Order[edit | edit source]

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 employee = criteriaQuery.from(Employee.class);
criteriaQuery.orderBy(criteriaBuilder.desc(employee.get("lastName")), criteriaBuilder.asc(employee.get("firstName")));
// Order by the last name, ignoring case.
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.orderBy(criteriaBuilder.asc(criteriaBuilder.upper(employee.get("lastName"))));
// Order by the address object (orders by its id).
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.orderBy(criteriaBuilder.asc(employee.get("address")));

Group By[edit | edit source]

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.

// Select the average salaries grouped by city.
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.multiselect(criteriaBuilder.avg(employee.<Number>get("salary")), employee.get("address").get("city"));
criteriaQuery.groupBy(employee.get("address").get("city"));
// Select the average salaries grouped by city, ordered by the average salary.
Root employee = criteriaQuery.from(Employee.class);
Expression avg = criteriaBuilder.avg(employee.<Number>get("salary"));
criteriaQuery.multiselect(avg, employee.get("address").get("city"));
criteriaQuery.groupBy(employee.get("address").get("city"));
criteriaQuery.orderBy(criteriaBuilder.asc(avg));
// Select employees and the count of their number of projects.
Root employee = criteriaQuery.from(Employee.class);
Expression project = employee.join("projects", JoinType.LEFT);
criteriaQuery.multiselect(e, criteriaBuilder.count(project));
criteriaQuery.groupBy(employee);

Having[edit | edit source]

The query having clause allows for the results of a group by to be filtered. The having clause is defined using the having API on CriteriaQuery with any Predicate object.

// Select the average salaries grouped by city, only including cities with average salaries over 100000.
Root employee = criteriaQuery.from(Employee.class);
Expression avg = criteriaBuilder.avg(employee.<Number>get("salary"));
criteriaQuery.multiselect(avg, employee.get("address").get("city"));
criteriaQuery.groupBy(employee.get("address").get("city"));
criteriaQuery.having(criteriaBuilder.greaterThan(avg, 100000));

CriteriaUpdate (JPA 2.1)[edit | edit source]

CriteriaUpdate defines a database update query. A CriteriaUpdate models all of the clauses of a JPQL update query. Elements from one CriteriaUpdate cannot be used in other CriteriaUpdates. A CriteriaUpdate is used with the EntityManager createQuery() API to create a JPA Query. Criteria updates should only be used for batch updates. For regular updates to objects, the objects should be read through the EntityManager in a transaction an modified in Java, and have the changes committed.

CriteriaUpdate defines the following clauses and options:

  • set(String, Object), set(Path, Object), set(Path, Expression) - Defines the update's set clause.
  • where(Expression), where(Predicate...) - Defines the update's where clause. By default all instances of the class are updated.

The Expressions, Predicates elements are defined using the CriteriaBuilder API and expressions derived from the from Root elements.

CriteriaUpdate examples[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Updates the salary to 90,000 of all Employee's making more than 100,000.
CriteriaUpdate update = criteriaBuilder.createCriteriaUpdate(Employee.class);
Root employee = update.from(Employee.class);
update.set("salary", 90000);
update.where(criteriaBuilder.greaterThan(employee.get("salary"), 100000));
Query query = entityManager.createQuery(update);
int rowCount = query.executeUpdate();

// Gives all Employees a 10% raise.
CriteriaUpdate update = criteriaBuilder.createCriteriaUpdate(Employee.class);
Root employee = update.from(Employee.class);
update.set(employee.get("salary"), criteriaBuilder.sum(employee.get("salary"), criteriaBuilder.quot(employee.get("salary"), 10));
Query query = entityManager.createQuery(update);
int rowCount = query.executeUpdate();

CriteriaDelete (JPA 2.1)[edit | edit source]

CriteriaDelete defines a database delete query. A CriteriaDelete models all of the clauses of a JPQL delete query. Elements from one CriteriaDelete cannot be used in other CriteriaDelete. A CriteriaDelete is used with the EntityManager createQuery() API to create a JPA Query. CriteriaDelete should only be used for batch deletes. For regular deletes of objects, the objects should be read through the EntityManager in a transaction and deleted through the remove() API, and have the changes committed.

CriteriaDelete defines the following clauses and options:

  • where(Expression), where(Predicate...) - Defines the delete's where clause. By default all instances of the class are deleted.

The Expressions, Predicates elements are defined using the CriteriaBuilder API and expressions derived from the from Root elements.

CriteriaDelete examples[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Deletes all Employee's making more than 100,000.
CriteriaDelete delete = criteriaBuilder.createCriteriaDelete(Employee.class);
Root employee = delete.from(Employee.class);
delete.where(criteriaBuilder.greaterThan(employee.get("salary"), 100000));
Query query = entityManager.createQuery(delete);
int rowCount = query.executeUpdate();

// Deletes all Employees.
CriteriaDelete delete = criteriaBuilder.createCriteriaDelete(Employee.class);
Root employee = delete.from(Employee.class);
Query query = entityManager.createQuery(delete);
int rowCount = query.executeUpdate();

Where[edit | edit source]

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

Comparison operations defined on CriteriaBuilder[edit | edit source]

Operation Description Example
equal, notEqual equal
criteriaBuilder.equal(employee.get("firstName"), "Bob")
lessThan, lt less than
criteriaBuilder.lessThan(employee.get("salary"), 100000)
greaterThan, gt greater than
criteriaBuilder.greaterThan(employee.get("salary"), criteriaBuilder.parameter(Integer.class, "sal"))
lessThanOrEqualTo, le less than or equal
criteriaBuilder.lessThanOrEqualTo(employee.get("salary"), 100000)
greaterThanOrEqualTo, ge greater than or equal
criteriaBuilder.greaterThanOrEqualTo(employee.get("salary"), criteriaBuilder.parameter(Integer.class, "sal"))
like, notLike evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional
criteriaBuilder.like(employee.get("firstName"), "A%")
criteriaBuilder.notLike(employee.get("firstName"), "%._%", '.')
between evaluates if the value is between the two values
criteriaBuilder.between(employee.<String>get("firstName"), "A", "C")
isNull compares the value to null, databases may not allow or have unexpected results when using = with null
criteriaBuilder.isNull(employee.get("endDate"))
employee.get("endDate").isNull()
in evaluates if the value is contained in the list
criteriaBuilder.in(employee.get("firstName")).value("Bob").value("Fred").value("Joe")
employee.get("firstName").in("Bob", "Fred", "Joe")
employee.get("firstName").in(criteriaBuilder.parameter(List.class, "names")

Logical operations defined on CriteriaBuilder[edit | edit source]

Operation Description Example
and and two or more predicates together
criteriaBuilder.and(criteriaBuilder.equal(employee.get("firstName"), "Bob"), criteriaBuilder.equal(employee.get("lastName"), "Smith"))
or or two or more predicates together
criteriaBuilder.or(criteriaBuilder.equal(employee.get("firstName"), "Bob"), criteriaBuilder.equal(employee.get("firstName"), "Bobby"))
not negate a predicate
criteriaBuilder.not(criteriaBuilder.or(criteriaBuilder.equal(employee.get("firstName"), "Bob"), criteriaBuilder.equal(employee.get("firstName"), "Bobby")))
criteriaBuilder.or(criteriaBuilder.equal(employee.get("firstName"), "Bob"), criteriaBuilder.equal(employee.get("firstName"), "Bobby")).not()
conjunction predicate for true
Predicate where = criteriaBuilder.conjunction();
if (name != null) {
    where = criteriaBuilder.and(where, criteriaBuilder.equal(employee.get("firstName"), name));
}
disjunction predicate for false
Predicate where = criteriaBuilder.disjunction();
if (name != null) {
    where = criteriaBuilder.or(where, criteriaBuilder.equal(employee.get("firstName"), name));
}

SubQuery[edit | edit source]

Subqueries can only be used in the where clause and having clause. A subQuery is created from a CriteriaQuery using the subQuery operation. Most subQuery usage restricts the subQuery to returning a single result and value, unless used with the CriteriaBuilder exists, all, any, or some operations, or with an in operation.

subQuery examples[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Find all manager that only manage below-average employees.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
Subquery subQuery = criteriaQuery.subquery(Employee.class);
Root employee_2 = subQuery.from(Employee.class);
subQuery.where(criteriaBuilder.and(employee_2.get("manager").equal(e), criteriaBuilder.equal(employee_2.get("productivity"), "below average").not());
criteriaQuery.where(criteriaBuilder.exists(subQuery).not());
Query query = entityManager.createQuery(criteriaQuery)
List<Employee> = query.getResultList();

// Find the employee with the lowest salary.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
Subquery subQuery = criteriaQuery.subquery(Employee.class);
Root employee_2 = subQuery.from(Employee.class);
subQuery.select(employee_2.get("salary"));
criteriaQuery.where(criteriaBuilder.lessThan(employee.get("salary"), criteriaBuilder.all(subQuery)));
Query query = entityManager.createQuery(criteriaQuery)
List<Employee> = query.getResultList();

Parameters[edit | edit source]

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.

Named parameter criteria example[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.where(
        criteriaBuilder.equal(employee.get("firstName"), criteriaBuilder.parameter(String.class, "first")),
        criteriaBuilder.equal(employee.get("lastName"), criteriaBuilder.parameter(String.class, "last"))
    );
Query query = entityManager.createQuery(criteriaQuery)
query.setParameter("first", "Bob");
query.setParameter("last", "Smith");
List<Employee> = query.getResultList();
Positional parameter criteria example[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.where(criteriaBuilder.equal(employee.get("firstName"), criteriaBuilder.parameter(String.class)), criteriaBuilder.equal(employee.get("lastName"), criteriaBuilder.parameter(String.class)));
Query query = entityManager.createQuery(criteriaQuery)
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> = query.getResultList();

Functions[edit | edit source]

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.

CriteriaBuilder database functions[edit | edit source]

Function Description Example
diff subtraction
criteriaBuilder.diff(employee.<Number>get("salary"), 1000)
sum addition
criteriaBuilder.sum(employee.<Number>get("salary"), 1000)
prod multiplication
criteriaBuilder.prod(employee.<Number>get("salary"), 2)
quot division
criteriaBuilder.quot(employee.<Number>get("salary"), 2)
abs absolute value
criteriaBuilder.abs(
    criteriaBuilder.diff(employee.<Number>get("salary"), employee.get("manager").<Number>get("salary")))
selectCase defines a case statement
criteriaBuilder.selectCase(employee.get("status")).
    when(0, "active").
    when(1, "consultant").
    otherwise("unknown")
criteriaBuilder.selectCase().
    when(criteriaBuilder.equal(employee.get("status"), 0), "active").
    when(criteriaBuilder.equal(employee.get("status"), 1), "consultant").
    otherwise("unknown")
coalesce evaluates to the first non null argument value
criteriaBuilder.coalesce(criteriaBuilder.concat(employee.<Number>get("salary"), 0)
concat concatenates two or more string values
criteriaBuilder.concat(
    criteriaBuilder.concat(employee.<String>get("firstName"), " "), employee.<String>get("lastName"))
currentDate the current date on the database
criteriaBuilder.currentDate()
currentTime the current time on the database
criteriaBuilder.currentTime()
currentTimestamp the current date-time on the database
criteriaBuilder.currentTimestamp()
length the character/byte length of the character or binary value
criteriaBuilder.length(employee.<String>get("lastName"))
locate the index of the string within the string, optionally starting at a start index
criteriaBuilder.locate("-", employee.<String>get("lastName"))
lower convert the string value to lower case
criteriaBuilder.lower(employee.<String>get("lastName"))
mod computes the remainder of dividing the first integer by the second
criteriaBuilder.mod(employee.<Integer>get("hoursWorked"), 8)
nullif returns null if the first argument to equal to the second argument, otherwise returns the first argument
criteriaBuilder.nullif(employee.<Number>get("salary"), 0)
sqrt computes the square root of the number
criteriaBuilder.sqrt(employee.<Number>get("salary"))
substring the substring from the string, starting at the index, optionally with the substring size
criteriaBuilder.substring(employee.<String>get("lastName"), 0, 2)
trim trims leading, trailing, or both spaces or optional trim character from the string
criteriaBuilder.trim(TrimSpec.TRAILING, employee.<String>get("lastName"))
criteriaBuilder.trim(employee.<String>get("lastName"))
criteriaBuilder.trim(TrimSpec.LEADING, '-', employee.<String>get("lastName"))
upper convert the string value to upper case
criteriaBuilder.upper(employee.<String>get("lastName"))

Special Operations[edit | edit source]

The Criteria API defines several special operations that are not database functions, but have special meaning in JPA. Some of these operations are defined on CriteriaBuilder and some are on specific Expression interfaces.

Criteria API special functions[edit | edit source]

Function Description Example
index the index of the ordered List element, only supported when @OrderColumn is used in the mapping,

defined on the ListJoin interface obtained from a From element using the joinList operation

Root employee = criteriaQuery.from(Employee.class);
ListJoin toDo = employee.joinList("toDoList");
criteriaQuery.multiselect(e, toDo);
criteriaQuery.where(criteriaBuilder.equal(toDo.index(), 1));
key, value the key or value of the Map element, defined on the MapJoin interface obtained from a From element using the joinMap operation
Root employee = criteriaQuery.from(Employee.class);
MapJoin p = employee.joinMap("priorities");
criteriaQuery.multiselect(e, p.value());
criteriaQuery.where(criteriaBuilder.equal(p.key(), "high"))
size the size of the collection relationships, this evaluates to a sub-select, defined on the CriteriaBuilder
criteriaBuilder.greaterThan(criteriaBuilder.size(employee.<Collection>get("managedEmployees")), 2)
isEmpty, isNotEmpty evaluates to true if the collection relationship is empty or not, this evaluates to a sub-select, defined on the CriteriaBuilder
criteriaBuilder.isEmpty(employee.<Collection>get("managedEmployees"))
isMember, isNotMember evaluates to true if the collection relationship contains the value, this evaluates to a sub-select, defined on the CriteriaBuilder
criteriaBuilder.isMember("write code", employee.<Collection>get("responsibilities"))
type the inheritance discriminator value, defined on any Path expression
criteriaBuilder.equal(p.type(), LargeProject.class)
as can be used to cast an un-typed expression to a typed expression, EclipseLink also allows this to down cast inherited types
criteriaBuilder.mod(employee.get("id").as(Integer.class), 2)
criteriaBuilder.greaterThan(p.as(LargeProject.class).get("budget"), 1000000)
function call a database specific function, defined on the CriteriaBuilder
criteriaBuilder.greaterThan(criteriaBuilder.function("TO_NUMBER", Number.class, p.get("areaCode")), 613)

Metamodel[edit | edit source]

JPA defines a meta-model that can be used at runtime to query information about the ORM mapping meta-data. The meta-model includes the list of mapped attributes for a class, and their mapping types and cardinality. The meta-model can be used with the Criteria API in place of using strings to reference the class attributes.

JPA defines a set of _ classes that are to be generated by the JPA provider, or IDE, that give compile time access to the meta-model. This allows typed static variables to be used in the Criteria API. This can reduce the occurrence of typos, or invalid queries in application code, by catching query issues at compile time, instead of during testing. It does however add complexity to the development process, as the meta-model static class needs to be generated, and be part of the development cycle.

Metamodel criteria example[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root<Employee> employee = criteriaQuery.from(entityManager.getMetamodel().entity(Employee.class));
criteriaQuery.where(criteriaBuilder.equal(employee.get(Employee_.firstName), "Bob"), criteriaBuilder.equal(employee.get(Employee_.lastName), "Smith"));
Query query = entityManager.createQuery(criteriaQuery)
List<Employee> = query.getResultList();

Tuple Queries[edit | edit source]

A Tuple defines a multi-select query result. Normally an object array is returned by JPA multi-select queries, but an object array is not a very useful data structure. A Tuple is a map-like structure that allows the results to be retrieved by name or index.

Tuple query examples[edit | edit source]
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.multiselect(employee.get("firstName").alias("first"), employee.get("lastName").alias("last"));
Query query = entityManager.createQuery(criteriaQuery);
List<Tuple> results = query.getResultList();
String first = results.get(0).get("first");
String last = results.get(0).get("last");