3 Java Persistence Query Language Extensions

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

This chapter includes the following sections:

Special Operators
EclipseLink Query Language

For more information on JQPL, see:

"Query Language" in the JPA Specification (http://jcp.org/en/jsr/detail?id=317)
"The Java Persistence Query Language" in The Java EE 6 Tutorial (http://docs.oracle.com/javaee/6/tutorial/doc/bnbtg.html)
"EclipseLink User Guide"

(http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL)

Special Operators

EclipseLink defines the following operators to perform database operations that would not be possible in standard JPQL:

COLUMN
FUNCTION
OPERATOR
SQL

CAST

Use CAST to convert a value to a specific database type.

Usage

The CAST function is database independent, but requires database support.

Examples

Example 3-1 shows how to use this JPQL extension.

Example 3-1 Using CAST EQL

CAST(e.salary NUMERIC(10,2))

COLUMN

Use COLUMN to access to unmapped columns in an object's table.

Usage

You can use COLUMN to access foreign key columns, inheritance discriminators, or primitive columns (such as ROWID). You can also use COLUMN in JPQL fragments inside the @AdditionalCriteria annotation.

Examples

Example 3-2 shows how to use the COLUMN EQL.

Example 3-2 Using COLUMN EQL

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

In Example 3-3, uses COLUMN EQL access a primitive column (ROWID).

Example 3-3 Using COLUMN with a Primitive Column

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

See Also

For more information, see:

"@AdditionalCriteria"

EXCEPT

When performing multiple queries, use EXCEPT to remove the results of a second query from the results of a first query.

Usage

The EXCEPT function is database independent, but requires database support.

Examples

Example 3-4 shows how to use this JPQL extension.

Example 3-4 Using EXCEPT EQL

SELECT e FROM Employee e
EXCEPT SELECT e FROM Employee e WHERE e.salary > e.manager.salary

See Also

For more information, see:

"UNION"
"INTERSECT"

EXTRACT

Use EXTRACT to retrieve the date portion of a date/time value.

Usage

The EXTRACT function is database independent, but requires database support

Examples

Example 3-5 shows how to use this JPQL extension.

Example 3-5 Using EXTRACT EQL

EXTRACT(YEAR, e.startDate)

FUNCTION

Use FUNCTION (formerly FUNC) to call database specific functions from JPQL

Usage

You can use FUNCTION to call database functions that are not supported directly in JPQL and to call user or library specific functions.

Note:

FUNCTION is database specific – it does not translate the function call in any way to support different databases as other JPQL functions do.

Use FUNCTION to call functions with normal syntax. Functions that require special syntax cannot be called with FUNCTION. Instead, use OPERATOR

Examples

Example 3-6 shows how to use this JPQL extension.

Example 3-6 Using FUNCTION EQL

SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) > 613
 
SELECT FUNCTION('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year

Example 3-7 shows how to use FUNCTION with Oracle Spatial queries

Example 3-7 Using FUNCTION EQL Oracle Spatial examples

SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'

SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC

See Also

For more information, see:

"OPERATOR"

INTERSECT

When performing multiple queries, use INTERSECT to return only results that are found in both queries.

Examples

Example 3-8 shows how to use this JPQL extension.

Example 3-8 Using INTERSECT EQL

SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2
SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode1
INTERSECT SELECT e FROM Employee e JOIN e.phones p WHERE p.areaCode = :areaCode2
SELECT e FROM Employee e
EXCEPT SELECT e FROM Employee e WHERE e.salary > e.manager.salary

See Also

For more information, see:

"UNION"
"EXCEPT"

ON

Use the ON clause to append additional conditions to a JOIN condition, such as for outer joins.

Usage

EclipseLink supports using the ON clause between two root level objects.

Examples

Example 3-9 shows how to use this JPQL extension.

Example 3-9 Using ON Clause EQ

SELECT e FROM Employee e LEFT JOIN e.address ON a.city = :city

SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address

See Also

For more information, see:

"JPQL" http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

OPERATOR

Use OPERATION to call any EclipseLink operator.

Usage

EclipseLink supports many database functions using standard operator names that are translated to different databases. EclipseLink operators are supported on any database that has an equivalent function (or set of functions). Use the EclipseLink ExpressionOperator class to define a custom operator or allow DatabasePlatform to override an operator..

OPERATOR is similar to FUNCTION, but allows the function to be database independent, and you can call 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

Examples

Example 3-10 shows how to use this JPQL extension.

Example 3-10 Using OPERATOR EQL

SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10

See Also

For more information, see:

REGEXP

Use REGEXP to determine if a string matches a regular expression.

Usage

To use the REGEXP function, your database must support regular expressions.

Examples

Example 3-11 shows how to use this JPQL extension.

Example 3-11 Using REGEXP EQL

e.lastName REGEXP 'îDr\.*'

See Also

For more information, see:

"JPQL" http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

SQL

Use SQL to integrate SQL within a JPQL statement. This provides an alternative to using native SQL queries simply because the query may require a function not supported in JPQL.

Usage

The SQL function includes both the SQL string (to inline into the JPQL statement) and the arguments to translate into the SQL string. Use a question mark character ( ? ) to define parameters within the SQL that are translated from the SQL function arguments.

You can use SQL to call database functions with non standard syntax, embed SQL literals, and perform any other SQL operations within JPQL. With SQL, you can still use JPQL for the query.

Examples

Example 3-12 shows how to use this JPQL extension.

Example 3-12 Using SQL EQ

SELECT p FROM Phone p WHERE SQL('CAST(? AS CHAR(3))', e.areaCode) = '613'

SELECT SQL('EXTRACT(YEAR FROM ?)', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year

SELECT e FROM Employee e ORDER BY SQL('? NULLS FIRST', e.startDate)

SELECT e FROM Employee e WHERE e.startDate = SQL('(SELECT SYSDATE FROM DUAL)')

See Also

For more information, see:

"JPQL" http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

TABLE

Use TABLE to access unmapped tables.

Usage

With the TABLE function, you use join, collection, history, auditing, or system tables in a JPQL query.

Examples

Example 3-13 shows how to use an audit table (unmapped) within a SELECT statement.

Example 3-13 Using TABLE EQL

SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)

See Also

For more information, see:

"JPQL" http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL

TREAT

Use TREAT to cast an object as its subclass value (that is, downcast related entities with inheritance).

Examples

Example 3-14 shows how to use this JPQL extension.

Example 3-14 Using TREAT EQL

SELECT e FROM Employee JOIN TREAT(e.projects AS LargeProject) 
p WHERE p.budget > 1000000

UNION

Use UNION to combine the results of two queries into a single query.

Usage

With UNION, the unique results from both queries will be returned. If you include the ALL option, the results found in both queries will be duplicated.

Examples

Example 3-15 shows how to use this JPQL extension.

Example 3-15 Using UNION EQL

SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city1
UNION SELECT MAX(e.salary) FROM Employee e WHERE e.address.city = :city2

See Also

For more information, see: