Community
Participate
Working Groups
I have three entities Employee(name), Customer(name) and Contact(Emp, daysVisited, Cus). Employee: Bob, Paul Customer: CompA, CompB Contacts: Con1 (Bob, 5 days, CompA), Con2 (Paul, 10 days, CompA), Con3 (Paul, 5 days, CompB) Assume the contacts are already read. Using query expression language like this to query all employees visited customer CompA for 5 days: ReadAllQuery raqEmp = new ReadAllQuery(Employee.class); raqEmp.conformResultsInUnitOfWork(); raqEmp.setInMemoryQueryIndirectionPolicy(new InMemoryQueryIndirectionPolicy(InMemoryQueryIndirectionPolicy.SHOULD_TRIGGER_INDIRECTION)); ExpressionBuilder ebEmp = new ExpressionBuilder(); Expression exprAnyOf = ebEmp.anyOf("contacts"); Expression exp = exprAnyOf.get("customer").equal(compA).and(exprAnyOf.get("daysVisted").equal(5)); raqEmp.setSelectionCriteria(exp); Paul is checked when conforming results in UnitOfWork: Paul has Con2 to customer CompA and Con3 with 5 days visited, so Paul is taken into the resultset. That is wrong, only Bob should be in the resultset as of Con1. If the contacts are not instantiated before, Paul ist not known to the cache. Issue is present in eclipselink 2.7.0. Commented Code is attachted.
Created attachment 271209 [details] Entitities and testcode
Isn't: raqEmp.setInMemoryQueryIndirectionPolicy(new InMemoryQueryIndirectionPolicy(InMemoryQueryIndirectionPolicy.SHOULD_THROW_INDIRECTION_EXCEPTION)); what you want?
This is the default setting and it works because conforming the cache is bypassed. At UnitOfWork.scanForConformingInstances() the policy switches to SHOULD_IGNORE_EXCEPTION_RETURN_NOT_CONFORMED. There is a remark to bug 3568141. With a little modification you see why I have to use SHOULD_TRIGGER_INDIRECTION: At the beginning assume the contacts are already read >>and for each contact daysVisited is set to 9.<< Do not commit to DB, go on using the UnitOfWork. Only when SHOULD_TRIGGER_INDIRECTION is used the resultSet of a following query for all employees visited customer CompA for 5 days counts to zero.
To my mind the evaluation of the expression either does wrong or the expression is incorrect constructed. In LogicalExpression.doesConform (the .and()) the expression is split into two parts: 1) exprAnyOf.get("customer").equal(compA) && 2) exprAnyOf.get("daysVisted").equal(5). If the expression is ok, both parts may not be evaluated independently. If the expression is wrong, I have to create one like builderA.anyOf("contacts").WHAT_HERE?(builderB.get("customer").equal(compA)and(builderB.(get("daysVisted").equal(5)))). But how to? A Sub(Report)Query is not applicable because SHOULD_TRIGGER_INDIRECTION could not be set.
Is someone out there who could help me?
'equal' instead of 'and' is what seems to work as expected, so for given example use: Expression exp = exprAnyOf.get("customer").equal(compA).equal(exprAnyOf.get("daysVisted").equal(5)); raqEmp.setSelectionCriteria(exp); instead of: Expression exp = exprAnyOf.get("customer").equal(compA).and(exprAnyOf.get("daysVisted").equal(5)); raqEmp.setSelectionCriteria(exp);
An exception occurses as the constructed SQL-statement is invalid: Missing right brace. It is the equal sign between the two terms: (t0.CUSTOMER_ID = ?) = (t0.DAYSVISTED = ?). [EL Warning]: 2018-02-26 11:33:28.88--UnitOfWork(1110014943)--Thread(Thread[main,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00907: Rechte Klammer fehlt Error Code: 907 Call: SELECT DISTINCT t1.ID, t1.NAME FROM tke_Contact t0, TKE_EMPLOYEE t1 WHERE (((t0.CUSTOMER_ID = ?) = (t0.DAYSVISTED = ?)) AND (t0.EMPLOYEE_ID = t1.ID)) bind => [2650, 5] Query: ReadAllQuery(referenceClass=Employee sql="SELECT DISTINCT t1.ID, t1.NAME FROM tke_Contact t0, TKE_EMPLOYEE t1 WHERE (((t0.CUSTOMER_ID = ?) = (t0.DAYSVISTED = ?)) AND (t0.EMPLOYEE_ID = t1.ID))") Exception in thread "main" Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00907: Rechte Klammer fehlt Error Code: 907 Call: SELECT DISTINCT t1.ID, t1.NAME FROM tke_Contact t0, TKE_EMPLOYEE t1 WHERE (((t0.CUSTOMER_ID = ?) = (t0.DAYSVISTED = ?)) AND (t0.EMPLOYEE_ID = t1.ID)) bind => [2650, 5] Query: ReadAllQuery(referenceClass=Employee sql="SELECT DISTINCT t1.ID, t1.NAME FROM tke_Contact t0, TKE_EMPLOYEE t1 WHERE (((t0.CUSTOMER_ID = ?) = (t0.DAYSVISTED = ?)) AND (t0.EMPLOYEE_ID = t1.ID))") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:682) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558) at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2002) at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:570) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:299) at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:694) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2738) at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2691) at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:495) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1168) at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899) at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1127) at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:403) at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1215) at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2896) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1786) at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1737) at test.bug526546.Main_bug526546.doMain(Main_bug526546.java:116) at test.bug526546.Main_bug526546.main(Main_bug526546.java:186) Caused by: java.sql.SQLSyntaxErrorException: ORA-00907: Rechte Klammer fehlt
right, this does not work neither. The thing is that how it works is very likely by design and observed behaviour is as documented in https://wiki.eclipse.org/Introduction_to_EclipseLink_Expressions_(ELUG)#Using_EclipseLink_Expression_API_for_Joins in particular see the note at that section, which says: "Note: Calling anyOf once would result in a different outcome than if you call it twice. For example, if you query for an employee with a telephone area code of 613 and a number of 123-4599, you would use a single anyOf and a temporary variable. If you query for an employee, who has a telephone with an area code of 613, and who has a telephone with a number of 123-4599, you would call anyOf twice."
I am sorry, I do not understand at all. The link is exactly the page I read when creating the code. I want to query all employees visited customer CompA for 5 days. So I create the reference exprAnyOf: Expression exprAnyOf = ebEmp.anyOf("contacts"); and use the reference twice: Expression exp = exprAnyOf.get("customer").equal(compA).and(exprAnyOf.get("daysVisted").equal(5)); If I like to query all employees visited customer CompA and all employees visited a customer for 5 days, I would use: ExpressionBuilder ebEmp = new ExpressionBuilder(); Expression exp = ebEmp.anyOf("contacts").equal(compA).and(ebEmp.anyOf("contacts").get("daysVisted").equal(5));
(In reply to thorsten kruse from comment #4) > If the expression is wrong, I have to create one like > > builderA.anyOf("contacts").WHAT_HERE?(builderB.get("customer"). > equal(compA)and(builderB.(get("daysVisted").equal(5)))). what about putting explicit join there? like ie: ExpressionBuilder ebEmp = raqEmp.getExpressionBuilder() ExpressionBuilder c = new ExpressionBuilder(Contact.class); Expression exprAnyOf = ebEmp.anyOf("contacts"); Expression exp = (exprAnyOf.get("customer").equal(compA).and(exprAnyOf.get("daysVisted").equal(5))) .and((c.get("customer").equal(compA)).and(c.get("employee").equal(ebEmp))); ...that seems to work on your sample
Well, yout suggestion seems to work. I do not test it at all, but with a first view everything works fine. Thanks a lot. To my mind, this solution is a workaround. The documentaion linked and quoted at comment 8 shows the way I use the expressions and it should work. To manage my application, I would have to adjust and test about 250 pieces of code. Almost impossible.
GitHub Pull Request 236 created by [lukasj] https://github.com/eclipse-ee4j/eclipselink/pull/236
Test case https://github.com/eclipse-ee4j/eclipselink/commit/7398bd134b6d22b755edc7274419ff01bc83f7da
Test Case (new link - Correction) https://github.com/sureshbn/eclipselink/commit/9b393b4e3aac200f1f1baa33455b2937b2060210
any chance you'll submit a pull request with that change?
GitHub Pull Request 293 created by [sureshbn] https://github.com/eclipse-ee4j/eclipselink/pull/293
The fix seems to be accepted, a UnitTest is created, great. 1) Will the fix be part of the next eclipselink release? 2) When will the fix / the next release be available? Regards, Thorsten
(In reply to thorsten kruse from comment #17) > The fix seems to be accepted, a UnitTest is created, great. > 1) Will the fix be part of the next eclipselink release? yes > 2) When will the fix / the next release be available? mid Dec or Jan; hopefully the former date > > Regards, Thorsten
GitHub Pull Request 329 created by [lukasj] https://github.com/eclipse-ee4j/eclipselink/pull/329
GitHub Pull Request 330 created by [lukasj] https://github.com/eclipse-ee4j/eclipselink/pull/330
this is already fixed in 2.6.6, 2.7.4 and master
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
