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.
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.
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)')
For more information, see: