Search code examples
springjpanativequerypageable

Spring JPA - How to create a Pageable with a NativeQuery?

I try to do the following inside a Spring Boot application : create a native query and page it so it can returns a page of a given number of elements from a @RestController.

Here's the snippet of my code, where em is the @PersistanceContext EntityManager, and the repository method is the following, knowing that queryString is the native query :

Query searchQuery = em.createNativeQuery(this.queryString, MyEntity.class);
List<MyEntity> resultsList = searchQuery.getResultList();
return new PageImpl<>(resultsList, PageRequest.of(index,size), resultsList.size());

My problem is that the Page returned has a content of the complete query result, not a content of the size of size parameter inside the PageRequest.of.

Has anybody faced the same issue and could give a working example on how to paginate a nativeQuery please ?

Thanks for your help

Solution

  • You are mixing Spring Data JPA (Pageable) with JPA EntityManager. You can't do that. If you are already using a native query then simply put the pagination in the query. You can use what your database supports, for example the standard: 

    SELECT [a_bunch_of_columns] 
  FROM dbo.[some_table]
  ORDER BY [some_column_or_columns] 
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY;