- NULLS FIRST/LAST
ONSQL defines an ON clause to joins, but JPA 2.0 JPQL does not. JPQL does not require an ON clause because when a relationship is joined, the ON clause comes from the join columns already defined in the mapping. Sometimes however it is desirable to append additional conditions to the join condition, normally in the case of outer joins. EclipseLink supports the ON clause, both for relationships joins, and to define joins between two independent objects. The JPA 2.1 draft defines an ON clause, but only on relationship joins, not on joins between independent objects.
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
UNION, INTERSECT, EXCEPTSQL supports UNION, INTERSECT and EXCEPT, but JPA 2.0 JPQL does not. Most unions can be done in terms of joins, but some can not, and some are more difficult to express using joins. EclipseLink supports UNION, INTERSECT and EXCEPT, including the ALL option to include duplicates.
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
NULLS FIRSTSQL supports NULLS FIRST, and NULLS LAST ordering options but JPA 2.0 JPQL does not. EclipseLink supports NULLS FIRST, and NULLS LAST in the ORDER BY clause.
SELECT e FROM Employee e LEFT JOIN e.manager m ORDER BY m.lastName NULLS FIRST
CASTSQL supports a CAST function to convert between datatypes, JPA 2.0 JPQL does not support this function. EclipseLink supports CAST allowing any value to be cast to any database type supported by the database.
SELECT CAST(e.salary NUMERIC(10,2)) FROM Employee e
EXTRACTSQL supports an EXTRACT function for accessing date/time values, JPA 2.0 JPQL does not support any functions for accessing date/time values. EclipseLink supports EXTRACT allowing any database supported date/time part value to be extracted from the date/time.
SELECT EXTRACT(YEAR, e.startDate) FROM Employee e
REGEXPRegular expression comparisons are supported by many databases, although there is no standard SQL syntax that has been adopted by major databases yet. JPA 2.0 JPQL does not support regular expressions. EclipseLink supports REGEXP on Oracle, PostgreSQL, MySQL, MongoDB, and other supporting databases.
SELECT e FROM Employee e WHERE e.lastName REGEXP '^Dr\.*'
FUNC and FUNCTIONSQL supports a lot more database functions than JPQL. Specific database vendors also provide their own set of functions. Users and libraries can also define their own database functions. JPA 2.0 JPQL provides no mechanism to call database specific functions. The JPA 2.1 draft defines a special FUNCTION operator in JPQL to allow calling a specific database function. EclipseLink 2.1 provided this support using the FUNC operator. EclipseLink 2.4 will also provide the same functionality using the JPA 2.1 FUNCTION operator.
SELECT FUNC('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year
OPERATORSome SQL functions have special syntax such as EXTRACT that takes a date part keyword (YEAR, MONTH, DAY), or CAST that takes a type name (NUMBER(10,2)). The FUNCTION operator cannot be used to call these special functions because of their special syntax. FUNCTION is also database specific, and does not allow the JPA provider to map the function to a different name on a different database platform. EclipseLink has support for over 80 database functions through defined EclipseLink ExpressionOperators. EclipseLink has always supported these operator using EclipseLink Expression queries, and now supports these operators using the JPQL OPERATOR keyword. OPERATOR allows calling any EclipseLink ExpressionOperator. EclipseLink ExpressionOperators are database independent, in that if a database provides a equivalent function it is used. ExpressionOperators support generating any syntax to allow calling database functions that require special syntax. The list of supported EclipseLink ExpressionOperators is here. Users can also define their own ExpressionOperators.
SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10
SQLThe SQL operator allows for any SQL to be embedded in the JPQL query. This allows a hybridization of JPQL and SQL, giving the advantages of both in the same query. Previously if any part of the query required something not supported by JPQL, the entire query would need to be rewritten as a native SQL query. Now JPQL can still be used, and the SQL operator can be used just for the parts that require SQL. The SQL operator accepts a variable number of arguments, which are translated into the SQL string using the
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)')
COLUMNThe COLUMN operator allows for any unmapped column to be referenced in JQPL. This can be used to access unmapped columns such as foreign key columns, inheritance discriminators, or system columns such as ROWID.
SELECT e FROM Employee e WHERE COLUMN('MANAGER_ID', e) = :id SELECT e FROM Employee e WHERE COLUMN('ROWID', e) = :id
TABLEThe TABLE operator allows for any unmapped table to be referenced in JQPL. This can be used to access join, collection, history, auditing, or system tables for use in JPQL queries.
SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)
JOIN FETCHJPQL does not allow using an alias on a JOIN FETCH, and does not allow nested join fetches. EclipseLink allows these.
SELECT e FROM Employee e JOIN FETCH e.address a ORDER BY a.city SELECT e FROM Employee e JOIN FETCH e.manager m JOIN FETCH m.manager
Criteria APIYou may be asking yourself, this is all great, but what about the Criteria API, can these extensions be used with that? EclipseLink 2.4 will provide a JpaCriteriaBuilder interface that allows access to EclipseLink specific functionality. Both Criteria queries and JPQL queries get translated to EclipseLink Expression queries, before being translated to SQL. EclipseLink Expressions support all of the above functionality through their API. JpaCriteriaBuilder defines two API toExpression() and fromExpression() to create Criteria Expression objects from EclipseLink Expression objects.
JpaCriteriaBuilder cb = (JpaCriteriaBuilder)em.getCriteriaBuilder(); CriteriaQuery
query = cb.createQuery(Employee.class); Root emp = query.from(Employee.class); query.where(cb.fromExpression(cb.toExpression(emp).get("firstName").regexp("^Dr\.*")));