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

Java 呼叫預存程序獲取自增主鍵完整實戰：JDBC CallableStatement 與 GeneratedKeys 兩大方案

編輯：Java 相關知識

前言

在資料庫操作中，插入資料後立即獲取自增主鍵 (ID) 是極高頻需求。Java 提供兩大標準方案：JDBC 3.0 Statement.RETURN_GENERATED_KEYS (通用、不依賴特定資料庫語法) 與 CallableStatement 呼叫預存程序 (封裝業務邏輯、效能優化)。本文深度解析兩種方案在 MySQL、SQL Server、Oracle、PostgreSQL 的實作差異與最佳實務。

方案一：JDBC GeneratedKeys (推薦：標準化、可移植)

核心 API

// 1. 準備 Statement 時指定 RETURN_GENERATED_KEYS
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// 或指定具體回傳欄位 (JDBC 4.0+)
PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"});

MySQL 完整範例

public class MySqlGeneratedKeys {
    public static Long insertUser(Connection conn, String name, String email) throws SQLException {
        String sql = "INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())";
        
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setString(2, email);
            int affected = ps.executeUpdate();
            
            if (affected == 0) throw new SQLException("插入失敗，無影響行數");
            
            // 獲取生成的鍵
            try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getLong(1); // 取第一欄 (通常是自增 ID)
                } else {
                    throw new SQLException("資料庫未回傳自增鍵");
                }
            }
        }
    }
}

PostgreSQL 範例 (需指定欄位名)

// PostgreSQL 建議指定回傳欄位
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql, new String[]{"id"})) {
    // ... 同前
    // getGeneratedKeys() 回傳的 ResultSet 只包含指定欄位
}

Oracle 範例 (序列 + RETURNING INTO)

// Oracle 12c+ 支援 IDENTITY 欄位，舊版需序列 + 觸發器
// JDBC 獲取方式特殊：需註冊 OUT 參數
String sql = "INSERT INTO users (id, name, email) VALUES (users_seq.NEXTVAL, ?, ?) RETURNING id INTO ?";
try (CallableStatement cs = conn.prepareCall(sql)) {
    cs.setString(1, name);
    cs.setString(2, email);
    cs.registerOutParameter(3, Types.BIGINT); // 註冊 OUT 參數
    cs.executeUpdate();
    return cs.getLong(3); // 直接取 OUT 參數值
}

方案二：CallableStatement 呼叫預存程序

MySQL 預存程序定義

DELIMITER //
CREATE PROCEDURE sp_insert_user(
    IN p_name VARCHAR(100),
    IN p_email VARCHAR(100),
    OUT p_new_id BIGINT
)
BEGIN
    INSERT INTO users (name, email, created_at) VALUES (p_name, p_email, NOW());
    SET p_new_id = LAST_INSERT_ID(); -- 獲取剛插入的自增 ID
END //
DELIMITER ;

Java 調用代碼

public class MySqlCallableStatement {
    public static Long callInsertUser(Connection conn, String name, String email) throws SQLException {
        String call = "{call sp_insert_user(?, ?, ?)}"; // JDBC Escape 語法
        
        try (CallableStatement cs = conn.prepareCall(call)) {
            cs.setString(1, name);           // IN 參數 1
            cs.setString(2, email);          // IN 參數 2
            cs.registerOutParameter(3, Types.BIGINT); // OUT 參數 3
            
            cs.execute(); // 執行預存程序
            
            return cs.getLong(3); // 獲取 OUT 參數值
        }
    }
}

SQL Server 預存程序

CREATE PROCEDURE sp_insert_user
    @name NVARCHAR(100),
    @email NVARCHAR(100),
    @new_id BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO users (name, email, created_at) VALUES (@name, @email, GETDATE());
    SET @new_id = SCOPE_IDENTITY(); -- 當前作用域最後自增值
END

// Java 調用 (相同模式)
String call = "{call sp_insert_user(?, ?, ?)}";
try (CallableStatement cs = conn.prepareCall(call)) {
    cs.setString(1, name);
    cs.setString(2, email);
    cs.registerOutParameter(3, Types.BIGINT);
    cs.execute();
    return cs.getLong(3);
}

兩大方案深度對比

