Introduction

In this article, we are going to see how we can generate Keyset Pagination queries with the Spring Data WindowIterator utility.

This is an alternative to the Blaze Persistence solution I documented in this article.

Domain Model

Considering we have the following PostComment entity that has both a createdOn and a monotonically increasing id:

@Entity
@Table(name = "post_comment")
public class PostComment {
⠀
    @Id
    private Long id;
⠀
    @ManyToOne(fetch = FetchType.LAZY)
    private Post post;
⠀
    @ManyToOne(fetch = FetchType.LAZY)
    private PostComment parent;
⠀
    private String review;
⠀
    @Enumerated(EnumType.ORDINAL)
    private Status status;
⠀
    @Column(name = "created_on")
    private LocalDateTime createdOn;
⠀
    private int votes;
}

As I explained in this article, to create the Keyset Pagination queries, we are going to need the created_on and id columns since we need to apply the pagination on the time-sorted post_comment records.

Streaming with the Spring Data WindowIterator utility

To use the Keyset Pagination with Spring Data, we need to create a Spring Data JPA method that returns a Window over the query result set, as illustrated by the following findByPost method in the PostCommentRepository:

@Repository
public interface PostCommentRepository 
        extends JpaRepository<PostComment, Long> {
⠀
    Window<PostComment> findByPost(
        Post post,
        Pageable pageable,
        ScrollPosition position
    );
}

Next, to iterate over the Window returned by the findByPost method call using the Keyset Pagination strategy, we can use the Spring Data WindowIterator utility:

WindowIterator<PostComment> commentWindowIterator = WindowIterator
    .of(position -> 
        postCommentRepository.findByPost(
            post,
            PageRequest.of(
                0,
                pageSize,
                Sort.by(
                    Sort.Order.desc(PostComment_.CREATED_ON),
                    Sort.Order.desc(PostComment_.ID)
                )
            ),
            position
        )
    )
    .startingAt(ScrollPosition.keyset());

The Spring WindowIterator and Window classes are associated as follows:

The WindowIterator allows us to choose between the standard SQL OFFSET or the Keyset Pagination strategies. The Keyset Pagination requires the position of the last element on a previous page to be used as the offset for the next page that has to be fetched, and that’s the reason we are passing the position reference to the findByPost method call.

Testing Time

Assuming we have created 30 PostComment entities:

int POST_COMMENT_COUNT = 30;

LocalDateTime timestamp = LocalDateTime.of(
    2024, 9, 26, 4, 0, 0, 0
);

long commentId = 1;

Post post = new Post()
    .setId(1L)
    .setTitle("Post nr. 1");

for (long i = 1; i <= POST_COMMENT_COUNT; i++) {
    post.addComment(
        new PostComment()
            .setId(commentId++)
            .setReview(String.format("Awesome post %d", i))
            .setStatus(PostComment.Status.PENDING)
            .setCreatedOn(timestamp.plusHours(commentId))
            .setVotes((int) (i % 7))
    );
}

When streaming over the Spring Data WindowIterator:

Post post = postRepository.getReferenceById(1L);

int pageSize = 10;

WindowIterator<PostComment> commentWindowIterator = WindowIterator.of(
    position -> postCommentRepository.findByPost(
        post,
        PageRequest.of(
            0,
            pageSize,
            Sort.by(
                Sort.Order.desc(PostComment_.CREATED_ON),
                Sort.Order.desc(PostComment_.ID)
            )
        ),
        position
    )
).startingAt(ScrollPosition.keyset());

commentWindowIterator.forEachRemaining(
    comment -> LOGGER.info(
        "Post comment {} created at {}",
        comment.getReview(),
        comment.getCreatedOn()
    )
);

Spring Data JPA generates the following log entries:

SELECT 
    pc.id, pc.created_on, pc.parent_id, 
    pc.post_id, pc.review, pc.status, pc.votes
FROM 
    post_comment pc
WHERE 
    pc.post_id = 1
ORDER BY 
    pc.created_on DESC, pc.id DESC
OFFSET 0 ROWS 
FETCH FIRST 11 ROWS ONLY

Post comment Awesome post 30 created at 2024-09-27T11:00
Post comment Awesome post 29 created at 2024-09-27T10:00
Post comment Awesome post 28 created at 2024-09-27T09:00
Post comment Awesome post 27 created at 2024-09-27T08:00
Post comment Awesome post 26 created at 2024-09-27T07:00
Post comment Awesome post 25 created at 2024-09-27T06:00
Post comment Awesome post 24 created at 2024-09-27T05:00
Post comment Awesome post 23 created at 2024-09-27T04:00
Post comment Awesome post 22 created at 2024-09-27T03:00
Post comment Awesome post 21 created at 2024-09-27T02:00

SELECT 
    pc.id, pc.created_on, pc.parent_id, 
    pc.post_id, pc.review, pc.status, pc.votes
FROM 
    post_comment pc
WHERE 
    pc.post_id = 1 AND 
    (
        pc.created_on < '2024-09-27 02:00:00.0' OR 
        pc.created_on = '2024-09-27 02:00:00.0' AND 
        pc.id < 21
    )
ORDER BY 
    pc.created_on DESC, pc.id DESC
OFFSET 0 ROWS 
FETCH FIRST 11 ROWS ONLY

Post comment Awesome post 20 created at 2024-09-27T01:00
Post comment Awesome post 19 created at 2024-09-27T00:00
Post comment Awesome post 18 created at 2024-09-26T23:00
Post comment Awesome post 17 created at 2024-09-26T22:00
Post comment Awesome post 16 created at 2024-09-26T21:00
Post comment Awesome post 15 created at 2024-09-26T20:00
Post comment Awesome post 14 created at 2024-09-26T19:00
Post comment Awesome post 13 created at 2024-09-26T18:00
Post comment Awesome post 12 created at 2024-09-26T17:00
Post comment Awesome post 11 created at 2024-09-26T16:00

SELECT 
    pc.id, pc.created_on, pc.parent_id, 
    pc.post_id, pc.review, pc.status, pc.votes
FROM 
    post_comment pc
WHERE 
    pc.post_id = 1 AND 
    (
        pc.created_on < '2024-09-26 16:00:00.0' OR 
        pc.created_on = '2024-09-26 16:00:00.0' AND 
        pc.id < 11
    )
ORDER BY 
    pc.created_on DESC, pc.id DESC
OFFSET 0 ROWS 
FETCH FIRST 11 ROWS ONLY

Post comment Awesome post 10 created at 2024-09-26T15:00
Post comment Awesome post 9 created at 2024-09-26T14:00
Post comment Awesome post 8 created at 2024-09-26T13:00
Post comment Awesome post 7 created at 2024-09-26T12:00
Post comment Awesome post 6 created at 2024-09-26T11:00
Post comment Awesome post 5 created at 2024-09-26T10:00
Post comment Awesome post 4 created at 2024-09-26T09:00
Post comment Awesome post 3 created at 2024-09-26T08:00
Post comment Awesome post 2 created at 2024-09-26T07:00
Post comment Awesome post 1 created at 2024-09-26T06:00

We can see that there are 3 queries since we have 30 records and the page size is 10.

Awesome, right?

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

Conclusion

The Spring Data WindowIterator and Window utilities provide support for fetching records using the Keyset Pagination strategy.

While we could achieve the same result with Blaze Persistence, the advantage of the Spring Data alternative is that we would spare an extra framework dependency to achieve this goal.

Insert details about how the information is going to be processed

DOWNLOAD NOW