JPQL vs SQL, why not have both

One of the most common questions I see on JPA, is users wanting to know how to write some specific SQL query as a JPQL query. Some of the time, they just need to learn JPQL, and their SQL can easily be converted to JPQL. Other times their SQL is using one of the many features on SQL that are not provided in JPQL, and their only option is to use a native SQL query.

In EclipseLink 2.4, we have greatly enhanced our JPQL support to support most features of SQL. I refer to EclipseLink's JPQL extensions as the EclipseLink Query Language, or EQL.

The JPQL support in EclipseLink 1.0 followed the JPA 1.0 BNF. It was not until the 2.1 release that we started adding some extensions, and removing restrictions. We introduced FUNC and TREAT in 2.1. FUNC allows calling any database function, and TREAT allows casting to a subclass for entities with inheritance.

In the upcoming 2.4 release several new features have been added: (available for download today here)

• ON
• UNION
• INTERSECT
• EXCEPT
• NULLS FIRST/LAST
• CAST
• EXTRACT
• REGEXP
• FUNCTION
• OPERATOR
• SQL
• COLUMN
• TABLE

EQL not only allows usage of more of the SQL syntax and functionality, but also allows the mixing of SQL, and SQL constructs within JPQL. EQL provides a hybrid query language that is object-oriented and database platform independent, but can still access raw data and database specific functionality when required.

ON

SQL 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, EXCEPT

SQL 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 FIRST

SQL 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

CAST

SQL 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

EXTRACT

SQL 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

REGEXP

Regular 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 FUNCTION

SQL 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

OPERATOR

Some 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

SQL

The 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 ? marker.

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

The 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

TABLE

The 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 FETCH

JPQL 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 API

You 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\.*")));

Summary

EQL offers a lot of functionality to make querying easier and more powerful. There are still a few features of SQL that would be difficult to expression with EQL, but the majority of SQL is feasible. Of coarse, there is nothing in JPA that forces you to use JPQL, and if you love SQL, you are still free to use native SQL queries.