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 
  private final DataSource dataSource;
4 
  public KeysetPaginationRepository(DataSource dataSource) {
      this.dataSource = dataSource;
  }
8 
  public Page<Order> fetchNextPage(Instant afterCreatedAt, long afterId, int limit) {
      String sql = """
          SELECT id, customer_email, total_cents, created_at
          FROM orders
          WHERE (created_at, id) < (?, ?)
          ORDER BY created_at DESC, id DESC
          LIMIT ?
          """;
17 
      List<Order> orders = new ArrayList<>(limit);
19 
      try (Connection conn = dataSource.getConnection();
           PreparedStatement ps = conn.prepareStatement(sql)) {
22 
          ps.setTimestamp(1, Timestamp.from(afterCreatedAt));
          ps.setLong(2, afterId);
          ps.setInt(3, limit + 1);
26 
          try (ResultSet rs = ps.executeQuery()) {
              while (rs.next()) {
                  orders.add(new Order(
                      rs.getLong("id"),
                      rs.getString("customer_email"),
                      rs.getLong("total_cents"),
                      rs.getTimestamp("created_at").toInstant()
                  ));
              }
          }
      } catch (SQLException e) {
          throw new DataAccessException("Failed to page orders", e);
      }
40 
      boolean hasMore = orders.size() > limit;
      if (hasMore) {
          orders.remove(orders.size() - 1);
      }
45 
      Cursor next = orders.isEmpty()
          ? null
          : new Cursor(orders.get(orders.size() - 1).createdAt(), orders.get(orders.size() - 1).id());
49 
      return new Page<>(orders, hasMore ? next : null);
  }
52}

01 / 01

STEP 01

Walkthrough

Space play ←→ step click any line

Three takeaways

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.
2A composite comparison like (created_at, id) < (?, ?) breaks ties deterministically when the primary sort column isn't unique.
3Fetching limit + 1 rows is a cheap trick to know whether another page exists without a separate count query.

Related explainers

java

@Component
@Converter
public class EncryptedStringConverter implements AttributeConverter<String, String> {

Transparent column encryption in Spring & JPA

encryption aes-gcm jpa-converter

Advanced 10 steps

java

package com.acme.billing.config;
 
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.context.properties.ConfigurationProperties;

Feature-flagged beans with Spring @ConditionalOnProperty

feature-flags conditional-beans strategy-pattern

Intermediate 5 steps

java

public static Map<String, String> parseCookieHeader(String header) {
    Map<String, String> cookies = new LinkedHashMap<>();
    if (header == null || header.isBlank()) {
        return cookies;

Parsing an HTTP Cookie header in Java

string-parsing http url-decoding

Intermediate 6 steps

java

public class TimedSocketReader {
 
    private static final int READ_TIMEOUT_MS = 5_000;
    private static final int CONNECT_TIMEOUT_MS = 3_000;

Reading a socket with connect and read timeouts

sockets timeouts io

Intermediate 8 steps

java

public final class EncodingDetector {
 
    public enum Encoding {
        UTF_8, UTF_16LE, UTF_16BE, UTF_32LE, UTF_32BE, ASCII, UNKNOWN

Detecting text encoding from raw bytes in Java

byte-manipulation encoding-detection bitwise-operations

Intermediate 8 steps

java

public final class ImportOrganizer {
 
    private static final Pattern IMPORT_LINE =
            Pattern.compile("^import\\s+(static\\s+)?([\\w.]+(?:\\.\\*)?)\\s*;\\s*$");