Jump to: navigation, search

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

(CriteriaQuery)
(CriteriaQuery)
Line 50: Line 50:
 
* <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.
  

Revision as of 07:45, 6 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 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 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.

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 employee = cq.from(Employee.class);
cq.where(cb.greaterThan(employee.get("salary"), 100000));
Query query = em.createQuery(cq);
List<Employee> result = query.getResultList();
 
// Query for a single object.
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
cq.where(cb.equal(employee.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 employee = cq.from(Employee.class);
Query query = em.createQuery(cq);
query.select(cb.max(employee.get("salary")));
BigDecimal result3 = (BigDecimal)query.getSingleResult();
 
// Query for a List of data elements.
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
Query query = em.createQuery(cq);
query.select(employee.get("firstName"));
List<String> result4 = query.getResultList();
 
// Query for a List of element arrays.
CriteriaQuery cq = cb.createQuery();
Root employee = cq.from(Employee.class);
Query query = em.createQuery(cq);
query.multiselect(employee.get("firstName"), employee.get("lastName"));
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 include MIN, MAX, AVG, SUM, COUNT. These functions can be used to return a single result, or can be used with a groupBy to return multiple results.

SELECT COUNT(e) FROM Employee e
SELECT MAX(e.salary) FROM Employee e

Constructors

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.

SELECT NEW com.acme.reports.EmpReport(e.firstName, e.lastName, e.salary) FROM Employee e

FROM Clause

The FROM clause defines what is being queried. A typical FROM clause will contain the entity name being queried and assign it an alias.

SELECT e FROM Employee e

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 table. The WHERE or ON clause should ensure the two objects are joined in some way.

SELECT e, a FROM Employee e, MailingAddress a WHERE e.address = a.address

The entity name used in JPQL comes from the name attribute of the @Entity annotation or XML. It 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).

SELECT e FROM com.acme.Employee e

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 clause. JOIN does not mean the relationships will be fetched, unless the FETCH option is included.

SELECT e FROM Employee e JOIN e.address a WHERE a.city = :city

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.

SELECT e FROM Employee e JOIN e.projects p JOIN e.projects p2 WHERE p.name = :p1 AND p2.name = :p2

JOIN 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.

SELECT e FROM Employee e JOIN FETCH e.address

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.

SELECT e FROM Employee e JOIN FETCH e.address a ORDER BY a.city

LEFT JOIN

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.

SELECT e FROM Employee e LEFT JOIN e.address a ORDER BY a.city



ORDER BY clause

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 e FROM Employee e ORDER BY e.lastName ASC, e.firstName, ASC
SELECT e FROM Employee e ORDER BY UPPER(e.lastName)
SELECT e FROM Employee e LEFT JOIN e.manager m ORDER BY m.lastName NULLS FIRST
SELECT e FROM Employee e ORDER BY e.address

GROUP BY Clause

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.

SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city
SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city ORDER BY AVG(e.salary)
SELECT e, COUNT(p) FROM Employee e LEFT JOIN e.projects p GROUP BY e

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.

SELECT AVG(e.salary), e.address.city FROM Employee e GROUP BY e.address.city HAVING AVG(e.salary) > 100000

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.

Comparison operations
Operation Description Example
= equal
e.firstName = 'Bob'
< less than
e.salary < 100000
> greater than
e.salary > :sal
< less than or equal
e.salary <= 100000
> greater than or equal
e.salary >= :sal
LIKE evaluates if the two string match, '%' and '_' are valid wildcards, and ESCAPE character is optional
e.firstName LIKE 'A%' OR e.firstName NOT LIKE '%._%' ESCAPE '.'
BETWEEN evaluates if the value is between the two values
e.firstName BETWEEN 'A' AND 'C'
IS NULL compares the value to null, databases may not allow or have unexpected results when using = with null
e.endDate IS NULL
IN evaluates if the value is contained in the list
e.firstName IN ('Bob', 'Fred', 'Joe')

The IN operation allows for a list of values or parameters, a single list parameter, or a sub-select.

e.firstName IN (:name1, :name2, :name3)
e.firstName IN (:name1)
e.firstName IN :names
e.firstName IN (SELECT e2.firstName FROM Employee e2 WHERE e2.lastName = 'Smith')

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.

e.firstName = (SELECT e2.firstName FROM Employee e2 WHERE e2.id = :id)
e.salary < (SELECT e2.salary FROM Employee e2 WHERE e2.id = :id)
e.firstName = ANY (SELECT e2.firstName FROM Employee e2 WHERE e.id <> e.id)
e.salary <= ALL (SELECT e2.salary FROM Employee e2)

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.

e.manager = e2.manager
e.manager = :manager
e.manager <> :manager
e.manager IS NULL
e.manager IS NOT NULL
e.manager IN (SELECT e2 FROM Employee e2 WHERE SIZE(e2.managedEmployees) < 2)
e.manager NOT IN (:manager1, :manager2)



Parameters

JPA defines named parameters, and positional parameters. Named parameters can be specified in JPQL using the syntax :<name>. Positional parameters can be specified in JPQL using the syntax ? or ?<position>. Positional parameters start at position 1 not 0.

Named parameter query example
Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = :first and e.lastName = :last");
query.setParameter("first", "Bob");
query.setParameter("last", "Smith");
List<Employee> = query.getResultList();
Positional parameter query example
Query query = em.createQuery("SELECT e FROM Employee e WHERE e.firstName = ? and e.lastName = ?");
query.setParameter(1, "Bob");
query.setParameter(2, "Smith");
List<Employee> = query.getResultList();


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 supported. For mathematical functions (+, -, /, *) BEDMAS rules apply.

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 FUNCTION, FUNC, and OPERATOR.

JPQL supported functions
Function Description Example
- subtraction
e.salary - 1000
+ addition
e.salary + 1000
* multiplication
e.salary * 2
/ division
e.salary / 2
ABS absolute value
ABS(e.salary - e.manager.salary)
CASE defines a case statement
CASE e.STATUS WHEN 0 THEN 'active' WHEN 1 THEN 'consultant' ELSE 'unknown' END
COALESCE evaluates to the first non null argument value
COALESCE(e.salary, 0)
CONCAT concatenates two or more string values
CONCAT(e.firstName, ' ', e.lastName)
CURRENT_DATE the current date on the database
CURRENT_DATE
CURRENT_TIME the current time on the database
CURRENT_TIME
CURRENT_TIMESTAMP the current date-time on the database
CURRENT_TIMESTAMP
LENGTH the character/byte length of the character or binary value
LENGTH(e.lastName)
LOCATE the index of the string within the string, optionally starting at a start index
LOCATE('-', e.lastName)
LOWER convert the string value to lower case
LOWER(e.lastName)
MOD computes the remainder of dividing the first integer by the second
MOD(e.hoursWorked / 8)
NULLIF returns null if the first argument to equal to the second argument, otherwise returns the first argument
NULLIF(e.salary, 0)
SQRT computes the square root of the number
SQRT(o.RESULT)
SUBSTRING the substring from the string, starting at the index, optionally with the substring size
SUBSTRING(e.lastName, 0, 2)
TRIM trims leading, trailing, or both spaces or optional trim character from the string
TRIM(TRAILING FROM e.lastName), TRIM(e.lastName), TRIM(LEADING '-' FROM e.lastName)
UPPER convert the string value to upper case
UPPER(e.lastName)


EclipseLink supported functions
Function Description Example
CAST casts the value to the database type (as of 2.4)
CAST(e.salary NUMERIC(10,2))
EXTRACT extracts the date part from the date/time value (as of 2.4)
EXTRACT(YEAR, e.startDate)
REGEXP evaluates if the string matches the regular expression (as of 2.4)
e.lastName REGEXP '^Dr\.*'


Special Functions

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

JPQL special functions
Function Description Example
INDEX the index of the ordered List element, only supported with @OrderColumn
SELECT l FROM Employee e JOIN e.toDoList l WHERE INDEX(l) = 1
KEY the key of the Map element
SELECT p FROM Employee e JOIN e.priorities p WHERE KEY(p) = 'high'
SIZE the size of the collection relationships, this evaluates to a sub-select
SELECT e FROM Employee e WHERE SIZE(e.managedEmployees) < 2
IS EMPTY evaluates to true if the collection relationship is empty, this evaluates to a sub-select
SELECT e FROM Employee e WHERE e.managedEmployees IS EMPTY
TYPE the inheritance discriminator value
SELECT p FROM Project p WHERE TYPE(p) = LargeProject
TREAT treat (cast) the object as its subclass value (JPA 2.1 draft)
SELECT e FROM Employee JOIN TREAT(e.projects AS LargeProject) p WHERE p.budget > 1000000
FUNCTION call a database function (JPA 2.1 draft)
SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) > 613


EclipseLink Extensions (EQL)

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

EclipseLink's JPQL extensions include:

  • Less restrictions than JPQL, allows sub-selects and functions within operations such as LIKE, IN, ORDER BY, constructors, functions etc.
  • Allow != in place of <>
  • FUNC operation to call database specific functions (now FUNCTION in JPA 2.1)
  • TREAT operation to downcast related entities with inheritance
  • OPERATOR operation to call EclipseLink database independent functions (EL 2.4)
  • SQL operation to mix SQL with JPQL (EL 2.4)
  • CAST and EXTRACT functions (EL 2.4)
  • REGEXP function for regular expression querying (EL 2.4)
  • Usage of sub-selects in the SELECT and FROM clause (EL 2.4)
  • ON clause support for defining JOIN and LEFT JOIN conditions (EL 2.4)
  • Joins between independent entities (EL 2.4)
  • Usage of an alias on a JOIN FETCH (EL 2.4)
  • COLUMN operation to allow querying on non mapped columns (EL 2.4)
  • TABLE operation to allow querying on non mapped tables (EL 2.4)
  • UNION, INTERSECT, EXCEPT support (EL 2.4)
  • Usage of object variables in =, <>, IN, IS NULL, and ORDER BY


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