Difference between revisions of "EclipseLink/Development/2.1/AdvancedJPA Queries"

Revision as of 11:06, 19 January 2010

bug 219814

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

http://www.eclipse.org/forums/index.phpt=msg&th=124404&start=0&S=6eb7686393e997064947f7e51e5aca88

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

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…)

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)

ELUG: Using_Advanced_Query_API_(ELUG)#How_to_Use_Hierarchical_Queries

• 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.

XDB Query Functions

ELUG content: Introduction_to_EclipseLink_Expressions_(ELUG)#XMLType_Functions

XDB XMLType functions:

• extract: Takes an XPath string and returns an XMLType which corresponds to the part of the original document that matches the XPath.
• extractValue: Takes an Xpath string and returns either a numerical or string value based on the contents of the node pointed to by the XPath.
• existsNode: Takes an Xpath expression and returns the number of nodes that match the Xpath.
• getStringVal: Gets the string representation of an XMLType object.
• getNumberVal: Gets the numerical representation of an XMLType object.
• isFragment: Evaluates to 0 if the XML is a well formed document. Evaluates to 1 if the document is a fragment.

Prior to addressing these query extensions we should make sure mapping XMLType columns is easily done through JPA annotations and/or orm/eclipselink-orm XML.

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