MSSQL 表定義 id INT IDENTITY(1,1) PRIMARY KEY,插入數據後需要立即獲取新生成的 ID,用於關聯插入、返回前端或後續業務。
| 方案 | SQL/方法 | 優點 | 缺點/風險 | 推薦度 |
|---|---|---|---|---|
| 存儲過程 + @@IDENTITY | SELECT @@IDENTITY | 早期版本通用 | 觸發器導致錯誤值、需額外存儲過程、耦合度高 | ⭐ |
| 存儲過程 + SCOPE_IDENTITY() | SELECT SCOPE_IDENTITY() | 當前作用域安全、無觸發器干擾 | 仍需存儲過程 | ⭐⭐ |
| JDBC 3.0 getGeneratedKeys | prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) | 標準 API、無需存儲過程、跨數據庫 | 驅動版本需支援、部分舊驅動有 bug | ⭐⭐⭐⭐⭐ |
| OUTPUT 子句 (SQL Server 2005+) | INSERT ... OUTPUT INSERTED.id VALUES (...) | 可獲取多列、集合操作、單語句原子性 | 語法專有、JDBC 需解析 ResultSet | ⭐⭐⭐⭐ |
<%@ 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) {}
}
%>
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);
getGeneratedKeyspoolPreparedStatements=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);
}