JSP 調用 SQL Server 存儲過程完整實例：建表、存儲過程、JSP 代碼與參數綁定詳解

前言

存儲過程能將業務邏輯下沉數據庫層，減少網絡往返、利用數據庫優化器、防 SQL 注入。本文給出 **完整可運行示例**：建表、創建帶輸出參數的存儲過程、JSP 調用代碼 (含 PreparedStatement/CallableStatement 兩種方式)、結果處理。

一、數據庫端準備 (SQL Server)

1. 建表

CREATE TABLE BookUser (
    UserID       INT IDENTITY(1,1) PRIMARY KEY,
    UserName     VARCHAR(50)  NOT NULL,
    Title        NVARCHAR(50) NOT NULL,
    Guid         UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    BirthDate    DATETIME     NOT NULL,
    Description  NTEXT        NOT NULL,
    Photo        IMAGE        NULL,
    Other        VARCHAR(50)  NULL DEFAULT '默認值'
);

2. 創建存儲過程 (帶輸出參數返回新增 ID)

CREATE PROCEDURE InsertUser
    @UserName     VARCHAR(50),
    @Title        VARCHAR(255),
    @Guid         UNIQUEIDENTIFIER,
    @BirthDate    DATETIME,
    @Description  NTEXT,
    @Photo        IMAGE,
    @Other        NVARCHAR(50),
    @UserID       INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 業務唯一性檢查
    IF EXISTS (SELECT 1 FROM BookUser WHERE UserName = @UserName)
    BEGIN
        RETURN 0; -- 返回碼 0 表示用戶名已存在
    END
    
    INSERT INTO BookUser (UserName, Title, Guid, BirthDate, Description, Photo, Other)
    VALUES (@UserName, @Title, @Guid, @BirthDate, @Description, @Photo, @Other);
    
    SET @UserID = SCOPE_IDENTITY(); -- 獲取剛插入的自增 ID
    RETURN 1; -- 成功
END;

二、JSP 調用代碼 (完整版)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*, java.util.*, javax.sql.*, javax.naming.*" %>
<!DOCTYPE html>
<html>
<head>
    <title>調用存儲過程插入用戶</title>
</head>
<body>
<h2>註冊新用戶</h2>

<%
    // 1. 獲取表單參數
    String userName = request.getParameter("userName");
    String title = request.getParameter("title");
    String birthDateStr = request.getParameter("birthDate"); // 格式 yyyy-MM-dd
    String description = request.getParameter("description");
    String other = request.getParameter("other");
    
    // 2. 基本校驗
    boolean hasError = false;
    String errorMsg = "";
    if (userName == null || userName.trim().isEmpty()) {
        hasError = true; errorMsg = "用戶名不能為空";
    }
    
    // 3. 無錯誤時執行存儲過程
    Integer newUserId = null;
    int returnCode = -1;
    
    if (!hasError) {
        Connection conn = null;
        CallableStatement cstmt = null;
        try {
            // --- 方式 A: DataSource + JNDI (生產推薦) ---
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/SqlServerDS");
            conn = ds.getConnection();
            
            // --- 方式 B: 直連 (測試用，請替換為您的連接信息) ---
            // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            // conn = DriverManager.getConnection(
            //     "jdbc:sqlserver://localhost:1433;databaseName=testdb;encrypt=false",
            //     "sa", "password");
            
            conn.setAutoCommit(false); // 事務控制
            
            // 4. 準備 CallableStatement
            String sql = "{call InsertUser(?, ?, ?, ?, ?, ?, ?, ?)}";
            cstmt = conn.prepareCall(sql);
            
            // 5. 綁定輸入參數 (按存儲過程定義順序)
            cstmt.setString(1, userName);
            cstmt.setString(2, title);
            cstmt.setString(3, UUID.randomUUID().toString()); // Guid
            cstmt.setDate(4, java.sql.Date.valueOf(birthDateStr)); // BirthDate
            cstmt.setString(5, description);
            cstmt.setNull(6, Types.BLOB); // Photo 暫存 NULL
            cstmt.setString(7, other != null ? other : "默認值");
            
            // 6. 註冊輸出參數 (第 8 參數)
            cstmt.registerOutParameter(8, Types.INTEGER);
            
            // 7. 執行
            cstmt.execute();
            
            // 8. 獲取輸出參數與返回碼
            newUserId = cstmt.getInt(8);
            returnCode = cstmt.getInt(1); // JDBC 規範：getInt(1) 獲取 RETURN 值 (視驅動而定，建議用 OUTPUT 參數)
            
            conn.commit();
            
        } catch (Exception e) {
            hasError = true;
            errorMsg = "數據庫錯誤: " + e.getMessage();
            if (conn != null) try { conn.rollback(); } catch (SQLException ignored) {}
            e.printStackTrace(); // 生產用 Logger
        } finally {
            // 9. 關閉資源
            if (cstmt != null) try { cstmt.close(); } catch (SQLException ignored) {}
            if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
        }
    }
%>

<% if (hasError) { %>
    <div style="color:red"><%= errorMsg %></div>
<% } else if (newUserId != null) { %>
    <div style="color:green">
        註冊成功！新用戶 ID: <%= newUserId %>, 返回碼: <%= returnCode %>
    </div>
<% } %>

<hr/>
<form method="post" action="">
    用戶名: <input type="text" name="userName" required/><br/>
    標題: <input type="text" name="title"/><br/>
    出生日期: <input type="date" name="birthDate" required/><br/>
    描述: <textarea name="description"></textarea><br/>
    其他: <input type="text" name="other"/><br/>
    <input type="submit" value="提交調用存儲過程"/>
</form>
</body>
</html>

三、關鍵技術點解析

四、常見錯誤與調試

1. 參數索引錯誤：JDBC 參數索引**從 1 開始**，對應 SQL 中 ?/參數順序。
2. 日期類型不匹配：用 setDate(index, java.sql.Date.valueOf("yyyy-MM-dd"))。
3. 驅動不支援 getInt(1) 取返回碼：改用額外 OUTPUT 參數 @RetCode INT OUTPUT。
4. 存儲過程不存在/權限不足：確認數據庫用戶有 EXECUTE 權限，Schema 正確 (dbo)。

五、現代化建議

• **MyBatis/Spring Data JPA** 映射存儲過程：@Procedure / <select statementType="CALLABLE">，避免手寫 JDBC。
• **業務邏輯盡量留在應用層**，存儲過程僅用於：極高性能批量、跨應用共享邏輯、歷史遷移。
• **版本控制**：存儲過程腳本納入 Flyway/Liquibase 遷移腳本管理。