Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

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

(DatabasePlatform Expression Operators)
 
(10 intermediate revisions by 2 users not shown)
Line 14: Line 14:
  
 
==Extensions to be implemented - in priority order==
 
==Extensions to be implemented - in priority order==
 +
 +
{|{{BMTableStyle}}
 +
|-{{BMTHStyle}}
 +
! Bug
 +
! Summary
 +
! Status
 +
|-
 +
| {{bug|300512}} || Add FUNCTION support to extended JPQL || Assigned - ailitchev
 +
|-
 +
| {{bug|211700}} || Batch reading using EXISTS and IN || Assigned - jsutherland
 +
|-
 +
| {{bug|259266}} || Down cast in queries || New
 +
|}
  
 
====All database functions====
 
====All database functions====
 +
''Implemented'' ({{bug|300512}}):
 +
Any native database function could be called in jpql.
 +
To call 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()]
 
:[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=====
 
=====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?
 
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====
+
=====Spatial Functionality via JPQL=====
 
: [http://www.eclipse.org/forums/index.php?S=645995efb907d1f81c9ac84afe05ef9f&t=msg&th=126379 PostGIS Intersects (forum)]  
 
: [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
 
: Oracle Spatial
:* SDO_WITHIN_DISTANCE
+
<source lang="java">
:* SDO_NN
+
SELECT ss FROM SimpleSpatial ss WHERE FUNC('mdsys.sdo_relate', ss.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY ss.id ASC
:* SDO_RELATE
+
</source>
:* SDO_FILTER
+
 
  
 
====Functions in select and order by clause====
 
====Functions in select and order by clause====
Line 34: Line 65:
 
* [http://forums.oracle.com/forums/thread.jspa?messageID=3903302&#3903302 ORDER BY RAND()] - platform specific?
 
* [http://forums.oracle.com/forums/thread.jspa?messageID=3903302&#3903302 ORDER BY RAND()] - platform specific?
 
* [http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html Aggregate functions in ORDER BY]
 
* [http://dev.eclipse.org/mhonarc/lists/eclipselink-users/msg03720.html Aggregate functions in ORDER BY]
* CONCAT projection query without WHERE
+
* CONCAT projection query without WHERE. Fixed.
 
<source lang="java">
 
<source lang="java">
 
// This fails:
 
// This fails:
Line 50: Line 81:
 
: SELECT e FROM Employee e WHERE e.address IN (SELECT a FROM Address…)
 
: 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'''
 
<source lang="java">
 
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();
 
</source>
 
 
''' Proposed JPQL''':
 
<source lang="java">
 
Query query = em.createQuery("Select emp(name) from Employee emp");
 
 
List<Employee> results = query.getResultsList();
 
</source>
 
  
 
====SQL Hints====
 
====SQL Hints====
Line 158: Line 170:
 
# Extend default FetchGroup<br>Currently any named or dynamic FetchGroup is assumed to be independent of any fetch configuration in the entity mappings. This means that these FetchGroup must fully repeat the default  FetchGroup configuration if requested. With this enhancement it will be possible to either make a new independent FetchGroup or to define a FetchGroup that is based on the default allowing the definition to simply add/remove attribute that differ from the default.
 
# Extend default FetchGroup<br>Currently any named or dynamic FetchGroup is assumed to be independent of any fetch configuration in the entity mappings. This means that these FetchGroup must fully repeat the default  FetchGroup configuration if requested. With this enhancement it will be possible to either make a new independent FetchGroup or to define a FetchGroup that is based on the default allowing the definition to simply add/remove attribute that differ from the default.
 
# Nested <br>Currently FetchGroup support is limited to attributes on the entity that is the target of a query. With nested FetchGroup support application developers will be able to define what parts of related objects are loaded. The current [[EclipseLink/Development/Incubator/Extensions/NestedFetchGroup]] incubator allows this to be specified on a relationship but it may be useful to also support specifying dynamic FetchGroups to use in the scope of a source query for all entities of a specified type.
 
# Nested <br>Currently FetchGroup support is limited to attributes on the entity that is the target of a query. With nested FetchGroup support application developers will be able to define what parts of related objects are loaded. The current [[EclipseLink/Development/Incubator/Extensions/NestedFetchGroup]] incubator allows this to be specified on a relationship but it may be useful to also support specifying dynamic FetchGroups to use in the scope of a source query for all entities of a specified type.
 +
# Dynamic FetchGroup in JPQL <br>This refers to specifying a dynamic FetchGroup that is used instead of the default one using JPQL.
 +
 +
'''Hint Example'''
 +
<source lang="java">
 +
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();
 +
</source>
 +
 +
''' Proposed JPQL''':
 +
<source lang="java">
 +
Query query = em.createQuery("Select emp(name) from Employee emp");
 +
 +
List<Employee> results = query.getResultsList();
 +
</source>
  
 
=== DatabasePlatform Expression Operators ===
 
=== DatabasePlatform Expression Operators ===
Line 256: Line 286:
 
|-
 
|-
 
| ExpressionOperator.toDate());
 
| ExpressionOperator.toDate());
|
 
|-
 
| ExpressionOperator.currentDate());
 
|
 
|-
 
| ExpressionOperator.currentTime());
 
|
 
|-
 
| ExpressionOperator.simpleMath(ExpressionOperator.Add, "+"));
 
|
 
|-
 
| ExpressionOperator.simpleMath(ExpressionOperator.Subtract, "-"));
 
|
 
|-
 
| ExpressionOperator.simpleMath(ExpressionOperator.Multiply, "*"));
 
|
 
|-
 
| ExpressionOperator.simpleMath(ExpressionOperator.Divide, "/"));
 
|
 
|-
 
| ExpressionOperator.negate());
 
 
|  
 
|  
 
|-
 
|-
Line 286: Line 295:
 
|-
 
|-
 
| ExpressionOperator.cosh());
 
| ExpressionOperator.cosh());
|
 
|-
 
| ExpressionOperator.abs());
 
 
|  
 
|  
 
|-
 
|-
Line 301: Line 307:
 
|-
 
|-
 
| ExpressionOperator.exp());
 
| ExpressionOperator.exp());
|
 
|-
 
| ExpressionOperator.sqrt());
 
 
|  
 
|  
 
|-
 
|-
Line 313: Line 316:
 
|-
 
|-
 
| ExpressionOperator.log());
 
| ExpressionOperator.log());
|
 
|-
 
| ExpressionOperator.mod());
 
 
|  
 
|  
 
|-
 
|-
Line 362: Line 362:
 
| ExpressionOperator.refToHex());
 
| ExpressionOperator.refToHex());
 
|
 
|
|- ExpressionOperator.value());
+
|-  
 +
| ExpressionOperator.value());
 
|
 
|
 
|}
 
|}
Line 402: Line 403:
 
| ExpressionOperator.currentTime());
 
| ExpressionOperator.currentTime());
 
| CURRENT_TIME
 
| CURRENT_TIME
 +
|-
 +
| ExpressionOperator.today());
 +
| CURRENT_TIMESTAMP
 
|-
 
|-
 
| ExpressionOperator.simpleMath(ExpressionOperator.Add, "+"));
 
| ExpressionOperator.simpleMath(ExpressionOperator.Add, "+"));
Line 427: Line 431:
 
| MOD
 
| MOD
 
|-
 
|-
| );
+
|  
 
| SIZE
 
| SIZE
 
|-
 
|-
| INDEX);
+
|  
| MOD
+
| INDEX
 
|}
 
|}
  
Line 454: Line 458:
 
: SELECT e FROM Employee e WHERE e.id in :list
 
: SELECT e FROM Employee e WHERE e.id in :list
 
: http://forums.oracle.com/forums/thread.jspa?threadID=871532
 
: http://forums.oracle.com/forums/thread.jspa?threadID=871532
 +
 +
[[Category:EclipseLink_Dev_2.1|Advanced JPA Queries]]

Latest revision as of 10:36, 1 June 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
This is the least integrated and JPA friendly approach. Users must unwrap the underlying native query and modify it using query/expression API. This approach has limitations with respect to the ability to augment expressions . Generally the selection criteria can only be appended to.
  • Query Hint
This is JPA friendly and portable as it allows a query to be augmented. This works well with optimizations options but does not support augmenting the selection criteria very well.
  • Criteria API
Using the CriteriaBuilder.function (DB functions) or custom expression factories allows for queries to be built combining extended functionality anywhere in the selection criteria. Any support added for additional JPQL features must be offered in extended criteria API usage.
  • JPQL Extended Grammar
Extending EclipseLink's interpretation of the specification defined JP QL grammar or adding additional functionality allows developers to use these features in literal queries (dynamic and named). These extensions are unlikely to be portable and may have future backwards compatibility issues with later versions of the JPA specification. Ideally these would only be added for highly requested query capabilities or features.

Extensions to be implemented - in priority order

Bug Summary Status
bug 300512 Add FUNCTION support to extended JPQL Assigned - ailitchev
bug 211700 Batch reading using EXISTS and IN Assigned - jsutherland
bug 259266 Down cast in queries New

All database functions

Implemented (bug 300512): Any native database function could be called in jpql. To call MyFunc(a, b, c) use FUNC('MyFunc', a, b, c)

Year function

YEAR()
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
PostGIS Intersects (forum)
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

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


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.

Native Queries returning non-Entity results

Currently JPA supports returning non-Entity type results using JPQL constructor to specify the class and the columns of mapped entities to return and populate the specified class mapping onto a constructor.

SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count)
       FROM Customer c JOIN c.orders o
       WHERE o.count > 100

This same type of functionality could be extended to native queries:

Query q = em.createNativeQuery("SELECT o.id, o.quantity " +
                               "FROM Order o, Item i " +
                               "WHERE (o.item = i.id) AND (i.name = ‘widget’)",
                               com.acme.OrderView.class);

In this case OrderView is not an entity of this persistence unit. It is simply a POJO that has a constructor that can be called with the values returned for id and quantity. The extension here is to allow non-entity class types in the createNativeQuery call and succeed if a constructor exists that matches the returned values. EclipseLink should be flexible in allowing the values to be passed through the ConversionManager if an exact type match is not found when looking up the constructor. This is to handle the fact that different JDBC drivers and even versions may return different data types.

Support array types for IN parameters

When binding named or positional parameters for IN operators EclipseLink should support passing array types.

Query q = em.createQuery("SELECT e FROM Employee e WHERE e.id IN :IDS");
q.setParameter("IDS", new int[] {1,2,3,4,5});
 
List<Employee> emps1To5 = q.getResultList();

Make Id type more flexible in em.find

When passing the id into a find call EclipseLink should be more flexible in the type.

Example: If the id column is int any numeric value that can be converted into an int without losing precision should be accepted.

Querying non-Entity Types

Currently EclipseLink JPA supports queries for entity types only. This could be extended to allow users to specify mapped superclasses, common interfaces, or base classes as their query target. These would be interpreted as heterogeneous queries where the results are built by combining the results of all applicable entity types.

Extend FetchGroup Functionality

In order to address the needs of customers migrating from other persistence solutions, including JDO, the FetchGroup support needs to be extended to support a number of use cases. This has been started in the EclipseLink/Development/Incubator/Extensions/NestedFetchGroup and EclipseLink/Development/Incubator/Extensions/FetchPlan incubators. this feature work will migrate these capabilities into the FetchGroup itself instead of being an extension.

  1. Adding a relationship attribute to a FetchGroup indicates that it must be loaded (FetchPlan)
    Currently adding a relationship attribute to a FetchGroup means that in the case of LAZY relationships the ValueHolder (woven) or IndirectContainer are built and set on the entity so they are ready to load the target when requested. In the case of EAGER relationships the values required (if any) from the source are read and the target is populated. This change will mean that if a relationship is specified in a FetchGroup it is populated in the results prior to being returned to the caller. The EclipseLink/Development/Incubator/Extensions/FetchPlan incubator addresses this through the use of a separate FetchPlan object combined with post execute query events to force lazy relationships to be loaded.
  2. Extend default FetchGroup
    Currently any named or dynamic FetchGroup is assumed to be independent of any fetch configuration in the entity mappings. This means that these FetchGroup must fully repeat the default FetchGroup configuration if requested. With this enhancement it will be possible to either make a new independent FetchGroup or to define a FetchGroup that is based on the default allowing the definition to simply add/remove attribute that differ from the default.
  3. Nested
    Currently FetchGroup support is limited to attributes on the entity that is the target of a query. With nested FetchGroup support application developers will be able to define what parts of related objects are loaded. The current EclipseLink/Development/Incubator/Extensions/NestedFetchGroup incubator allows this to be specified on a relationship but it may be useful to also support specifying dynamic FetchGroups to use in the scope of a source query for all entities of a specified type.
  4. 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();

DatabasePlatform Expression Operators

The following operators are defined in DatasourcePlatform.initializePlatformOperators. Those already covered by JPA 2.0 querying capabilities should be removed.

ExpressionOperator Extended JPA solution (proposed)
ExpressionOperator.chr()
ExpressionOperator.hexToRaw()
ExpressionOperator.initcap()
ExpressionOperator.initcap());
ExpressionOperator.instring());
ExpressionOperator.soundex());
ExpressionOperator.leftPad());
ExpressionOperator.leftTrim());
ExpressionOperator.leftTrim2());
ExpressionOperator.replace());
ExpressionOperator.rightPad());
ExpressionOperator.rightTrim());
ExpressionOperator.rightTrim2());
ExpressionOperator.substringSingleArg());
ExpressionOperator.toNumber());
ExpressionOperator.toChar());
ExpressionOperator.toCharWithFormat());
ExpressionOperator.translate());
ExpressionOperator.trim2());
ExpressionOperator.ascii());
ExpressionOperator.locate2());
ExpressionOperator.nullIf());
ExpressionOperator.ifNull());
ExpressionOperator.addMonths());
ExpressionOperator.dateToString());
ExpressionOperator.lastDay());
ExpressionOperator.monthsBetween());
ExpressionOperator.nextDay());
ExpressionOperator.roundDate());
ExpressionOperator.toDate());
ExpressionOperator.ceil());
ExpressionOperator.cos());
ExpressionOperator.cosh());
ExpressionOperator.acos());
ExpressionOperator.asin());
ExpressionOperator.atan());
ExpressionOperator.exp());
ExpressionOperator.floor());
ExpressionOperator.ln());
ExpressionOperator.log());
ExpressionOperator.power());
ExpressionOperator.round());
ExpressionOperator.sign());
ExpressionOperator.sin());
ExpressionOperator.sinh());
ExpressionOperator.tan());
ExpressionOperator.tanh());
ExpressionOperator.trunc());
ExpressionOperator.greatest());
ExpressionOperator.least());
ExpressionOperator.standardDeviation());
ExpressionOperator.variance());
ExpressionOperator.deref());
ExpressionOperator.ref());
ExpressionOperator.refToHex());
ExpressionOperator.value());

Extensions already implemented in 2.0

DatabasePlatform Expression Operators

ExpressionOperator JPQL
ExpressionOperator.concat() CONCAT
ExpressionOperator.substring()); SUBSTRING
ExpressionOperator.trim()); TRIM
ExpressionOperator.toLowerCase); LOWER
ExpressionOperator.toUpperCase()); UPPER
ExpressionOperator.length()); LENGTH
ExpressionOperator.locate()); LOCALE
ExpressionOperator.currentDate()); CURRENT_DATE
ExpressionOperator.currentTime()); CURRENT_TIME
ExpressionOperator.today()); CURRENT_TIMESTAMP
ExpressionOperator.simpleMath(ExpressionOperator.Add, "+")); +
ExpressionOperator.simpleMath(ExpressionOperator.Subtract, "-")); -
ExpressionOperator.simpleMath(ExpressionOperator.Multiply, "*")); *
ExpressionOperator.simpleMath(ExpressionOperator.Divide, "/")); /
ExpressionOperator.negate()); -
ExpressionOperator.abs()); ABS
ExpressionOperator.sqrt()); SQRT
ExpressionOperator.mod()); MOD
SIZE
INDEX

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

Back to the top