Java 呼叫 SQL Server 預存程序完整實戰：JDBC CallableStatement 參數綁定與結果集處理

企業級應用常需呼叫 SQL Server 預存程序封裝複雜業務邏輯。本文基於 JDBC CallableStatement 完整示範輸入/輸出/輸入輸出參數綁定、結果集游標處理、返回值獲取、異常轉換與事務邊界控制，並給出生產環境連接池配置建議。

預存程序範例 (T-SQL)

CREATE PROCEDURE usp_TransferFunds
    @FromAccount VARCHAR(20),
    @ToAccount   VARCHAR(20),
    @Amount      DECIMAL(18,2),
    @ResultCode  INT OUTPUT,           -- 0 成功，非 0 失敗代碼
    @ResultMsg   NVARCHAR(200) OUTPUT  -- 詳細訊息
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE Accounts SET Balance -= @Amount WHERE AccountNo = @FromAccount;
            UPDATE Accounts SET Balance += @Amount WHERE AccountNo = @ToAccount;
            INSERT INTO TransferLog (FromAcct, ToAcct, Amount, CreateTime)
            VALUES (@FromAccount, @ToAccount, @Amount, GETDATE());
        COMMIT TRANSACTION;
        SET @ResultCode = 0;
        SET @ResultMsg = N'轉帳成功';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ResultCode = ERROR_NUMBER();
        SET @ResultMsg = ERROR_MESSAGE();
    END CATCH
END

JDBC 呼叫完整代碼

public class StoredProcDemo {
    // 連接池建議使用 HikariCP，配置見文末
    private static final String SQL = "{call usp_TransferFunds(?, ?, ?, ?, ?)}";

    public static TransferResult transfer(String from, String to, BigDecimal amount) {
        try (Connection conn = dataSource.getConnection();
             CallableStatement cs = conn.prepareCall(SQL)) {

            // 1. 綁定輸入參數
            cs.setString(1, from);
            cs.setString(2, to);
            cs.setBigDecimal(3, amount);

            // 2. 註冊輸出參數 (對應 JDBC 型別)
            cs.registerOutParameter(4, Types.INTEGER);      // @ResultCode
            cs.registerOutParameter(5, Types.NVARCHAR);     // @ResultMsg

            // 3. 執行
            boolean hasResultSet = cs.execute();

            // 4. 讀取輸出參數 (需在結果集處理後)
            int code = cs.getInt(4);
            String msg = cs.getString(5);

            // 5. 處理多結果集 (若預存程序有 SELECT)
            if (hasResultSet) {
                try (ResultSet rs = cs.getResultSet()) {
                    while (rs.next()) {
                        // 處理游標資料
                    }
                }
                // 可能有多個結果集
                while (cs.getMoreResults()) {
                    try (ResultSet rs = cs.getResultSet()) { /* ... */ }
                }
            }

            // 6. 獲取返回值 (若有 RETURN 陳述式)
            // int returnVal = cs.getInt(1); // 參數索引 1 為返回值

            return new TransferResult(code == 0, code, msg);

        } catch (SQLException e) {
            // 轉換為業務異常，保留 SQLState、ErrorCode
            throw new BizException("SP_EXEC_FAILED", e.getSQLState(), e.getErrorCode(), e);
        }
    }

    record TransferResult(boolean success, int code, String message) {}
}

關鍵 API 對照表

進階主題

1. 事務邊界控制

• 預存程序內自管事務 (如範例) → JDBC 連接設 autoCommit=true (預設)
• Java 端控制事務 → conn.setAutoCommit(false)，執行完 conn.commit()，異常 conn.rollback()
• **避免雙重事務嵌套**，否則可能導致死鎖或語義混亂

2. 連接池優化配置 (HikariCP + SQL Server)

spring.datasource.hikari:
  maximum-pool-size: 20
  minimum-idle: 5
  connection-timeout: 30000
  idle-timeout: 600000
  max-lifetime: 1800000
  connection-test-query: SELECT 1
  data-source-properties:
    sendStringParametersAsUnicode: true   # 避免 NVARCHAR 參數隱式轉換導致索引失效
    packetSize: 8192                      # 大批量操作調大封包
    applicationName: MyApp_Transfer       # 便於 SQL Server 端監控識別

3. 異常處理最佳實踐

• 捕獲 SQLException 後，務必記錄 getSQLState() (標準代碼) 與 getErrorCode() (廠商代碼)
• SQL Server 常見錯誤碼：2627 (PK 衝突)、547 (FK 衝突)、1205 (死鎖受害者)、2601 (唯一索引衝突)
• 將其轉換為業務異常碼 (如 DUPLICATE_KEY, DEADLOCK)，上層統一處理

4. 測試策略

• 單元測試：Mock CallableStatement 驗證參數綁定順序與型別
• 整合測試：Testcontainers 啟動真實 SQL Server 容器，驗證端到端流程
• 效能基準：JMeter 壓測預存程序吞吐量，對比動態 SQL 差異

避坑清單

• ❌ 忘記 registerOutParameter → SQLException: The parameter is not an output parameter
• ❌ 輸出參數型別註冊錯誤 (如 NVARCHAR 註冊為 VARCHAR) → 資料截斷或亂碼
• ❌ 執行 execute() 後直接讀取 OUT 參數未處理結果集 → 部分驅動要求先消費結果集
• ❌ 連接池未設 sendStringParametersAsUnicode=true → 參數隱式轉換導致索引掃描
• ✅ 使用 try-with-resources 確保 Connection/CallableStatement/ResultSet 關閉

結語

預存程序將業務邏輯下沉資料庫層，配合 JDBC CallableStatement 標準化呼叫，能有效減少網路往返、利用資料庫端執行計劃快取。掌握參數綁定順序、型別映射、結果集游標處理與事務邊界，是 Java 後端工程師處理複雜寫入場景的必備技能。