Introduction

In this article, I’m going to show you how you can use the Keyset Pagination technique with Spring or Spring Boot.

While the default offset-based pagination provided by Spring Data PagingAndSortingRepository is useful in many situations, if you have a large result set that you have to iterate over, then the Keyset Pagination or Seek Method technique provides better performance.

What is Keyset Pagination

As explained in this article, Keyset Pagination or Seek Method allows us to use an index when seeking the first element of a given page that we want to load.

A Top-N Keyset Pagination query that loads the latest 25 Post entities looks as follows:

SELECT 
    id,
    title,
    created_on
FROM 
    post
ORDER BY 
    created_on DESC, 
    id DESC
FETCH FIRST 25 ROWS ONLY

And the Next-N query that loads the second, third, or nth page looks like this:

SELECT 
    id,
    title,
    created_on
FROM 
    post
WHERE
  (created_on, id) < 
  (:previousCreatedOn, :previousId)
ORDER BY 
    created_on DESC, 
    id DESC
FETCH FIRST 25 ROWS ONLY

As you can see, the Keyset Pagination queries are DB-specific, so we need a framework that can provide us with the API that abstracts this functionality while generating the proper SQL queries for each supported relational database.

That framework is called Blaze Persistence, and it supports Keyset Pagination for JPA entity queries.

How to use Keyset Pagination with Spring

When using Spring, the data access logic is implemented using Spring Data Repositories. Therefore, the basic data access methods are defined by the JpaRepository, and the custom logic can be abstracted in one or more custom Spring Data Repository classes.

The PostRepository is the Post entity Data Access Object, and it looks like this:

@Repository
public interface PostRepository 
        extends JpaRepository<Post, Long>, CustomPostRepository {
}

As explained in this article, if we want to provide extra data access methods, we can make the PostRepository extend a CustomPostRepository where we will define the custom data access logic.

The CustomPostRepository looks as follows:

public interface CustomPostRepository {

    PagedList<Post> findTopN(
        Sort sortBy, 
        int pageSize
    );

    PagedList<Post> findNextN(
        Sort orderBy, 
        PagedList<Post> previousPage
    );
}

And the CustomPostRepositoryImpl class that implements the CustomPostRepository interface looks as follows:

public class CustomPostRepositoryImpl 
        implements CustomPostRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private CriteriaBuilderFactory criteriaBuilderFactory;

    @Override
    public PagedList<Post> findTopN(
            Sort sortBy, 
            int pageSize) {
        return sortedCriteriaBuilder(sortBy)
            .page(0, pageSize)
            .withKeysetExtraction(true)
            .getResultList();
    }

    @Override
    public PagedList<Post> findNextN(
            Sort sortBy, 
            PagedList<Post> previousPage) {
        return sortedCriteriaBuilder(sortBy)
            .page(
                previousPage.getKeysetPage(),
                previousPage.getPage() * previousPage.getMaxResults(),
                previousPage.getMaxResults()
            )
            .getResultList();
    }

    private CriteriaBuilder<Post> sortedCriteriaBuilder(
            Sort sortBy) {
        CriteriaBuilder<Post> criteriaBuilder = criteriaBuilderFactory
            .create(entityManager, Post.class);
            
        sortBy.forEach(order -> {
            criteriaBuilder.orderBy(
                order.getProperty(), 
                order.isAscending()
            );
        });
        
        return criteriaBuilder;
    }
}

The ForumService uses the PostRepository KeySet Pagination methods like this:

@Service
@Transactional(readOnly = true)
public class ForumService {

    @Autowired
    private PostRepository postRepository;

    public PagedList<Post> firstLatestPosts(
            int pageSize) {
        return postRepository.findTopN(
            Sort.by(
                Post_.CREATED_ON
            ).descending().and(
                Sort.by(
                    Post_.ID
                ).descending()
            ), 
            pageSize
        );
    }

    public PagedList<Post> findNextLatestPosts(
            PagedList<Post> previousPage) {
        return postRepository.findNextN(
            Sort.by(
                Post_.CREATED_ON
            ).descending().and(
                Sort.by(
                    Post_.ID
                ).descending()
            ),
            previousPage
        );
    }
}

Testing Time

Assuming we have created 50 Post entities:

LocalDateTime timestamp = LocalDateTime.of(
    2021, 12, 30, 12, 0, 0, 0
);

LongStream.rangeClosed(1, POST_COUNT).forEach(postId -> {
    Post post = new Post()
    .setId(postId)
    .setTitle(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            postId
        )
    )
    .setCreatedOn(
        Timestamp.valueOf(timestamp.plusMinutes(postId))
    );

    entityManager.persist(post);
});

When loading the first page, we get the expected result:

PagedList<Post> topPage = forumService.firstLatestPosts(PAGE_SIZE);

assertEquals(POST_COUNT, topPage.getTotalSize());

assertEquals(POST_COUNT / PAGE_SIZE, topPage.getTotalPages());

assertEquals(1, topPage.getPage());

List<Long> topIds = topPage.stream().map(Post::getId).toList();
	
assertEquals(Long.valueOf(50), topIds.get(0));
assertEquals(Long.valueOf(49), topIds.get(1));

And, the SQL query that was executed on PostgreSQL looks as follows:

SELECT 
    p.id AS col_0_0_,
    p.created_on AS col_1_0_,
    p.id AS col_2_0_,
    (
        SELECT count(*)
        FROM post post1_
    ) AS col_3_0_,
    p.id AS id1_0_,
    p.created_on AS created_2_0_,
    p.title AS title3_0_
FROM 
    post p
ORDER BY 
    p.created_on DESC,
    p.id DESC
LIMIT 25

When loading the second page, we get the next latest 25 Post entities:

PagedList<Post> nextPage = forumService.findNextLatestPosts(topPage);

assertEquals(2, nextPage.getPage());

List<Long> nextIds = nextPage.stream().map(Post::getId).toList();

assertEquals(Long.valueOf(25), nextIds.get(0));
assertEquals(Long.valueOf(24), nextIds.get(1));

And the underlying SQL query looks as follows:

SELECT 
    p.id AS col_0_0_,
    p.created_on AS col_1_0_,
    p.id AS col_2_0_,
    (
        SELECT count(*)
        FROM post post1_
    ) AS col_3_0_,
    p.id AS id1_0_,
    p.created_on AS created_2_0_,
    p.title AS title3_0_
FROM 
    post p
WHERE 
    (p.created_on, p.id) < 
    ('2021-12-30 12:26:00.0', 26) AND 0=0
ORDER BY 
    p.created_on DESC,
    p.id DESC
LIMIT 25

Cool, right?

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Keyset Pagination is very useful when implementing an infinite scrolling solution, and while there is no built-in support for it in Spring Data, we can easily implement it ourselves using Blaze Persistence and custom Spring Data Repositories.

Insert details about how the information is going to be processed

DOWNLOAD NOW