Pagination in Spring using @RestController, but without using Spring Data Rest Pageable?

Question

I know using Spring Data Rest I can use the in-built functionality of Pagination Like this

Page<Product> findByCategoryId(@RequestParam("id") Long id, Pageable pageable);

However, I am in project I am using Spring mvc @RestController and want to achive the same functionality I tried like this:-

Session currentSession = entityManager.unwrap(Session.class);
Query<Product> theQuery = currentSession.createQuery("from Product", Product.class);
theQuery.setFirstResult((pageNumber-1) * pageSize); // This is 0 based
theQuery.setMaxResults(pageSize);
List<Product> dataList = theQuery.getResultList();  
return dataList;

It works but I don't get the count of total number of records in the table. And for UI pagination I need that.

So do I have to hit 2 queries everytime first like above then 1 query to fetch record size. (Which can cause data sync problems if records are updated)

Or

Is there a better way to achieve this in SINGLE QUERY

You cannot do this in a single query, you need 2. 1 for the count, the other for the page elements. Also this isn't Spring Data REST but rather a basic ffeature of Spring Data, so you don't need Spring Data Rest to be using pageable (you can use it with plain Spring Data JPA as well). — M. Deinum, Apr 21 '20 at 05:43

score 1 · Accepted Answer · answered Apr 21 '20 at 05:40

1

If you need the total number of records then you must create a second query.

You could do that in one query with a subquery but then you cannot use Entities as return type.

Regarding data sync problems: If you run both queries in the same transaction there is no problem.

Btw. why do you unwrap the Hiberante Session? There is no need for that in your example.

answered Apr 21 '20 at 05:40

Simon Martinelli

34,053
5
48
82

"Btw. why do you unwrap the Hiberante Session? There is no need for that in your example." - Then how would I create a session object to create query? – Sanyam Madaan Apr 21 '20 at 06:07
entityManager.createQuery. EntityManager is the equivalent to Session in JPA. Session is Hiberante legacy – Simon Martinelli Apr 21 '20 at 06:09
Returning to my original question, so how does Pageable internally works. Does it use single or multiple query to fetch total record size. – Sanyam Madaan Apr 21 '20 at 06:12
You are not using Pageable with Spring Data JPA. If you use JPA Repository you could return Page. And Spring Data JPA creates two queries – Simon Martinelli Apr 21 '20 at 06:17
If my answer is helpful I would appreciate if you accept it. – Simon Martinelli Apr 21 '20 at 07:05

Pagination in Spring using @RestController, but without using Spring Data Rest Pageable?

1 Answers1