Eclipse Community Forums: EclipseLink » Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work

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

Eclipse User

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

Eclipse User

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

Eclipse User

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 16:07

Eclipse User

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 03:09

Eclipse User

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 03:29

Eclipse User

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 03:30] by Moderator

Re: Eclipselink - JPA 2.0 - Select with lockMode and maxResult doesn't work [message #1490917 is a reply to message #1489279]

Fri, 28 November 2014 09:57

Eclipse User

Unfortunately I was unable to get this working now. I had some problems with changing where clause on the fly from OraclePlatform#printSQLSelectStatement(...) method. Will give it a try again laer.
Just keep watching bug# 453208.

Previous Topic:	Reading BLOB image from MySQL database in spring mvc
Next Topic:	Inherited subtypes in collection operations?

Goto Forum:

-=] Back to Top [=-

Current Time: Fri Jul 11 09:12:02 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter