Implementing pagination in JPA EntityManager createNativeQuery

Question

I am writing a query using JPA EntityManager createNativeQuery and I want to apply pagination. Following is the code I have implemented so far:

private static final int NO_OF_RESULTS_IN_A_PAGE = 30;

public Page<Car> getFilteredCars(int page, String model, String manufacturedYear) {
    Pageable pageable = PageRequest.of(page, NO_OF_RESULTS_IN_A_PAGE, Sort.by("addedDate").descending());
    StringBuilder sb = new StringBuilder("SELECT c.* FROM car c WHERE c.model = ").append(model).append(" AND");
    if(manufacturedYear != null) {
        sb.append(" c.manufactured_year = ").append(manufacturedYear).append(" AND");
    }
    sb.append(" c.for_sale = true ORDER BY c.added_date DESC");

    Query query = entityManager.createNativeQuery(sb.toString(), Car.class);
    query.setFirstResult(page * NO_OF_RESULTS_IN_A_PAGE);
    query.setMaxResults((page * NO_OF_RESULTS_IN_A_PAGE) + NO_OF_RESULTS_IN_A_PAGE);

    Page<Car> results = new PageImpl<>(query.getResultList(), pageable, 0);
    return results;
}

The problem is that in the new PageImpl<> constructor, I have to pass the total number of items as the third argument. And before the query runs, I don't know how many items are there. If I pass 0 (as in the code above), I will only get back the first page and the total items will be 30 even when there are actually over 1000 items in the database. There is also a simpler constructor for PageImpl taking only one argument (the query.getResultList()), but then the result would be unpaginated. This has caused me to run the query twice, once to get the total number of items returned from the query (unpaginated) and then using it to get the paginated results. Clearly there must be a better way to achieve this, which I have not been able to figure out.

Please note that my actual query is much more complex that the one depicted above involving multiple if-else conditional checks and hence not suitable to query via simple JpaRepository implementation or by using @Query with which pagination could have been easily achieved.

Why wouldn't you be able to use the normal route? Use the `JpaSpecificationExecutor` (another interface you can implement) and use specifications to dynamically build your query AND have the pagaable support. Else you need to run another query to get the number of total records, you generally do this with a count query and not the same query without pagination. — M. Deinum, Nov 19 '19 at 10:24

score 2 · Answer 1 · answered Nov 19 '19 at 09:18

I fear there is no other way than to let the DB count the total number of cars through an additional query before you perform the actual select. Like

SELECT count(1) FROM car c WHERE c.model = ...

Simple counts are much faster than selects that returns n>1 rows. Of course the counting will slow down the execution but on modern DB this will cost a few milliseconds only - something we humans will not recognize ;-)

Implementing pagination in JPA EntityManager createNativeQuery

1 Answers1