Spring JDBC 分頁查詢實戰：自定義 PaginationHelper 實現物理分頁與總頁數統計

前言

Spring `JdbcTemplate` 無內建分頁支持，大數據量下全量查詢易導致 OOM。本文實現一個通用 `PaginationHelper`，採用「兩條 SQL」策略：COUNT(*) 統計總行數 → 計算總頁數 → `LIMIT/OFFSET` 取當前頁數據，兼容 MySQL、Oracle、PostgreSQL 等主流數據庫。

核心類設計

1. Page<E> 分頁結果封裝

public class Page<E> {
    private int pageNumber;       // 當前頁碼 (1-based)
    private int pagesAvailable;   // 總頁數
    private List<E> pageItems = new ArrayList<>(); // 當前頁數據

    // getter/setter 省略
}

2. PaginationHelper<E> 核心邏輯

public class PaginationHelper<E> {
    public Page<E> fetchPage(JdbcTemplate jt, 
                              String sqlCountRows,   // SELECT COUNT(*) ...
                              String sqlFetchRows,   // SELECT id, name ... ORDER BY ...
                              Object[] args,         // 參數陣列
                              int pageNo,            // 1-based
                              int pageSize,          // 每頁條數
                              ParameterizedRowMapper<E> rowMapper) {

        // 1. 統計總行數
        int rowCount = jt.queryForObject(sqlCountRows, args, Integer.class);
        
        // 2. 計算總頁數
        int pageCount = rowCount / pageSize;
        if (rowCount > pageSize * pageCount) pageCount++;

        // 3. 構建 Page 對象
        Page<E> page = new Page<>();
        page.setPageNumber(pageNo);
        page.setPagesAvailable(pageCount);

        // 4. 物理分頁查詢（使用 ResultSetExtractor 手動控制游標）
        final int startRow = (pageNo - 1) * pageSize;
        jt.query(sqlFetchRows, args, new ResultSetExtractor<Page<E>>() {
            @Override
            public Page<E> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<E> items = page.getPageItems();
                int currentRow = 0;
                while (rs.next() && currentRow < startRow + pageSize) {
                    if (currentRow >= startRow) {
                        items.add(rowMapper.mapRow(rs, currentRow));
                    }
                    currentRow++;
                }
                return page;
            }
        });
        return page;
    }
}

Service 層調用示例

public Page<Company> getCompanies(int pageNo, int pageSize) throws SQLException {
    PaginationHelper<Company> ph = new PaginationHelper<>();
    return ph.fetchPage(
        jdbcTemplate,
        "SELECT COUNT(*) FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",
        "SELECT id, name FROM company WHERE user_id = ? AND deleted = 0 ORDER BY name",
        new Object[]{userId},
        pageNo, pageSize,
        new ParameterizedRowMapper<Company>() {
            @Override
            public Company mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new Company(rs.getInt(1), rs.getString(2));
            }
        }
    );
}

數據庫方言適配建議

最佳實踐：將分頁 SQL 方言抽象為 `Dialect` 接口，`PaginationHelper` 注入具體實現，實現「寫一次 Java 代碼，多庫部署」。

效能優化提示

1. 緩存總頁數：對於低頻變更的列表（如字典表），用 Redis 緩存 `rowCount`，避免每次 `COUNT(*)` 全表掃描。
2. 索引覆蓋：`WHERE` + `ORDER BY` 列建立聯合索引，`COUNT(*)` 可走索引掃描。
3. 游標分頁：超大表（億級）改用 `WHERE id > lastId ORDER BY id LIMIT size`（Keyset Pagination），避免 `OFFSET` 深分頁性能崩塌。

總結

`PaginationHelper` 以 **兩條 SQL + ResultSetExtractor** 實現通用物理分頁，核心優勢：零侵入業務代碼、支援任意 `RowMapper`、易擴展多方言。生產環境建議結合「總數緩存 + Keyset 分頁」應對高併發大數據場景。