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/Development/2.1/AdvancedJPA Queries"
< EclipseLink | Development | 2.1
(→Extensions to be implemented - in priority order) |
(→Connect by) |
||
Line 40: | Line 40: | ||
:? | :? | ||
− | ==== | + | ==== HIERARCHICAL QUERIES (Oracle) ==== |
− | : | + | |
+ | : ELUG: [[Using_Advanced_Query_API_%28ELUG%29#How_to_Use_Hierarchical_Queries]] | ||
+ | |||
+ | |||
+ | :* START WITH | ||
+ | :* CONNECT BY | ||
+ | :* ORDER SIBLINGS | ||
====Pessimistic locking "for update of emp"==== | ====Pessimistic locking "for update of emp"==== |
Revision as of 10:01, 19 January 2010
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 HIERARCHICAL QUERIES (Oracle)
- 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
- ?
HIERARCHICAL QUERIES (Oracle)
- START WITH
- CONNECT BY
- ORDER SIBLINGS
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