A little hack for cursor-based pagination

When a table has many records, fetching later pages with an offset can be slow: the database has to skip all the records before that offset. Cursor-based pagination uses a record as the starting point for the next page. With a suitable index, the database can jump to that point directly.

To tell the caller whether another page exists, fetch one more record than the page size. If that extra record is present, return a cursor for the next page along with the current page's records. This example uses querydsl:

import com.querydsl.jpa.impl.JPAQueryFactory
 
data class Paginated<T>(
    val data: List<T>,
    val next: Long?
)
 
@Repository
class UserRepository(
    private val entityManager: EntityManager
) {
    private val queryFactory = JPAQueryFactory(entityManager)
 
    fun findUsers(pageSize: Int, next: Long?): Paginated<User> {
        val query = queryFactory
            .selectFrom(QUser.user)
            .orderBy(QUser.user.name.desc())
 
        next?.let {
          queryFactory
            .selectFrom(QUser.user)
            .where(QUser.user.id.eq(it))
            .fetchOne()?.let { cursor ->
                query.where(QUser.user.name.lt(cursor.name))
            }
        }
 
        // #1. fetch just 1 more record to check if there is a next page
        val users = query
            .limit((pageSize + 1).toLong())
            .fetch()
 
        // #2. check next cursor by checking the size of fetched records
        val nextCursor = if (users.size > pageSize) users[pageSize - 1].id else null
 
        return Paginated(users.take(pageSize), nextCursor?.id)
    }
}

comments