Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Criteria"

(CriteriaQuery)
(JpaCriteriaBuilder native Expression example)
(75 intermediate revisions by the same user not shown)
Line 8: Line 8:
 
* [http://www.eclipse.org/eclipselink/api/latest/javax/persistence/criteria/CriteriaBuilder.html CriteriaBuilder]
 
* [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/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/javax/persistence/criteria/package-summary.html criteria package]
 
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/jpa/JpaCriteriaBuilder.html JpaCriteriaBuilder]
 
* [http://www.eclipse.org/eclipselink/api/latest/org/eclipse/persistence/jpa/JpaCriteriaBuilder.html JpaCriteriaBuilder]
Line 21: Line 31:
 
=Criteria API=
 
=Criteria API=
 
The Java Persistence Criteria API is used to define dynamic queries through the construction of object-based
 
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.
+
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-safe mode, and the non-typed mode.  The type-safe 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 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.
 
The Criteria API was added in JPA 2.0, and EclipseLink 2.0.
Line 37: Line 49:
 
* <tt>createQuery()</tt> - Creates a <tt>CriteriaQuery</tt>.
 
* <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>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.
+
* <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.
 
<tt>CriteriaBuilder</tt> also defines all supported comparison operations and functions used for defining the query's clauses.
Line 50: Line 62:
 
* <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>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>select(Selection)</tt> - Defines the query's select clause.  If not set, the first root will be selected by default.
* <tt>multiselect(Selection...)</tt> - Defines a multi-select query.
+
* <tt>multiselect(Selection...)</tt>, <tt>multiselect(List<Selection>)</tt> - Defines a multi-select query.
* <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>where(Expression)</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>where(Predicate...)</tt> - Defines the query's where clause. By default all instances of the class are selected.
+
* <tt>groupBy(Expression...)</tt>, <tt>groupBy(List<Expression>)</tt> - Defines the query's group by clause.  By default the results are not grouped.
* <tt>orderBy(Order...)</tt> - Defines the query's order clause.  By default the results are not ordered.
+
* <tt>having(Expression)</tt>, <tt>having(Predicate...)</tt> - Defines the query's having clause.  Having allows grouped results to be filtered.
* <tt>orderBy(List<Order>)</tt> - Defines the query's order clause.  By default the results are not ordered.
+
* <tt>groupBy(Expression...)</tt> - Defines the query's group by clause.  By default the results are not grouped.
+
* <tt>groupBy(List<Expression>)</tt> - Defines the query's group by clause.  By default the results are not grouped.
+
* <tt>having(Expression)</tt> - Defines the query's having clause.  Having allows grouped results to be filtered.
+
* <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.
 
* <tt>subquery(Class)</tt> - Creates a <tt>Subquery</tt> to be used in one of the other clauses.
  
Line 70: Line 77:
 
// Query for a List of objects.
 
// Query for a List of objects.
 
CriteriaQuery cq = cb.createQuery();
 
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
+
Root e = cq.from(Employee.class);
cq.where(cb.greaterThan(employee.get("salary"), 100000));
+
cq.where(cb.greaterThan(e.get("salary"), 100000));
 
Query query = em.createQuery(cq);
 
Query query = em.createQuery(cq);
 
List<Employee> result = query.getResultList();
 
List<Employee> result = query.getResultList();
Line 77: Line 84:
 
// Query for a single object.
 
// Query for a single object.
 
CriteriaQuery cq = cb.createQuery();
 
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
+
Root e = cq.from(Employee.class);
cq.where(cb.equal(employee.get("id"), cb.parameter(Long.class, "id")));
+
cq.where(cb.equal(e.get("id"), cb.parameter(Long.class, "id")));
 
Query query = em.createQuery(cq);
 
Query query = em.createQuery(cq);
 
query.setParameter("id", id);
 
query.setParameter("id", id);
Line 85: Line 92:
 
// Query for a single data element.
 
// Query for a single data element.
 
CriteriaQuery cq = cb.createQuery();
 
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
+
Root e = cq.from(Employee.class);
 +
cq.select(cb.max(e.get("salary")));
 
Query query = em.createQuery(cq);
 
Query query = em.createQuery(cq);
query.select(cb.max(employee.get("salary")));
 
 
BigDecimal result3 = (BigDecimal)query.getSingleResult();
 
BigDecimal result3 = (BigDecimal)query.getSingleResult();
  
 
// Query for a List of data elements.
 
// Query for a List of data elements.
 
CriteriaQuery cq = cb.createQuery();
 
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
+
Root e = cq.from(Employee.class);
 +
cq.select(e.get("firstName"));
 
Query query = em.createQuery(cq);
 
Query query = em.createQuery(cq);
query.select(employee.get("firstName"));
 
 
List<String> result4 = query.getResultList();
 
List<String> result4 = query.getResultList();
  
 
// Query for a List of element arrays.
 
// Query for a List of element arrays.
 
CriteriaQuery cq = cb.createQuery();
 
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
+
Root e = cq.from(Employee.class);
 +
cq.multiselect(e.get("firstName"), employee.get("lastName"));
 
Query query = em.createQuery(cq);
 
Query query = em.createQuery(cq);
query.multiselect(employee.get("firstName"), employee.get("lastName"));
 
 
List<Object[]> result5 = query.getResultList();
 
List<Object[]> result5 = query.getResultList();
 
</source>
 
</source>
Line 109: Line 116:
  
 
====Aggregation functions====
 
====Aggregation functions====
Aggregation functions can include summary information on a set of objects.  These include MIN, MAX, AVG, SUM, COUNT.  These functions can be used to return a single result, or can be used with a <tt>groupBy</tt> to return multiple results.
+
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.
  
<source lang="SQL">SELECT COUNT(e) FROM Employee e</source>
+
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="SQL">SELECT MAX(e.salary) FROM Employee e</source>
+
<source lang="java">
 +
CriteriaBuilder cb = em.getCriteriaBuilder();
  
====Constructors ====
+
// Count the total employees
The NEW operator can be used with the fully qualified class name to return data objects from a JPQL query. These will not be managed objects, and the class must define a constructor that matches the arguments of the constructor and their 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.
+
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();
  
<source lang="SQL">SELECT NEW com.acme.reports.EmpReport(e.firstName, e.lastName, e.salary) FROM Employee e</source>
+
// 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>
  
===FROM Clause===
+
====Constructors ====
The FROM clause defines what is being queriedA typical FROM clause will contain the entity name being queried and assign it an alias.
+
The <tt>construct</tt> operator on <tt>CriteriaBuilder</tt> can be used with a class and values to return data objects from a criteria queryThese 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="SQL">SELECT e FROM Employee e</source>
+
<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>
  
JPQL allows for multiple root level objects to be queried.  Caution should be used when doing this, as it can result in Cartesian products of the two tableThe WHERE or ON clause should ensure the two objects are joined in some way.
+
===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 queryA <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="SQL">SELECT e, a FROM Employee e, MailingAddress a WHERE e.address = a.address</source>
+
<source lang="java">
 +
Root e = cq.from(Employee.class);
 +
</source>
  
The entity name used in JPQL comes from the name attribute of the @Entity annotation or XMLIt defaults to the simple entity class name.  EclipseLink also allows for the fully qualified class name of the entity to be used (as of EclipseLink 2.4).
+
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 tableThe <i>where</i> clause should ensure the two objects are joined in some way.
  
<source lang="SQL">SELECT e FROM com.acme.Employee e</source>
+
<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====
+
====Join====
A JOIN clause can also be used in the FROM clause.  The JOIN clause allows any of the object's relationships to be joined into the query so they can be used in the WHERE clauseJOIN does not mean the relationships will be fetched, unless the FETCH option is included.
+
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="SQL">SELECT e FROM Employee e JOIN e.address a WHERE a.city = :city</source>
+
<source lang="java">
 +
Root e = cq.from(Employee.class);
 +
Join a = e.join("address");
 +
cq.where(cb.equal(a.get("city"), city);
 +
</source>
  
JOIN 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.
+
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="SQL">SELECT e FROM Employee e JOIN e.projects p JOIN e.projects p2 WHERE p.name = :p1 and p2.name = :p2</source>
+
<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>
  
====JOIN FETCH====
+
====Fetch====
The FETCH option can be used on a JOIN 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.
+
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="SQL">SELECT e FROM Employee e JOIN FETCH e.address</source>
+
<source lang="java">
 +
Root e = cq.from(Employee.class);
 +
Fetch a = e.fetch("address");
 +
cq.select(e);
 +
</source>
  
JOIN FETCH normally does not allow an alias, but as of EclipseLink 2.4 an alias is allowed.  The alias should be used with caution, as it can affect how the resulting objects are built.  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.
+
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.
  
<source lang="SQL">SELECT e FROM Employee e JOIN FETCH e.address a ORDER BY a.city</source>
+
====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.
  
====LEFT JOIN====
+
<source lang="java">
By default all joins in JPQL 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 options.
+
Root e = cq.from(Employee.class);
 +
Join a = e.join("address", JoinType.LEFT);
 +
cq.order(a.get("city"));
 +
</source>
  
<source lang="SQL">SELECT e FROM Employee e LEFT JOIN e.address a ORDER BY a.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.
  
===ORDER BY clause===
+
<source lang="java">
ORDER BY allows the ordering of the results to be specified. Multiple values can be ordered, either ascending (ASC) or descending (DESC). EclipseLink allows functions, sub-selects and other operations in the ORDER BY clause. EclipseLink allows objects expressions to be used in the ORDER BY.  In the case of entity objects, they are ordered by their Id, in case of embeddable objects, they are ordered by all of their fields. EclipseLink (as of 2.4) also allows for NULL ordering to be specified (either FIRST or LAST).
+
// 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="sql">SELECT e FROM Employee e ORDER BY e.lastName ASC, e.firstName, ASC</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="sql">SELECT e FROM Employee e ORDER BY UPPER(e.lastName)</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>
  
<source lang="sql">SELECT e FROM Employee e LEFT JOIN e.manager m ORDER BY m.lastName NULLS FIRST</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="sql">SELECT e FROM Employee e ORDER BY e.address</source>
+
<source lang="java">
 
+
// Select the average salaries grouped by city, only including cities with average salaries over 100000.
===GROUP BY Clause===
+
Root e = cq.from(Employee.class);
GROUP BY allows for summary information to be computed on a set of objects.  GROUP BY is normally used in conjunction with aggregation functions.  EclipseLink supports using objects, functions and sub-selects in the GROUP BY clause.
+
Expression avg = cb.avg(e.<Number>get("salary"));
 
+
cq.multiselect(avg, e.get("address").get("city"));
<source lang="sql">SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city</source>
+
cq.groupBy(e.get("address").get("city"));
 
+
cq.having(cb.greaterThan(avg, 100000));
<source lang="sql">SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city ORDER BY AVG(e.salary)</source>
+
</source>
 
+
<source lang="sql">SELECT e, COUNT(p) FROM Employee e LEFT JOIN e.projects p GROUP BY e</source>
+
 
+
===HAVING Clause===
+
The HAVING clause allows for the results of a GROUP BY to be filtered.  EclipseLink supports using comparisons, objects, functions and sub-selects in the HAVING clause.
+
 
+
<source lang="sql">SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city HAVING AVG(e.salary) > 100000</source>
+
 
+
==WHERE Clause==
+
The WHERE clause is normally the main part of the query as it defines the conditions that filter what is returned.  The WHERE clause can use any comparison operation, logical operation, functions, attributes, objects, and sub-selects.  The comparison operations include =, <, >, <=, >=, <>, LIKE, BETWEEN, IS NULL, and IN.  NOT can also be used with any comparison operation (NOT LIKE, NOT BETWEEN, IS NOT NULL, NOT IN).  The logical operations include AND, OR, and NOT.
+
  
EclipseLink also supports the REGEXP operation to perform regular expression comparisons (requires database to support regular expressions).  EclipseLink allows for functions and sub-selects to be used with any operation.
+
==Where==
 +
The <i>where</i> clause is normally the main part of the query as it defines the conditions (predicates) that filter what is returnedThe <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}}
 
