JDBC 批处理操作详解

JDBC 批处理操作详解

一、批处理的核心概念

批处理允许将多个SQL语句作为一个单元发送到数据库执行，主要优势：

二、批处理实现方式

1. Statement 批处理（不推荐）

try (Statement stmt = conn.createStatement()) {
    stmt.addBatch("INSERT INTO users(name) VALUES ('Alice')");
    stmt.addBatch("UPDATE accounts SET balance=1000 WHERE id=1");
    stmt.addBatch("DELETE FROM logs WHERE date < '2023-01-01'");
    
    int[] updateCounts = stmt.executeBatch();
} catch (SQLException e) {
    // 异常处理
}

2. PreparedStatement 批处理（推荐⭐️）

String sql = "INSERT INTO employees (name, email, dept) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    // 关闭自动提交以提高性能
    conn.setAutoCommit(false);
    
    for (Employee emp : employeeList) {
        pstmt.setString(1, emp.getName());
        pstmt.setString(2, emp.getEmail());
        pstmt.setString(3, emp.getDepartment());
        pstmt.addBatch();  // 添加到批处理
        
        // 分段提交（避免内存溢出）
        if (i % 1000 == 0) {
            pstmt.executeBatch();
            conn.commit();
        }
    }
    
    // 执行剩余批处理
    int[] updateCounts = pstmt.executeBatch();
    conn.commit();
    
} catch (BatchUpdateException bue) {
    // 处理批处理异常
    int[] updateCounts = bue.getUpdateCounts();
    // 部分成功处理
} finally {
    conn.setAutoCommit(true); // 恢复自动提交
}

三、批处理关键技术点

1. 批处理执行方法对比

2. 事务管理策略

conn.setAutoCommit(false);  // 开启事务
try {
    // 执行批处理
    conn.commit();          // 提交事务
} catch (SQLException e) {
    conn.rollback();        // 回滚事务
}

3. 分段批处理实现

int batchSize = 500;
int count = 0;

for (Data data : dataList) {
    // 设置参数...
    pstmt.addBatch();
    
    if (++count % batchSize == 0) {
        pstmt.executeBatch();
        pstmt.clearBatch(); // 可选：清除缓存
    }
}

// 执行最后一批
if (count % batchSize != 0) {
    pstmt.executeBatch();
}

四、不同数据库的批处理优化

1. MySQL 优化

// JDBC URL添加参数
String url = "jdbc:mysql://localhost/db?rewriteBatchedStatements=true";

// 使用VALUES批量语法（更高性能）
String sql = "INSERT INTO users (name) VALUES (?)";
// 自动重写为：INSERT INTO users (name) VALUES (...),(...),...

2. Oracle 优化

// 设置批处理大小
pstmt.setExecuteBatch(100);

// 使用Oracle特定批量语法
/*+ APPEND_VALUES */  // 直接路径插入提示

3. PostgreSQL 优化

// 使用COPY命令（最高性能）
CopyManager copyManager = ((PGConnection) conn).getCopyManager();
copyManager.copyIn("COPY table FROM STDIN WITH CSV", reader);

五、批处理性能对比测试

插入10,000条记录测试结果：

六、高级批处理技巧

1. 批量更新与删除

String updateSql = "UPDATE products SET price = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
    for (Product p : products) {
        pstmt.setBigDecimal(1, p.getNewPrice());
        pstmt.setInt(2, p.getId());
        pstmt.addBatch();
    }
    pstmt.executeBatch();
}

2. 批量存储过程调用

String callSql = "{call update_employee_salary(?, ?)}";
try (CallableStatement cstmt = conn.prepareCall(callSql)) {
    for (Employee emp : employees) {
        cstmt.setInt(1, emp.getId());
        cstmt.setBigDecimal(2, emp.getNewSalary());
        cstmt.addBatch();
    }
    cstmt.executeBatch();
}

3. 混合操作批处理

try (Statement stmt = conn.createStatement()) {
    stmt.addBatch("INSERT INTO logs (message) VALUES ('Batch start')");
    stmt.addBatch("UPDATE counters SET value = value + 1");
    stmt.addBatch("INSERT INTO logs (message) VALUES ('Batch end')");
    
    stmt.executeBatch();
}

七、异常处理最佳实践

1. BatchUpdateException 处理

try {
    int[] results = pstmt.executeBatch();
} catch (BatchUpdateException bue) {
    int[] partialResults = bue.getUpdateCounts();
    SQLException rootEx = bue.getNextException();
    
    // 处理部分成功场景
    for (int i = 0; i < partialResults.length; i++) {
        if (partialResults[i] == Statement.EXECUTE_FAILED) {
            System.err.println("第 " + (i+1) + " 条语句执行失败");
        }
    }
    
    // 继续处理后续批处理
}

2. 事务恢复策略

conn.setAutoCommit(false);
try {
    // 执行批处理...
    conn.commit();
} catch (BatchUpdateException bue) {
    // 根据业务决定回滚或部分提交
    if (shouldRollback(bue)) {
        conn.rollback();
    } else {
        conn.commit(); // 提交成功部分
    }
}

八、性能优化策略

1. 批处理大小调优

   // 最佳批处理大小参考
   int optimalBatchSize = 0;
   if (databaseType == "MySQL") optimalBatchSize = 500-1000;
   if (databaseType == "Oracle") optimalBatchSize = 100-500;
   if (databaseType == "PostgreSQL") optimalBatchSize = 1000-5000;
   

2. JVM内存配置

   # 增加堆内存处理大数据量
   java -Xms512m -Xmx4g -jar application.jar
   

3. 连接池配置

   # HikariCP配置
   maximumPoolSize=20
   connectionTimeout=30000
   

九、常见问题解决方案

问题1：内存溢出(OOM)

• 解决方案：
  • 减少批处理大小（500-5000条/批）
  • 使用分段提交
  • 增加JVM堆内存

问题2：批处理未实际执行

• 检查MySQL的rewriteBatchedStatements=true参数
• 确认executeBatch()被调用
• 检查是否设置了setAutoCommit(true)

问题3：部分批处理失败

• 使用事务确保原子性
• 捕获BatchUpdateException处理部分成功
• 实现重试机制

十、总结：批处理最佳实践

1. 始终使用PreparedStatement：安全且高效
2. 合理设置批处理大小：根据数据库和硬件调整
3. 显式管理事务：手动控制提交/回滚
4. 使用分段提交：避免内存溢出
5. 数据库特定优化：

   // MySQL
   url += "&rewriteBatchedStatements=true&useServerPrepStmts=true";
   
   // Oracle
   pstmt.setExecuteBatch(100);
   

6. 资源清理：

   finally {
       pstmt.clearBatch();
       conn.setAutoCommit(true);
   }