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"
(→JPQL equivalent to Oracle's NVL function) |
(→Batch joins) |
||
Line 90: | Line 90: | ||
:Accessible through hints ("for update" only). | :Accessible through hints ("for update" only). | ||
− | ==== | + | ====Force Relationships to be loaded ==== |
− | + | ||
− | : | + | This [http://www.eclipse.org/forums/index.php?t=msg&th=123943&start=0&S=f9832b9dae179e13094b3c027540647f request] resulted in the creation of 2 extension incubators. One for [[EclipseLink/Development/Incubator/Extensions/NestedFetchGroup]] and one for [[EclipseLink/Development/Incubator/Extensions/FetchPlan]]. |
+ | |||
+ | Doug: I believe both of these can be addressed through additional functionality be added to FetchGroup when these incubators are ported into trunk. | ||
===Extensions already implemented in 2.0=== | ===Extensions already implemented in 2.0=== |
Revision as of 10:33, 19 January 2010
Contents
- 1 Query Extension Types
- 2 Extensions to be implemented - in priority order
- 2.1 All database functions
- 2.2 Spatial Functionality via JPQL
- 2.3 Functions in select and order by clause
- 2.4 Fields and tables, "from X x, Table(Y) y", "x.field(y)"
- 2.5 Object parameters and nested parameters "(:employee).id"
- 2.6 Dynamic FetchGroup in JPQL
- 2.7 SQL Hints
- 2.8 HIERARCHICAL QUERIES (Oracle)
- 2.9 Pessimistic locking "for update of emp"
- 2.10 Force Relationships to be loaded
- 2.11 Extensions already implemented in 2.0
Query Extension Types
The goal of this effort is to increase the usability of advanced EclipseLink query capabilities through JPA. As these are reviewed and prioritized they must be categorized by the type of extension that will be supported.
Native API
Query Hint
Criteria API
JPQL Extended Grammar
Extensions to be implemented - in priority order
All database functions
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?
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
- ORDER BY RAND() - platform specific?
- Aggregate functions in ORDER BY
- CONCAT projection query without WHERE
// 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…)
Dynamic FetchGroup in JPQL
This refers to specifying a dynamic FetchGroup that is used instead of the default one using JPQL.
Hint Example
Query query = em.createQuery("Select emp from Employee emp"); FetchGroup fg = new FetchGroup(); fg.addAttribute("name"); query.setHint(QueryHints.FETCH_GROUP, fg); List<Employee> results = query.getResultsList();
Proposed JPQL:
Query query = em.createQuery("Select emp(name) from Employee emp"); List<Employee> results = query.getResultsList();
SQL Hints
Currently EclipseLink supports specifying (Oracle) SQL hints using [ DatabaseQuery.setHintString("...")] (see Using_Advanced_Query_API_(ELUG)#How_to_Use_Oracle_Hints) or through JPA using QueryHints.HINT. These hints are limited in where they can be generated in the resulting SQL.
These appear to be Oracle specific. Is this approach useful on other database platforms?
HIERARCHICAL QUERIES (Oracle)
- START WITH
- CONNECT BY
- ORDER SIBLINGS
Pessimistic locking "for update of emp"
- Accessible through hints ("for update" only).
Force Relationships to be loaded
This request resulted in the creation of 2 extension incubators. One for EclipseLink/Development/Incubator/Extensions/NestedFetchGroup and one for EclipseLink/Development/Incubator/Extensions/FetchPlan.
Doug: I believe both of these can be addressed through additional functionality be added to FetchGroup when these incubators are ported into trunk.
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