預存程序能將商業邏輯下沉至資料庫層,提升效能與安全性。本文提供完整的建表、建立預存程序、JSP 呼叫三步驟實作範例,展示輸入/輸出參數、唯一識別碼、二進位資料等進階型別處理。
CREATE TABLE BookUser (
UserID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserName VARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
Title NVARCHAR(50) COLLATE Chinese_PRC_CI_AS NOT NULL,
Guid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
BirthDate DATETIME NOT NULL,
Description NTEXT COLLATE Chinese_PRC_CI_AS NOT NULL,
Photo IMAGE NULL,
Other VARCHAR(50) COLLATE Chinese_PRC_CI_AS NULL DEFAULT '預設值'
);
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; -- 取得剛插入的自增 ID
RETURN 1; -- 成功
END
GO
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" import="java.sql.*" %>
<!DOCTYPE html>
<html><head><title>呼叫預存程序範例</title></head><body>
<%
// 資料庫連線設定 (實務請外部化)
String url = "jdbc:sqlserver://localhost:1433;databaseName=YourDB";
String user = "sa"; String pwd = "yourPassword";
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
conn = DriverManager.getConnection(url, user, pwd);
// 準備呼叫: {call 程序名(?, ?, ...)}
String sql = "{call InsertUser(?, ?, ?, ?, ?, ?, ?, ?)}";
cstmt = conn.prepareCall(sql);
// 設定輸入參數 (索引從 1 開始)
cstmt.setString(1, "張三"); // @UserName
cstmt.setString(2, "工程師"); // @Title
cstmt.setString(3, java.util.UUID.randomUUID().toString()); // @Guid
cstmt.setTimestamp(4, new java.sql.Timestamp(System.currentTimeMillis())); // @BirthDate
cstmt.setString(5, "這是詳細描述內容..."); // @Description
cstmt.setBytes(6, new byte[0]); // @Photo (示範空 byte[])
cstmt.setString(7, "其他資訊"); // @Other
// 註冊輸出參數
cstmt.registerOutParameter(8, java.sql.Types.INTEGER); // @UserID
// 執行
boolean hasResultSet = cstmt.execute();
int returnCode = cstmt.getInt(8); // 取得 OUTPUT 參數
int spReturn = cstmt.getUpdateCount(); // 取得 RETURN 值 (0/1)
out.println("<h3>執行結果</h3>");
out.println("預存程序返回碼: " + spReturn + "<br>");
out.println("新用戶 ID (OUTPUT): " + returnCode + "<br>");
} catch (Exception e) {
e.printStackTrace();
out.println("<font color='red'>錯誤: " + e.getMessage() + "</font>");
} finally {
try { if (cstmt != null) cstmt.close(); } catch (Exception ignore) {}
try { if (conn != null) conn.close(); } catch (Exception ignore) {}
}
%>
</body></html>
| 項目 | 說明 |
|---|---|
CallableStatement | JDBC 標準介面,專門呼叫預存程序,支援 IN/OUT/INOUT 參數 |
{call ...} 語法 | JDBC Escape 語法,跨資料庫可移植 |
registerOutParameter(index, type) | 執行前必須註冊 OUTPUT 參數的 JDBC 型別 |
@@IDENTITY | SQL Server 取得最後插入的自增識別值 |
RETURN 0/1 | 預存程序返回狀態碼,透過 getUpdateCount() 取得 |
| 二進位資料 | setBytes() / setBinaryStream() 對應 IMAGE/VARBINARY |
| 唯一識別碼 | Java UUID.randomUUID() 對應 SQL Server UNIQUEIDENTIFIER |
DataSource (JNDI) 或設定檔,勿硬編碼Connection 生命週期