How do I implement Hibernate Pagination using a cursor (so the results stay consistent, despite new data being added to the table being paged)?

Question

Is there any way to maintain a database cursor using Hibernate between web requests?

Basically, I'm trying to implement pagination, but the data that is being paged is consistently changing (i.e. new records are added into the database). We are trying to set it up such that when you do your initial search (returning a maximum of 5000 results), and you page through the results, those same records always appear on the same page (i.e. we're not continuously running the query each time next and previous page buttons are clicked). The way we're currently implementing this is by merely selecting 5000 (at most) primary keys from the table we're paging, storing those keys in memory, and then just using 20 primary keys at a time to fetch their details from the database. However, we want to get away from having to store these keys in memory and would much prefer a database cursor that we just keep going back to and moving backwards and forwards over the cursor to generate pages.

I tried doing this with Hibernate's ScrollableResults but found that I could not call methods like next() and previous() would cause an exception if you within a different web request / Hibernate session (no surprise there).

Is there any way to reattach a ScrollableResults object to a Session, much the same way you would reattach a detached database object to make it persistent?

Answer 1

Never use offset because offset also reads all the data before the offset, which is very inefficient.

You need to order by an indexed unique property and return the last item property's value in your API call and use a WHERE clause to start from where you left. This last item's property value will be your cursor position. For example, a simple paginated query that uses the primary key id as cursor would be like this:

List<MyEntity> entities = entityManager
    .createQuery("""
        FROM
            MyEntity e
        WHERE
            e.id > :cursorPosition
        ORDER BY
            e.id ASC
    """, MyEntity.class)
    .setParameter("cursorPosition", cursorPosition)
    .setMaxResults(pageSize)
    .getResultList()

The first call to the API, the cursorPosition value can be 0. The second one you will receive from the client the cursor that the client received from the first call. See how Google Maps paginated places query works with the nextPageToken attribute.

Your cursor has to be a string that identifies all parameters of your query. So if you have additional parameters it must be retrievable with the cursor.

I believe you can do this in multiple ways. One way is concatenating all parameters and cursorPosition in a string, encode it in a URL friendly string like Base64 and when receiving back decode it and split the string into the original parameters:

 String nextPageToken = Base64.getUrlEncoder()
     .encodeToString("indexProperty=id&cursorPos=123&ageBiggerThan=65".getBytes())

Your api call will return a json like this:

{
    "items": [ ... ],
    "nextPageToken": "aW5kZXhQcm9wZXJ0eT1pZCZjdXJzb3JQb3M9MTIzJmFnZUJpZ2dlclRoYW49NjU="
}

And the client next call:

GET https://www.example.com/api/myservice/v1/myentity?pageToken=aW5kZXhQcm9wZXJ0eT1pZCZjdXJzb3JQb3M9MTIzJmFnZUJpZ2dlclRoYW49NjU=

The part of concatenating and splitting the cursor string may be tiresome, I really don't know if there is a library that handles this work of creating the tokens and parsing it, I am actually in this question because I was looking for it. But my guess is that GSON or Jackson can save you lines of code on this.

Answer 2

Essentially you're on your own for this one. What you want to do is take a look at the OpenSessionInView filter and build your own so that instead of making a new HibernateSession per request, you pull one out of a cache that's associated with the user's web session.

If you don't have a framework like Spring WebFlow that gives you some conversation structure, you're going to need to build that too. Since you probably want some way to manage the lifecycle of that Hibernate session beyond "When the web session expires." You also most likely do not want two user threads from the same web session but different browser tabs sharing a hibernate session. (Hilarity is likely to ensue.)