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

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.STATUS WHEN 0 THEN 'active'  WHEN 1 THEN '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	TRIM(TRAILING FROM e.lastName),  TRIM(e.lastName),  TRIM(LEADING '-' FROM e.lastName)
UPPER	convert the string value to upper case	UPPER(e.lastName)

EclipseLink supported functions

Function	Description	Example
CAST	casts the value to the database type (as of 2.4)	CAST(e.salary NUMERIC(10,2))
EXTRACT	extracts the date part from the date/time value (as of 2.4)	EXTRACT(YEAR, e.startDate)
REGEXP	evaluates 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

Function	Description	Example
INDEX	the index of the ordered List element, only supported with @OrderColumn	SELECT toDo FROM Employee e JOIN e.toDoList toDo  WHERE INDEX(toDo) = 1
KEY	the key of the Map element	SELECT p FROM Employee e JOIN e.priorities p  WHERE KEY(p) = 'high'
SIZE	the 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 EMPTY	evaluates 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 OF	evaluates 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
TYPE	the inheritance discriminator value	SELECT p FROM Project p  WHERE TYPE(p) = LargeProject
TREAT	treat (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
FUNCTION	call 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