Pagination in Spring Boot Applications

Pagination in web applications is a mechanism to separate a big result set into smaller chunks. Providing a fluent pagination navigator could increase both the value of your website for search engines and enhance user experience through minimizing the response time. The famous example of pagination is Google's search result page. Normally, the result page is separated into several pages. To avoid the bad user experience mentioned before, a lot of sites show only the current, the first, the last, and some adjacent pages.

To implement pagination in the Spring framework we can choose different alternatives. The Spring framework provides an out-of-the-box feature for pagination that needs the number of pages and the number of elements per page. This is very useful when we would implement "static pagination," which can offer a user to choose between different pages.

How can we implement dynamic pagination, which offers automatically loading the content of a page as we scroll down? In this article, I will present both static and dynamic pagination and different ways to implement such services.

Static Pagination With Spring Data

This is the most convenient way to implement pagination in a web application. It only needs to get the page and the number of result per page for a query. Instead of using Repository or CrudRepository, you should use PaginationAndSortingRepository, which accepts an object of the "Pageable" type.

The Pageable object needs the number of a page and the number of the element per page. These attributes of a pageable object will return a different result in a query. The result is a "page" that has the element from specific rows of the whole result set. The response also includes the metadata of the total element of the specific query you sent, as well as total pages. This metadata information could be useful for the frontend and to build a UI including links to previous and next pages. In this link, you can see the result of such approach of pagination.

How to Implement Static Pagination

Here, you can find the sample code of the repository and service layer, which gives back a paged result from the database. If you turn on the debugging logs, either in application.properties or in a logback file, you can see the queries that will be created by Spring Data.

@Repository
public interface SomethingRepository extends PaginationAndSortingRepository<Something, Long> {
    @Query("Select s from  Something s "
            + "join s.somethingelse as se "
            + "where se.id = :somethingelseid ")
    Page<Something> findBySomethingElseId(@Param("somethingelseid") long somethingelseid,
                                                                        Pageable pageable);

@Service
public class SomethingService {

    private SomethingRepository somethingRepository;

    @Autowired
    public SomethingService(SomethingRepository somethingRepository){
        this.somethingRepository = somethingRepository;
    }

    @Transactional(readOnly=true)
    public PageDto getSomething(long somethingElseId, int page, int size){
         Page<Something> somethings = somethingRepository.findBySomethingElseId(somethingElseId, new PageResult(page, size));

        return new PageDto(somethings.getContent()
                .stream()
                .map(SomethingDto::createDto)
                .sorted(comparing(SomethingDto::getDatum))
                .collect(toList()), somethings.getTotalElements(), somethings.getTotalPages();
    }
}

@Controller
//....

Dynamic Pagination With a Native Query

As was mentioned before, another type of pagination is dynamic pagination. This kind of pagination can be done with a native query in the Spring framework, as the following code sample shows. For this kind, you need to specify an offset row and a limit, which is the number of elements after this offset. You need also write your "page" class and map the response list to it.

How to Implement Dynamic Pagination Using a Native Query

Here, you can find the repository and service layers and your data transfer object (DTO), which will be used for mapping our result and sending it to the controller layer.

public interface CustomSomethingRepository {
    List<Something> findPagedResultBySomethingElseId(long somethingElseId, int offset, int limit);
}

public class SomethingRepositoryImpl implements CustomSomethingRepository {
    @Autowired
    private EntityManager em;


    @SuppressWarnings("unchecked")
    @Override
    public List<Something> findPagedResultBySomethingElseId(long somethingElseId, int offset, int limit) {

        String query = "select s.* from Something s "
                + "join somethingelse selse on selse.id = s.fk_somethingelse "
                + "where selse.id = :somethingElseId "
                + "order by selse.date";

        Query nativeQuery = em.createNativeQuery(query);
        nativeQuery.setParameter("somethingElseId", somethingElseId);

        //Paginering
        nativeQuery.setFirstResult(offset);
        nativeQuery.setMaxResults(limit);

        final List<Object[]> resultList = nativeQuery.getResultList();
        List<Something> somethingList = Lists.newArrayList();
        resultList.forEach(object -> somethingList.add(//map obj to something));
        return somethingList;
    }
}

Hibernate translates your query as follows:

SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( select TOP(?) t as page0_ from Something s join s.somethingelse as selse order by selse.date ) inner_query ) SELECT page0_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?

@Service
public class SomethingService {

    private SomethingRepository somethingRepository;

    @Autowired
    public SomethingService(SomethingRepository somethingRepository){
        this.somethingRepository = somethingRepository;
    }

    @Transactional(readOnly=true)
    public PageDto getSomething(long somethingElseId, int page, int size){
         List<Something> somethings = somethingRepository.findBySomethingElseId(somethingElseId, offset, limit);

        return new PagedResult<>(somethings
                .stream()
                .map(SomethingDto::createDto)
                .sorted(comparing(SomethingDto::getDatum))
                .collect(toList()), somethings.getTotalElements(), somethings.getTotalPages();
    }
}

@Controller
//....

public class PagedResult<T> {
    public static final long DEFAULT_OFFSET = 0;
    public static final int DEFAULT_MAX_NO_OF_ROWS = 100;

    private int offset;
    private int limit;
    private long totalElements;
    private List<T> elements;

    public PagedResult(List<T> elements, long totalElements, int offset, int limit) {
        this.elements = elements;
        this.totalElements = totalElements;
        this.offset = offset;
        this.limit = limit;
    }


    public boolean hasMore() {
        return totalElements > offset + limit;
    }

    public boolean hasPrevious() {
        return offset > 0 && totalElements > 0;
    }

    public long getTotalElements() {
        return totalElements;
    }

    public int  getOffset() {
        return offset;
    }

    public int getLimit() {
        return limit;
    }

    public List<T> getElements() {
        return elements;
    }
}

Pros and Cons

Pros: Fewer SQL queries will be generated, compared to using Spring Data. These complex queries cannot be written in Spring Data, and we have to specify our query as a native one, which can still be paged by using this methodology.

Cons: The "object" array must map to a Java object. It is painful and hard to maintain.

How to Implement OffsetLimit Pagination With Spring Data

As far as I know, there is no "out-of-the-box" support for what you need in default Spring Data repositories. But you can create a custom implementation of Pageable objects that will take limit/offset parameters.

Make a pageable object and pass it to PaginationAndSortingRepository:

public class OffsetLimitRequest implements Pageable {
    private int limit;
    private int offset;

    public OffsetLimitRequest(int offset, int limit){
        this.limit = limit;
        this.offset = offset;
    }
        @Override
    public int getPageNumber() {
        return 0;
    }

    @Override
    public int getPageSize() {
        return limit;
    }

    @Override
    public int getOffset() {
        return offset;
    }
    ....
}

It means there is no need to change the repository layer. The only change you would need is to make is to the service layer, as follows:

@Service
public class SomethingService {
    private SomethingRepository somethingRepository;

    @Autowired
    public SomethingService(SomethingRepository somethingRepository){
        this.somethingRepository = somethingRepository;
    }

    @Transactional(readOnly=true)
    public PageDto getSomething(long somethingElseId, int page, int size){
        Page<Something> somethings = somethingRepository.findBySomethingElseId(somethingElseId, new OffsetLimitRequest(offset, limit));
        return new PageDto(somethings.getContent()
                .stream()
                .map(SomethingDto::createDto)
                .sorted(comparing(SomethingDto::getDatum))
                .collect(toList()), somethings.getTotalElements(), somethings.getTotalPages();
    }
}

Note that you don't need to map the result manually, and it will take off a good amount of time from development.

Sort the Result

As you can see in the code snippet above, you can sort the result based on properties using the Stream interface in Java 8. You can also develop your pageable object with a "sort" object argument from the package "org.springframework.data.domain". A sort object can be initiated with a direction, which is ASC or DESC, and an iterable object of your property names of your entity. Our previous OffsetLimitRequest class changed as follows:

public class OffsetLimitRequest implements Pageable, Serializable {
    private static final long serialVersionUID = -4541509938956089562L;

    private int limit;
    private int offset;
    private Sort sort;

    public OffsetLimitRequest(int offset, int limit, Sort sort){
        this.limit = limit;
        this.offset = offset;
        this.sort = sort;
    }


    @Override
    public int getPageNumber() {
        return 0;
    }

    @Override
    public int getPageSize() {
        return limit;
    }

    @Override
    public int getOffset() {
        return offset;
    }

    @Override
    public Sort getSort() {
        return sort;
    }
    ....
}

Which can be initiated in our service layer:

@Service
public class SomethingService {
    private SomethingRepository somethingRepository;

    @Autowired
    public SomethingService(SomethingRepository somethingRepository) {
        this.somethingRepository = somethingRepository;
    }

    @Transactional(readOnly=true)
    public PageDto getSomething(long somethingElseId, int page, int size) {
        List<String> sortingOnSomethingsAttributes = Arrays.asList("attr1", "attr2");
        Page<Something> somethings = somethingRepository.findBySomethingElseId(somethingElseId, new OffsetLimitRequest(offset, limit, new Sort(Sort.Direction.fromString("ASC"), sortingOnSomethingsAttributes));
        return new PageDto(somethings.getContent()
                .stream()
                .map(SomethingDto::createDto)
                .collect(toList()), somethings.getTotalElements(), somethings.getTotalPages();
    }
}

Note that we don't need to be sorted in the Stream's return statement. When we handle big result sets, it is better to have sorting functions near the database to get better performance.

Useful Links

1. Spring Data reference

2. The Art of Pagination

3. Pagination With Spring MVC and Spring Data JPA

4. REST Pagination in Spring