iBATIS 整合 Oracle 分頁存儲過程實作範例

存儲過程規格

支援動態 SQL 片段、分頁參數、總筆數/總頁數輸出、SYS_REFCURSOR 回傳。

CREATE OR REPLACE PROCEDURE p_query_split (
  p_sql_script   IN  VARCHAR2,   -- 完整查詢 SQL (不含 ORDER BY)
  p_page_size    IN  NUMBER,     -- 每頁筆數
  p_page_index   IN  NUMBER,     -- 當前頁碼 (1-based)
  p_total_count  OUT NUMBER,     -- 總筆數
  p_total_page   OUT NUMBER,     -- 總頁數
  p_cursor       OUT SYS_REFCURSOR
) AS
  v_sql_cnt   VARCHAR2(4000);
  v_sql       VARCHAR2(4000);
  v_start_idx NUMBER;
  v_end_idx   NUMBER;
  v_page_size NUMBER;
  v_page_idx  NUMBER;
BEGIN
  v_page_size := NVL(p_page_size, 10);
  v_page_idx  := NVL(p_page_index, 1);

  -- 1. 統計總筆數
  v_sql_cnt := 'SELECT COUNT(*) FROM (' || p_sql_script || ')';
  EXECUTE IMMEDIATE v_sql_cnt INTO p_total_count;

  -- 2. 計算總頁數
  p_total_page := CEIL(p_total_count / v_page_size);

  -- 3. 修正頁碼
  IF v_page_idx > p_total_page THEN v_page_idx := p_total_page; END IF;
  IF v_page_idx < 1 THEN v_page_idx := 1; END IF;

  -- 4. 計算 ROWNUM 範圍
  v_start_idx := (v_page_idx - 1) * v_page_size + 1;
  v_end_idx   := v_page_idx * v_page_size;

  -- 5. 組裝分頁查詢 (使用 FIRST_ROWS 提示優化首頁回應)
  v_sql := 'SELECT /*+ FIRST_ROWS */ * FROM ( ' ||
           '  SELECT a.*, ROWNUM rn FROM ( ' || p_sql_script || ' ) a ' ||
           '  WHERE ROWNUM <= ' || v_end_idx ||
           ' ) WHERE rn >= ' || v_start_idx;

  OPEN p_cursor FOR v_sql;
END p_query_split;
/
SHOW ERRORS

iBATIS SQL Map 設定

1. parameterMap (輸入/輸出參數映射)

<parameterMap id="pageParams" class="java.util.HashMap">
  <parameter property="sqlScript" jdbcType="VARCHAR" mode="IN"/>
  <parameter property="pageSize"   jdbcType="INTEGER" mode="IN"/>
  <parameter property="pageIndex"  jdbcType="INTEGER" mode="IN"/>
  <parameter property="totalCount" jdbcType="INTEGER" mode="OUT"/>
  <parameter property="totalPage"  jdbcType="INTEGER" mode="OUT"/>
  <parameter property="cursor"     jdbcType="ORACLECURSOR" mode="OUT"/>
</parameterMap>

2. procedure 呼叫定義

<procedure id="queryPage" parameterMap="pageParams">
  { CALL p_query_split(?, ?, ?, ?, ?, ?) }
</procedure>

3. Java 呼叫範例

Map params = new HashMap<>();
params.put("sqlScript", "SELECT empno, ename, sal FROM emp WHERE deptno = 10 ORDER BY sal DESC");
params.put("pageSize", 10);
params.put("pageIndex", 2);

getSqlMapClientTemplate().queryWithRowHandler("queryPage", params, new RowHandler() {
  @Override
  public void handleRow(Object row) {
    Map r = (Map) row;
    System.out.println(r.get("EMPNO") + " " + r.get("ENAME") + " " + r.get("SAL"));
  }
});

int totalCount = (Integer) params.get("totalCount");
int totalPage  = (Integer) params.get("totalPage");
System.out.println("Total: " + totalCount + ", Pages: " + totalPage);

關鍵注意事項

• 動態 SQL 注入風險：p_sql_script 直接拼接。生產環境必須嚴格驗證或改用固定 SQL + 綁定變數。
• 游標關閉：iBATIS 會自動關閉 SYS_REFCURSOR，但若手動處理請確保 CLOSE。
• 效能：COUNT(*) 需掃描全結果集。大資料量可考慮快取總筆數、使用近似值或 Keyset 分頁。
• Oracle 12c+：可簡化為 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY，但存儲過程動態 SQL 仍需拼接。