4 Replies Latest reply: Feb 27, 2012 12:22 AM by 740591 RSS

    JPA Paging does not work

    740591
      Hello,

      I use paging in my application
      public List<Info> loadInfo(int firstResult, int maxResult){
                List<Info> info = null;
                try {
                             info = em.createQuery("FROM STUDENT").setMaxResults(maxResult).setFirstResult(firstResult).getResultList();
                } catch (Exception ex) {
                     log.error(ex.getMessage(), ex);
                }
                return info;
           }
      so
      loadInfo(0,5)
      works fine but
      loadInfo(5,5)
      throws exception
      18:43:20,353 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8080-5) SQL Error: 0, SQLState: null
      18:43:20,353 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8080-5) The requested operation is not supported on forward only result sets.
      18:43:20,355 INFO  [org.hibernate.engine.jdbc.internal.LogicalConnectionImpl] (http--0.0.0.0-8080-5) HHH000106: Forcing container resource cleanup on transaction complet
      ion
      18:43:20,358 ERROR [net.fina.server.returndefinition.impl.ReturnDefinitionSession] (http--0.0.0.0-8080-5) org.hibernate.exception.GenericJDBCException: The requested ope
      ration is not supported on forward only result sets.: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: The requested operation is no
      t supported on forward only result sets.
              at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1356) [hibernate-entitymanager-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1284) [hibernate-entitymanager-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:261) [hibernate-entitymanager-4.0.0.Final.jar:4.0.0.Final]
              at net.fina.server.returndefinition.impl.ReturnDefinitionSession.loadDefinitions(ReturnDefinitionSession.java:36) [fina-server-1.0.0.jar:]
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_27]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_27]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_27]
              at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_27]
              at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactor
      y.java:72) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:37) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:45) [jboss-as-jpa-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.0.CR1.jar:7.1
      .0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInNoTx(CMTTxInterceptor.java:211) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.as.ejb3.tx.CMTTxInterceptor.notSupported(CMTTxInterceptor.java:288) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:188) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [jboss-as-ejb3-7.1
      .0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.security.AuthorizationInterceptor.processInvocation(AuthorizationInterceptor.java:106) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:76) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.TCCLInterceptor.processInvocation(TCCLInterceptor.java:45) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:57) [jboss-as-ejb3-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:165) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.as.ee.component.ViewDescription$1.processInvocation(ViewDescription.java:173) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
              at org.jboss.as.ee.component.ProxyInvocationHandler.invoke(ProxyInvocationHandler.java:72) [jboss-as-ee-7.1.0.CR1.jar:7.1.0.CR1
              at net.fina.web.server.services.info.InfoServiceImpl.loadDefinitions(ReturnDefinitionServiceImpl.java:47)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_27]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_27]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_27]
              at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_27]
              at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:569) [gwt-servlet-2.4.0.jar:]
              at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208) [gwt-servlet-2.4.0.jar:]
              at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248) [gwt-servlet-2.4.0.jar:]
              at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62) [gwt-servlet-2.4.0.jar:]
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:754) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
              at org.jboss.weld.servlet.ConversationPropagationFilter.doFilter(ConversationPropagationFilter.java:62) [weld-core-1.1.4.Final.jar:2011-11-22 20:01]
              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:280)
              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)
              at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
              at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)
              at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:489)
              at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.1.0.CR1.jar:7.1.0.CR1]
              at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:151)
              at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)
              at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
              at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
              at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362)
              at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:897)
              at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:626)
              at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:2033)
              at java.lang.Thread.run(Thread.java:662) [:1.6.0_27]
      Caused by: org.hibernate.exception.GenericJDBCException: The requested operation is not supported on forward only result sets.
              at org.hibernate.exception.internal.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:148) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.exception.internal.SQLStateConverter.convert(SQLStateConverter.java:136) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:108) [hibernate-core-4.0.0.Final.
      jar:4.0.0.Final]
              at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at $Proxy136.absolute(Unknown Source)   at org.hibernate.loader.Loader.advance(Loader.java:1651) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.getResultSet(Loader.java:1982) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.doQuery(Loader.java:829) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.doList(Loader.java:2463) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.doList(Loader.java:2449) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.Loader.list(Loader.java:2274) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) [hibernate-core-4.0.0.Final.jar:4.0.0.Final]
              at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:252) [hibernate-entitymanager-4.0.0.Final.jar:4.0.0.Final]
              ... 71 more
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The requested operation is not supported on forward only result sets.
              at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)
              at com.microsoft.sqlserver.jdbc.SQLServerResultSet.throwNotScrollable(SQLServerResultSet.java:375)
              at com.microsoft.sqlserver.jdbc.SQLServerResultSet.verifyResultSetIsScrollable(SQLServerResultSet.java:398)
              at com.microsoft.sqlserver.jdbc.SQLServerResultSet.absolute(SQLServerResultSet.java:1473)
              at org.jboss.jca.adapters.jdbc.WrappedResultSet.absolute(WrappedResultSet.java:131)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [:1.6.0_27]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [:1.6.0_27]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [:1.6.0_27]
              at java.lang.reflect.Method.invoke(Method.java:597) [:1.6.0_27]
              at org.hibernate.engine.jdbc.internal.proxy.AbstractResultSetProxyHandler.continueInvocation(AbstractResultSetProxyHandler.java:104) [hibernate-core-4.0.0.Final.
      jar:4.0.0.Final]
              ... 87 more
      I use SQL Server 2008 Express and evet set parameter in persistence.xml
                              <property name="hibernate.jdbc.use_scrollable_resultset" value="true"/>
      But cant understand what is wrong? Did i miss something?
        • 1. Re: JPA Paging does not work
          gimbal2
          Quote from the documentation:

          "Enables use of JDBC2 scrollable resultsets by Hibernate. This property is only necessary when using user supplied JDBC connections, Hibernate uses connection metadata otherwise."

          "Connection metadata" in this case I interpret as "we figure it out ourselves"; this article makes me thing that:

          http://www.javalobby.org/java/forums/t63849.html

          The conclusion that I draw from all this is that if you set the proper Hibernate dialect, Hibernate SHOULD be using scrollable resultsets on its own to make pagination work. So did you configure the wrong dialect, or is it perhaps time to visit a hibernate forum to ask around if others have run into this issue?


          Side note: not related to this problem, but in my experience pagination doesn't work reliably unless you have some sort of order by clause in there. Otherwise the DBMS can return results in any arbitrary ordering, which can be different each "page" you fetch.
          • 2. Re: JPA Paging does not work
            740591
            I have added following:
            info = em.createQuery("FROM STUDENT s ORDER BY s.code ASC");
            and my dialect is following:
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />
            now it works fine , thanks
            • 3. Re: JPA Paging does not work
              gimbal2
              dato.java wrote:
              I have added following:
              info = em.createQuery("FROM STUDENT s ORDER BY s.code ASC");
              and my dialect is following:
              <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />
              now it works fine , thanks
              Yeah but was it the order by that changed things, or did you change the dialect? Might be interesting info for people that stumble on to this thread through Google.
              • 4. Re: JPA Paging does not work
                740591
                The main reason was dialect not query itself