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:
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 defines the following operators to perform database operations that would not be possible in standard JPQL:
EclipseLink provides access to the following EclipseLink EQL functions:
Use CAST
to convert a value to a specific database type.
The CAST
function is database independent, but requires database support.
Example 3-1 shows how to use this JPQL extension.
Use COLUMN
to access to unmapped columns in an object's table.
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.
Example 3-2 shows how to use the COLUMN
EQL.
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
For more information, see:
When performing multiple queries, use EXCEPT
to remove the results of a second query from the results of a first query.
The EXCEPT
function is database independent, but requires database support.
Example 3-4 shows how to use this JPQL extension.
SELECT e FROM Employee e EXCEPT SELECT e FROM Employee e WHERE e.salary > e.manager.salary
For more information, see:
Use EXTRACT
to retrieve the date portion of a date/time value.
The EXTRACT
function is database independent, but requires database support
Example 3-5 shows how to use this JPQL extension.
Use FUNCTION
(formerly FUNC
) to call database specific functions from JPQL
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
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
For more information, see:
When performing multiple queries, use INTERSECT
to return only results that are found in both queries.
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
For more information, see:
Use the ON
clause to append additional conditions to a JOIN
condition, such as for outer joins.
EclipseLink supports using the ON
clause between two root level objects.
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
For more information, see:
Use OPERATION
to call any EclipseLink operator.
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
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
For more information, see:
Use REGEXP
to determine if a string matches a regular expression.
To use the REGEXP
function, your database must support regular expressions.
Example 3-11 shows how to use this JPQL extension.
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.
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.
Example 3-12 shows how to use this JPQL extension.
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)')
Use TABLE
to access unmapped tables.
With the TABLE
function, you use join, collection, history, auditing, or system tables in a JPQL query.
Example 3-13 shows how to use an audit table (unmapped) within a SELECT
statement.
Use TREAT
to cast an object as its subclass value (that is, downcast related entities with inheritance).
Example 3-14 shows how to use this JPQL extension.
Use UNION
to combine the results of two queries into a single query.
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.
Example 3-15 shows how to use this JPQL extension.
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
For more information, see: