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"
(→Query Extension Types) |
(→Native Queries returning non-Entity results) |
||
Line 129: | Line 129: | ||
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. | 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. | ||
+ | |||
+ | === 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. | ||
=== DatabasePlatform Expression Operators === | === DatabasePlatform Expression Operators === |
Revision as of 05:45, 20 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 XDB Query Functions
- 2.12 Native Queries returning non-Entity results
- 2.13 Querying non-Entity Types
- 2.14 DatabasePlatform Expression Operators
- 3 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
- 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
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.
XDB Query 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.
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.
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.concat() | |
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.substring()); | |
ExpressionOperator.substringSingleArg()); | |
ExpressionOperator.toNumber()); | |
ExpressionOperator.toChar()); | |
ExpressionOperator.toCharWithFormat()); | |
ExpressionOperator.translate()); | |
ExpressionOperator.trim()); | |
ExpressionOperator.trim2()); | |
ExpressionOperator.ascii()); | |
ExpressionOperator.length()); | |
ExpressionOperator.locate()); | |
ExpressionOperator.locate2()); | |
ExpressionOperator.nullIf()); | |
ExpressionOperator.ifNull()); | |
ExpressionOperator.addMonths()); | |
ExpressionOperator.dateToString()); | |
ExpressionOperator.lastDay()); | |
ExpressionOperator.monthsBetween()); | |
ExpressionOperator.nextDay()); | |
ExpressionOperator.roundDate()); | |
ExpressionOperator.toDate()); | |
ExpressionOperator.today()); | |
ExpressionOperator.currentDate()); | |
ExpressionOperator.currentTime()); | |
ExpressionOperator.simpleMath(ExpressionOperator.Add, "+")); | |
ExpressionOperator.simpleMath(ExpressionOperator.Subtract, "-")); | |
ExpressionOperator.simpleMath(ExpressionOperator.Multiply, "*")); | |
ExpressionOperator.simpleMath(ExpressionOperator.Divide, "/")); | |
ExpressionOperator.negate()); | |
ExpressionOperator.ceil()); | |
ExpressionOperator.cos()); | |
ExpressionOperator.cosh()); | |
ExpressionOperator.abs()); | |
ExpressionOperator.acos()); | |
ExpressionOperator.asin()); | |
ExpressionOperator.atan()); | |
ExpressionOperator.exp()); | |
ExpressionOperator.sqrt()); | |
ExpressionOperator.floor()); | |
ExpressionOperator.ln()); | |
ExpressionOperator.log()); | |
ExpressionOperator.mod()); | |
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()); | |
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