Java 呼叫 SQL Server 預存程序完整指南 (JDBC CallableStatement)

前言

在 Java 應用中透過 JDBC 呼叫 SQL Server 預存程序，核心類別為 CallableStatement。本文對比 Oracle 差異，提供查詢型、異動型 (INSERT/UPDATE/DELETE) 完整範例，涵蓋驅動載入、連線建立、參數綁定、結果集處理、資源釋放等完整流程。

一、環境與驅動

• JDBC 驅動：mssql-jdbc-x.x.x.jre8.jar (Microsoft 官方，支援 JDBC 4.2+)
• Maven 依賴：

  <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>12.4.2.jre11</version>
  </dependency>

• 驅動類別：com.microsoft.sqlserver.jdbc.SQLServerDriver
• 連線 URL 格式：jdbc:sqlserver://host:1433;databaseName=DB;encrypt=true;trustServerCertificate=true

二、標準呼叫流程 (6 步驟)

1. 載入驅動：Class.forName(driverClass)
2. 建立連線：DriverManager.getConnection(url, user, pwd)
3. 建立 CallableStatement：conn.prepareCall("{call ProcName(?, ?)}")
4. 綁定參數：cs.setInt(1, id); cs.setString(2, name); cs.registerOutParameter(3, Types.INTEGER)
5. 執行：cs.execute() 或 cs.executeQuery() / cs.executeUpdate()
6. 處理結果 / 取得輸出參數：ResultSet rs = cs.getResultSet(); int out = cs.getInt(3);
7. 關閉資源 (逆序)：cs.close(); conn.close();

三、範例 1：查詢型預存程序 (回傳 ResultSet)

-- SQL Server
CREATE PROCEDURE dbo.SelBook
AS
SELECT BookID, BookName, Author FROM dbo.Books;

// Java
public void queryBooks() {
    String url = "jdbc:sqlserver://localhost:1433;databaseName=Library;encrypt=true;trustServerCertificate=true";
    String user = "app_user", pwd = "P@ssw0rd";

    try (Connection conn = DriverManager.getConnection(url, user, pwd);
         CallableStatement cs = conn.prepareCall("{call dbo.SelBook}"); // 無參數
         ResultSet rs = cs.executeQuery()) {

        while (rs.next()) {
            int id = rs.getInt("BookID");
            String name = rs.getString("BookName");
            String author = rs.getString("Author");
            System.out.printf("%d\t%s\t%s%n", id, name, author);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

四、範例 2：異動型預存程序 (輸入參數 + 輸出參數)

-- SQL Server
CREATE PROCEDURE dbo.AddBook
    @BookID   INT,
    @BookName VARCHAR(100),
    @Author   VARCHAR(100),
    @Result   INT OUTPUT  -- 0:成功, -1:重複
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM Books WHERE BookName = @BookName)
    BEGIN
        SET @Result = -1; RETURN;
    END
    INSERT INTO Books (BookID, BookName, Author) VALUES (@BookID, @BookName, @Author);
    SET @Result = 0;
END;

// Java
public int addBook(int id, String name, String author) {
    String sql = "{call dbo.AddBook(?, ?, ?, ?)}";
    try (Connection conn = DriverManager.getConnection(url, user, pwd);
         CallableStatement cs = conn.prepareCall(sql)) {

        cs.setInt(1, id);
        cs.setString(2, name);
        cs.setString(3, author);
        cs.registerOutParameter(4, Types.INTEGER);  // 註冊輸出參數

        cs.execute();  // 異動型用 execute()
        return cs.getInt(4);  // 取得輸出參數值
    } catch (SQLException e) {
        e.printStackTrace();
        return -99;
    }
}

五、進階主題

1. 多結果集處理

若預存程序包含多個 SELECT，需依序呼叫 getMoreResults() 切換：

boolean hasResults = cs.execute();
int resultSetIndex = 0;
while (hasResults || cs.getUpdateCount() != -1) {
    if (hasResults) {
        ResultSet rs = cs.getResultSet();
        System.out.println("ResultSet #" + (++resultSetIndex));
        while (rs.next()) { /* 處理 */ }
        rs.close();
    }
    hasResults = cs.getMoreResults();
}

2. 交易控制

conn.setAutoCommit(false);
try {
    // 多個 CallableStatement 操作
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);
}

3. 連線池最佳實踐

• 生產環境務必使用連線池 (HikariCP, DBCP2, Tomcat JDBC Pool)。
• 設定 maximumPoolSize、minimumIdle、connectionTimeout、idleTimeout、maxLifetime。
• 啟用 validationQuery="SELECT 1" 並設定 testOnBorrow=true。

六、常見錯誤與排除

七、SQL Server vs Oracle 呼叫差異速查

結語

掌握 CallableStatement 標準流程、參數綁定、結果集切換、交易控制與連線池整合，即能在 Java 應用中高效、安全地呼叫 SQL Server 預存程序。建議將資料存取層封裝為 DAO/Repository，統一異常處理、資源管理與日誌記錄，提升程式碼可維護性。