1. Java Persistence API (JPA) Extensions Reference for Oracle TopLink
  2. Java Persistence Query Language Extensions

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 Oracle TopLink specific features.

This chapter includes the following sections:

For more information on JQPL, see:

Special Operators

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

EclipseLink Query Language

Oracle TopLink provides access to the following EclipseLink EQL functions:

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.

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

Example 3-2 Using COLUMN EQL

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

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:

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:

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-7 shows how to use FUNCTION with Oracle Spatial queries

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 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:

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:

ON

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

Usage

Oracle TopLink 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:

OPERATOR

Use OPERATION to call any Oracle TopLink operator.

Usage

Oracle TopLink supports many database functions using standard operator names that are translated to different databases. Oracle TopLink 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 Oracle TopLink 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:

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:

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:

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: