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 11:54 Go to next message
Silvano Bellani is currently offline Silvano Bellani
Messages: 2
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 10:04 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
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 10:20 Go to previous message
Silvano Bellani is currently offline Silvano Bellani
Messages: 2
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.
Previous Topic:Birt with JBOSS 5 Issue
Next Topic:Opened EntityManager
Goto Forum:
  


Current Time: Sat Aug 02 00:55:39 EDT 2014

Powered by FUDForum. Page generated in 0.01504 seconds