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

Question

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

score 4 · Accepted Answer · answered Dec 12 '19 at 08:15

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;

score 1 · Answer 2 · answered Dec 11 '19 at 21:38

1

this is example of using native query with pagination:

@Query("SELECT c FROM Customer As c INNER JOIN Offer as f  on f.id=c.specialOffer.id  inner join User As u on u.id=f.user.id where u.id=?1 And c.status=?2")  
Page<Customer> getAllCustomerToShop(Integer shopId,String status,Pageable pageable)

and then you can call it as:

getAllCustomerToShop(shopId,"status",PageRequest.of(index, PAGE_SIZE));

answered Dec 11 '19 at 21:38

Amir Serry

326
2
13

1

Thanks for your proposition but that's not what I tru to achieve. My query is built dynamically based on many inputs with a dynamic number of parameters that are bound before execution. The use of the @Query annotation is not possible. – Sooron Dec 12 '19 at 05:45
2

for starters, its JPQL and not native query – Mohit Singh Jan 24 '22 at 09:20

score 1 · Answer 3 · answered Dec 12 '19 at 08:05

1

Modify your code as follows

Query searchQuery = em.createNativeQuery(this.queryString, MyEntity.class)
                    .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
                    .setMaxResults(pageable.getPageSize());

answered Dec 12 '19 at 08:05

Nikolai Shevchenko

7,083
8
33
42

1

Be careful about the first result. Your solution won't work correctly because it will return the very next page instead of the requested one. – franta kocourek Jan 22 '21 at 13:12

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

3 Answers3