SQL

Use SQL to integrate SQL within a JPQL statement. This provides an alternative to using native SQL queries simply because the query may require a function not supported in JPQL.

Usage

The SQL function includes both the SQL string (to inline into the JPQL statement) and the arguments to translate into the SQL string. Use a question mark character ( ? ) to define parameters within the SQL that are translated from the SQL function arguments.

You can use SQL to call database functions with non standard syntax, embed SQL literals, and perform any other SQL operations within JPQL. With SQL, you can still use JPQL for the query.

Examples

Example 3-12 shows how to use this JPQL extension.

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)')

See Also

For more information, see:

• "JPQL"
  http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL