Java 存儲過程實戰：CallableStatement、函數回傳值與 ResultSet 進階

為什麼選擇存儲過程？

• 效能：減少網路往返，複雜邏輯在資料庫端一次完成。
• 封裝：SQL 細節隱藏，Java 只呼叫 {call proc(?,?)}，資料庫結構變更不影響 Java 代碼。
• 工具友善：PL/SQL、T-SQL 等語法高亮、除錯器完善，優於字串拼接 SQL。
• 免費：主流資料庫 (Oracle/PostgreSQL/DB2/MySQL/SQL Server) 內建，無需昂貴應用伺服器授權。

基礎調用：CallableStatement

try (Connection con = pool.getConnection();
     CallableStatement cs = con.prepareCall("{call set_death_age(?, ?)}")) {

    cs.setString(1, "dylan thomas");
    cs.setInt(2, 39);
    cs.execute();
} catch (SQLException e) { /* 處理 */ }

• prepareCall 參數為 SQL Escape 語法：{call procedure_name(?, ...)}。
• CallableStatement 繼承 PreparedStatement，支援所有 setXxx 方法。

封裝最佳實務：Static Helper 方法

public class PoetDao {
    public static void setDeathAge(Poet poet, int age) throws SQLException {
        try (Connection con = pool.getConnection();
             CallableStatement cs = con.prepareCall("{call set_death_age(?, ?)}")) {
            cs.setString(1, poet.getName());
            cs.setInt(2, age);
            cs.execute();
        }
    }
}

優點：集中管理、易測試、可自動產生代碼。

函數回傳值：registerOutParameter

PostgreSQL pl/pgsql 函數範例：

CREATE FUNCTION snuffed_it_when(VARCHAR) RETURNS INTEGER AS $$
DECLARE poet_id INT; poet_age INT;
BEGIN
    SELECT id INTO poet_id FROM poets WHERE name = $1;
    SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id;
    RETURN poet_age;
END; $$ LANGUAGE plpgsql;

Java 調用：

try (Connection con = pool.getConnection();
     CallableStatement cs = con.prepareCall("{? = call snuffed_it_when(?)}")) {

    cs.registerOutParameter(1, Types.INTEGER); // 註冊回傳類型
    cs.setString(2, "dylan thomas");
    cs.execute();
    int age = cs.getInt(1); // 索引 1 為回傳值
}

錯誤類型會拋出 SQLException (JDBC 4.0+ 為 SQLDataException)。

進階：回傳 ResultSet (游標)

某些資料庫支援回傳 refcursor (Oracle SYS_REFCURSOR、PostgreSQL refcursor、DB2)。JDBC 標準不直接支援，但驅動程式提供擴充：

PostgreSQL 存儲過程回傳游標

CREATE PROCEDURE list_early_deaths() RETURNS refcursor AS $$
DECLARE toesup refcursor;
BEGIN
    OPEN toesup FOR
        SELECT p.name, d.age FROM poets p JOIN deaths d ON p.id = d.mort_id
        WHERE d.age < 60;
    RETURN toesup;
END; $$ LANGUAGE plpgsql;

Java 取得 ResultSet

try (Connection con = pool.getConnection();
     CallableStatement cs = con.prepareCall("{? = call list_early_deaths()}")) {

    con.setAutoCommit(false); // PostgreSQL 需交易
    cs.registerOutParameter(1, Types.OTHER); // 游標用 OTHER
    cs.execute();

    ResultSet rs = (ResultSet) cs.getObject(1); // 強轉
    while (rs.next()) {
        System.out.println(rs.getString(1) + " died at " + rs.getInt(2));
    }
    rs.close();
}

映射模式：分離資料存取與業務處理

將 ResultSet 遍歷邏輯抽象為回調介面：

public interface PoetDeathProcessor {
    void process(String name, int age);
}

public static void mapEarlyDeaths(PoetDeathProcessor processor) throws SQLException {
    try (Connection con = pool.getConnection();
         CallableStatement cs = con.prepareCall("{? = call list_early_deaths()}")) {
        con.setAutoCommit(false);
        cs.registerOutParameter(1, Types.OTHER);
        cs.execute();
        try (ResultSet rs = (ResultSet) cs.getObject(1)) {
            while (rs.next()) {
                processor.process(rs.getString(1), rs.getInt(2));
            }
        }
    }
}

// 使用：匿名類別 / Lambda (JDK 8+)
mapEarlyDeaths((name, age) -> System.out.println(name + ": " + age));
mapEarlyDeaths((name, age) -> jdbcTemplate.update("INSERT INTO log VALUES(?,?)", name, age));

優點：資料存取邏輯不變，業務處理可任意替換，符合開放封閉原則。

資料庫移植性考量

結語

存儲過程是資料庫邊界的最佳封裝。掌握 CallableStatement、OUT 參數、ResultSet 回傳 三大核心模式，配合映射回調模式，能讓 Java 應用在保持乾淨架構的同時，充分發揮資料庫運算優勢。