維度	GeneratedKeys (PreparedStatement)	CallableStatement (預存程序)
標準化程度	JDBC 標準，跨資料庫可移植	資料庫特定語法，移植需改 SP
SQL 複雜度	單一 INSERT	可封裝多表操作、邏輯判斷、事務
效能	網路往返 1 次	網路往返 1 次，但 SP 預編譯優化
安全性	參數化防注入	參數化 + 權限控制 (只授予 EXECUTE)
除錯難度	SQL 可見、易除錯	SP 在資料庫端、需資料庫工具除錯
版本控制	SQL 在代碼中、Git 追蹤	SP 在資料庫、需遷移腳本管理
適用場景	簡單 CRUD、微服務、多資料庫支援	複雜業務邏輯、批次處理、舊系統重構

進階場景：批次插入獲取多個 ID

public class BatchInsertWithKeys {
    public static List batchInsertUsers(Connection conn, List users) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            for (User u : users) {
                ps.setString(1, u.getName());
                ps.setString(2, u.getEmail());
                ps.addBatch();
            }
            
            int[] results = ps.executeBatch();
            
            // 獲取批次生成的鍵 (MySQL 5.7+、PG 9.5+ 支援)
            try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                List ids = new ArrayList<>();
                while (generatedKeys.next()) {
                    ids.add(generatedKeys.getLong(1));
                }
                return ids;
            }
        }
    }
}

⚠️ 注意：批次獲取生成鍵的支援度因資料庫版本而異，生產環境必須實測驗證。

通用封裝：DAO 基類模板

public abstract class BaseDao {
    protected final DataSource dataSource;
    
    public BaseDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    /** 執行 INSERT 並回傳自增 ID */
    protected Long executeInsert(String sql, Object... params) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            setParameters(ps, params);
            int affected = ps.executeUpdate();
            if (affected == 0) return null;
            
            try (ResultSet keys = ps.getGeneratedKeys()) {
                return keys.next() ? keys.getLong(1) : null;
            }
        }
    }
    
    /** 執行預存程序並獲取 OUT 參數 */
    protected Long executeProcedure(String callSql, OutParameterHandler handler, Object... inParams) 
            throws SQLException {
        try (Connection conn = dataSource.getConnection();
             CallableStatement cs = conn.prepareCall(callSql)) {
            
            // 設定 IN 參數
            for (int i = 0; i < inParams.length; i++) {
                cs.setObject(i + 1, inParams[i]);
            }
            
            // 註冊 OUT 參數 (由 handler 定義)
            handler.registerOutParameters(cs);
            
            cs.execute();
            
            return handler.extractOutValue(cs);
        }
    }
    
    private void setParameters(PreparedStatement ps, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            ps.setObject(i + 1, params[i]);
        }
    }
    
    @FunctionalInterface
    protected interface OutParameterHandler {
        void registerOutParameters(CallableStatement cs) throws SQLException;
        Long extractOutValue(CallableStatement cs) throws SQLException;
    }
}

// 使用範例
public class UserDao extends BaseDao {
    public Long insertUser(String name, String email) throws SQLException {
        return executeInsert(
            "INSERT INTO users (name, email) VALUES (?, ?)", 
            name, email
        );
    }
    
    public Long insertUserViaSp(String name, String email) throws SQLException {
        return executeProcedure(
            "{call sp_insert_user(?, ?, ?)}",
            cs -> {
                cs.registerOutParameter(3, Types.BIGINT);
                return cs.getLong(3);
            },
            name, email
        );
    }
}

資料庫特定陷阱總結

資料庫	陷阱	解決方案
MySQL	`LAST_INSERT_ID()` 為連線級，併發安全	直接用 GeneratedKeys 或 SP 內 `SET @id = LAST_INSERT_ID()`
PostgreSQL	需指定回傳欄位 `new String[]{"id"}`	JDBC 4.1+ `prepareStatement(sql, columnNames)`
Oracle	無自增欄位 (12c 前)，需序列+觸發器	使用 `RETURNING id INTO ?` 或 12c+ `GENERATED AS IDENTITY`
SQL Server	`SCOPE_IDENTITY()` 而非 `@@IDENTITY`	SP 內用 `SCOPE_IDENTITY()` 避免觸發器干擾
MariaDB	兼容 MySQL 行為	同 MySQL