Querydsl vs. JPA Criteria, Part 4: Pagination

Let's move forward in our journey of discovering and comparing JPA Criteria and Querydsl approaches. This post demonstrates a pagination feature in a custom query.

This series consists of these articles:

• Introduction
• Metadata
• Upgrade to Spring Boot 3.0
• Pagination (this article) 

In This Article, You Will Learn:

• What is pagination?
• How to use the pagination with Spring Data
• Using the pagination with JPA Criteria
• Using the pagination with Querydsl

What Is Pagination?

Wikipedia uses this definition:

Pagination is used to divide returned data and display it on multiple pages within one web page. Pagination also includes the logic of preparing and displaying the links to the various pages.

In other words, an application should serve only a manageable/reasonable amount of data. There are several reasons for that:

• To spare system resources and avoid resource starvation (usually memory) either on the server or the client side.
• To provide the user with a reasonable amount of data that can be used. Can you imagine browsing thousands or millions of entries?

So, the pagination is used for slicing query data (reducing a result list) into smaller and more manageable data parts.

Pagination Basics in Spring Data JPA

Spring Data uses these classes in order to support the pagination:

• Pageable —for a pagination request (i.e., to specify the pagination request).
• Page — for a pagination response (i.e., a single page content with the desired data and page metadata).

Let's start with the Pageable class first.

Pageable

Pageable is an interface for PageRequest class allowing to specify pagination information, mainly these attributes:

• A page index (a zero-based index),
• A page size and
• Sort instance.

Usually, Spring MVC (e.g., REST service) creates a Pagination instance for us based on a request. We can also create the instance ourselves as:

• Pageable.unpaged() - when we don't care about pagination (e.g., in tests).
• PageRequest.of(0, 2, Sort.by(ASC, ID)) (see below) — to create a PageRequest instance. The of method is overridden, allowing us to create a PageRequest instance based on our needs.

Page

Page is also an interface, and the basic implementation is PageImpl class. The construction of PageImpl instance should be clear from the implementation of the Page.empty() method:

static <T> Page<T> empty(Pageable pageable) {
  return new PageImpl<>(Collections.emptyList(), pageable, 0);
}

Let's discuss it more (including examples) in the next paragraph to shed light on it.

Pagination Approaches

We can paginate data directly (to separate the page from the full data set) or the source of the data (to enhance a query to return only the data for the requested page).

Pagination of Data

When we already have data that we want to paginate with Pageable instance, then we can use a utility method like this:

public static <T> Page<T> toPage(List<T> fromCollection, @NonNull Pageable pageable) {
  try {
    List<T> resources = emptyIfNull(fromCollection).stream()
      .skip(pageable.getOffset())
      .limit(pageable.getPageSize())
      .collect(toList());
    return new PageImpl<>(resources, pageable, fromCollection.size());
  } catch (UnsupportedOperationException uoe) {
    return new PageImpl<>(fromCollection, pageable, fromCollection.size());
  }
}

Pagination of Query

Pagination of data is not effective as we need to load a lot of data to be simply thrown away / unused. Therefore, the best way is to limit query results. Of course, this is very specific for every technology. Let's focus on the query limitation with Spring Data JPA in this article.

Pagination with JPA Criteria

Let's start with JPA Criteria as it represents the traditional approach. The basic concept of a custom query is covered in my first Introduction article. 

For pagination, we need to have two queries in our findCitiesByCountry method below. The first query stands for loading a page from the data (lines 4-9), and the other one is used for counting a total count of the data (see lines 11-16).

private Page<City> findCitiesByCountry(String countryName, Pageable pageable) {
  CriteriaBuilder cb = em.getCriteriaBuilder();

  CriteriaQuery<City> query = cb.createQuery(City.class);
  Root<City> cityRoot = query.from(City.class);
  query
    .select(cityRoot)
    .where(cb.equal(cityRoot.get(country).get(Country_.name), countryName));
  List<City> pagedData = paginateQuery(em.createQuery(query), pageable).getResultList();

  CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
  Root<City> cityCountRoot = countQuery.from(City.class);
  countQuery
    .select(cb.count(cityCountRoot))
    .where(cb.equal(cityCountRoot.get(country).get(Country_.name), countryName));
  var totalCount = em.createQuery(countQuery).getSingleResult();

  return new PageImpl<>(pagedData, pageable, totalCount);
}

