兩種批量更新 API 對比
| API | 程式碼模式 | 優點 | 缺點 | 推薦場景 |
|---|
| Statement | `addBatch(sql1); addBatch(sql2); executeBatch()` | 可批次執行不同 SQL | 無預編譯、SQL 注入風險、大量資料效能差 | 極少量、異構 SQL |
| PreparedStatement | `ps = prepare(sql); loop{ setParams; addBatch(); } executeBatch()` | **預編譯一次、參數化安全、執行計畫重用、網路往返最少** | 單一 SQL 模板 | **大批量同結構 DML (首選)** |
PreparedStatement 批量更新標準範本
String sql = "INSERT INTO TargetTable (Col1, Col2) VALUES (?, ?)";
try (Connection conn = getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
for (DataRow row : dataList) {
ps.setInt(1, row.getCol1());
ps.setString(2, row.getCol2());
ps.addBatch();
// 可選：每 1000 條 executeBatch() 防 OOM
if (++count % 1000 == 0) ps.executeBatch();
}
ps.executeBatch(); // 剩餘
conn.commit();
} catch (SQLException e) {
conn.rollback();
throw e;
}
關鍵效能參數：JDBC 驅動版本決定生死
必須使用 Microsoft JDBC Driver 1.2+ (sqljdbc4.jar)
- **1.0 版 (sqljdbc.jar) 不支援真正的批量更新**，會拋出：
`SQLServerException: sp_cursoropen/sp_cursorprepare: 該語句參數只能是一個批...`
- 1.2+ 版原生支援 `executeBatch` 伺服器端批次執行，效能提升 **10-100 倍**。
連線字串優化參數
jdbc:sqlserver://127.0.0.1:1433;databaseName=mydb;selectMethod=cursor;sendStringParametersAsUnicode=false;
- `selectMethod=cursor`：大結果集串流讀取，避免記憶體暴增。
- `sendStringParametersAsUnicode=false`：非 Unicode 字串參數不轉換，減少網路封包。
大批量資料遷移進階策略
- 分批提交：每 1000-5000 行 `executeBatch()` + `commit()`，避免長交易鎖升級、記錄檔暴漲、OOM。
- 停用索引/約束：匯入前 `DISABLE` 非叢集索引、FK、CHECK，匯入後 `REBUILD`/`ENABLE`。
- 最小記錄模式：`BULK_LOGGED` 復原模式 + `TABLOCK` Hint (堆積表) 或叢集索引有序輸入，最小化記錄量。
- BCP / SqlBulkCopy：極大量 (百萬級+) 時，`SqlBulkCopy` (ADO.NET) 或 `bcp.exe` 命令列效能遠超 JDBC 批量。
常見錯誤排查
| 錯誤 | 原因 | 解決 |
|---|
| `sp_cursoropen` 錯誤 | JDBC 1.0 驅動 | 升級 1.2+ (sqljdbc4.jar) |
| 批量更新極慢 | 未使用 PreparedStatement / 自動提交開啟 | 改用 PS + 關閉自動提交 |
| 記憶體溢位 (OOM) | 單次批次過大、結果集未串流 | 分批 + selectMethod=cursor |
| 死結 | 多執行緒並發批量更新同表、索引順序不一 | 統一存取順序、縮短交易、考慮分區表 |
結語
SQL Server 批量更新鐵律：**PreparedStatement + 關閉自動提交 + 分批 executeBatch + JDBC 1.2+ 驅動**。配合索引策略、復原模式、BCP 工具，可應對從萬級到億級的資料遷移挑戰。驅動版本是隱形門檻，務必納入專案依賴管理強制鎖定。
