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.

Insert details about how the information is going to be processed

DOWNLOAD NOW