View source for EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Criteria

{{EclipseLink_UserGuide
|info=y
|toc=y
|eclipselink=y
|eclipselinktype=JPA
|api=y
|apis=
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/CriteriaBuilder.html CriteriaBuilder]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/CriteriaQuery.html CriteriaQuery]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Selection.html Selection]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Root.html Root]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/From.html From]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Join.html Join]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/ListJoin.html ListJoin]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/MapJoin.html MapJoin]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Path.html Path]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Expression.html Expression]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/Predicate.html Predicate]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/Tuple.html Tuple]
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/package-summary.html criteria package]
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/jpa/JpaCriteriaBuilder.html JpaCriteriaBuilder]
|nativeapi=y
|nativeapis=
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/expressions/Expression.html Expression]
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/expressions/ExpressionBuilder.html ExpressionBuilder]
|examples=y
|example=
*[[EclipseLink/Examples/JPA/2.0/Criteria|How to define dynamic queries using the Criteria API]]
}}

=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 programatically 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|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 benefit from EclipseLink's parse cache.

The Criteria API was added in JPA 2.0, and EclipseLink 2.0.

{{EclipseLink_Spec
|section=Chapter 6 "Criteria API"
}}

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

<tt>CriteriaBuilder</tt> defines API to create <tt>CriteriaQuery</tt> objects:
* <tt>createQuery()</tt> - Creates a <tt>CriteriaQuery</tt>.
* <tt>createQuery(Class)</tt> - Creates a <tt>CriteriaQuery</tt> using generics to avoid casting the result class.
* <tt>createTupleQuery()</tt> - Creates a <tt>CriteriaQuery</tt> that returns map like <tt>Tuple</tt> objects, instead of object arrays for multiselect queries.  See [[#Tuple Queries|Tuple Queries]]

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

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

<tt>CriteriaQuery</tt> defines the following clauses and options:
* <tt>distinct(boolean)</tt> - 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.
* <tt>from(Class)</tt> - 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.
* <tt>from(EntityType)</tt> - 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.
* <tt>select(Selection)</tt> - Defines the query's select clause.  If not set, the first root will be selected by default.
* <tt>multiselect(Selection...)</tt>, <tt>multiselect(List<Selection>)</tt> - Defines a multi-select query.
* <tt>where(Expression)</tt>, <tt>where(Predicate...)</tt> - Defines the query's where clause. By default all instances of the class are selected.
* <tt>orderBy(Order...)</tt>, <tt>orderBy(List<Order>)</tt> - Defines the query's order clause.  By default the results are not ordered.
* <tt>groupBy(Expression...)</tt>, <tt>groupBy(List<Expression>)</tt> - Defines the query's group by clause.  By default the results are not grouped.
* <tt>having(Expression)</tt>, <tt>having(Predicate...)</tt> - Defines the query's having clause.  Having allows grouped results to be filtered.
* <tt>subquery(Class)</tt> - Creates a <tt>Subquery</tt> to be used in one of the other clauses.

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

=====''CriteriaQuery examples''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();

// Query for a List of objects.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.where(cb.greaterThan(e.get("salary"), 100000));
Query query = em.createQuery(cq);
List<Employee> result = query.getResultList();

// Query for a single object.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.where(cb.equal(e.get("id"), cb.parameter(Long.class, "id")));
Query query = em.createQuery(cq);
query.setParameter("id", id);
Employee result2 = (Employee)query.getSingleResult();

// Query for a single data element.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.select(cb.max(e.get("salary")));
Query query = em.createQuery(cq);
BigDecimal result3 = (BigDecimal)query.getSingleResult();

// Query for a List of data elements.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.select(e.get("firstName"));
Query query = em.createQuery(cq);
List<String> result4 = query.getResultList();

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

===Selection===
A <tt>Selection</tt> define what is selected by a query.  A <tt>Selection</tt> can be any object expression, attribute expression, function, sub-select, constructor or aggregation function.  An <tt>alias</tt> can be defined for a <tt>Selection</tt> using the <tt>alias()</tt> 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 <tt>groupBy</tt> to return multiple results.

Aggregate functions are defined on <tt>CriteriaBuilder</tt> and include:
* <tt>max(Expression)</tt> - Return the maximum value for all of the results.  Used for numeric types.
* <tt>greatest(Expression)</tt> - Return the maximum value for all of the results.  Used for non-numeric types.
* <tt>min(Expression)</tt> - Return the minimum value for all of the results.  Used for numeric types.
* <tt>least(Expression)</tt> - Return the minimum value for all of the results.  Used for non-numeric types.
* <tt>avg(Expression)</tt> - Return the mean average of all of the results.  A <tt>Double</tt> is returned.
* <tt>sum(Expression)</tt> - Return the sum of all of the results.
* <tt>sumAsLong(Expression)</tt> - Return the sum of all of the results.  A <tt>Long</tt> is returned.
* <tt>sumAsDouble(Expression)</tt> - Return the sum of all of the results.  A <tt>Double</tt> is returned.
* <tt>count(Expression)</tt> - Return the count of all of the results. <tt>null</tt> values are not counted.  A <tt>Long</tt> is returned.
* <tt>countDistinct(Expression)</tt> - Return the count of all of the distinct results. <tt>null</tt> values are not counted.  A <tt>Long</tt> is returned.

<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();

// Count the total employees
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
cq.select(cb.count(employee));
Query query = em.createQuery(cq);
List<Employee> result = query.getResultList();

// Maximum salary
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
cq.select(cb.max(employee.get("salary"));
Query query = em.createQuery(cq);
List<Employee> result = query.getResultList();
</source>

====Constructors ====
The <tt>construct</tt> operator on <tt>CriteriaBuilder</tt> 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.

<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.select(cb.construct(EmpReport.class, e.get("firstName"), e.get("lastName"), e.get("salary")));
Query query = em.createQuery(cq);
List<EmpReport> result = query.getResultList();
</source>

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

<source lang="java">
Root e = cq.from(Employee.class);
</source>

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 <i>where</i> clause should ensure the two objects are joined in some way.

<source lang="java">
// Select the employees and the mailing addresses that have the same address.
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
Root a = cq.from(MailingAddress.class);
cq.multiselect(e, a);
cq.where(cb.equal(e.get("address"), a.get("address"));
Query query = em.createQuery(cq);
List<Object[]> result = query.getResultList();
</source>

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

<source lang="java">
Root e = cq.from(Employee.class);
Join a = e.join("address");
cq.where(cb.equal(a.get("city"), city);
</source>

The <tt>join</tt> 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.

<source lang="java">
// All employees who work on both projects.
Root e = cq.from(Employee.class);
Join p = e.join("projects");
Join p2 = e.join("projects");
cq.where(cb.and(cb.equal(p.get("name"), p1), cb.equal(p2.get("name"), p2));
</source>

====Fetch====
The <tt>fetch</tt> operation can be used on a <tt>From</tt> 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.

<source lang="java">
Root e = cq.from(Employee.class);
Fetch a = e.fetch("address");
cq.select(e);
</source>

Caution should be used in using a <tt>Fetch</tt> in the <i>where</i> 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 <i>where</i> 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 <tt>join</tt> and <tt>fetch</tt> 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 <tt>JoinType</tt> as an argument to the <tt>join</tt> or <tt>fetch</tt> operation.

<source lang="java">
Root e = cq.from(Employee.class);
Join a = e.join("address", JoinType.LEFT);
cq.order(a.get("city"));
</source>

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

<source lang="java">
// 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")));
</source>

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

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

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

<source lang="java">
// Select the average salaries grouped by city.
Root e = cq.from(Employee.class);
cq.multiselect(cb.avg(e.<Number>get("salary")), e.get("address").get("city"));
cq.groupBy(e.get("address").get("city"));
</source>

<source lang="java">
// Select the average salaries grouped by city, ordered by the average salary.
Root e = cq.from(Employee.class);
Expression avg = cb.avg(e.<Number>get("salary"));
cq.multiselect(avg, e.get("address").get("city"));
cq.groupBy(e.get("address").get("city"));
cq.orderBy(avg);
</source>

<source lang="java">
// Select employees and the count of their number of projects.
Root e = cq.from(Employee.class);
Expression p = e.join("projects", JoinType.LEFT);
cq.multiselect(e, cb.count(p));
cq.groupBy(e);
</source>

===Having===
The query <i>having</i> clause allows for the results of a <i>group by</i> to be filtered.  The <i>having</i> clause is defined using the <tt>having</tt> API on <tt>CriteriaQuery</tt> with any <tt>Predicate</tt> object.

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

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

{|{{BMTableStyle}}
|+{{BMTableCaptionStyle}}|Comparison operations defined on <tt>CriteriaBuilder</tt>
|-{{BMTHStyle}}
| Operation
| Description
| Example
|- 
| <tt>equal</tt>, <tt>notEqual</tt>
| equal
| <source lang="java">cb.lessThan(e.get("firstName"), "Bob")</source>
|-
| <tt>lessThan</tt>, <tt>lt</tt>
| less than
| <source lang="java">cb.lessThan(e.get("salary"), 100000)</source>
|-
| <tt>greaterThan</tt>, <tt>gt</tt>
| greater than
| <source lang="java">cb.greaterThan(e.get("salary"), cb.parameter(Integer.class, "sal"))</source>
|-
|-
| <tt>lessThanOrEqualTo</tt>, <tt>le</tt>
| less than or equal
| <source lang="java">cb.lessThanOrEqualTo(e.get("salary"), 100000)</source>
|-
| <tt>greaterThanOrEqualTo</tt>, <tt>ge</tt>
| greater than or equal
| <source lang="java">cb.greaterThanOrEqualTo(e.get("salary"), cb.parameter(Integer.class, "sal"))</source>
|-
| <tt>like</tt>, <tt>notLike</tt>
| evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional
| <source lang="java">cb.or(cb.like(e.get("firstName"), "A%")</source>
<source lang="java">cb.notLike(e.get("firstName"), "%._%", '.'))</source>
|-
| <tt>between</tt>
| evaluates if the value is between the two values
| <source lang="java">cb.between(e.<String>get("firstName"), "A", "C"))</source>
|-
| <tt>isNull</tt>
| compares the value to null, databases may not allow or have unexpected results when using = with null
| <source lang="java">cb.isNull(e.get("endDate"))</source>
<source lang="java">e.get("endDate").isNull()</source>
|-
| <tt>in</tt>
| evaluates if the value is contained in the list
| <source lang="java">cb.in(e.get("firstName")).value("Bob").value("Fred").value("Joe")</source>
<source lang="java">e.get("firstName").in("Bob", "Fred", "Joe")</source>
<source lang="java">e.get("firstName").in(cb.parameter(List.class, "names")</source>
|-
|}

Logical operations defined on <tt>CriteriaBuilder</tt>:
{|{{BMTableStyle}}
|+{{BMTableCaptionStyle}}|Logical operations defined on <tt>CriteriaBuilder</tt>
|-{{BMTHStyle}}
| Operation
| Description
| Example
|- 
| <tt>and</tt>
| <i>and</i> two or more predicates together
| <source lang="java">cb.and(cb.equal(e.get("firstName"), "Bob"), cb.equal(e.get("lastName"), "Smith"))</source>
|- 
| <tt>or</tt>
| <i>or</i> two or more predicates together
| <source lang="java">cb.or(cb.equal(e.get("firstName"), "Bob"), cb.equal(e.get("firstName"), "Bobby"))</source>
|-
| <tt>not</tt>
| negate a predicate
| <source lang="java">cb.not(cb.or(cb.equal(e.get("firstName"), "Bob"), cb.equal(e.get("firstName"), "Bobby")))</source>
<source lang="java">cb.or(cb.equal(e.get("firstName"), "Bob"), cb.equal(e.get("firstName"), "Bobby")).not()</source>
|-
| <tt>conjunction</tt>
| predicate for true
| <source lang="java">Predicate where = cb.conjunction();
if (name != null) {
    where = cb.and(where, cb.equal(e.get("firstName"), name);
}</source>
|-
| <tt>disjunction</tt>
| predicate for false
| <source lang="java">Predicate where = cb.disjunction();
if (name != null) {
    where = cb.or(where, cb.equal(e.get("firstName"), name);
}</source>
|-
|}

==Subquery==
Subqueries can be used in the Criteria API in the select, where, order, group by, or having clauses.  A subquery is created from a <tt>CriteriaQuery</tt> using the <tt>subquery</tt> operation.  Most subquery usage restricts the subquery to returning a single result and value, unless used with the <tt>CriteriaBuilder</tt> <tt>exists</tt>, <tt>all</tt>, <tt>any</tt>, or <tt>some</tt> operations, or with an <tt>in</tt> operation.

=====''Subquery examples''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();

// Find all manager that only manager below average employees.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
Subquery sq = cq.subquery(Employee.class);
Root e2 = cq.from(Employee.class);
sq.where(cb.and(e2.get("manager").equal(e), cb.equal(e2.get("productivity"), "below average").not());
cq.where(cb.exists(sq).not());
Query query = em.createQuery(cq)
List<Employee> = query.getResultList();

// Find the employee with the lowest salary.
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
Subquery sq = cq.subquery(Employee.class);
Root e2 = cq.from(Employee.class);
sq.select(e2.get("salary"));
cq.where(cb.lessThan(e.get("salary"), cb.all(sq)));
Query query = em.createQuery(cq)
List<Employee> = query.getResultList();
</source>

==Parameters==
Parameters can be defined using the <tt>parameter</tt> API on <tt>CriteriaBuilder</tt>.  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 <tt>1</tt> not <tt>0</tt>.

=====''Named parameter criteria example''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.where(cb.equal(e.get("firstName"), cb.parameter(String.class, "first")), cb.equal(e.get("lastName"), cb.parameter(String.class, "last")));
Query query = em.createQuery(cq)
query.setParameter("first", "Bob");
query.setParameter("last", "Smith");
List<Employee> = query.getResultList();
</source>

=====''Positional parameter criteria example''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root e = cq.from(Employee.class);
cq.where(cb.equal(e.get("firstName"), cb.parameter(String.class)), cb.equal(e.get("lastName"), cb.parameter(String.class)));
Query query = em.createQuery(cq)
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> = query.getResultList();
</source>

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

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

==Special Operations==
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 <tt>CriteriaBuilder</tt> and some are on specific Expression interfaces.

{|{{BMTableStyle}}
|+{{BMTableCaptionStyle}}|JPQL special functions
|-{{BMTHStyle}}
| Function
| Description
| Example
|- 
| <tt>index</tt>
| the index of the ordered List element, only supported when @OrderColumn is used in the mapping,
defined on the <tt>ListJoin</tt> interface obtained from a <tt>From</tt> element using the <tt>joinList</tt> operation
| <source lang="java">Root e = cq.from(Employee.class);
ListJoin toDo = e.joinList("toDoList");
cq.multiselect(e, toDo);
cq.where(cb.equal(toDo.index(), 1));</source>
|- 
| <tt>key</tt>, <tt>value</tt>
| the key or value of the Map element, defined on the <tt>MapJoin</tt> interface obtained from a <tt>From</tt> element using the <tt>joinMap</tt> operation
| <source lang="java">Root e = cq.from(Employee.class);
MapJoin p = e.joinMap("priorities");
cq.multiselect(e, p.value());
cq.where(cb.equal(p.key(), "high"))</source>
|- 
| <tt>size</tt>
| the size of the collection relationships, this evaluates to a sub-select, defined on the <tt>CriteriaBuilder</tt>
| <source lang="java">cb.greaterThan(cb.size(e.<Collection>get("managedEmployees")), 2)</source>
|- 
| <tt>isEmpty</tt>, <tt>isNotEmpty</tt>
| evaluates to true if the collection relationship is empty or not, this evaluates to a sub-select, defined on the <tt>CriteriaBuilder</tt>
| <source lang="java">cb.isEmpty(e.<Collection>get("managedEmployees"))</source>
|- 
| <tt>isMember</tt>, <tt>isNotMember</tt>
| evaluates to true if the collection relationship contains the value, this evaluates to a sub-select, defined on the <tt>CriteriaBuilder</tt>
| <source lang="java">cb.isMember("write code", e.<Collection>get("responsibilities"))</source>
|- 
| <tt>type</tt>
| the inheritance discriminator value, defined on any <tt>Path</tt> expression
| <source lang="java">cb.equal(p.type(), LargeProject.class)</source>
|- 
| <tt>as</tt>
| can be used to cast an un-typed expression to a typed expression, EclipseLink also allows this to down cast inherited types
| <source lang="java">cb.mod(e.get("id").as(Integer.class), 2)</source>
<source lang="java">cb.greaterThan(p.as(LargeProject.class).get("budget"), 1000000)</source>
|- 
| <tt>function</tt>
| call a database specific function, defined on the <tt>CriteriaBuilder</tt>
| <source lang="java">cb.greaterThan(cb.function("TO_NUMBER", Number.class, p.get("areaCode")), 613)</source>
|}

==Metamodel==
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 <tt>_</tt> 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.

More more information on the JPA meta-model and on how to generate the meta-model see, [[EclipseLink/UserGuide/JPA/Advanced JPA Development/Metamodel|Metamodel]]

=====''Metamodel criteria example''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<Employee> e = cq.from(em.getMetamodel().entity(Employee.class));
cq.where(cb.equal(e.get(Employee_.firstName), "Bob"), cb.equal(e.get(Employee_.lastName), "Smith"));
Query query = em.createQuery(cq)
List<Employee> = query.getResultList();
</source>

==Tuple Queries==
A <tt>Tuple</tt> 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 <tt>Tuple</tt> is a map-like structure that allows the results to be retrieved by name or index.

=====''Tuple query examples''=====
<source lang="java">
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root e = cq.from(Employee.class);
cq.multiselect(e.get("firstName").alias("first"), employee.get("lastName").alias("last"));
Query query = em.createQuery(cq);
List<Tuple> results = query.getResultList();
String first = results.get(0).get("first");
String last = results.get(0).get("last");
</source>

==JpaCriteriaBuilder and EclipseLink Extensions==
EclipseLink's Criteria API support has fewer restrictions than what JPA specifies.
In general sub-queries and object path expressions are allowed in most places, including:
* Sub-queries in the select, group by, and order clauses;
* Sub-query usage with functions;
* <i>in</i> usage with object path expressions;
* Order by usage with object path expressions.

EclipseLink's Criteria API support is built on top of EclipseLink native <tt>Expression</tt> API.  EclipseLink provides the <tt>JpaCriteriaBuilder</tt> interface to allow the conversion of native <tt>Expression</tt> objects to and from JPA <tt>Expression</tt> objects.  This allows the EclipseLink native <tt>Expression</tt> API to be mixed with the JPA Criteria API.  Support for <tt>JpaCriteriaBuilder</tt> was added in EclipseLink 2.4.

The EclipseLink native <tt>Expression</tt> API provides the following additional functionality:
* Additional database functions (over 80 database functions are supported);
* Usage of custom <tt>ExpressionOperators</tt>;
* Embedding of SQL within an Expression query;
* Usage of sub-selects in the <i>from</i> clause;
* <i>on</i> clause support;
* Access to unmapped columns and tables;
* Historical querying.

EclipseLink <tt>Expressions</tt> can be combined with EclipseLink <tt>DatabaseQuerys</tt> to provide additional functionality:
* Unions, intersect and except clauses;
* Hierarchical connect by clauses;
* Batch fetching.

=====''JpaCriteriaBuilder native Expression example''=====
<source lang="java">
JpaCriteriaBuilder cb = (JpaCriteriaBuilder)em.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(Employee.class);
Root e = cq.from(Employee.class);
cq.where(cb.fromExpression(cb.toExpression(e.get("firstName")).regexp("^Dr\.*")));
</source>

{{EclipseLink_JPA
|previous= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL|JPQL]]
|next=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Named|Named Queries]]
|up=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying|Querying]]
|version=2.4 DRAFT}}