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 EntityManagercreateQuery() API.

SELECT

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.

FROM

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

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.

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 ORDERBY 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 LEFTJOIN e.address a ORDERBY a.city

ON

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 draft, and is supported in EclipseLink 2.4. EclipseLink also supports usage of the ON clause between two root level objects.

ORDER BY

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 (as of 2.4) also allows for NULL ordering to be specified (either FIRST or LAST).

GROUP BY

HAVING

UNION

WHERE

UPDATE

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 SQL UPDATE statement, but with JPQL conditional expressions. Update queries do not allow joins, but do support sub-selects. 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 WHERE 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

You can perform bulk removal of entities with the DELETE statement. Delete queries provide an equivalent to the SQL DELETE statement, but with JPQL conditional expressions. Delete queries do not allow joins, but do support sub-selects. Delete queries should only be used for bulk deletes, regular deletes to objects should be performed through calling the EntityManagerremove() 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 WHERE clause contains the conditional expression.

Delete query example

Note: 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 is not 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.

The objects in the shared cache that match the delete query will be invalidated to ensure subsequent persistence contexts do not see the removed objects.

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.

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.STATUSWHEN0THEN'active'WHEN1THEN'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

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 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 WHEREINDEX(l)=1

KEY

the key of the Map element

SELECT p FROM Employee e JOIN e.priorities p WHEREKEY(p)='high'

SIZE

the size of the collection relationships, this evaluates to a sub-select

SELECT e FROM Employee e WHERESIZE(e.managedEmployees)<2

IS EMPTY

evaluates to true if the collection relationship is empty, this evaluates to a sub-select

EclipseLink special functions

EclipseLink defines several special JPQL functions that allow performing database operations that are not possible in basic JPQL. These include FUNC, OPERATOR, SQL and COLUMN.

FUNC

FUNC allows for a database function to be call from JPQL. It allows calling any database functions not supported directly in JPQL, and calling user or library specific functions. FUNC is database specific, in that it does not translate the function call in any way to support different databases as other JPQL functions do. FUNC can only be used to call functions with normal syntax, functions that require special syntax cannot be called with FUNC. FUNC has been replaced by FUNCTION in JPA 2.1, so FUNCTION should be used as of EclipseLink 2.4.

OPERATOR

OPERATOR allows for any EclipseLink operator to be called. EclipseLink supports many database functions using standard operator names that are then translated to different databases. EclipseLink operators are supported on any database that has an equivalent function (or set of functions). The EclipseLink ExpressionOperator clas can be used to allow a DatabasePlatform to override an operator, or define custom operators. OPERATOR is similar to FUNC, but allows the function to be database independent, and allows calling functions that require special syntax.

The supported EclipseLink operators include:

Abs

ToUpperCase

ToLowerCase

Chr

Concat

Coalesce

Case

HexToRaw

Initcap

Instring

Soundex

LeftPad

LeftTrim

RightPad

RightTrim

Substring

Translate

Ascii

Length

CharIndex

Cast

Extract

CharLength

Difference

Reverse

Replicate

Right

Locate

ToNumber

ToChar

AddMonths

DateToString

MonthsBetween

NextDay

RoundDate

AddDate

DateName

DatePart

DateDifference

TruncateDate

NewTime

Nvl

NewTime

Ceil

Cos

Cosh

Acos

Asin

Atan

Exp

Sqrt

Floor

Ln

Log

Mod

Power

Round

Sign

Sin

Sinh

Tan

Tanh

Trunc

Greatest

Least

Add

Subtract

Divide

Multiply

Atan2

Cot

Deref

Ref

RefToHex

Value

ExtractXml

ExtractValue

ExistsNode

GetStringVal

GetNumberVal

IsFragment

SDO_WITHIN_DISTANCE

SDO_RELATE

SDO_FILTER

SDO_NN

NullIf

OPERATOR examples

SQL

SQL allows for the usage and integration of SQL within JPQL. SQL includes the SQL string to inline into the JPQL and the arguments to translate into the SQL string. The '?' character is used to define parameters within the SQL that are translated from the SQL function arguments. SQL allows for the calling of database functions with non standard syntax, the embedding of SQL literals, and performing any other SQL operations within JPQL. The SQL function offers an alternative to using native SQL queries just because one part of the query requires something that is not supported in JPQL. Now JPQL can still be used for the query, and the SQL function used for the SQL specific parts.

COLUMN

COLUMN allows access to unmapped columns in an object's table. This can be used to access foreign key columns, inheritance discriminators, or primitive columns such as ROWID. COLUMN can also be used in JPQL fragments inside the @AdditionalCriteria annotation.

COLUMN examples

SELECT e FROM Employee e WHERECOLUMN('MANAGER_ID', e)= :id

SELECT e FROM Employee e WHERECOLUMN('ROWID', e)= :id

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.