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.
Difference between revisions of "EclipseLink/UserGuide/JPA/Basic JPA Development/Querying/Support for Native Database Functions"
< EclipseLink | UserGuide | JPA | Basic JPA Development | Querying
m |
|||
Line 13: | Line 13: | ||
To call the database function <tt>MyFunc(a, b, c)</tt> use the following: | To call the database function <tt>MyFunc(a, b, c)</tt> use the following: | ||
− | <tt>FUNC('MyFunc', a, b, c)</tt> | + | <tt>FUNC('MyFunc', a, b, c)</tt> |
− | == | + | The following examples show how to use FUNC call database functions that are not natively supported by JPQL. |
− | The following SQL is used to query the number of rows inserted in a table and group by year | + | |
+ | ===SQL YEAR Function Example === | ||
+ | The following SQL is used to query the number of rows inserted in a table and group by year: | ||
<source lang="sql"> | <source lang="sql"> | ||
Line 32: | Line 34: | ||
</source> | </source> | ||
− | == | + | === NVL and INULL Function Example=== |
− | The Oracle PL/SQL NVL function | + | 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: |
<source lang="java"> | <source lang="java"> | ||
Line 43: | Line 45: | ||
</source> | </source> | ||
− | == | + | === Oracle Spatial Examples === |
<source lang="java"> | <source lang="java"> |
Revision as of 15:01, 25 February 2011
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
Doc in process 02/25/11
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
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