Get total count rows for Pageable custom query in Spring repository

Question

I implement pagination like this:

List<Products> products = productRepository.findAllProducts(productsRequest.getInitiatorType(), "ACTIVE",
      new PageRequest(page, 100, Sort.Direction.DESC, "insertDate"));

But how can I get Total size for this query? Only duplicate query like this?

  @Query("SELECT t FROM Products t WHERE t.isApproved = true AND t.partnerId = ?1 AND t.categories.status = ?2")
  List<OpcClProducts> findAllOpcClProducts(String senderId, String status, Pageable pageable);


  @Query("SELECT COUNT(t) FROM Products t WHERE t.isApproved = true AND t.partnerId = ?1 AND t.categories.status = ?2")
  long getTotalCount(String senderId, String status);

And call 2 query: for totalCount and for data?

And while you're at it, rename the entity to `Product`. An instance is one Product. String isn't named Strings, because each instance is one String. — JB Nizet, Jan 30 '18 at 08:06
@andrewTobilko Thank you for reminding me! I was not 100% sure about that ^^' — Noixes, Feb 02 '18 at 12:53
@ip696 have a look at the answer provided by Noixes. Does it resolve the issue? — Andrew Tobilko, Feb 02 '18 at 13:00
So there is a brain out there that thinks the same way mine does. — Paramvir Singh Karwal, Jul 06 '19 at 20:07

score 22 · Accepted Answer · edited May 09 '18 at 11:13

22

Try changing your object to

Page<Products> p ..

p should have the information you are looking for. :) (Posted here because they wanted me to) ^^'

edited May 09 '18 at 11:13

rjdkolb

10,377
11
69
89

answered Feb 02 '18 at 12:51

Noixes

1,158
12
25

1

I haven't checked it out, but I believe it's the right answer. +1 – Andrew Tobilko Feb 02 '18 at 12:58
1

page.getTotalElements() will give you number of rows returned by query page.getTotalPages() will give you number of pages with respect to page size (item per page you configured) – Hus Mukh Jul 01 '20 at 02:12
absolutely useful information that came first in my search results... – aswzen Sep 03 '20 at 05:33
Using spring boot data jdbc, if your interface extends `PagingAndSortingRepository` and the query returns more than one result, you get `org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 50` – jbaranski Dec 29 '20 at 16:44

Get total count rows for Pageable custom query in Spring repository

1 Answers1