Keyset Pagination with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to show you how you can use the SQL Keyset Pagination or Seek Method technique with JPA, Hibernate.
As explained in this article, Keyset Pagination can help you navigate large result sets without having to scan the entire database index that’s required for fetching the result set in predefined sorting order.
Keyset Pagination with JPA and Hibernate
Neither JPA nor Hibernate has built-in support for Keyset Pagination. Luckily, besides many other useful features, Blaze Persistence provides support for Keyset Pagination for both JPA and Hibernate.
The first step is to create a CriteriaBuilderFactory
using the existing JPA EntityManagerFactory
object reference.
CriteriaBuilderFactory cbf = Criteria .getDefault() .createCriteriaBuilderFactory entityManagerFactory );
Top-N Keyset Pagination query
To get the first page of a given result set, we need a TOP-N query that can be created as follows:
PagedList<Post> postPage = cbf .create(entityManager, Post.class) .orderByAsc(Post_.CREATED_ON) .orderByAsc(Post_.ID) .page(0, pageSize) .withKeysetExtraction(true) .getResultList();
The create
method of the CriteriaBuilderFactory
object allows you to build the Keyset Pagination query using a Fluent-style API.
The orderByAsc
method calls define the ORDER BY query criteria, and we can use the JPA entity Metamodel to reference the entity property names.
The page
method call instructs that we want to fetch the number of elements indicated by the pageSize
attribute starting from the first position in the matching result set.
The withKeysetExtraction
method instructs Blaze Persistence to set the KeysetPage
that we will further use in the Next-N queries to know which element becomes the Offset of the next page.
The PagedList
returned object extends the Java List
object and defines several page-related methods:
For example, using the PagedList
interface, we can get the matching record count, the current page number, and the total number of pages:
LOGGER.info("Matching entity count: {}", postPage.getTotalSize()); LOGGER.info("Page count: {}", postPage.getTotalPages()); LOGGER.info("Current page number: {}", postPage.getPage()); LOGGER.info("Post ids: {}", postPage.stream() .map(Post::getId) .toList() );
And, this is the output printed in the logs:
Matching entity count: 50 Page count: 5 Current page number: 1 Post ids: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
The reason PagedList
knows the number of matching elements and so it’s able to calculate the number of pages is that it includes an extra column that counts the matching result sets when executing the SQL query:
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 ASC NULLS LAST, p.id ASC LIMIT 10
Next-N Keyset Pagination query
To navigate to the second page, we can generate the following Next-N query on PostgreSQL:
postPage = cbf .create(entityManager, Post.class) .orderByAsc(Post_.CREATED_ON) .orderByAsc(Post_.ID) .page( postPage.getKeysetPage(), postPage.getPage() * postPage.getMaxResults(), postPage.getMaxResults() ) .getResultList();
The page
method provides the previous KeysetPage
that will be used to set the Offset information, and we instruct that we want to skip over the elements of the previous rendered page.
The above Blaze Persistence Criteria query generated the following SQL query:
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 > '2021-10-09 12:10:00.0' OR p.created_on IS NULL OR p.created_on = '2021-10-09 12:10:00.0' AND p.id > 10 ORDER BY p.created_on ASC NULLS LAST, p.id ASC LIMIT 10
The WHERE clause contains the Keyset Pagination Offset logic, which uses the last rendered element on the previous page as the Offset info.
When checking the page number and the fetched elements:
LOGGER.info("Current page number: {}", postPage.getPage()); LOGGER.info("Post ids: {}", postPage.stream() .map(Post::getId) .toList() );
We get back the expected results:
Current page number: 2 Post ids: [11, 12, 13, 14, 15, 16, 17, 18, 19, 20]
And, the Next-N query remains the same when fetching any of the remaining pages:
postPage = cbf .create(entityManager, Post.class) .orderByAsc(Post_.CREATED_ON) .orderByAsc(Post_.ID) .page( postPage.getKeysetPage(), postPage.getPage() * postPage.getMaxResults(), postPage.getMaxResults() ) .getResultList(); LOGGER.info("Current page number: {}", postPage.getPage()); LOGGER.info("Post ids: {}", postPage.stream() .map(Post::getId) .toList() );
And, when running the query for the third page, we get back the expected results:
Current page number: 3 Post ids: [21, 22, 23, 24, 25, 26, 27, 28, 29, 30]
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
The Keyset Pagination or Seek Method technique is very useful when navigating large result sets, and while JPA and Hibernate don’t have built-in support for it, Blaze Persistence addresses this limitation.
The Blaze Persistence API not only provides support for both Offset and Keyset Pagination, but you can use it to build complex SQL queries in a programmatic fashion.

The latest Spring Data JPA seems to have added Keyset-based scrolling support:
https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html#jpa.query-methods.scroll
https://github.com/spring-projects/spring-data-jpa/issues/2878
https://github.com/spring-projects/spring-data-commons/issues/2151
Thanks for the reference.