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/Development/2.1/AdvancedJPA Queries
< EclipseLink | Development | 2.1
Revision as of 09:57, 19 January 2010 by Unnamed Poltroon (Talk) (→Extensions to be implemented - in priority order)
Contents
- 1 Extensions to be implemented - in priority order
- 1.1 All database functions
- 1.2 Spatial Functionality via JPQL
- 1.3 Functions in select and order by clause
- 1.4 Order by should be applicable to any field type
- 1.5 Fields and tables, "from X x, Table(Y) y", "x.field(y)"
- 1.6 Object parameters and nested parameters "(:employee).id"
- 1.7 Fetch groups "Select emp(id, name) from Employee emp"
- 1.8 SQL hints
- 1.9 Connect by
- 1.10 Pessimistic locking "for update of emp"
- 1.11 Batch joins
- 2 Extensions already implemented in 2.0
Extensions to be implemented - in priority order
All database functions
JPQL equivalent to Oracle's NVL function
Spatial Functionality via JPQL
- PostGIS Intersects (forum)
- Oracle Spatial
- SDO_WITHIN_DISTANCE
- SDO_NN
- SDO_RELATE
- SDO_FILTER
Functions in select and order by clause
- http://forums.oracle.com/forums/thread.jspa?messageID=3903302�
- http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html
- Already works for select clause (tested ABS), doesn’t work for order by clause.
- However fails:
- SELECT CONCAT(e.firstName, e.lastName) FROM Employee e
- but works:
- SELECT CONCAT(e.firstName, e.lastName) FROM Employee e WHERE e.id > 0
Order by should be applicable to any field type
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…)
Fetch groups "Select emp(id, name) from Employee emp"
- ?
SQL hints
- ?
Connect by
- ?
Pessimistic locking "for update of emp"
- Accessible through hints ("for update" only).
Batch joins
- http://www.eclipse.org/forums/index.php?t=msg&th=123943&start=0&S=f9832b9dae179e13094b3c027540647f
- Accessible through hints.
Extensions already implemented in 2.0
Like with escapes, any types
- Works for strings:
- SELECT e FROM Employee e WHERE e.firstName LIKE "John"
- Have not tested for other types.
- Which types should be tested?
Nested fetch joins (both inner and outer)
- http://www.eclipse.org/forums/index.php?t=msg&goto=379275&#msg_379275
- http://www.eclipse.org/forums/index.php?&t=msg&th=125842
- Inner:
- SELECT p FROM SmallProject p JOIN p.teamLeader e JOIN e.address a WHERE p.name = 'Project1' or e.firstName = 'John' and a.province = 'ON'
- with setHint("eclipselink.join-fetch", "p.teamLeader.address")
- Outer:
- SELECT p FROM SmallProject p LEFT JOIN p.teamLeader e LEFT JOIN e.address a WHERE p.name = 'Project1' or e.firstName = 'John' and a.province = 'ON'
- with setHint("eclipselink.left-join-fetch", "p.teamLeader.address")
- Note that aliasing on the right side of FETCH is explicitly prohibited by the spec., so seems like no extension in JBQL is possible.
Pass list as parameter
- SELECT e FROM Employee e WHERE e.id in :list
- http://forums.oracle.com/forums/thread.jspa?threadID=871532