Announcement Announcement Module
Collapse
No announcement yet.
Using spring-data jpa, named queries and pagination Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using spring-data jpa, named queries and pagination

    Hi,

    I am still test driving spring data. On a given entity, I am not getting the second page of data (dao returns null instead of "page").

    Code:
        @Query("select t from appointment  t where t.person.id = :person and t.start < :start")
        public Page<Termin> getHistory(@Param("person") String id, @Param("start") Date start, Pageable p);

    Code:
         Page<Appointment> page = dao.getHistory(action.getId(), new Date(), new PageRequest(
                    action.getPage(), action.getLength(), new Sort("start")));

    As I said: first page is loaded fine. Page is null afterwards.

    Any tips?

  • #2
    As I said: first page is loaded fine. Page is null afterwards.
    Could you post the complete error stack trace if you received or post an image showing such null part?

    The date type for your Table is date or datetime?

    How many results you get if you make the SQL in your console or with a visual query tool?

    Comment


    • #3
      This is driving me crazy...

      What I have tried so far:

      - downgraded from snapshots to RC1 (with core-common 1.1.0-M2)
      - tried the code without pagination
      - I can walk over all records
      - tried to page to the second data page of appointments of any user in the db

      Paging on methods provided by the interface works (like Page<Hui> findAll(Pageable thing) ). So my guess is I have done something wrong on paging on my own defined query.

      Comment


      • #4
        I think I found it:

        When paging, spring-data-jpa proxies issue a count statement against the database.
        The problem is, the count statement in my case does have

        Code:
        limit 30, 2
        at the end of the statement, and this makes mysql deliver null rows...

        Here is the log from the db:

        Code:
        select count(appointment0_.id) as col_0_0_ from appointment appointment0_ where appointment0_.user_FK='159' and appointment0_.start<'2011-07-18 21:14:40' order by appointment0_.start desc limit 30, 2
        Is this my fault or a bug?

        Comment


        • #5
          It is my fault: one can provide the countQuery as attribute in the query annotation. That should do the trick, the only problem is I still get the "limit x,y" added to my countQuery.

          Here is the changed Query annotation:
          Code:
             @Query(
                  value = "select t from Termin t where t.patient.id = :patient and t.beginn < :before order by t.beginn desc",
                  countQuery = "select count(distinct t ) from Termin t where t.patient.id = :patient and t.beginn < :before")
              public Page<Termin> getTerminListeForPatient(@Param("patient") String patientId, @Param("before") Date before, Pageable p);
          Count query for the first page:

          Code:
          /* select count(distinct t ) from Termin t where t.patient.id = :patient and t.beginn < :before */ select count(distinct termin0_.id) as col_0_0_ from termine termin0_ where termin0_.patient_FK='abc' and termin0_.beginn<'2011-07-19 01:07:47' limit 2
          Count query for the second page:

          Code:
          /* select count(distinct t ) from Termin t where t.patient.id = :patient and t.beginn < :before */ select count(distinct termin0_.id) as col_0_0_ from termine termin0_ where termin0_.patient_FK='abc' and termin0_.beginn<'2011-07-19 01:07:47' limit 30, 2
          The added limit is wrong.

          Help?
          Last edited by p.g.taboada; Jul 18th, 2011, 06:13 PM.

          Comment


          • #6
            Thanks for the detailled explaination. Seems you discovered a bug [0]. Will keep you posted on the progress.

            [0] https://jira.springsource.org/browse/DATAJPA-77

            Comment


            • #7
              Fix deployed to snapshot repository.

              Comment


              • #8
                We use pagination queries which are using @Query annotation. I am getting page 0 correctly but when I try to access any other page I am getting null pointer. Is this related to this bug (https://jira.springsource.org/browse/DATAJPA-77 )

                Thanks

                Comment


                • #9
                  What version are you using? Could you please post the exception stacktrace?

                  Comment


                  • #10
                    stacktrace

                    Hi Oliver,

                    The version was 1.0.0.RC1 and when I upgraded to 1.0.0.RELEASE the problem is gone. Here is the exception though:

                    java.lang.NullPointerException
                    com.vmware.darwin.service.application.ApplicationS erviceImpl.findFlatApplicationFiltered(Application ServiceImpl.java:90)
                    sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
                    sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
                    sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
                    java.lang.reflect.Method.invoke(Method.java:597)
                    org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:311)
                    org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:183)
                    org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :150)
                    org.springframework.security.access.intercept.aopa lliance.MethodSecurityInterceptor.invoke(MethodSec urityInterceptor.java:67)
                    org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :172)
                    org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:110)
                    org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :172)
                    org.springframework.aop.interceptor.ExposeInvocati onInterceptor.invoke(ExposeInvocationInterceptor.j ava:89)
                    org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :172)
                    org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:202)
                    $Proxy102.findFlatApplicationFiltered(Unknown Source)
                    com.vmware.darwin.web.delegate.application.Applica tionControllerDelegate.findFlatApplicationFiltered (ApplicationControllerDelegate.java:55)
                    com.vmware.darwin.web.service.application.Applicat ionController.findFlatApplicationFiltered(Applicat ionController.java:98)
                    sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
                    sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
                    sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
                    java.lang.reflect.Method.invoke(Method.java:597)
                    org.springframework.web.bind.annotation.support.Ha ndlerMethodInvoker.invokeHandlerMethod(HandlerMeth odInvoker.java:176)
                    org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.invokeHandlerMethod(An notationMethodHandlerAdapter.java:436)
                    org.springframework.web.servlet.mvc.annotation.Ann otationMethodHandlerAdapter.handle(AnnotationMetho dHandlerAdapter.java:424)
                    org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:863)
                    org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:792)
                    org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:851)
                    org.springframework.web.servlet.FrameworkServlet.d oGet(FrameworkServlet.java:756)
                    javax.servlet.http.HttpServlet.service(HttpServlet .java:617)
                    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 368)
                    org.springframework.security.web.access.intercept. FilterSecurityInterceptor.invoke(FilterSecurityInt erceptor.java:109)
                    org.springframework.security.web.access.intercept. FilterSecurityInterceptor.doFilter(FilterSecurityI nterceptor.java:83)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.access.ExceptionT ranslationFilter.doFilter(ExceptionTranslationFilt er.java:97)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.session.SessionMa nagementFilter.doFilter(SessionManagementFilter.ja va:100)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.authentication.An onymousAuthenticationFilter.doFilter(AnonymousAuth enticationFilter.java:78)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.servletapi.Securi tyContextHolderAwareRequestFilter.doFilter(Securit yContextHolderAwareRequestFilter.java:54)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.savedrequest.Requ estCacheAwareFilter.doFilter(RequestCacheAwareFilt er.java:35)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.authentication.http://www.BasicAuthenticationFilter...ilter.java:177)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.context.SecurityC ontextPersistenceFilter.doFilter(SecurityContextPe rsistenceFilter.java:79)
                    org.springframework.security.web.FilterChainProxy$ VirtualFilterChain.doFilter(FilterChainProxy.java: 380)
                    org.springframework.security.web.FilterChainProxy. doFilter(FilterChainProxy.java:169)
                    org.springframework.web.filter.DelegatingFilterPro xy.invokeDelegate(DelegatingFilterProxy.java:346)
                    org.springframework.web.filter.DelegatingFilterPro xy.doFilter(DelegatingFilterProxy.java:259)
                    org.springframework.web.filter.CharacterEncodingFi lter.doFilterInternal(CharacterEncodingFilter.java :88)
                    org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)

                    Comment

                    Working...
                    X