數據割接專案中遭遇批量更新效能與穩定性問題,經排查歸因於 Statement 類別選擇錯誤與 JDBC 驅動版本不匹配。記錄避坑指南。
Statement stmt = conn.createStatement();
stmt.addBatch("UPDATE t SET c=1 WHERE id=1");
stmt.addBatch("UPDATE t SET c=2 WHERE id=2");
// ... 成千上萬條不同 SQL
stmt.executeBatch(); // 每條都需編譯,效能極差
缺點: 每條 SQL 獨立編譯,無法重用執行計畫,網路往返開銷大,大批量時極易超時或 OOM。
String sql = "UPDATE t SET col1=?, col2=? WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
for (DataRecord rec : batchList) {
ps.setInt(1, rec.getCol1());
ps.setString(2, rec.getCol2());
ps.setLong(3, rec.getId());
ps.addBatch();
}
int[] results = ps.executeBatch(); // 單次網路往返,預編譯重用
優勢: 僅編譯一次,參數綁定極快,單次網路往返提交整批,效能提升 10-100 倍。
舊版 com.microsoft.jdbc.sqlserver.SQLServerDriver(SQL Server 2000 驅動)不支援 真正的批量更新,executeBatch() 實際仍逐條發送,且易拋出:
SQLServerException: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single statement or a stored procedure...
-- Maven 依賴範例com.microsoft.sqlserver mssql-jdbc 12.2.0.jre11
jdbc:sqlserver://127.0.0.1:1433;databaseName=fax;selectMethod=cursor; sendStringParametersAsUnicode=false;applicationName=BatchImport;
selectMethod=cursor:大結果集流式讀取,避免 OOM。sendStringParametersAsUnicode=false:非 Unicode 資料庫可減少網路傳輸量。applicationName:便於 SQL Server Profiler 追蹤來源。SET NOCOUNT ON 減少網路封包。SQL Server 2008+ 支援傳遞資料表參數,單次呼叫存儲過程完成批量 UPSERT,效能更強、原子性更好。
-- 資料庫端建立類型 CREATE TYPE dbo.BatchUpdateType AS TABLE (Id BIGINT, Col1 INT, Col2 NVARCHAR(50)); -- Java 端使用 Struct/Array 傳遞(需驅動 4.2+ 支援)