Java 呼叫 SQL Server 預存程序實現分頁查詢完整範例

架構設計

採用兩個預存程序配合：

1. proc_GetPagesAndRecord：計算總頁數與總筆數。
2. proc_FenYe：動態 SQL 實現實際分頁查詢。

資料庫端：預存程序定義

-- 取得總頁數與總筆數
CREATE PROC proc_GetPagesAndRecord
    @pagesize INT,
    @n INT OUTPUT,      -- 總頁數
    @total INT OUTPUT   -- 總筆數
AS
BEGIN
    SELECT @total = COUNT(*) FROM titles;
    SELECT @n = CEILING(CAST(@total AS FLOAT) / @pagesize);
END
GO

-- 核心分頁查詢（SQL Server 2000/2005 相容寫法）
CREATE PROC proc_FenYe
    @pagesize INT,
    @currentpage INT
AS
BEGIN
    DECLARE @str VARCHAR(1000), @rowcount INT, @totalpage INT;
    SELECT @rowcount = COUNT(*) FROM titles;
    SELECT @totalpage = CEILING(CAST(@rowcount AS FLOAT) / @pagesize);

    IF @currentpage > 1
    BEGIN
        IF @currentpage > @totalpage SET @currentpage = @totalpage;
        SET @str = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR(5)) + 
                   ' * FROM titles WHERE title_id NOT IN (' +
                   'SELECT TOP ' + CAST(@pagesize * (@currentpage - 1) AS VARCHAR(5)) + 
                   ' title_id FROM titles ORDER BY title_id) ORDER BY title_id';
    END
    ELSE
    BEGIN
        SET @str = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR(5)) + 
                   ' * FROM titles ORDER BY title_id';
    END
    EXEC(@str);
END
GO

Java 端：DAO 封裝

public class Procedure_Fenye {
    private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String URL = "jdbc:sqlserver://localhost:1433;databasename=pubs";
    
    private Connection getConn() throws Exception {
        Class.forName(DRIVER);
        return DriverManager.getConnection(URL, "sa", "sa");
    }

    // 取得總頁數與總筆數
    public int[] getPage(int pageSize) {
        int[] result = {0, 0};
        try (Connection conn = getConn();
             CallableStatement cs = conn.prepareCall("{call proc_GetPagesAndRecord(?,?,?)}")) {
            cs.setInt(1, pageSize);
            cs.registerOutParameter(2, Types.INTEGER); // pages
            cs.registerOutParameter(3, Types.INTEGER); // total
            cs.execute();
            result[0] = cs.getInt(2);
            result[1] = cs.getInt(3);
        } catch (Exception e) { e.printStackTrace(); }
        return result;
    }

    // 取得指定頁資料
    public List> getRecord(int pageSize, int currentPage) {
        List> data = new ArrayList<>();
        try (Connection conn = getConn();
             CallableStatement cs = conn.prepareCall("{call proc_FenYe(?,?)}")) {
            cs.setInt(1, pageSize);
            cs.setInt(2, currentPage);
            try (ResultSet rs = cs.executeQuery()) {
                while (rs.next()) {
                    List row = new ArrayList<>();
                    row.add(rs.getString(1));
                    row.add(rs.getString(2));
                    row.add(rs.getString(3));
                    data.add(row);
                }
            }
        } catch (Exception e) { e.printStackTrace(); }
        return data;
    }

    public static void main(String[] args) {
        Procedure_Fenye dao = new Procedure_Fenye();
        int pageSize = 5;
        List> page1 = dao.getRecord(pageSize, 1);
        page1.forEach(r -> System.out.println(r.get(0) + " || " + r.get(1) + " || " + r.get(2)));
        int[] pager = dao.getPage(pageSize);
        System.out.printf("每頁 %d 筆，共 %d 頁，總計 %d 筆%n", pageSize, pager[0], pager[1]);
    }
}

效能與現代化建議

• SQL Server 2012+ 建議改用 OFFSET ... FETCH NEXT 標準語法，效能更佳且無動態 SQL 注入風險。
• 大資料量場景：考慮 Keyset Pagination（游標分頁）避免深分頁效能劣化。
• 連線池：生產環境必須使用 HikariCP / DBCP2 管理連線。
• 參數化查詢：範例已使用 CallableStatement 防注入。