Use FUNCTION
(formerly FUNC
) to call database specific functions from JPQL
You can use FUNCTION
to call database functions that are not supported directly in JPQL and to call user or library specific functions.
Note:
FUNCTION
is database specific – it does not translate the function call in any way to support different databases as other JPQL functions do.
Use FUNCTION
to call functions with normal syntax. Functions that require special syntax cannot be called with FUNCTION
. Instead, use OPERATOR
Example 3-6 shows how to use this JPQL extension.
Example 3-6 Using FUNCTION EQL
SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) > 613 SELECT FUNCTION('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year
Example 3-7 shows how to use FUNCTION
with Oracle Spatial queries
Example 3-7 Using FUNCTION EQL Oracle Spatial examples
SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'
SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC
For more information, see: