Revision as of 15:04, 25 February 2011

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

Query Keys	Querying	Transactions
EclipseLink Home JPA User Guide: Table of Contents, Search
How to contribute to this guide...