Java Persistence/JPQL

=JPQL= The Java Persistence Query Language (JPQL) is the query language defined by JPA. JPQL is similar to SQL, but operates on objects, attributes and relationships instead of tables and columns. JPQL can be used for reading (SELECT), as well as bulk updates (UPDATE) and deletes (DELETE). JPQL can be used in a NamedQuery (through annotations or XML) or in dynamic queries using the EntityManager createQuery API.

For the JPQL BNF see BNF.

Select Queries
Select queries can be used to read objects from the database. Select queries can return a single object or data element, a list of objects or data elements, or an object array of multiple objects and data.

SELECT Clause
The SELECT clause can contain object expressions, attribute expressions, functions, sub-selects, constructors and 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 GROUP BY to return multiple results.

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.

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.

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

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. Some JPA providers also allow for the full class name.


 * EclipseLink : TopLink - allow for the fully qualified class name of the entity to be used.

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.

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

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.

JOIN FETCH does not allow an alias in the JPA spec, but some JPA providers may allow it.


 * EclipseLink : TopLink - allow an alias.

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.

ON (JPA 2.1)
The join condition used for a join comes from the mapping's join columns. This means that the JPQL user is normally free from having to know how every relationship is joined. In some cases it is desirable to append additional conditions to the join condition, normally in the case of outer joins. This can be done through the ON clause. The ON clause is defined in the JPA 2.1 specifiation, and may be supported by some JPA providers.


 * EclipseLink : Hibernate : TopLink - support the ON clause.

Sub-selects in FROM clause
JPA does not support sub-selects in the FROM clause. Some JPA providers may support this.


 * EclipseLink : TopLink - support sub-selects in the FROM clause.

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). The JPA 1.0 and 2.0 BNFs restrict the usage of functions and sub-selects in the ORDER BY, but the JPA 2.1 draft removes most of these.


 * EclipseLink : TopLink - allows functions, sub-selects, NULLS FIRST/LAST, object expressions, and other operations in the ORDER BY clause.

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.

HAVING Clause
The HAVING clause allows for the results of a GROUP BY to be filtered.

UNION
JPA does not support the SQL UNION, INTERSECT and EXCEPT operations. Some JPA providers may support these.


 * EclipseLink : TopLink - support UNION, INTERSECT, and EXCEPT.

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.

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

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.

Update Queries
You can perform bulk update of entities with the UPDATE statement. This statement operates on a single entity type and sets one or more single-valued properties of the entity subject to the condition in the WHERE clause. Update queries provide an equivalent to the <tt>SQL UPDATE</tt> statement, but with JPQL conditional expressions.

Update queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Update queries can only update attributes of the object or its embeddables, its relationships cannot be updated. Complex update queries are dependent on the database's update support, and may make use of temp tables on some databases.

Update queries should only be used for bulk updates, regular updates to objects should be made by using the object's set methods within a transaction and committing the changes.

Update queries return the number of modified rows on the database (row count).

This example demonstrates how to use an update query to give employees a raise. The <tt>WHERE</tt> clause contains the conditional expression.

Update query example
The persistence context is not updated to reflect results of update operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

The objects in the shared cache that match the update query will be invalidated to ensure subsequent persistence contexts see the updated data.

Delete Queries
You can perform bulk removal of entities with the <tt>DELETE</tt> statement. Delete queries provide an equivalent to the <tt>SQL DELETE</tt> statement, but with JPQL conditional expressions.

Delete queries do not allow joins, but do support sub-selects. OneToOne and ManyToOne relationships can be traversed in the WHERE clause. Collection relationships can still be queried through using an EXISTS in the WHERE clause with a sub-select. Complex delete queries are dependent on the database's delete support, and may make use of temp tables on some databases.

Delete queries should only be used for bulk deletes, regular deletes to objects should be performed through calling the <tt>EntityManager</tt> <tt>remove</tt> API.

Delete queries return the number of deleted rows on the database (row count).

This example demonstrates how to use a delete query to remove all employees who are not assigned to a department. The <tt>WHERE</tt> clause contains the conditional expression.

Delete query example
Delete queries are polymorphic: any entity subclass instances that meet the criteria of the delete query will be deleted. However, delete queries do not honor cascade rules: no entities other than the type referenced in the query and its subclasses will be removed, even if the entity has relationships to other entities with cascade removes enabled. Delete queries will delete the rows from join and collection tables.

The persistence context may not be updated to reflect results of delete operations. If you use a transaction-scoped persistence context, you should either execute the bulk operation in a transaction all by itself, or be the first operation in the transaction. That is because any entity actively managed by the persistence context will remain unaware of the actual changes occurring at the database level.

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

Literals
Literal values can be in-lined in JPQL for standard Java types. In general it is normally better to use parameters instead of in-lining values. In-lined arguments will prevent the JPQL from benefiting from the EclipseLink's JPQL parser cache, and can potentially make the application vulnerable to JPQL injections attacks.

Each Java types defines its own in-lining syntax:
 * To define a ' (quote) character in a string, the quote is double quoted, i.e..
 * To define a ' (quote) character in a string, the quote is double quoted, i.e..

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. Some JPA provider may support additional functions.


 * EclipseLink : TopLink - also support CAST, EXTRACT, and REGEXP.

Special Operators
JPQL defines several special operators that are not database functions, but have special meaning in JPQL. These include INDEX, KEY, SIZE, IS EMPTY, TYPE, FUNCTION and TREAT.


 * EclipseLink : TopLink - also support FUNCTION, TREAT, FUNC, OPERATOR, SQL, COLUMN, and TABLE.

JPQL special operators
/Runtime