Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work(Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work)
Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1066234] Mon, 01 July 2013 15:54 Go to next message
Silvano Bellani is currently offline Silvano BellaniFriend
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 #1066412 is a reply to message #1066234] Tue, 02 July 2013 14:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Please file a feature request to have this supported, as there might be ways on the different databases to create SQL to do what you want. One way to lock the objects will be to use a separate query, possibly by passing in the ids from the paginated query. Or you might try using a scrollable cursor instead:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/Pagination#Using_a_ScrollableCursor

Best Regards,
Chrie
Re: Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1066414 is a reply to message #1066412] Tue, 02 July 2013 14:20 Go to previous messageGo to next message
Silvano Bellani is currently offline Silvano BellaniFriend
Messages: 3
Registered: July 2013
Location: Italy
Junior Member
It is not possible to use a separate query because the query is used as a semaphore to processing or not data.

I think it is a query construction problem :

eclipselink produce this query :

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 > ?


hibernate jpa 2.0 produce this query :

SELECT A AS a1, B AS a2 FROM SAMPLE WHERE A IN (?,?,?) and ROWNUM<=? FOR UPDATE


No error occurred if I execute this last query.
Re: Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1462682 is a reply to message #1066414] Wed, 05 November 2014 21:07 Go to previous messageGo to next message
Matthew Michal is currently offline Matthew MichalFriend
Messages: 1
Registered: November 2014
Junior Member
Sorry for bumping such an old topic but I just came across this same issue with 2.5.2. Was a request ever filed for this issue?
Re: Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1463107 is a reply to message #1462682] Thu, 06 November 2014 08:09 Go to previous messageGo to next message
Silvano Bellani is currently offline Silvano BellaniFriend
Messages: 3
Registered: July 2013
Location: Italy
Junior Member
No, never filed this issue.
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 Go to previous message
Tomas Kraus is currently offline Tomas KrausFriend
Messages: 3
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

Previous Topic:Reading BLOB image from MySQL database in spring mvc
Next Topic:Nondeterministic exception when creating JAXBContext
Goto Forum:
  


Current Time: Thu Nov 27 14:36:36 GMT 2014

Powered by FUDForum. Page generated in 0.01938 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software