Simple hibernate Query not scrollable

Question

I'm trying to set-up scrolling for a hibernate query. Unfortunately I keep getting a SQL exception. If I use the same query string without scrolling everything works fine.

This works fine:

String query = "from Art";
List<Art> arts = entityManager.createQuery(query).getResultList();

This fails with SQLException:

String query = "from Art";
Session session = (Session) entityManager.getDelegate();
StatelessSession statelessSession = session.getSessionFactory().openStatelessSession();
org.hibernate.Query q = statelessSession.createQuery(query);
q.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
q.setFetchSize(Integer.MIN_VALUE);
ScrollableResults sr = q.scroll(ScrollMode.FORWARD_ONLY);

Any ideas why this simple query is failing for scrolling?

Exception stack trace:

11036 07 Aug 2015 08:09:49 SqlExceptionHelper DEBUG 188575 kb - could not execute query using scroll [select art0_.phar as phar1_1_, art0_.arttyp as arttyp2_1_, art0_.bagdossier as bagdossi3_1_, art0_.bagno as bagno4_1_, art0_.bagsl as bagsl5_1_, art0_.bagslc as bagslc6_1_, art0_.bg as bg7_1_, art0_.biocid as biocid8_1_, art0_.cdbg as cdbg9_1_, art0_.cdso1 as cdso10_1_, art0_.cdso2 as cdso11_1_, art0_.ce as ce12_1_, art0_.clincd as clincd13_1_, art0_.cool as cool14_1_, art0_.del as del15_1_, art0_.depcd as depcd16_1_, art0_.depot as depot17_1_, art0_.dscrd as dscrd18_1_, art0_.dscrf as dscrf19_1_, art0_.dscrlongd as dscrlon20_1_, art0_.dscrlongf as dscrlon21_1_, art0_.dscrpackd as dscrpac22_1_, art0_.dscrpackf as dscrpac23_1_, art0_.dt as dt24_1_, art0_.esist as esist25_1_, art0_.exp as exp26_1_, art0_.ggl as ggl27_1_, art0_.grdfr as grdfr28_1_, art0_.grpcd as grpcd29_1_, art0_.hospcd as hospcd30_1_, art0_.hscd as hscd31_1_, art0_.img as img32_1_, art0_.img2 as img33_1_, art0_.inslim as inslim34_1_, art0_.light as light35_1_, art0_.limpts as limpts36_1_, art0_.loacd as loacd37_1_, art0_.mini as mini38_1_, art0_.mult as mult39_1_, art0_.nopcs as nopcs40_1_, art0_.noti as noti41_1_, art0_.outsal as outsal42_1_, art0_.pcktypd as pcktypd43_1_, art0_.pcktypf as pcktypf44_1_, art0_.PHARNUM as PHARNUM45_1_, art0_.pdat as pdat46_1_, art0_.ptyp as ptyp47_1_, art0_.PRDNO as PRDNO48_1_, art0_.prdno as prdno48_1_, art0_.qty as qty49_1_, art0_.qtyud as qtyud50_1_, art0_.qtyuf as qtyuf51_1_, art0_.RESULT_ID as RESULT_70_1_, art0_.salecd as salecd52_1_, art0_.sist as sist53_1_, art0_.depth as depth54_1_, art0_.height as height55_1_, art0_.width as width56_1_, art0_.sloplus as sloplus57_1_, art0_.smcat as smcat58_1_, art0_.smcdat as smcdat59_1_, art0_.smdat as smdat60_1_, art0_.smno as smno61_1_, art0_.sortd as sortd62_1_, art0_.sortf as sortf63_1_, art0_.sttox as sttox64_1_, art0_.syn1D as syn65_1_, art0_.syn1F as syn66_1_, art0_.temp as temp67_1_, art0_.tradeable as tradeab68_1_, art0_.vat as vat69_1_ from ART art0_]
java.sql.SQLException: Invalid argument in JDBC call
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.outOfRangeArgument(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.setFetchSize(Unknown Source)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1912)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.scroll(Loader.java:2627)
    at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:561)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:439)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performScroll(HQLQueryPlan.java:355)
    at org.hibernate.internal.StatelessSessionImpl.scroll(StatelessSessionImpl.java:763)
    at org.hibernate.internal.QueryImpl.scroll(QueryImpl.java:91)
    at com.xxx.search.importer.search.logindex.persistence.DefaultLogIndexService.openScrollableResult(DefaultLogIndexService.java:140)
    at com.xxx.search.importer.search.logindex.persistence.DefaultLogIndexService$$FastClassBySpringCGLIB$$15d41451.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:629)
    at com.xxx.search.importer.search.logindex.persistence.DefaultLogIndexService$$EnhancerBySpringCGLIB$$7e6bb957.openScrollableResult(<generated>)
    at com.xxx.search.importer.search.logindex.persistence.DefaultLogIndexServiceTest.thatScrollableResultSetWorks(DefaultLogIndexServiceTest.java:77)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.hsqldb.HsqlException: Invalid argument in JDBC call
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 47 more

Answer 1

As the stack trace shows, your JDBC driver doesn't accept Integer.MIN_VALUE as the fetch size. It's out of range:

org.hsqldb.jdbc.JDBCUtil.outOfRangeArgument(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.setFetchSize(Unknown Source)

You shouldn't need to specify that fetch size. This, AFAIK, is only necessary for MySQL, which uses this hack to prevent it from loading everything in memory. Decent databases don't need that hack.