|
|
| Line 1: |
Line 1: |
| − | {{EclipseLink_UserGuide
| + | #REDIRECT[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#FUNC] |
| − | |info=y
| + | |
| − | |toc=n
| + | |
| − | |eclipselink=y
| + | |
| − | |eclipselinktype=JPA}}
| + | |
| − | | + | |
| − | =Support for Native Database Functions Using FUNC=
| + | |
| − | | + | |
| − | The JPQL keyword FUNC is used to support native database functions.
| + | |
| − | | + | |
| − | To call the database function <tt>MyFunc(a, b, c)</tt> use the following:
| + | |
| − | | + | |
| − | <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.
| + | |
| − | | + | |
| − | ===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">
| + | |
| − | SELECT
| + | |
| − | YEAR(whenRegistered) Y,
| + | |
| − | COUNT(registration.id) C
| + | |
| − | FROM registration
| + | |
| − | GROUP BY Y
| + | |
| − | </source>
| + | |
| − | | + | |
| − | The following example shows how to use FUNC to run that query:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | SELECT FUNC('YEAR', whenRegistered) Y, COUNT(registration.id) C FROM registration GROUP BY Y
| + | |
| − | </source>
| + | |
| − | | + | |
| − | === 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:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | // 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
| + | |
| − | </source>
| + | |
| − | | + | |
| − | === Oracle Spatial Examples ===
| + | |
| − | | + | |
| − | The following examples show how to use FUNC with Oracle Spatial queries:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | 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'
| + | |
| − | </source>
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC
| + | |
| − | </source>
| + | |
| − | | + | |
| − | {{EclipseLink_JPA
| + | |
| − | |previous= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query Keys|Query Keys]]
| + | |
| − | |next= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Transactions|Transactions]]
| + | |
| − | |up= [[EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying|Querying]]
| + | |
| − | |version=2.2.0 DRAFT}}
| + | |
