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/Release/2.1.0/JPAQueryEnhancements"
(→Query Casting) |
|||
Line 1: | Line 1: | ||
+ | = FUNC = | ||
+ | {{bug|300512}} | ||
+ | |||
+ | ==== Overview ==== | ||
+ | |||
+ | A new JPQL keyword has been added to JPQL to support native database functions. | ||
+ | |||
+ | To call the DB function MyFunc(a, b, c) use FUNC('MyFunc', a, b, c) | ||
+ | |||
+ | ==== Year function==== | ||
+ | :[http://www.eclipse.org/forums/index.php?t=msg&th=124404&start=0&S=6eb7686393e997064947f7e51e5aca88 YEAR()] | ||
+ | <source lang="java"> | ||
+ | SELECT FUNC('YEAR', whenRegistered) Y, COUNT(registration.id) C FROM registration GROUP BY Y | ||
+ | </source> | ||
+ | |||
+ | =====JPQL equivalent to Oracle's NVL function===== | ||
+ | EclipseLink's ExpressionOperator.ifNull will generate "NVL". How can this be used in JPQL and Criteria defined queries? | ||
+ | <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> | ||
+ | |||
+ | =====Spatial Functionality via JPQL===== | ||
+ | : [http://www.eclipse.org/forums/index.php?S=645995efb907d1f81c9ac84afe05ef9f&t=msg&th=126379 PostGIS Intersects (forum)] | ||
+ | <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> | ||
+ | : Oracle Spatial | ||
+ | <source lang="java"> | ||
+ | SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC | ||
+ | </source> | ||
+ | |||
+ | |||
+ | ====Functions in select and order by clause==== | ||
+ | |||
+ | * [http://forums.oracle.com/forums/thread.jspa?messageID=3903302� ORDER BY RAND()] - platform specific? | ||
+ | * [http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html Aggregate functions in ORDER BY] | ||
+ | * CONCAT projection query without WHERE. Fixed. | ||
+ | <source lang="java"> | ||
+ | // This fails: | ||
+ | SELECT CONCAT(e.firstName, e.lastName) FROM Employee e | ||
+ | // This works: | ||
+ | SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0 | ||
+ | </source> | ||
+ | : [http://forums.oracle.com/forums/thread.jspa?threadID=870722 ORDER BY boolean] | ||
+ | |||
+ | ====Fields and tables, "from X x, Table(Y) y", "x.field(y)"==== | ||
+ | :? | ||
+ | |||
+ | ====Object parameters and nested parameters "(:employee).id"==== | ||
+ | :Saw several times users attempting something like: | ||
+ | : SELECT e FROM Employee e WHERE e.address IN (SELECT a FROM Address…) | ||
+ | |||
= Query Casting = | = Query Casting = | ||
{{bug|259266}} | {{bug|259266}} |
Revision as of 11:28, 11 June 2010
FUNC
Overview
A new JPQL keyword has been added to JPQL to support native database functions.
To call the DB function MyFunc(a, b, c) use FUNC('MyFunc', a, b, c)
Year function
SELECT FUNC('YEAR', whenRegistered) Y, COUNT(registration.id) C FROM registration GROUP BY Y
JPQL equivalent to Oracle's NVL function
EclipseLink's ExpressionOperator.ifNull will generate "NVL". How can this be used in JPQL and Criteria defined queries?
// 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
Spatial Functionality via JPQL
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'
- Oracle Spatial
SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC
Functions in select and order by clause
- ORDER BY RAND() - platform specific?
- Aggregate functions in ORDER BY
- CONCAT projection query without WHERE. Fixed.
// This fails: SELECT CONCAT(e.firstName, e.lastName) FROM Employee e // This works: SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0
Fields and tables, "from X x, Table(Y) y", "x.field(y)"
- ?
Object parameters and nested parameters "(:employee).id"
- Saw several times users attempting something like:
- SELECT e FROM Employee e WHERE e.address IN (SELECT a FROM Address…)
Query Casting
This shows examples on how to use EclipseLink to define queries on inheritance hierarchies with down casting to specific classes. OverviewJPA/ORM developers will use this query feature to query across attributes in subclasses. This feature is available in JPQL, EclipseLink Expressions and Criteria API. JPA2.0 TypeExtensions to the expression framework to limit the results to those of a specific subclass have already been implemented as part of the JPA 2.0 effort. Expression.type(Class) is available in the expression framework and equivalent functionality is available in JPQL. e.g. "select p from Employee e join e.projects p where type(p) = LargeProject" can be used to retrieve all the LargeProjects (Subclass of Project) from Employee. JPQL Extensions to use DowncastJPQL is extended to cast in the FROM clause. The format of this will use the keyword "TREAT" and be part of the join clause. The following is an example: select e from Employee e join TREAT(e.projects AS LargeProject) lp where lp.budget = value Criteria APIJPA Criteria API already provides a casting operator. It is Expression.as(type). As it is defined by JPA 2.0, Expression.as(type) does a simple cast that allows matching of types within the generics. EclipseLink 2.1 extends criteria API to allow a cast using Expression.as(type). The as method has been extended to check the hierarchy and if type is a subclass of the type for the expression that as is being called on a cast will be implemented. Here is a criteria query that will do a cast: Root<Employee> empRoot = cq1.from(getEntityManagerFactory().getMetamodel().entity(Employee.class)); Join<Employee, Project> join = empRoot.join("projects"); Path exp = ((Path)join.as(LargeProject.class)).get("budget"); cq1.where(qb1.equal(exp, new Integer(1000)) ); Calling a cast on a JOIN node will permanently alter that node. i.e. In the example above, after calling join.as(LargeProject.class), join will refer to a LargeProject. EclipseLink Expression Support for DowncastWe will implement Expression.as(Class). The following is an example of how one could use it: ReadAllQuery raq = new ReadAllQuery(Employee.class); Expression criteria = raq.getExpressionBuilder().anyOf("projects").as(LargeProject.class).get("budget").greaterThan(100); raq.setSelectionCriteria(criteria); List resultList = query.getResultList(); In this query Employee has a xToMany mapping to Project. LargeProject is a subclass of Project and the "budget" attribute is contained on LargeProject.
Example SQLThe following query: Select e from Employee e join e.projects project Will currently produce the following sql: SELECT <select list> FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_EMP_PROJ t4, CMP3_PROJECT t3, CMP3_SALARY t2 WHERE ((t2.EMP_ID = t1.EMP_ID) AND ((t4.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t4.projects_PROJ_ID)))
Expression criteria = project.as(LargeProject.class).get("budget").greaterThan(100); raq.setSelectionCriteria(criteria); The following SQL will be produced: SELECT <select list> FROM CMP3_PROJECT t3 LEFT OUTER JOIN CMP3_LPROJECT t4 ON (t4.PROJ_ID = t3.PROJ_ID),CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_EMP_PROJ t5, CMP3_SALARY t2 WHERE (((t4.BUDGET > ?) AND (t2.EMP_ID = t1.EMP_ID)) AND ((t5.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t5.projects_PROJ_ID))) bind => [100.0] The changes as listed above in bold. |
Contents |