Java呼叫SQL Server預存程序：命名參數完整實戰範例

為何偏好命名參數？

網路常見範例多用setXXX(int index, ...)索引式參數，但命名參數setXXX(String name, ...)可讀性更高、維護更易，尤其參數眾多時優勢明顯。本文提供建表、預存程序、JSP呼叫完整可執行代碼。

資料庫端準備

-- 建立BookUser資料表
CREATE TABLE [BookUser] (
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [Title] [nvarchar](50) NOT NULL,
    [Guid] [uniqueidentifier] DEFAULT (newid()) NOT NULL,
    [BirthDate] [datetime] NOT NULL,
    [Description] [ntext] NOT NULL,
    [Photo] [image] NULL,
    [Other] [varchar](50) DEFAULT ('預設值') NULL,
    CONSTRAINT [PK_BookUser] PRIMARY KEY CLUSTERED ([UserID])
);

-- 建立InsertUser預存程序
CREATE PROCEDURE InsertUser
    @UserName varchar(50), @Title varchar(255), @Guid uniqueidentifier,
    @BirthDate DateTime, @Description ntext, @Photo image, @Other nvarchar(50),
    @UserID int OUTPUT
AS
    SET NOCOUNT ON
    IF EXISTS (SELECT UserID FROM BookUser WHERE UserName = @UserName)
        RETURN 0
    ELSE BEGIN
        INSERT INTO BookUser (UserName,Title,Guid,BirthDate,Description,Photo,Other)
        VALUES (@UserName,@Title,@Guid,@BirthDate,@Description,@Photo,@Other)
        SET @UserID = @@IDENTITY
        RETURN 1
    END

JSP呼叫關鍵代碼

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password=";
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}"; // 第一個?為return value

Connection cn = DriverManager.getConnection(url);
CallableStatement cmd = cn.prepareCall(sql);

java.util.UUID Guid = java.util.UUID.randomUUID();
FileInputStream f = new FileInputStream(application.getRealPath("") + "testLogo.gif");
Date rightNow = Date.valueOf("2007-9-9");

cmd.setString("UserName", "mengxianhui"); // 命名參數
cmd.setString("Title", "孟憲會");
cmd.setString("Guid", Guid.toString());
cmd.setDate("BirthDate", rightNow); // 注意：同名參數可重複設定
cmd.setString("Description", "【孟子E章】");
cmd.setBinaryStream("Photo", f, f.available());
cmd.setString("Other", null);

cmd.registerOutParameter(1, java.sql.Types.INTEGER); // return value索引1
cmd.registerOutParameter("UserID", java.sql.Types.INTEGER); // OUTPUT參數命名

cmd.execute();
int returnValue = cmd.getInt(1);
int userID = cmd.getInt("UserID");

關鍵注意事項

1. 需下載Microsoft官方JDBC驅動程式（非jTDS）
2. 預存程序RETURN值對應registerOutParameter(1, ...)索引1
3. OUTPUT參數可用名稱註冊registerOutParameter("UserID", ...)
4. setDate與setString可針對同名參數多次呼叫，驅動程式會正確處理
5. 資源釋放置於finally區塊，避免連線洩漏