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.
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.
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) |
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.
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:
|
|
|
|
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
Comments
Post a Comment