| Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1066234] |
Mon, 01 July 2013 15:54  |
 Silvano Bellani
Messages: 3
Registered: July 2013
Location: Italy |
Junior Member |
|
|
Hi all,
I made a simple named query 'Select s from Sample s where s.a in :aList' where the parameter :aList was a list of string. I configured it on orm.xml file and i called it 'selectAll'
This was a java code for executed a query :
public List<Sample> selectForUpdate() {
Query query = em.createNamedQuery("selectAll");
List<String> aList = new ArrayList<String>();
aList.add("A");
aList.add("B");
aList.add("C");
query.setParameter("aList", aList);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
query.setMaxResults(10);
return query.getResultList();
}
When I executed it with oracle db an error occur :
[EL Finest]: query: 2013-07-01 17:13:51.263--UnitOfWork(509386980)--Thread(Thread[main,5,main])--Execute query ReadAllQuery(name="Sample.selectAll" referenceClass=Sample sql="SELECT A, B FROM SAMPLE WHERE (A IN ?)")
[EL Finest]: connection: 2013-07-01 17:13:56.815--ServerSession(763016767)--Connection(1492837435)--Thread(Thread[main,5,main])--Connection acquired from connection pool [default].
[EL Finer]: transaction: 2013-07-01 17:13:56.815--ClientSession(1209507780)--Connection(1492837435)--Thread(Thread[main,5,main])--begin transaction
[EL Fine]: sql: 2013-07-01 17:13:56.815--ClientSession(1209507780)--Connection(1492837435)--Thread(Thread[main,5,main])--SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT A AS a1, B AS a2 FROM SAMPLE WHERE (A IN (?,?,?)) FOR UPDATE) a WHERE ROWNUM <= ?) WHERE rnum > ?
bind => [A, B, C, 10, 0]
[EL Fine]: sql: 2013-07-01 17:13:56.906--ClientSession(1209507780)--Thread(Thread[main,5,main])--SELECT 1 FROM DUAL
[EL Warning]: 2013-07-01 17:13:56.916--UnitOfWork(509386980)--Thread(Thread[main,5,main])--Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00907: missing right parenthesis
Error Code: 907
Call: SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT A AS a1, B AS a2 FROM SAMPLE WHERE (A IN (?,?,?)) FOR UPDATE) a WHERE ROWNUM <= ?) WHERE rnum > ?
bind => [A, B, C, 10, 0]
Query: ReadAllQuery(name="Sample.selectAll" referenceClass=Sample sql="SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT A AS a1, B AS a2 FROM SAMPLE WHERE (A IN ?) FOR UPDATE) a WHERE ROWNUM <= ?) WHERE rnum > ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:679)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:558)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1995)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:296)
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:2714)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2667)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:477)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1155)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:899)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1114)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:402)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1202)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
at it.tasgroup.test.jpa.dao.JpaSampleDaoImpl.selectForUpdate(JpaSampleDaoImpl.java:64)
at it.tasgroup.test.TestJpaSampleDao.testSelectByIdForUpdate(TestJpaSampleDao.java:23)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:911)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1120)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1242)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3446)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3491)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeSelect(DatabaseAccessor.java:1002)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:641)
... 47 more
I tried to change a db (for example DB2) but the error was logically the same.
If I executed it only with lockmode parameter no error occured.
If I executed it only with maxResult parameter no error occured.
I use the eclipselink 2.5.0 library.
|
|
|
|
|
|
|
| Re: Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1489279 is a reply to message #1463107] |
Thu, 27 November 2014 08:29  |
Tomas Kraus
Messages: 9
Registered: November 2014 |
Junior Member |
|
|
I was working on this last week. See bugs.eclipse.org/bugs/show_bug.cgi?id=453208 for details.
Problem is in OraclePlatform#printSQLSelectStatement(...) code which is always generating query with min and max ROWNUM limits ans is always using two selects as envelope. Unfortunately this does not work with 'FOR UPDATE' keyword triggered by pessimistic locking hint.
I know how to make this working for specific case when the minimum limit is not set. In such a case query like
SELECT t1.EMP_ID AS a1
FROM CMP3_EMPLOYEE t1
LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID),
CMP3_SALARY t2
WHERE t2.EMP_ID = t1.EMP_ID
AND ROWNUM <= 5
FOR UPDATE;
could be used and there is no reason to put it into additional subselect.
But with the minimum limit set it shall be enclosed in another select because of the way how ROWNUM works and such a query does not work in combination with FOR UPDATE.
[Updated on: Thu, 27 November 2014 08:30] Report message to a moderator |
|
|
|
Powered by
FUDForum. Page generated in 0.03419 seconds
] 
Search
Help
Register
Login
Home
