Spring JdbcTemplate 實現 Oracle 分頁查詢最佳實踐

背景：JdbcTemplate 缺乏內建分頁

Spring 的 JdbcTemplate 簡化了 JDBC 操作，但不同於 HibernateTemplate，它未提供內建分頁功能。queryForList(sql) 會一次性加載所有記錄，面對萬級數據性能急劇下降。本文展示基於 Oracle ROWNUM 偽列的通用分頁工具類實現。

分頁工具類設計

package com.deity.ranking.util;

import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class Pagination extends JdbcDaoSupport {
    public static final int DEFAULT_PAGE_SIZE = 10;

    private int numPerPage;      // 每頁記錄數
    private int totalRows;       // 總記錄數
    private int totalPages;      // 總頁數
    private int currentPage;     // 當前頁碼
    private int startIndex;      // 起始行號 (0-based)
    private int lastIndex;       // 結束行號 (1-based, 包含)
    private List resultList;     // 當前頁數據
    private JdbcTemplate jTemplate;

    /** 簡易構造函數：默認第 1 頁、每頁 10 條 */
    public Pagination(String sql) {
        if (jTemplate == null) throw new IllegalArgumentException("jTemplate 未初始化");
        if (sql == null || sql.isEmpty()) throw new IllegalArgumentException("SQL 不能為空");
        new Pagination(sql, 1, DEFAULT_PAGE_SIZE, jTemplate);
    }

    /** 完整構造函數 */
    public Pagination(String sql, int currentPage, int numPerPage, JdbcTemplate jTemplate) {
        if (jTemplate == null) throw new IllegalArgumentException("jTemplate 未初始化");
        if (sql == null || sql.isEmpty()) throw new IllegalArgumentException("SQL 不能為空");

        setNumPerPage(numPerPage);
        setCurrentPage(currentPage);
        setJdbcTemplate(jTemplate);

        // 1. 查總記錄數
        String countSql = "SELECT COUNT(*) FROM (" + sql + ") totalTable";
        setTotalRows(getJdbcTemplate().queryForObject(countSql, Integer.class));

        // 2. 計算衍生屬性
        setTotalPages();
        setStartIndex();
        setLastIndex();

        // 3. 構建 Oracle 分頁 SQL
        StringBuilder pageSql = new StringBuilder();
        pageSql.append("SELECT * FROM (");
        pageSql.append("  SELECT temp.*, ROWNUM rn FROM (");
        pageSql.append(sql);
        pageSql.append(") temp WHERE ROWNUM <= ").append(lastIndex);
        pageSql.append(") WHERE rn > ").append(startIndex);

        // 4. 執行查詢
        setResultList(getJdbcTemplate().queryForList(pageSql.toString()));
    }

    // ---- Getter / Setter 省略 ----

    private void setTotalPages() {
        totalPages = (totalRows + numPerPage - 1) / numPerPage;
    }
    private void setStartIndex() {
        startIndex = (currentPage - 1) * numPerPage;
    }
    private void setLastIndex() {
        if (totalRows < numPerPage) {
            lastIndex = totalRows;
        } else if (currentPage < totalPages || totalRows % numPerPage == 0) {
            lastIndex = currentPage * numPerPage;
        } else { // 最後一頁且不足一頁
            lastIndex = totalRows;
        }
    }
}

業務層調用示例

public List findSeasonRankingList(Long areaId, int rankYear, int rankMonth,
                                  Long categoryId, String characterName) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT C.USERID userid, D.POSNAME posname, C.GAMEID gameid, ")
       .append("C.AMOUNT amount, C.RANK rank FROM (")
       .append("  SELECT B.USERID, B.POSID, A.DISTRICT_CODE, A.GAMEID, ")
       .append("  A.AMOUNT, A.RANK ")
       .append("  FROM TB_FS_RANK A ")
       .append("  LEFT JOIN TB_CHARACTER_INFO B ")
       .append("    ON A.DISTRICT_CODE = B.DISTRICT_CODE ")
       .append("   AND A.GAMEID = B.GAMEID ");

    if (areaId != null && areaId > 0)
        sql.append("  AND A.DISTRICT_CODE = ").append(areaId);
    if (rankYear > 1970 && rankMonth > 0)
        sql.append("  AND A.DT = fn_time_convert(to_date('")
           .append(rankYear).append("-").append(rankMonth).append("','YYYY-MM')) ");
    if (categoryId != null && categoryId > 0)
        sql.append("  AND A.CID = ").append(categoryId);
    if (characterName != null && !characterName.trim().isEmpty())
        sql.append("  AND A.GAMEID = '").append(characterName.trim()).append("' ");

    sql.append("  ORDER BY RANK ASC) C ")
       .append("LEFT JOIN TB_FS_POSITION D ON C.POSID = D.POSID ")
       .append("ORDER BY C.RANK");

    // 第 1 頁，每頁 10 條
    Pagination page = new Pagination(sql.toString(), 1, 10, getJdbcTemplate());
    return page.getResultList();
}

關鍵點總結

• 雙層 ROWNUM 子查詢：內層限制上限，外層過濾下限，兼容 Oracle 10g+。
• 總記錄數查詢使用 COUNT(*) FROM (原查詢)，確保條件一致。
• 工具類繼承 JdbcDaoSupport，自動獲得 JdbcTemplate，無需手動傳遞。
• 參數校驗、邊界處理（最後一頁不足一頁）均在構造函數內完成，業務代碼極簡潔。

遷移建議

若使用 MySQL、PostgreSQL、SQL Server 等數據庫，只需替換構建分頁 SQL 的邏輯（LIMIT/OFFSET、ROW_NUMBER() OVER 等），其餘邏輯不變，實現跨庫分頁抽象。