Java/JSP 中調用 SQL Server 儲存過程完整示例

採用 setXXX(String parameterName, XXX x) 具名參數方式調用儲存過程，比索引方式更直觀。

建表與儲存過程

CREATE TABLE [BookUser] (...);
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

Java 調用代碼

CallableStatement cs = conn.prepareCall("{call InsertUser(?,?,?,?,?,?,?,?)}");
cs.setString("UserName", "test");
cs.setString("Title", "Mr.");
cs.setString("Guid", UUID.randomUUID().toString());
cs.setDate("BirthDate", new Date(System.currentTimeMillis()));
cs.setString("Description", "desc");
cs.setNull("Photo", Types.BLOB);
cs.setString("Other", "other");
cs.registerOutParameter("UserID", Types.INTEGER);
cs.execute();
int userId = cs.getInt("UserID");

具名參數免去記憶順序，程式碼可讀性大增。