EclipseLink and JPQL

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.
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 and OPERATOR.
JPQL supported functions
FunctionDescriptionExample
-subtraction
e.salary - 1000
+addition
e.salary + 1000
*multiplication
e.salary * 2
/division
e.salary / 2
ABSabsolute value
ABS(e.salary - e.manager.salary)
CASEdefines a case statement
CASE e.STATUS WHEN 0 THEN 'active' 
WHEN 1 THEN 'consultant' ELSE 'unknown' END
COALESCEevaluates to the first non null argument value
COALESCE(e.salary, 0)
CONCATconcatenates two or more string values
CONCAT(e.firstName, ' ', e.lastName)
CURRENT_DATEthe current date on the database
CURRENT_DATE
CURRENT_TIMEthe current time on the database
CURRENT_TIME
CURRENT_TIMESTAMPthe current date-time on the database
CURRENT_TIMESTAMP
LENGTHthe character/byte length of the character or binary value
LENGTH(e.lastName)
LOCATEthe index of the string within the string, optionally starting at a start index
LOCATE('-', e.lastName)
LOWERconvert the string value to lower case
LOWER(e.lastName)
MODcomputes the remainder of dividing the first integer by the second
MOD(e.hoursWorked / 8)
NULLIFreturns null if the first argument to equal to the second argument, otherwise returns the first argument
NULLIF(e.salary, 0)
SQRTcomputes the square root of the number
SQRT(o.result)
SUBSTRINGthe substring from the string, starting at the index, optionally with the substring size
SUBSTRING(e.lastName, 0, 2)
TRIMtrims leading, trailing, or both spaces or optional trim character from the string
TRIM(TRAILING FROM e.lastName)
TRIM(e.lastName)
TRIM(LEADING '-' FROM e.lastName)
UPPERconvert the string value to upper case
UPPER(e.lastName)

EclipseLink supported functions
FunctionDescriptionExample
CASTcasts the value to the database type (as of 2.4)
CAST(e.salary NUMERIC(10,2))
EXTRACTextracts the date part from the date/time value (as of 2.4)
EXTRACT(YEAR, e.startDate)
REGEXPevaluates if the string matches the regular expression (as of 2.4)
e.lastName REGEXP '^Dr\.*'

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 (as of 2.4) also defines several special functions FUNCTION,  OPERATOR, SQL and COLUMN.
JPQL special operators
FunctionDescriptionExample
INDEXthe index of the ordered List element, only supported with @OrderColumn
SELECT toDo FROM Employee e JOIN e.toDoList toDo 
WHERE INDEX(toDo) = 1
KEYthe key of the Map element
SELECT p FROM Employee e JOIN e.priorities p 
WHERE KEY(p) = 'high'
SIZEthe size of the collection relationships, this evaluates to a sub-select
SELECT e FROM Employee e WHERE 
SIZE(e.managedEmployees) < 2
IS EMPTY, IS NOT EMPTYevaluates to true if the collection relationship is empty, this evaluates to a sub-select
SELECT e FROM Employee e 
WHERE e.managedEmployees IS EMPTY
MEMBER OF, NOT MEMBER OFevaluates to true if the collection relationship contains the value, this evaluates to a sub-select
SELECT e FROM Employee e 
WHERE 'write code' MEMBER OF e.responsibilities
TYPEthe inheritance discriminator value
SELECT p FROM Project p 
WHERE TYPE(p) = LargeProject
TREATtreat (cast) the object as its subclass value (JPA 2.1 draft)
SELECT e FROM Employee JOIN TREAT(e.projects 
AS LargeProject) p WHERE p.budget > 1000000
FUNCTIONcall a database function (JPA 2.1 draft)
SELECT p FROM Phone p WHERE 
FUNCTION('TO_NUMBER', p.areaCode) > 613

EclipseLink special operators

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

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. OPERATOR requires EclipseLink 2.4.
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
SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10

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. SQL requires EclipseLink 2.4.
SQL examples
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)')

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 requires EclipseLink 2.4.
COLUMN examples
SELECT e FROM Employee e WHERE COLUMN('MANAGER_ID', e) = :id
SELECT e FROM Employee e WHERE COLUMN('ROWID', e) = :id

TABLE

TABLE allows access to unmapped tables. This can be used to access join, collection, history, auditing, or system tables for use in JPQL queries. TABLE requires EclipseLink 2.4.
TABLE example
SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)

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.
  • Allow != in place of <>
  • FUNCTION operation to call database specific functions (now FUNCTION in JPA 2.1)
  • TREAT operation to downcast related entities with inheritance
  • OPERATOR operation to call EclipseLink database independent functions (EL 2.4)
  • SQL operation to mix SQL with JPQL (EL 2.4)
  • CAST and EXTRACT functions (EL 2.4)
  • REGEXP function for regular expression querying (EL 2.4)
  • Usage of sub-selects in the SELECT and FROM clause (EL 2.4)
  • ON clause support for defining JOIN and LEFT JOIN conditions (EL 2.4)
  • Joins between independent entities (EL 2.4)
  • Usage of an alias on a JOIN FETCH (EL 2.4)
  • COLUMN operation to allow querying on non mapped columns (EL 2.4)
  • TABLE operation to allow querying on non mapped tables (EL 2.4)
  • UNION, INTERSECT, EXCEPT support (EL 2.4)
  • Usage of object variables in =, <>, IN, IS NULL, and ORDER BY

Featured Post

Updated Coupons / promo codes for Google Apps for Work / Business

To redeem: 1. Sign up for Google Apps   2. Go to your billing settings  3. Choose your payment plan  4. Enter your promo code ...