{|{{BMTableStyle}}
|+{{BMTableCaptionStyle}}|Comparison operations
+
|+{{BMTableCaptionStyle}}|Comparison operations defined on <tt>CriteriaBuilder</tt>
 
|-{{BMTHStyle}}
 
|-{{BMTHStyle}}
 
| Operation
 
| Operation
Line 197: Line 290:
 
| Example
 
| Example
 
|-  
 
|-  
| =
+
| <tt>equal</tt>, <tt>notEqual</tt>
 
| equal
 
| equal
| <source lang="sql">e.firstName = 'Bob'</source>
+
| <source lang="java">cb.lessThan(e.get("firstName"), "Bob")</source>
 
|-
 
|-
| <
+
| <tt>lessThan</tt>, <tt>lt</tt>
 
| less than
 
| less than
| <source lang="sql">e.salary < 100000</source>
+
| <source lang="java">cb.lessThan(e.get("salary"), 100000)</source>
 
|-
 
|-
| >
+
| <tt>greaterThan</tt>, <tt>gt</tt>
 
| greater than
 
| greater than
| <source lang="sql">e.salary > :sal</source>
+
| <source lang="java">cb.greaterThan(e.get("salary"), cb.parameter(Integer.class, "sal"))</source>
 
|-
 
|-
 
|-
 
|-
| <
+
| <tt>lessThanOrEqualTo</tt>, <tt>le</tt>
 
| less than or equal
 
| less than or equal
| <source lang="sql">e.salary <= 100000</source>
+
| <source lang="java">cb.lessThanOrEqualTo(e.get("salary"), 100000)</source>
 
|-
 
|-
| >
+
| <tt>greaterThanOrEqualTo</tt>, <tt>ge</tt>
 
| greater than or equal
 
| greater than or equal
| <source lang="sql">e.salary >= :sal</source>
+
| <source lang="java">cb.greaterThanOrEqualTo(e.get("salary"), cb.parameter(Integer.class, "sal"))</source>
 
|-
 
|-
| LIKE
+
| <tt>like</tt>, <tt>notLike</tt>
 
| evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional
 
| evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional
| <source lang="sql">e.firstName LIKE 'A%' OR e.firstName NOT LIKE '%._%' ESCAPE '.'</source>
+
| <source lang="java">cb.or(cb.like(e.get("firstName"), "A%")</source>
 +
<source lang="java">cb.notLike(e.get("firstName"), "%._%", '.'))</source>
 
|-
 
|-
| BETWEEN
+
| <tt>between</tt>
 
| evaluates if the value is between the two values
 
| evaluates if the value is between the two values
| <source lang="sql">e.firstName BETWEEN 'A' AND 'C'</source>
+
| <source lang="java">cb.between(e.<String>get("firstName"), "A", "C"))</source>
 
|-
 
|-
| IS NULL
+
| <tt>isNull</tt>
 
| compares the value to null, databases may not allow or have unexpected results when using = with null
 
| compares the value to null, databases may not allow or have unexpected results when using = with null
| <source lang="sql">e.endDate IS NULL</source>
+
| <source lang="java">cb.isNull(e.get("endDate"))</source>
 +
<source lang="java">e.get("endDate").isNull()</source>
 
|-
 
|-
| IN
+
| <tt>in</tt>
 
| evaluates if the value is contained in the list
 
| evaluates if the value is contained in the list
| <source lang="sql">e.firstName IN ('Bob', 'Fred', 'Joe')</source>
+
| <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>
 
|-
 
|-
 
|}
 
|}
  
The IN operation allows for a list of values or parameters, a single list parameter, or a sub-select.
+
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>
 +
|-
 +
|}
  
<source lang="sql">
+
==Subquery==
e.firstName IN (:name1, :name2, :name3)
+
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.
e.firstName IN (:name1)
+
e.firstName IN :names
+
e.firstName IN (SELECT e2.firstName from Employee e2 WHERE e2.lastName = 'Smith')
+
</source>
+
  
A sub-select can be used with any operation provided it returns a single value, or if the ALL or ANY options are used.  ALL indicates the operation must be true for all elements returned by the sub-select, ANY indicates the operation must be true for any of the elements returned by the sub-select.
+
=====''Subquery examples''=====
 +
<source lang="java">
 +
CriteriaBuilder cb = em.getCriteriaBuilder();
  
<source lang="sql">
+
// Find all manager that only manager below average employees.
e.firstName = (SELECT e2.firstName from Employee e2 WHERE e2.id = :id)
+
CriteriaQuery cq = cb.createQuery();
e.salary < (SELECT e2.salary from Employee e2 WHERE e2.id = :id)
+
Root e = cq.from(Employee.class);
e.firstName = ANY (SELECT e2.firstName from Employee e2 WHERE e.id <> e.id)
+
Subquery sq = cq.subquery(Employee.class);
e.salary <= ALL (SELECT e2.salary from Employee e2)
+
Root e2 = cq.from(Employee.class);
</source>
+
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();
  
EclipseLink allows the =, <>, IS NULL, IS NOT NULL, IN and NOT IN operations on objects.  If IN is used on an object and the object has a composite Id, this requires the database to support nested IN lists.
+
// Find the employee with the lowest salary.
 
+
CriteriaQuery cq = cb.createQuery();
<source lang="sql">
+
Root e = cq.from(Employee.class);
e.manager = e2.manager
+
Subquery sq = cq.subquery(Employee.class);
e.manager = :manager
+
Root e2 = cq.from(Employee.class);
e.manager <> :manager
+
sq.select(e2.get("salary"));
e.manager IS NULL
+
cq.where(cb.lessThan(e.get("salary"), cb.all(sq)));
e.manager IS NOT NULL
+
Query query = em.createQuery(cq)
e.manager IN (SELECT e2 from Employee e2 WHERE SIZE(e2.managedEmployees) < 2)
+
List<Employee> = query.getResultList();
e.manager NOT IN (:manager1, :manager2)
+
 
</source>
 
</source>
 
 
 
 
  
 
==Parameters==
 
==Parameters==
JPA defines named parameters, and positional parameters. Named parameters can be specified in JPQL using the syntax <tt>:<name></tt>.
+
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.
Positional parameters can be specified in JPQL using the syntax <tt>?</tt> or <tt>?<position></tt>.  Positional parameters start at position <tt>1</tt> not <tt>0</tt>.
+
For positional parameters only the parameter type is specified.  Positional parameters start at position <tt>1</tt> not <tt>0</tt>.
  
=====''Named parameter query example''=====
+
=====''Named parameter criteria example''=====
 
<source lang="java">
 
<source lang="java">
Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = :first and e.lastName = :last");
+
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("first", "Bob");
 
query.setParameter("last", "Smith");
 
query.setParameter("last", "Smith");
Line 282: Line 414:
 
</source>
 
</source>
  
=====''Positional parameter query example''=====
+
=====''Positional parameter criteria example''=====
 
<source lang="java">
 
<source lang="java">
Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = ? and e.lastName = ?");
+
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(1, "Bob");
 
query.setParameter(2, "Smith");
 
query.setParameter(2, "Smith");
 
List<Employee> = query.getResultList();
 
List<Employee> = query.getResultList();
 
</source>
 
</source>
 
 
  
 
==Functions==
 
==Functions==
JPQL supports several database functions.  These functions are database independent in name and syntax, but require database support.  If the database supports an equivalent function or different syntax the standard JPQL function is supported, if the database does not provide any way to perform the function, then it is not supportedFor mathematical functions (+, -, /, *) BEDMAS rules apply.
+
Several database functions are supported by the Criteria APIAll 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.
In EclipseLink's JPQL support functions can be used in the SELECT, WHERE, ORDER BY, GROUP BY and HAVING clauses, as well as inside other functions, with comparison operators, and in constructors.
+
 
+
EclipseLink provides support for several functions beyond the JPA spec.  EclipseLink also supports calling specific database functions through <tt>FUNCTION</tt>, <tt>FUNC</tt>, and <tt>OPERATOR</tt>.
+
  
 
{|{{BMTableStyle}}
 
{|{{BMTableStyle}}
|+{{BMTableCaptionStyle}}|JPQL supported functions
+
|+{{BMTableCaptionStyle}}|<tt>CriteriaBuilder</tt> database functions
 
|-{{BMTHStyle}}
 
|-{{BMTHStyle}}
 
| Function
 
| Function
Line 306: Line 437:
 
| Example
 
| Example
 
|-  
 
|-  
| -
+
| <tt>diff</tt>
 
| subtraction
 
| subtraction
| <source lang="sql">e.salary - 1000</source>
+
| <source lang="java">cb.diff(e.<Number>get("salary"), 1000)</source>
 
|-
 
|-
| +
+
| <tt>sum</tt>
 
| addition
 
| addition
| <source lang="sql">e.salary + 1000</source>
+
| <source lang="java">cb.sum(e.<Number>get("salary"), 1000)</source>
 
|-
 
|-
| *
+
| <tt>prod</tt>
 
| multiplication
 
| multiplication
| <source lang="sql">e.salary * 2</source>
+
| <source lang="java">cb.prod(e.<Number>get("salary"), 2)</source>
 
|-
 
|-
| /
+
| <tt>quot</tt>
 
| division
 
| division
| <source lang="sql">e.salary / 2</source>
+
| <source lang="java">cb.quot(e.<Number>get("salary"), 2)</source>
 
|-
 
|-
| ABS
+
| <tt>abs</tt>
 
| absolute value
 
| absolute value
| <source lang="sql">ABS(e.salary - e.manager.salary)</source>
+
| <source lang="java">cb.abs(
 +
    cb.diff(e.<Number>get("salary"), e.get("manager").<Number>get("salary")))</source>
 
|-
 
|-
| CASE
+
| <tt>selectCase</tt>
 
| defines a case statement
 
| defines a case statement
| <source lang="sql">CASE e.status WHEN 0 THEN 'active' WHEN 1 THEN 'consultant' ELSE 'unknown' END</source>
+
| <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>
 
|-
 
|-
| COALESCE
+
| <tt>coalesce</tt>
 
| evaluates to the first non null argument value
 
| evaluates to the first non null argument value
| <source lang="sql">COALESCE(e.salary, 0)</source>
+
| <source lang="java">cb.coalesce(cb.concat(e.<Number>get("salary"), 0)</source>
 
|-
 
|-
| CONCAT
+
| <tt>concat</tt>
 
| concatenates two or more string values
 
| concatenates two or more string values
| <source lang="sql">CONCAT(e.firstName, ' ', e.lastName)</source>
+
| <source lang="java">cb.concat(
 +
    cb.concat(e.<String>get("firstName"), " "), e.<String>get("lastName"))</source>
 
|-
 
|-
| CURRENT_DATE
+
| <tt>currentDate</tt>
 
| the current date on the database
 
| the current date on the database
| <source lang="sql">CURRENT_DATE</source>
+
| <source lang="sql">cb.currentDate()</source>
 
|-
 
|-
| CURRENT_TIME
+
| <tt>currentTime</tt>
 
| the current time on the database
 
| the current time on the database
| <source lang="sql">CURRENT_TIME</source>
+
| <source lang="sql">cb.currentTime()</source>
 
|-
 
|-
| CURRENT_TIMESTAMP
+
| <tt>currentTimestamp</tt>
 
| the current date-time on the database
 
| the current date-time on the database
| <source lang="sql">CURRENT_TIMESTAMP</source>
+
| <source lang="sql">cb.currentTimestamp()</source>
 
|-
 
|-
| LENGTH
+
| <tt>length</tt>
 
| the character/byte length of the character or binary value
 
| the character/byte length of the character or binary value
| <source lang="sql">LENGTH(e.lastName)</source>
+
| <source lang="java">cb.length(e.<String>get("lastName"))</source>
 
|-
 
|-
| LOCATE
+
| <tt>locate</tt>
 
| the index of the string within the string, optionally starting at a start index
 
| the index of the string within the string, optionally starting at a start index
| <source lang="sql">LOCATE('-', e.lastName)</source>
+
| <source lang="java">cb.locate("-", e.<String>get("lastName"))</source>
 
|-
 
|-
| LOWER
+
| <tt>lower</tt>
 
| convert the string value to lower case
 
| convert the string value to lower case
| <source lang="sql">LOWER(e.lastName)</source>
+
| <source lang="java">cb.lower(e.<String>get("lastName"))</source>
 
|-
 
|-
| MOD
+
| <tt>mod</tt>
 
| computes the remainder of dividing the first integer by the second
 
| computes the remainder of dividing the first integer by the second
| <source lang="sql">MOD(e.hoursWorked / 8)</source>
+
| <source lang="java">cb.mod(e.<Integer>get("hoursWorked"), 8)</source>
 
|-
 
|-
| NULLIF
+
| <tt>nullif</tt>
 
| returns null if the first argument to equal to the second argument, otherwise returns the first argument
 
| returns null if the first argument to equal to the second argument, otherwise returns the first argument
| <source lang="sql">NULLIF(e.salary, 0)</source>
+
| <source lang="java">cb.nullif(e.<Number>get("salary"), 0)</source>
 
|-
 
|-
| SQRT
+
| <tt>sqrt</tt>
 
| computes the square root of the number
 
| computes the square root of the number
| <source lang="sql">SQRT(o.result)</source>
+
| <source lang="java">cb.sqrt(e.<Number>get("salary"))</source>
 
|-
 
|-
| SUBSTRING
+
| <tt>substring</tt>
 
| the substring from the string, starting at the index, optionally with the substring size
 
| the substring from the string, starting at the index, optionally with the substring size
| <source lang="sql">SUBSTRING(e.lastName, 0, 2)</source>
+
| <source lang="java">cb.substring(e.<String>get("lastName"), 0, 2)</source>
 
|-
 
|-
| TRIM
+
| <tt>trim</tt>
 
| trims leading, trailing, or both spaces or optional trim character from the string
 
| trims leading, trailing, or both spaces or optional trim character from the string
| <source lang="sql">TRIM(TRAILING FROM e.lastName), TRIM(e.lastName), TRIM(LEADING '-' FROM e.lastName)</source>
+
| <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>
 
|-
 
|-
| UPPER
+
| <tt>upper</tt>
 
| convert the string value to upper case
 
| convert the string value to upper case
| <source lang="sql">UPPER(e.lastName)</source>
+
| <source lang="java">cb.upper(e.<String>get("lastName"))</source>
 
|}
 
|}
  
 
+
==Special Operations==
{|{{BMTableStyle}}
+
The Criteria API defines several special operations that are not database functions, but have special meaning in JPASome of these operations are defined on <tt>CriteriaBuilder</tt> and some are on specific Expression interfaces.
|+{{BMTableCaptionStyle}}|EclipseLink supported functions
+
|-{{BMTHStyle}}
+
| Function
+
| Description
+
| Example
+
|-
+
| CAST
+
| casts the value to the database type (as of 2.4)
+
| <source lang="sql">CAST(e.salary NUMERIC(10,2))</source>
+
|-
+
| EXTRACT
+
| extracts the date part from the date/time value (as of 2.4)
+
| <source lang="sql">EXTRACT(YEAR, e.startDate)</source>
+
|-
+
| REGEXP
+
| evaluates if the string matches the regular expression (as of 2.4)
+
| <source lang="sql">e.lastName REGEXP '^Dr\.*'</source>
+
|}
+
 
+
 
+
===Special Functions===
+
JPQL defines several special functions that are not database functions, but have special meaning in JPQLThese 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.
+
  
 
{|{{BMTableStyle}}
 
{|{{BMTableStyle}}
Line 419: Line 539:
 
| Example
 
| Example
 
|-  
 
|-  
| INDEX
+
| <tt>index</tt>
| the index of the ordered List element, only supported with @OrderColumn
+
| the index of the ordered List element, only supported when @OrderColumn is used in the mapping,
| <source lang="sql">SELECT l FROM Employee e join e.toDoList l WHERE INDEX(l) = 1</source>
+
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>
 
|-  
 
|-  
| KEY
+
| <tt>key</tt>, <tt>value</tt>
| the key of the Map element
+
| 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="sql">SELECT p FROM Employee e join e.priorities p WHERE KEY(p) = 'high'</source>
+
| <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>
 
|-  
 
|-  
| SIZE
+
| <tt>size</tt>
| the size of the collection relationships, this evaluates to a sub-select
+
| the size of the collection relationships, this evaluates to a sub-select, defined on the <tt>CriteriaBuilder</tt>
| <source lang="sql">SELECT e FROM Employee e WHERE SIZE(e.managedEmployees) < 2</source>
+
| <source lang="java">cb.greaterThan(cb.size(e.<Collection>get("managedEmployees")), 2)</source>
 
|-  
 
|-  
| IS EMPTY
+
| <tt>isEmpty</tt>, <tt>isNotEmpty</tt>
| evaluates to true if the collection relationship is empty, this evaluates to a sub-select
+
| 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="sql">SELECT e FROM Employee e WHERE e.managedEmployees IS EMPTY</source>
+
| <source lang="java">cb.isEmpty(e.<Collection>get("managedEmployees"))</source>
 
|-  
 
|-  
| TYPE
+
| <tt>isMember</tt>, <tt>isNotMember</tt>
| the inheritance discriminator value
+
| evaluates to true if the collection relationship contains the value, this evaluates to a sub-select, defined on the <tt>CriteriaBuilder</tt>
| <source lang="sql">SELECT p FROM Project p WHERE TYPE(p) = LargeProject</source>
+
| <source lang="java">cb.isMember("write code", e.<Collection>get("responsibilities"))</source>
 
|-  
 
|-  
| TREAT
+
| <tt>type</tt>
| treat (cast) the object as its subclass value (JPA 2.1 draft)
+
| the inheritance discriminator value, defined on any <tt>Path</tt> expression
| <source lang="sql">SELECT e FROM Employee JOIN TREAT(e.projects as LargeProject) p WHERE p.budget > 1000000</source>
+
| <source lang="java">cb.equal(p.type(), LargeProject.class)</source>
 
|-  
 
|-  
| FUNCTION
+
| <tt>as</tt>
| call a database function (JPA 2.1 draft)
+
| can be used to cast an un-typed expression to a typed expression, EclipseLink also allows this to down cast inherited types
| <source lang="sql">SELECT p FROM Phone p where FUNCTION('TO_NUMBER', e.areaCode) > 613</source>
+
| <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.
  
==EclipseLink Extensions (EQL)==
+
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]]
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:
+
=====''Metamodel criteria example''=====
* Less restrictions than JPQL, allows sub-selects and functions within operations such as LIKE, IN, ORDER BY, constructors, functions etc.
+
<source lang="java">
* Allow != in place of <>
+
CriteriaBuilder cb = em.getCriteriaBuilder();
* [[#FUNC|FUNC]] operation to call database specific functions (now FUNCTION in JPA 2.1)
+
CriteriaQuery cq = cb.createQuery();
* [[#Special Functions|TREAT]] operation to downcast related entities with inheritance
+
Root<Employee> e = cq.from(em.getMetamodel().entity(Employee.class));
* [[#OPERATOR|OPERATOR]] operation to call EclipseLink database independent functions (EL 2.4)
+
cq.where(cb.equal(e.get(Employee_.firstName), "Bob"), cb.equal(e.get(Employee_.lastName), "Smith"));
* [[#SQL|SQL]] operation to mix SQL with JPQL (EL 2.4)
+
Query query = em.createQuery(cq)
* [[#Functions|CAST]] and [[#Functions|EXTRACT]] functions (EL 2.4)
+
List<Employee> = query.getResultList();
* [[#Functions|REGEXP]] function for regular expression querying (EL 2.4)
+
</source>
* Usage of sub-selects in the SELECT and [[#FROM|FROM]] clause (EL 2.4)
+
* [[#ON|ON]] clause support for defining JOIN and LEFT JOIN conditions (EL 2.4)
+
* [[#ON|Joins]] between independent entities (EL 2.4)
+
* Usage of an alias on a [[#JOIN FETCH|JOIN FETCH]] (EL 2.4)
+
* [[#COLUMN|COLUMN]] operation to allow querying on non mapped columns (EL 2.4)
+
* [[#TABLE|TABLE]] operation to allow querying on non mapped tables (EL 2.4)
+
* [[#UNION|UNION]], INTERSECT, EXCEPT support (EL 2.4)
+
* Usage of object variables in =, <>, IN, IS NULL, and ORDER BY
+
  
 +
==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
 
{{EclipseLink_JPA
 
|previous= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL|JPQL]]
 
|previous= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL|JPQL]]
|next=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Native|Native SQL Queries]]
+
|next=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Named|Named Queries]]
 
|up=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying|Querying]]
 
|up=[[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying|Querying]]
 
|version=2.4 DRAFT}}
 
|version=2.4 DRAFT}}

Revision as of 10:32, 18 June 2012

EclipseLink JPA

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

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

Elug javaspec icon.gif

For more information, see Chapter 6 "Criteria API" in the JPA Specification.

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.
  • createTupleQuery() - Creates a CriteriaQuery that returns map like Tuple objects, instead of object arrays for multiselect queries. See Tuple Queries

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 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
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();

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.
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();

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.

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();

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.

// 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();

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.

Root e = cq.from(Employee.class);
Join a = e.join("address");
cq.where(cb.equal(a.get("city"), city);

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.

// 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));

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.

Root e = cq.from(Employee.class);
Fetch a = e.fetch("address");
cq.select(e);

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.

Root e = cq.from(Employee.class);
Join a = e.join("address", JoinType.LEFT);
cq.order(a.get("city"));

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.

// 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"));
// 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);
// 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);

Having

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 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));

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

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

Logical operations defined on CriteriaBuilder:

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

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 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
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();

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.

Named parameter criteria example
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();
Positional parameter criteria example
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();

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.

CriteriaBuilder database functions
Function Description Example
diff subtraction
cb.diff(e.<Number>get("salary"), 1000)
sum addition
cb.sum(e.<Number>get("salary"), 1000)
prod multiplication
cb.prod(e.<Number>get("salary"), 2)
quot division
cb.quot(e.<Number>get("salary"), 2)
abs absolute value
cb.abs(
    cb.diff(e.<Number>get("salary"), e.get("manager").<Number>get("salary")))
selectCase defines a case statement
cb.selectCase(e.get("status")).
    when(0, "active").
    when(1, "consultant").
    otherwise("unknown")
cb.selectCase().
    when(cb.equal(e.get("status"), 0), "active").
    when(cb.equal(e.get("status"), 1), "consultant").
    otherwise("unknown")
coalesce evaluates to the first non null argument value
cb.coalesce(cb.concat(e.<Number>get("salary"), 0)
concat concatenates two or more string values
cb.concat(
    cb.concat(e.<String>get("firstName"), " "), e.<String>get("lastName"))
currentDate the current date on the database
cb.currentDate()
currentTime the current time on the database
cb.currentTime()
currentTimestamp the current date-time on the database
cb.currentTimestamp()
length the character/byte length of the character or binary value
cb.length(e.<String>get("lastName"))
locate 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 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 CriteriaBuilder and some are on specific Expression interfaces.

JPQL special functions
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 e = cq.from(Employee.class);
ListJoin toDo = e.joinList("toDoList");
cq.multiselect(e, toDo);
cq.where(cb.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 e = cq.from(Employee.class);
MapJoin p = e.joinMap("priorities");
cq.multiselect(e, p.value());
cq.where(cb.equal(p.key(), "high"))
size the size of the collection relationships, this evaluates to a sub-select, defined on the CriteriaBuilder
cb.greaterThan(cb.size(e.<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
cb.isEmpty(e.<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
cb.isMember("write code", e.<Collection>get("responsibilities"))
type the inheritance discriminator value, defined on any Path expression
cb.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
cb.mod(e.get("id").as(Integer.class), 2)
cb.greaterThan(p.as(LargeProject.class).get("budget"), 1000000)
function call a database specific function, defined on the CriteriaBuilder
cb.greaterThan(cb.function("TO_NUMBER", Number.class, p.get("areaCode")), 613)

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 _ 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, Metamodel

Metamodel criteria example
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();

Tuple Queries

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
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");

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;
  • in usage with object path expressions;
  • Order by usage with object path expressions.

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

The EclipseLink native Expression API provides the following additional functionality:

  • Additional database functions (over 80 database functions are supported);
  • Usage of custom ExpressionOperators;
  • Embedding of SQL within an Expression query;
  • Usage of sub-selects in the from clause;
  • on clause support;
  • Access to unmapped columns and tables;
  • Historical querying.

EclipseLink Expressions can be combined with EclipseLink DatabaseQuerys to provide additional functionality:

  • Unions, intersect and except clauses;
  • Hierarchical connect by clauses;
  • Batch fetching.
JpaCriteriaBuilder native Expression example
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\.*")));

Eclipselink-logo.gif
Version: 2.4 DRAFT
Other versions...

Back to the top