使用 preparedstatement 执行批量进度跟踪的核心做法是:在循环中每处理一定数量的批次后调用 getupdatecount() 获取当前批次的更新行数,并累加计算总进度。具体而言,你需要设置一个合理的批次大小(如 1000),在每次 executebatch() 成功后立即检查返回值数组,将非负值相加得到本批次成功数,从而实时反馈进度。

如何配置批次大小以平衡性能与进度粒度
通过控制 addBatch() 的累积数量并在达到阈值时触发 executeBatch(),可以精确控制进度报告的频率。通常建议批次大小在 500 到 2000 之间,过小会增加网络往返开销,过大则可能导致内存溢出或进度更新滞后。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class BatchProgressDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "password";
int batchSize = 1000;
int totalRecords = 10000;
try (Connection conn = DriverManager.getConnection(url, user, password)) {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
int successCount = 0;
for (int i = 0; i < totalRecords; i++) {
pstmt.setString(1, "User_" + i);
pstmt.setString(2, "user_" + i + "@example.com");
pstmt.addBatch();
if ((i + 1) % batchSize == 0) {
int[] results = pstmt.executeBatch();
for (int count : results) {
if (count >= 0) {
successCount += count;
}
}
System.out.println("Processed: " + successCount + " / " + totalRecords);
pstmt.clearBatch();
}
}
// 处理剩余记录
if (totalRecords % batchSize != 0) {
int[] results = pstmt.executeBatch();
for (int count : results) {
if (count >= 0) {
successCount += count;
}
}
}
System.out.println("Final Success Count: " + successCount);
} catch (Exception e) {
e.printStackTrace();
}
}
}如何处理批量执行中的部分失败情况
当批量执行发生异常或部分语句失败时,executeBatch() 可能抛出 BatchUpdateException。此时可以通过捕获该异常并调用其 updateCounts() 方法,分析已成功执行的批次索引,从而确定实际完成的进度,避免重复处理或数据不一致。
立即学习“Java免费学习笔记(深入)”;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class PartialFailureDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "password";
int batchSize = 500;
int totalRecords = 2000;
try (Connection conn = DriverManager.getConnection(url, user, password)) {
String sql = "INSERT INTO users (id, name) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
int processedUpTo = 0;
try {
for (int i = 0; i < totalRecords; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "Name_" + i);
pstmt.addBatch();
if ((i + 1) % batchSize == 0) {
pstmt.executeBatch();
pstmt.clearBatch();
processedUpTo = i + 1;
}
}
// 处理剩余
if (totalRecords % batchSize != 0) {
pstmt.executeBatch();
processedUpTo = totalRecords;
}
} catch (BatchUpdateException e) {
int[] counts = e.getUpdateCounts();
// 统计成功数量
int successInBatch = 0;
for (int c : counts) {
if (c >= 0) successInBatch++;
else break; // 遇到失败停止计数
}
processedUpTo -= (batchSize - successInBatch);
System.err.println("Failed at record: " + processedUpTo);
}
System.out.println("Successfully processed up to: " + processedUpTo);
} catch (Exception e) {
e.printStackTrace();
}
}
}基于 executeBatch() 返回值的进度计算逻辑
executeBatch() 返回的是一个 int[] 数组,其中每个元素对应一次 addBatch() 的操作结果。正值表示受影响的行数,BATCH_UPDATE_SUCCESSFUL (0) 表示成功但无影响行数,BATCH_UPDATE_FAILED (-3) 表示失败。在计算进度时,应遍历此数组,仅对 >= 0 的值进行累加,忽略 -3,以确保进度统计准确反映实际写入数据库的记录数。


