The query is adjusted to load only a specific slice of data by using the paginateQuery method on line 9. Here, we just use these methods:

• setFirstResult — to set the position of the first result to retrieve and 
• setMaxResults — to set the maximum number of results to retrieve.

public static <T> TypedQuery<T> paginateQuery(TypedQuery<T> query, Pageable pageable) {
  if (pageable.isPaged()) {
    query.setFirstResult((int) pageable.getOffset());
    query.setMaxResults(pageable.getPageSize());
  }
  return query;
}

In the end, we can verify our code by paginateCitiesInCountry test method as:

• Check the pagination metadata (lines 8-9) and
• Verify the expected content (lines 10-13).

@Test
void paginateCitiesInCountry() {
  var countryName = "USA";
  var pageable = PageRequest.of(0, 2, Sort.by(ASC, ID));

  var pagedResult = findCitiesByCountry(countryName, pageable);

  assertThat(pagedResult.getTotalElements()).isEqualTo(5);
  assertThat(pagedResult.getTotalPages()).isEqualTo(3);
  assertThat( pagedResult.getContent() )
    .hasSize( 2 )
    .map( City::getName )
    .containsExactly("Atlanta", "Chicago");
}

Additional information can be found here:

• JPA Pagination
• JPA Pagination Sorting

Pagination With Querydsl

Now, we can focus on the Querydsl approach. The custom query in Querydsl is covered in my first Introduction article as well.

Again, the findAllCountriesHavingCity method contains two queries. The first one is used for loading a total count (line 3), and the second one is used for loading data itself (lines 4-6). The query result is converted into a Page instance with the help of the PageableExecutionUtils.getPage method provided by Querydsl (line 7).

public Page<Country> findAllCountriesHavingCity(
  	@NonNull String cityName, @NonNull String cityState, Pageable pageable) {
  Long totalCount = findCountriesHavingCityQuery(city.country.count(), cityName, cityState).fetchOne();
  JPAQuery<Country> query = findCountriesHavingCityQuery(city.country, cityName, cityState);
  ofNullable(getQuerydsl()).ifPresent(querydsl -> querydsl.applyPagination(pageable, query));
  List<Country> pagedData = query.fetch();
  return PageableExecutionUtils.getPage(pagedData, pageable, () -> totalCount);
}

For both queries mentioned above, we use a query template defined in the findCountriesHavingCityQuery method. The benefit of this "template" is in avoiding duplicated code to have a shared definition of the query body. Every query has to specify the expression to be put to select and apply the pagination for the data query.

private <T> JPAQuery<T> findCountriesHavingCityQuery(
  	Expression<T> expression, String cityName, String cityState) {
  return new JPAQuery<Country>(em)
    .select(expression)				
    .from(city)
    .where(city.name.like(cityName)
           .and(city.state.like(cityState)));
}

As usual, we can verify our code by these tests:

• exactValues — to check the search by an exact value (lines 5-15) and
• wildcard — to check like the search by a partial value (lines 18-25).

@Nested
class FindAllCountriesHavingCity {

  @Test
  void exactValue() {
    var cityName = "San Francisco";

    var result = countryRepository.findAllCountriesHavingCity(cityName, "California", unpaged());

    assertThat(result).singleElement().satisfies(c -> {
      assertThat(c.getId()).isPositive();
      assertThat(c.getName()).isEqualTo(USA);
      assertThat(c.getCities()).map(City::getName).contains(cityName);
    });
  }

  @Test
  void wildcard() {
    var result = countryRepository.findAllCountriesHavingCity("%an%", "%i%", unpaged());

    assertThat(result).singleElement().satisfies(c -> {
      assertThat(c.getName()).isEqualTo(USA);
      assertThat(c.getCities()).map(City::getName).contains("Atlanta", "San Francisco");

    });
  }

}

You might be wondering why we use ofNullable (see line 4 in our findAllCountriesHavingCity method above). Well, we don't need it at all. It's there in order to satisfy Sonar. See the warning without it: 

Additional information can be found here.

Conclusion

This article has covered the usage of pagination in custom queries. First, the basics of pagination were explained. Next, we showed the pagination usage with JPA Criteria. In the end, we demonstrated the very same approach but with the Querydsl framework.

The complete source code presented above is available in my GitHub repository.