[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-759) problem for mixxing setmaxresults and setlockmode
Frederic Leitenberger (JIRA)
noreply at atlassian.com
Wed Nov 14 14:15:29 EST 2007
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-759?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_28833 ]
Frederic Leitenberger commented on HHH-759:
-------------------------------------------
Hello again, i have serous trouble with this.
I need to use ORDER BY, MAXROWS and FOR UPDATE in one query.
e.g.:
Criteria criteria = getSession().createCriteria(MyClass.class)
.add(Restrictions.eq("myField", false))
.add(Order.desc("oderField"))
.setLockMode(LockMode.UPGRADE)
.setMaxResults(1);
What i want form this query is:
- select the rows for my restricitions
- order the result
- limit the result
- lock the result
What it currently get (simplified) is:
select * from (
select *
from myClass
where myField = 0
order by usage desc
) where rownum <= ?
for update of this_.id
As we already know this doesn't work:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at de.iccs.api.facades.ResellerBlockFacade.listResellerFreeBlocksForAssignment(ResellerBlockFacade.java:245)
at de.iccs.api.facades.ResellerBlockFacade.allocateNumbersInternal(ResellerBlockFacade.java:1095)
at de.iccs.api.facades.GeoNumberFacade.allocateGeoNumbersInternal(GeoNumberFacade.java:979)
at de.iccs.api.facades.GeoNumberFacade.reserveGeoNumbersInternal(GeoNumberFacade.java:938)
at de.iccs.api.facades.GeoNumberFacade.allocateGeoNumbersForAllocateEleven(GeoNumberFacade.java:798)
at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a.CGLIB$allocateGeoNumbersForAllocateEleven$2(<generated>)
at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a$$FastClassByCGLIB$$2ccb1367.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:167)
at de.iccs.api.facades.interceptors.FacadeInterceptor.intercept(FacadeInterceptor.java:17)
at de.iccs.api.facades.interceptors.HibernateFacadeInterceptor.intercept(HibernateFacadeInterceptor.java:82)
at de.iccs.api.facades.GeoNumberFacade$$EnhancerByCGLIB$$cf19ce9a.allocateGeoNumbersForAllocateEleven(<generated>)
at de.iccs.api.facades.GeoNumberFacadeTest.testAllocateGeoNumbersForAllocateEleven(GeoNumberFacadeTest.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:128)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: ORA-00904: "THIS_"."ID": invalid identifier
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:420)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:452)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2929)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 33 more
When i change the setMaxRows-call to:
if (maxResults > 0) criteria.add(sqlRestriction("rownum <= ?", maxResults, new IntegerType()));
... or simply ...
criteria.add(Restrictions.sqlRestriction("rownum <= 1"));
... the following is generated:
select *
from myClass
where myField = 0
and rownum <= ?
order by usage desc
for update of this_.id
The statement can be executed, but does the following (in exaclty this order):
- select the rows for my restricitions
- limit the result (unordered)
- order the result (the one selected row)
- lock the result
For comparison again what i wanted form this query:
- select the rows for my restricitions
- order the result
- limit the result
- lock the result
Any idea how to solve this?
I had an idea of selecting and locking only an id, but i am still not sure if that solves the problem in any way.
Another idea was to drop the setMaxRows part and user iterate to get as many rows as needed an then close the iterator and cursor with Hibernate.closeIterator(iter).
But HB-308 and HHH-249 indicate that this will not be available very soon considering the comments and the last-modified timestamps.
And since it would we somewhat difficult and stupid to bypass the critera-functionality i am stuck here.
Any help would be great !
> problem for mixxing setmaxresults and setlockmode
> -------------------------------------------------
>
> Key: HHH-759
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-759
> Project: Hibernate3
> Issue Type: Bug
> Affects Versions: 3.0.2
> Environment: Client:windows2000 professional,jre1.4.2, oracle8client,eclipse3.1-RC3,hibernate-tools 3.0 alpha4a,JBossIDE1.5M1
> Server:Solaris 2.8, oracle 8.1.7
> Reporter: johnhua
> Priority: Minor
>
> there is a problem for mixxing setmaxresults and setlockmode.
> the problem is that "ORA-00904: invalid column name".
> The error info is as the below:
> Hibernate: select * from ( select idmapp0_.RI as col_0_0_ from PCTMNGT.IDMAP_P idmapp0_ where idmapp0_.STATUS=? ) where rownum <= ? for update of idmapp0_.RI
> 14:21:54,076 DEBUG AbstractBatcher:AbstractBatcher.java:365 - preparing statement
> 14:21:54,082 DEBUG AbstractBatcher:AbstractBatcher.java:285 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
> 14:21:54,083 DEBUG AbstractBatcher:AbstractBatcher.java:403 - closing statement
> 14:21:54,090 WARN JDBCExceptionReporter:JDBCExceptionReporter.java:71 - SQL Error: 904, SQLState: 42000
> 14:21:54,091 ERROR JDBCExceptionReporter:JDBCExceptionReporter.java:72 - ORA-00904: invalid column name
> The source code is as the below:
> //query an unused emid for new tone
> query = av_session.createQuery("select v_idmap from IdmapP as v_idmap where v_idmap.status=:v_status");
> query.setCharacter("v_status",EMID_STATUS.UNUSED.charValue());
> query.setMaxResults(1);
> query.setLockMode("v_idmap",LockMode.UPGRADE);
> it = query.iterate();
> if( !it.hasNext() )
> {
> tx.rollback();
> lv_err = "addRing::doAddRingFile: no unused EMID for new tone in idmap. toneid="+av_tone;
> throw new InterfaceErrException(lv_err,INTERFACE_RETCODE.SYSTEM_ERR);
> }
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list