您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 相關知識

Java 調用資料庫預存程序完整指南：從基礎 CallableStatement 到進階 ResultSet 返回與效能優化

編輯：Java 相關知識

前言：為什麼選擇預存程序？

預存程序是儲存在資料庫端執行的程式碼單元。相較於 EJB 或 CORBA 等應用伺服器方案，預存程序具備顯著優勢：

成本效益：主流 DBMS（Oracle、PostgreSQL、DB2、SQL Server）均免費內建支援，無昂貴授權費與維運成本
效能優化：減少網路往返——將多條 SQL 封裝為單次預存程序呼叫，大幅降低延遲
SQL 原生表達：PL/SQL、pl/pgsql、Procedural SQL 等語言專為 SQL 設計，比 Java/C 更自然表達資料邏輯
架構解耦：Java 程式碼只呼叫介面，資料庫結構變更（如加欄位、拆表）無需修改 Java 端
工具生態完善：TOAD、TORA 等 IDE 提供語法高亮、除錯、版控等專業支援

基礎呼叫：CallableStatement 核心用法

JDBC 透過 CallableStatement（繼承自 PreparedStatement）支援預存程序呼叫。標準呼叫語法：{ call procedure_name(?, ?) }，問號代表參數佔位符。

範例：設定詩人逝世年齡

try {
    int age = 39;
    String poetName = "dylan thomas";
    CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }");
    proc.setString(1, poetName);
    proc.setInt(2, age);
    proc.execute();
} catch (SQLException e) {
    // 錯誤處理：參數型別錯誤、資料不存在等
}

關鍵優勢：無需 Stub 類別、無需配置檔，僅需 JDBC 驅動程式即可完成呼叫。

進階場景一：SQL 操作封裝與原子性保證

高度正規化資料庫常需多表操作（查詢 ID → 插入關聯表）。將邏輯移入預存程序可實現：

單次網路呼叫完成多步驟操作
資料庫內部執行，避免應用層事務管理複雜度
PL/SQL 等語言原生支援 SQL 語法，程式碼可讀性遠超 Java 拼接 SQL

Oracle PL/SQL 實作範例

create procedure set_death_age(poet VARCHAR2, poet_age NUMBER) as
    poet_id NUMBER;
begin
    SELECT id INTO poet_id FROM poets WHERE name = poet;
    INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age);
end set_death_age;

架構優勢：Java 端完全屏蔽資料庫結構變更。若未來在 poets 表新增 death_age 欄位優化查詢，僅需修改預存程序，Java 程式碼零變動。

Java 端模板化呼叫模式

public static void setDeathAge(Poet dyingBard, int age) throws SQLException {
    Connection con = null;
    CallableStatement proc = null;
    try {
        con = connectionPool.getConnection();
        proc = con.prepareCall("{ call set_death_age(?, ?) }");
        proc.setString(1, dyingBard.getName());
        proc.setInt(2, age);
        proc.execute();
    } finally {
        try { if (proc != null) proc.close(); } catch (SQLException ignore) {}
        if (con != null) con.close();
    }
}

建議採用 static 方法集中管理呼叫模板，甚至可透過腳本自動生成呼叫程式碼，確保可維護性。

進階場景二：函數返回值處理

預存函數可返回單一值，呼叫語法調整為 { ? = call function_name(?) }，需預先註冊輸出參數型別：

connection.setAutoCommit(false);
CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }");
proc.registerOutParameter(1, Types.INTEGER);
proc.setString(2, poetName);
proc.execute();
int age = proc.getInt(1);  // 注意：輸出參數索引從 1 開始

型別不匹配將拋出 RuntimeException，同 ResultSet 型別錯誤行為一致。

進階場景三：複雜返回值——游標（RefCursor）處理

此為預存程序替代應用伺服器的關鍵能力。部分 DBMS（Oracle、PostgreSQL、DB2）支援從預存程序返回游標引用，JDBC 驅動提供 ResultSet 包裝。

PostgreSQL pl/pgsql 返回游標範例

create procedure list_early_deaths() return refcursor as $$
declare
    toesup refcursor;
begin
    open toesup for 
        SELECT poets.name, deaths.age 
        FROM poets, deaths 
        WHERE poets.id = deaths.mort_id AND deaths.age < 60;
    return toesup;
end; $$ language 'plpgsql';

Java 端游標處理與 Mapping 模式

static void sendEarlyDeaths(PrintWriter out) {
    Connection con = null;
    CallableStatement toesUp = null;
    try {
        con = ConnectionPool.getConnection();
        con.setAutoCommit(false);  // PostgreSQL 需顯式事務
        toesUp = con.prepareCall("{ ? = call list_early_deaths() }");
        toesUp.registerOutParameter(1, Types.OTHER);  // 關鍵：使用 OTHER 型別
        toesUp.execute();
        ResultSet rs = (ResultSet) toesUp.getObject(1);  // 強制轉型
        while (rs.next()) {
            out.println(rs.getString(1) + " was " + rs.getInt(2) + " years old.");
        }
        rs.close();
    } catch (SQLException e) {
        // 資源釋放
    }
}

Mapping 模式進階：行為注入與複用

定義介面 ProcessPoetDeaths，實現 sendDeath 方法，透過匿名類別注入具體行為：

public interface ProcessPoetDeaths {
    void sendDeath(String name, int age);
}

static void mapEarlyDeaths(ProcessPoetDeaths mapper) {
    // ... 獲取 ResultSet 同前 ...
    while (rs.next()) {
        mapper.sendDeath(rs.getString(1), rs.getInt(2));
    }
}

// 使用時注入具體行為
static void sendEarlyDeaths(final PrintWriter out) {
    mapEarlyDeaths(new ProcessPoetDeaths() {
        public void sendDeath(String name, int age) {
            out.println(name + " was " + age + " years old.");
        }
    });
}

此模式將「資料獲取」與「資料處理」完全解耦，單一預存程序可支援多種下游處理邏輯（列印、報表、快取、轉檔等），極大提升程式碼複用率。