Sayfalama — OFFSET mi, Keyset mi?

1
-- Matches the ORDER BY exactly, so the database can seek straight to the cursor.
2
CREATE INDEX ix_tx_account_created_id
3
    ON account_transaction (account_id, created_at DESC, id DESC);

1
interface TransactionRepository extends JpaRepository<AccountTransaction, Long> {
2

3
    @Query("""
4
        select t from AccountTransaction t
5
        where t.accountId = :accountId
6
        order by t.createdAt desc, t.id desc
7
        """)
8
    List<AccountTransaction> firstPage(String accountId, Limit limit);
9

10
    // (createdAt, id) < (:createdAt, :id), spelled out: ties on the timestamp are broken by id.
11
    @Query("""
12
        select t from AccountTransaction t
13
        where t.accountId = :accountId
14
          and (t.createdAt < :createdAt or (t.createdAt = :createdAt and t.id < :id))
15
        order by t.createdAt desc, t.id desc
16
        """)
17
    List<AccountTransaction> pageAfter(String accountId, Instant createdAt, long id, Limit limit);
18
}

1
/** The last row a client saw, sent back opaquely to continue from there. */
2
record Cursor(Instant createdAt, long id) {
3

4
    String encode() {
5
        String raw = createdAt.toEpochMilli() + ":" + id;
6
        return Base64.getUrlEncoder().withoutPadding().encodeToString(raw.getBytes(StandardCharsets.UTF_8));
7
    }
8

9
    static Cursor decode(String token) {
10
        String[] parts = new String(Base64.getUrlDecoder().decode(token), StandardCharsets.UTF_8).split(":");
11
        return new Cursor(Instant.ofEpochMilli(Long.parseLong(parts[0])), Long.parseLong(parts[1]));
12
    }
13

14
    static Cursor of(AccountTransaction last) {
15
        return new Cursor(last.getCreatedAt(), last.getId());
16
    }
17
}

1
@RestController
2
@RequestMapping("/accounts/{accountId}/transactions")
3
class TransactionHistoryController {
4

5
    private static final int PAGE_SIZE = 20;
6
    private final TransactionRepository transactions;
7

8
    TransactionHistoryController(TransactionRepository transactions) {
9
        this.transactions = transactions;
10
    }
11

12
    @GetMapping
13
    HistoryPage page(@PathVariable String accountId, @RequestParam(required = false) String cursor) {
14
        List<AccountTransaction> rows = cursor == null
15
                ? transactions.firstPage(accountId, Limit.of(PAGE_SIZE))
16
                : pageAfter(accountId, Cursor.decode(cursor));
17
        String next = rows.size() < PAGE_SIZE ? null : Cursor.of(rows.getLast()).encode();
18
        return new HistoryPage(rows.stream().map(TransactionView::of).toList(), next);
19
    }
20

21
    private List<AccountTransaction> pageAfter(String accountId, Cursor after) {
22
        return transactions.pageAfter(accountId, after.createdAt(), after.id(), Limit.of(PAGE_SIZE));
23
    }
24
}