Announcement Announcement Module
Collapse
No announcement yet.
Is it possible to use @Query and Pageable? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Is it possible to use @Query and Pageable?

    I have a simple query that I specify for a method in my interface that extends PagingAndSortingRepository and I'm receiving a strange error when Spring Data JPA tries to create the count query. Obviously for a simple query like this I don't need to specify the @Query annotation, but I was just trying to run a simple test to see if pagination on @Query based annotations are possible. So far it seems not, but maybe I'm doing something wrong.

    I'm using the following library versions:
    Spring Framework: 3.2.2.RELEASE
    Spring Data JPA: 1.3.0.RELEASE
    Hibernate: 4.1.9.Final

    Here is the method signature:

    Code:
    @Query("from User where lastName = :lastName")
      public Page<User> listUsers(@Param("lastName") String lastName, Pageable pageable);
    Here is the error that Hibernate is generating:

    Code:
    org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 14 [select count(where) from com.ccs.model.User where lastName = :lastName]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 14 [select count(where) from com.ccs.model.User where lastName = :lastName]
    Thanks,

    Jeff

  • #2
    I am seeing the same (well similar) error "unexpected token: where" with a similar setup. I am wondering if you ever managed to solve your problem.

    Comment


    • #3
      To avoid someone sharing my xkcd experience, I will post my findings.

      Basically you need to explicitly name the table.

      In other words

      Code:
      @Query("from User u where u.lastName = :lastName")
      instead of

      @Query("from User where lastName = :lastName")).
      My working examples:

      Code:
      public interface MyDataMartRepository extends PagingAndSortingRepository<MyDataMart, String> {
      
          @Query("select new org.example.MyDTO(code, name, " +
          		"msisdn, submissionDate) " +
                  "from MyDataMart mdm " +
                  "where (mdm.submissionDate between :startDate and :endDate) " +
                  "and mdm.code = :code " +
                  "group by mdm.code, mdm.name, mdm.msisdn")    
          Page<MyDTO> findByCode(@Param("code") String code, @Param("startDate") Date startDate, @Param("endDate") Date endDate, Pageable p);
      
          @Query("select mdm from MyDataMart mdm where (mdm.submissionDate between :startDate and :endDate) and mdm.code = :code")
          Page<MyDataMart> findByCodeAndSubmissionDateBetween(@Param("code") String code, @Param("startDate") Date startDate, @Param("endDate") Date endDate, Pageable p);
      
      }
      Note: the 2nd method could work with and without the Query annotation. I wrote the method as part of my process of elimination to see if I could get any paginated query to work. I wanted to establish if the error was with the @Query annotation or with my mapping of the repository model object (which turns out to be neither thank goodness).

      Spring I still love you

      Comment


      • #4
        Originally posted by dagmar View Post
        To avoid someone sharing my xkcd experience, I will post my findings.

        Basically you need to explicitly name the table.

        In other words

        Code:
        @Query("from User u where u.lastName = :lastName")
        instead of

        Code:
        @Query("from User where lastName = :lastName")
        That's odd. Perhaps there are some differences depending upon Hibernate version? (Which version are you using?)

        I just tested the following:

        Code:
        public interface MemberRepository 
            extends JpaRepository<Member, Long> {
            ...
            @Query("from Member where lastname IN ?1")
            List<Member> findByLastnames(List<String> lastname);
            ...
        with the following dependencies:
        • Hibernate 4.1.10 final
        • Spring 3.2.1 RELEASE
        • Spring Data JPA 1.3.2 RELEASE
        • MySQL 5.1.23

        and the query works. That's a bit of a surprise to me: I thought it was necessary to have the object alias "m", as in "from Member m where m.lastname ...", but I guess not, at least for Hibernate 4.1+.

        It would be interesting to know which version of Hibernate you are using in this particular case.

        ----
        Updated, May 25, 2013:

        I just tested the query without the alias against EclipseLink 2.4.0. It does not work. EclipseLink also seems to require that the query include a "select" clause, as in

        Code:
        @Query("select m from Member m where m.lastname IN ?1")
        Last edited by pfurbacher; May 25th, 2013, 03:09 PM. Reason: corrected MySQL driver version

        Comment


        • #5
          My dependencies are:
          * Hibernate 4.1.4 final
          * Spring 3.2.1 RELEASE
          * Spring Data JPA 1.2.0 RELEASE
          * MySQL 5.5.31
          Last edited by Oliver Gierke; May 25th, 2013, 09:56 AM.

          Comment


          • #6
            Executing a pagination query requires a second, derived query to be executed to calculate the total number of elements available. That additional query is created from the manually defined query. Strictly speaking, the ones you showed not working are invalid as JPA requires a select clause to be present (see section 4.8 of the JPA 2.0/2.1 spec). The most core reason for the derivation to fail is very likely to be the missing alias. So from your example

            Code:
            from Member where lastname IN ?1
            we'd have to derive a

            Code:
            select count(…) from Member where lastname IN ?1
            The problem here is, that we have nothing to fill the placeholder in the count(…) projection as we'd have to alter the references in the entire query. Long story short: rewrite your query in a JPA compliant way (select clause plus aliases):

            Code:
            select m from Member m where m.lastname IN ?1

            Comment

            Working...
            X