Pagination and sorting

Key points

• Add a Pageable parameter and Spring Data appends limit and offset. Returning Page adds a count(*).
• Return Slice when the screen only needs a next button: it fetches one extra row instead of counting the whole table.
• Sort by something unique. With ties in the sort key, two consecutive pages can show you the same row twice and skip another.
• OFFSET is not a seek. offset 100000 makes the database read and throw away 100000 rows before yours.
• Keyset paging - where id > :lastSeen order by id limit 20 - stays flat at any depth, but it cannot jump to page N.
• Never combine a collection join fetch with a Pageable. Hibernate warns, then pages in memory.

Example

public interface BookRepository extends JpaRepository<Book, Long> {

    Page<Book> findByAuthorName(String name, Pageable page);   // + select count(*)

    Slice<Book> findByArchivedFalse(Pageable page);            // no count query

    // keyset: the database seeks straight to the row after the last one seen
    @Query("select b from Book b where b.id > :lastSeen order by b.id")
    List<Book> nextPage(@Param("lastSeen") long lastSeen, Pageable limit);
}

// page 3, 20 per page, newest first - and id breaks the ties
Pageable page = PageRequest.of(2, 20, Sort.by("publishedOn").descending().and(Sort.by("id")));
Page<Book> result = books.findByAuthorName("Bloch", page);

result.getTotalElements();   // the extra count query paid for this
books.nextPage(lastId, PageRequest.ofSize(20));   // no count, no offset

Offset paging is fine for page 3 and terrible for page 3000.