萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 獲取 MSSQL 自增主鍵值:SCOPE_IDENTITY()、OUTPUT 子句與 JDBC 3.0 getGeneratedKeys 實戰

問題背景

MSSQL 表定義 id INT IDENTITY(1,1) PRIMARY KEY,插入數據後需要立即獲取新生成的 ID,用於關聯插入、返回前端或後續業務。

方案對比

方案SQL/方法優點缺點/風險推薦度
存儲過程 + @@IDENTITYSELECT @@IDENTITY早期版本通用觸發器導致錯誤值、需額外存儲過程、耦合度高⭐
存儲過程 + SCOPE_IDENTITY()SELECT SCOPE_IDENTITY()當前作用域安全、無觸發器干擾仍需存儲過程⭐⭐
JDBC 3.0 getGeneratedKeysprepareStatement(sql, Statement.RETURN_GENERATED_KEYS)標準 API、無需存儲過程、跨數據庫驅動版本需支援、部分舊驅動有 bug⭐⭐⭐⭐⭐
OUTPUT 子句 (SQL Server 2005+)INSERT ... OUTPUT INSERTED.id VALUES (...)可獲取多列、集合操作、單語句原子性語法專有、JDBC 需解析 ResultSet⭐⭐⭐⭐

最佳實踐:JDBC 3.0 getGeneratedKeys 完整代碼

<%@ page import="java.sql.*, javax.sql.DataSource, javax.naming.*" contentType="text/html;charset=UTF-8" %>
<%
  String sql = "INSERT INTO users (username, email, create_time) VALUES (?, ?, GETDATE())";
  Connection conn = null;
  PreparedStatement pstmt = null;
  ResultSet generatedKeys = null;
  long newId = -1;

  try {
      Context ctx = new InitialContext();
      DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/mssql");
      conn = ds.getConnection();

      // 關鍵:Statement.RETURN_GENERATED_KEYS
      pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
      pstmt.setString(1, "new_user");
      pstmt.setString(2, "user@example.com");

      int affected = pstmt.executeUpdate();
      if (affected > 0) {
          generatedKeys = pstmt.getGeneratedKeys();
          if (generatedKeys.next()) {
              newId = generatedKeys.getLong(1); // 獲取自增 ID
              out.println("插入成功,新 ID = " + newId);
          }
      }
  } catch (Exception e) {
      e.printStackTrace();
      out.println("錯誤:" + e.getMessage());
  } finally {
      try { if (generatedKeys != null) generatedKeys.close(); } catch (Exception ignored) {}
      try { if (pstmt != null) pstmt.close(); } catch (Exception ignored) {}
      try { if (conn != null) conn.close(); } catch (Exception ignored) {}
  }
%>

OUTPUT 子句方案 (需 SQL Server 2005+)

String sql = "INSERT INTO users (username, email, create_time) " +
             "OUTPUT INSERTED.id " +
             "VALUES (?, ?, GETDATE())";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "new_user");
pstmt.setString(2, "user@example.com");
ResultSet rs = pstmt.executeQuery(); // 注意:executeQuery 而非 executeUpdate
if (rs.next()) {
    long newId = rs.getLong(1);
}

舊版存儲過程方案 (僅供維護參考)

-- 建立存儲過程
CREATE PROCEDURE sp_insert_user
    @username NVARCHAR(50),
    @email NVARCHAR(100),
    @newId INT OUTPUT
AS
BEGIN
    INSERT INTO users (username, email, create_time) VALUES (@username, @email, GETDATE());
    SET @newId = SCOPE_IDENTITY(); -- 關鍵:使用 SCOPE_IDENTITY()
END
GO

-- JSP 調用
CallableStatement cstmt = conn.prepareCall("{call sp_insert_user(?, ?, ?)}");
cstmt.setString(1, "new_user");
cstmt.setString(2, "user@example.com");
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
int newId = cstmt.getInt(3);

關鍵注意事項

  • 絕對不要用 @@IDENTITY:若表上有觸發器插入其他表,會返回觸發器產生的身份值
  • SCOPE_IDENTITY() 限於當前作用域,安全可靠
  • IDENT_CURRENT('table') 返回表最後生成的身份值,跨會話、並發下不安全
  • JDBC 驅動版本:Microsoft JDBC Driver 12.4+ (mssql-jdbc) 或 jTDS 1.3.1+ 穩定支援 getGeneratedKeys
  • 連線池配置:poolPreparedStatements=true 可提升準備語句復用性能

多表關聯插入事務範例

conn.setAutoCommit(false);
try {
    // 1. 插入主訂單
    long orderId = insertOrder(conn, orderData); // 使用 getGeneratedKeys
    // 2. 批量插入訂單項
    insertOrderItems(conn, orderId, items);
    // 3. 扣減庫存
    updateStock(conn, items);
    conn.commit();
} catch (Exception e) {
    conn.rollback();
    throw e;
} finally {
    conn.setAutoCommit(true);
}
copyright © 萬盛學電腦網 all rights reserved