Pages

Tuesday, May 15, 2012

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.

NULLS FIRST

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.

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.

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.

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.

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.

Does this mean that I won't get any problems when accessing the same table using JPA and using the native API within the same transaction. And could there be any problems with referential integrity constraints when using both ORM technologies together?

It is very useful information. I have question for you. does eclipse Link support union with order by cluase. my sample query here is select emp from Emp emp where empId= :empId UNION select emp from Emp emp where deptId=: deptID order by emp.empID. I have tired this on it is working without Order by but when i use Order by i am getting The query contains a malformed ending.

We provide consulting, development services, and products in the field of intelligence automation.
Intelligence automation projects are complex and unpredictable, if your organization is in development,
or investigating an automation project, we can provide advice, development services and sub-contracting.