Bugzilla – Full Text Bug Listing
| Summary: | Improve Support for ON Clause | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Product: | z_Archived | Reporter: | Bernard Missing name <bht237> | ||||||||||||
| Component: | Eclipselink | Assignee: | Nobody - feel free to take it <nobody> | ||||||||||||
| Status: | RESOLVED FIXED | QA Contact: | |||||||||||||
| Severity: | enhancement | ||||||||||||||
| Priority: | P3 | CC: | jamesssss, pascal.filion, tom.ware | ||||||||||||
| Version: | unspecified | ||||||||||||||
| Target Milestone: | --- | ||||||||||||||
| Hardware: | PC | ||||||||||||||
| OS: | Windows Vista | ||||||||||||||
| Whiteboard: | |||||||||||||||
| Attachments: |
|
||||||||||||||
|
Description
Bernard Missing name
Created attachment 167594 [details]
Demo case NetBeans 6.8 project in zip file
The attached example shows asks for a way to generate an ON clause that contains some foreign key joins and, in addition a way to pass in a parameter to further constrain the query.
Current EclipseLink allows you to use a query key to define multiple components to a join and include them in the ON clause. It does not, however allow you to pass a parameter into the query key. As a result:
We can support this:
1. SELECT p.name, f.id FROM product p LEFT OUTER JOIN product_favorite f
ON (p.id = f.product_id AND f.user_id = "myUserId")
Bug not this:
2. SELECT p.name, f.id FROM product p LEFT OUTER JOIN product_favorite f
ON (p.id = f.product_id AND f.user_id = :userId)
To support #1 a DescriptorCustomizer should be written that defines a query key. Something like (Database fields do not conform to example):
...
OneToOneQueryKey queryKey = new OneToOneQueryKey();
queryKey.setName("favoriteqk");
queryKey.setReferenceClass(Favorite.class);
builder = new ExpressionBuilder();
queryKey.setJoinCriteria(
builder.getField("FAVORITE.FAV_ID").equal(
builder.getParameter("PROD.PROD_ID")).and(builder.getField("FAVORITE.UID").equal(new ConstantExpression("user", builder"))));
descriptor.addQueryKey(queryKey);
Then a ReadAllQuery can be created that traverses that query key:
...
ReadAllQuery query = new ReadAllQuery(Product.class);
ExpressionBuilder eb = query.getExpressionBuilder();
query.addJoinedExpression(eb.getAllowingNull("favouriteqk"));
List<Employee> emps = (List)getDbSession().executeQuery(query, args);
Some work has recently been checked in to allow query keys to be used in JPQL, so it is possible the following JPQL will also work if the query key is defined (untested)
select p from Product p join p.favouriteqk f
I am changing this bug to an enhancement request to support an ON clause that takes a parameters.
Tom, from your comments it appears that the new solution returns only Product not both Product with ProductFavorite via the DTO ProductWithFavoriteDTO. We need the DTO in the constructor of the query to be type safe. I think it would be really easy to just adhere to the testcase which was created as a minimum requirement. Saves a lot of discussions. I would think that if the requirements of this testcase are not met than we don't have a useful solution. Many thanks again for your support. The above was intended as an example. To get results of multiple types from your query you need to use a ReportQuery. Here is some documentation: http://wiki.eclipse.org/Introduction_to_EclipseLink_Queries_(ELUG)#Report_Query Query would be roughly: ReadAllQuery query = new ReportQuery(); ExpressionBuilder eb = query.getExpressionBuilder(); Expression join = eb.getAllowingNull("favouriteqk"); query.addJoinedExpression(join); query.addItem("name", eb.get("name")); query.addItem("id", join.get("id")); List<Employee> emps = (List)getDbSession().executeQuery(query, args); The JpaHelper class listed in our API provides a getReportQuery and a getReadAllQuery method that can be used on a query constructed through JPQL or Criteria API. It would be possible to use that query as a starting point. Here is a link to recent API docs where you can find JpaHelper http://www.eclipse.org/eclipselink/api/2.0.1/index.html Note to the implementer of this enhancement. The attached example provides an excellent example of what is needed. Support for parameters in the ON clause is key to making this work for users. i.e. the ":userid" part of this query. SELECT p.name, f.id FROM product p LEFT OUTER JOIN product_favorite f ON (p.id = f.product_id AND f.user_id = :userId) Created attachment 204705 [details]
patch to add on clause support to expressions
This initial patch adds support for ON clause from EclipseLink Expressions. Since JPQL generates Expressions, this is a pre-req to this feature. Am looking into JPQL next. See design document, http://wiki.eclipse.org/EclipseLink/DesignDocs/312146 In case you didn't know, when I last heard, ON was getting consideration in the next JPA specification. 'might be worth trying to aline the JPQL with what is proposed if it exists in the spec now. SVN trunk pending commit - bug#312146 - ON clause https://bugs.eclipse.org/bugs/show_bug.cgi?id=312146 Added ON clause support to Expressions. Code review: Andrei (pending) Changes: - changed invalid methods in Expression abstract class to throw invalid method exceptions. - added new join and leftJoin API to Expression. - added support for expression builders to be joined - changed some internal fields in expressions to List from Vector. - added on clause support to QueryKeyExpression - fixed non fetch join expressions to be aliased. - fixed normalize to set session on all builders to avoid left builder errors. - added addNonFetchJoin API to ObjectLevelReadQuery. - added on clause and join tests to expression test suite Created attachment 211686 [details]
add jpql support for on
This adds support for the ON clause in JPQL in the Hermes parser. Only support ON with relationship joins still require joins of independent objects. SVN trunk commit: Bug#312146 - ON clause JPQL support https://bugs.eclipse.org/bugs/show_bug.cgi?id=312146 This adds initial support for ON clause in JPQL using the Hermes parser. Still require support for joining independent objects through ON. Changes: - Append join expression from ON when processing joins in AbstractReadAllQueryVisitor. - Fixed unsupported error messages in Expression for joins. - Fixed PessimisticLockingExtendedScopeTestSuite to defer SQLServer tests correctly in test, not when building suite, as platform may not be set yet and can cause errors in testing tool. - Added ON clause tests to JUnitJPQLComplexTestSuite and JUnitJPQLValidationTestSuite. - Fixed typo in HavingClause. - Added onClause to Join - Added OnClause, BNF and Factory to Hermes parser. Created attachment 212937 [details]
switches to hermes parser
SVN trunk commit: Bug#312146 JPQL This adds a persistence unit property for setting the JPQL parser (ANTLR or Hermes), and allows the validation level (JPA 1/2/2.1/EL) to be set. This also switches the default JPQL parser to be Hermes to allow the nightly testing to run with it. Code review: Andrei (pending) Changes: - Switches default JPQL parser to Hermes. - Adds persistence unit properties for JPQL parser and validation level. - Allows JPQL queries to obtain cache hits on indexed fields by default. - Added test model for inner joins. Created attachment 213457 [details]
switches to hermes attempt2
Fixed in 2.4 The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink |