1

Keyset Pagination with Spring

 2 years ago
source link: https://vladmihalcea.com/keyset-pagination-spring/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Last modified:

If you are trading Stocks and Crypto using Revolut, then you are going to love RevoGain!

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.

Keyset Pagination with Spring

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?

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK