java 52 lines · 8 steps

How keyset pagination works in Java

Page through orders using a composite cursor instead of OFFSET, keeping queries fast at any depth.

Explained by highlit
Intermediate pagination sql jdbc cursor database
1public class KeysetPaginationRepository {
2 
3 private final DataSource dataSource;
4 
5 public KeysetPaginationRepository(DataSource dataSource) {
6 this.dataSource = dataSource;
7 }
8 
9 public Page<Order> fetchNextPage(Instant afterCreatedAt, long afterId, int limit) {
10 String sql = """
11 SELECT id, customer_email, total_cents, created_at
12 FROM orders
13 WHERE (created_at, id) < (?, ?)
14 ORDER BY created_at DESC, id DESC
15 LIMIT ?
16 """;
17 
18 List<Order> orders = new ArrayList<>(limit);
19 
20 try (Connection conn = dataSource.getConnection();
21 PreparedStatement ps = conn.prepareStatement(sql)) {
22 
23 ps.setTimestamp(1, Timestamp.from(afterCreatedAt));
24 ps.setLong(2, afterId);
25 ps.setInt(3, limit + 1);
26 
27 try (ResultSet rs = ps.executeQuery()) {
28 while (rs.next()) {
29 orders.add(new Order(
30 rs.getLong("id"),
31 rs.getString("customer_email"),
32 rs.getLong("total_cents"),
33 rs.getTimestamp("created_at").toInstant()
34 ));
35 }
36 }
37 } catch (SQLException e) {
38 throw new DataAccessException("Failed to page orders", e);
39 }
40 
41 boolean hasMore = orders.size() > limit;
42 if (hasMore) {
43 orders.remove(orders.size() - 1);
44 }
45 
46 Cursor next = orders.isEmpty()
47 ? null
48 : new Cursor(orders.get(orders.size() - 1).createdAt(), orders.get(orders.size() - 1).id());
49 
50 return new Page<>(orders, hasMore ? next : null);
51 }
52}
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Keyset pagination filters by the last row's sort key rather than skipping rows, so query cost stays constant no matter how deep you page.
  2. 2A composite comparison like (created_at, id) < (?, ?) breaks ties deterministically when the primary sort column isn't unique.
  3. 3Fetching limit + 1 rows is a cheap trick to know whether another page exists without a separate count query.

Related explainers

java 
public class SlidingLogRateLimiter {
 
    private final int maxRequests;
    private final long windowMillis;

How a sliding-log rate limiter works

rate-limiting concurrency sliding-window
Advanced 8 steps
java 
public final class Slugifier {
 
    private static final Pattern NON_LATIN = Pattern.compile("[^\\w-]");
    private static final Pattern WHITESPACE = Pattern.compile("[\\s]+");

Building a URL slugifier in Java

regex unicode-normalization string-processing
Intermediate 8 steps
java 
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
 
    @Query("""

Keyset pagination with Spring Data JPA

pagination cursor jpa
Advanced 8 steps
java 
public record FixedWidthField(String name, int start, int end) {
 
    public String extract(String line) {
        int from = Math.min(start, line.length());

Parsing fixed-width text in Java

records parsing streams
Intermediate 7 steps
java 
@RestController
@RequestMapping("/api/quotes")
public class QuoteStreamController {
 

Streaming market quotes with Spring WebFlux

reactive-streams backpressure server-sent-events
Advanced 9 steps
java 
@Controller
public class BookController {
 
    private final BookService bookService;

Solving GraphQL N+1 with batch mapping in Spring

graphql n+1-problem batching
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

How keyset pagination works in Java — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code