How can I do pagination with Spring Data?

Question

I have a backend app where I need to query a list of elements and I return Page instead of List our Stream. I want to know if what I am doing well the pagination(because I have an error while testing on POSTMAN). Tihis is my code divided by layers...

This is my controller

@GetMapping(value = "/findAll/{page]/{count}")
    //@PreAuthorize("hasAnyRole('NORMALUSER')")
    public ResponseEntity<Response<Page<Gasto>>> findAllByCurrentUser(HttpServletRequest request, 
            @RequestBody Gasto gasto, 
            @PathVariable int page,
            @PathVariable int count) {
        Response<Page<Gasto>> response = new Response<>();
        Page<Gasto> gastos = null;

        User userRequest = userFromRequest(request);
        gastos = gastosService.findAllByCurrentUser(userRequest.getId(), page, count);
        response.setData(gastos);
        return ResponseEntity.ok(response);
    }

This is my service class

@Override
    public Page<Gasto> findAllByCurrentUser(Long userId,int page, int count) {
        Pageable pages =  new PageRequest(page, count);
        return gastoRepository.findAllByCurrentUser(userId,pages);
    }

This is my JPA Repository

@Query(
        value = "FROM Gasto g where g.user.id = :userId order by g.id \n#pageable\n",
        countQuery = "SELECT COUNT(g) FROM Gasto g WHERE where g.user.id = :userId",
        nativeQuery = true 

        )
Page<Gasto> findAllByCurrentUser(@Param("userId") Long userId,Pageable pageable);

This is my error in POSTMAN

{
    "timestamp": 1528154349243,
    "status": 404,
    "error": "Not Found",
    "message": "No message available",
    "path": "/ExpApp/api/gastos/fin"
}

This is my error:

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:919) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2424) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) ~[hibernate-entitymanager-5.0.12.Final.jar:5.0.12.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:190) ~[spring-data-jpa-1.11.12.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:89) ~[spring-data-jpa-1.11.12.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:128) ~[spring-data-jpa-1.11.12.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:118) ~[spring-data-jpa-1.11.12.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:494) ~[spring-data-commons-1.13.12.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:477) ~[spring-data-commons-1.13.12.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56) ~[spring-data-commons-1.13.12.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
    ... 120 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Gasto g where g.user.id = 2 order by g.id 
#pageable

Christophe Douy · Answer 1 · 2018-06-05T22:10:11.317

Edit :

Your query is indeed not a valid SQL one. You're lacking the "SELECT *" in your value, and you have a double "where" in your countQuery. Should be :

@Query(
        value = "SELECT * FROM Gasto g where g.user.id = :userId order by g.id \n#pageable\n",
        countQuery = "SELECT COUNT(g) FROM Gasto g WHERE g.user.id = :userId",
        nativeQuery = true 
        )
Page<Gasto> findAllByCurrentUser(@Param("userId") Long userId,Pageable pageable);

As it's native SQL, i'm also skeptic about the "g.user.id" but I'll leave that up to your model.

I managed to reproduce this use case with a simple example on both Spring Boot 1.5.13.RELEASE and 2.0.2.RELEASE, the #pageable is mandatory only previous to Spring Boot 2, and it correctly returns a Page with the proper content .

@Query(
        value = "SELECT * FROM test t where t.id = ?1 order by t.id \n#pageable\n",
        countQuery = "SELECT COUNT(t.id) FROM test t WHERE t.id = ?1",
        nativeQuery = true
)
Page<Test> findNativeWithPageable(Long id, Pageable pageable);

and

@Entity
public class Test {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
//getters/setters
}

Spring 2.X now has a test to ensure the pageable native queries are working as expected : https://github.com/spring-projects/spring-data-jpa/commit/68efc510dfcd1c5a7df76b7c8bac4a7198471158

The doc seems to indicate that #pageable is not required, how does it behave without it for you ?

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

There's another question on SO with a few workarounds :

Spring Data and Native Query with pagination

org.springframework.dao.InvalidDataAccessResourceUsageException this is the error it gives to me — José Nobre, Jun 05 '18 at 18:23
Can you add your spring data version, and the generated queries in your original post ? The full stacktrace of the exception may also be useful. — Christophe Douy, Jun 05 '18 at 18:38
I changes my query and now I got a different kind of behavior. In POSTMAN the query is always empty — José Nobre, Jun 05 '18 at 21:54

How can I do pagination with Spring Data?

1 Answers1