Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Support for Native Database Functions
< EclipseLink | UserGuide | JPA | Basic JPA Development | Querying
Revision as of 15:04, 25 February 2011 by Unnamed Poltroon (Talk)
EclipseLink JPA
EclipseLink | |
Website | |
Download | |
Community | |
Mailing List • Forums • IRC • mattermost | |
Issues | |
Open • Help Wanted • Bug Day | |
Contribute | |
Browse Source |
Support for Native Database Functions Using FUNC
The JPQL keyword FUNC is used to support native database functions.
To call the database function MyFunc(a, b, c) use the following:
FUNC('MyFunc', a, b, c)
The following examples show how to use FUNC call database functions that are not natively supported by JPQL.
SQL YEAR Function Example
The following SQL is used to query the number of rows inserted in a table and group by year:
SELECT YEAR(whenRegistered) Y, COUNT(registration.id) C FROM registration GROUP BY Y
The following example shows how to use FUNC to run that query:
SELECT FUNC('YEAR', whenRegistered) Y, COUNT(registration.id) C FROM registration GROUP BY Y
NVL and INULL Function Example
The Oracle PL/SQL NVL function and the MYSQL IFNULL function are used to replace NULL values by another values. The following example shows how to use FUNC with these functions:
// on Oracle SELECT FUNC('NVL', e.firstName, 'NoFirstName'), func('NVL', e.lastName, 'NoLastName') FROM Employee e // on MySQL SELECT FUNC('IFNULL', e.firstName, 'NoFirstName'), func('IFNULL', e.lastName, 'NoLastName') FROM Employee e
Oracle Spatial Examples
The following examples show how to use FUNC with Oracle Spatial queries:
SELECT a FROM Asset a, Geography selectedGeography WHERE selectedGeography.id = :id AND a.id IN :id_list AND FUNC('ST_Intersects', a.geometry, selectedGeography.geometry) = 'TRUE'
SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC