PHP PDO单事务多表批量插入功能开发求助

核心思路 #

基于现有DataBaseManager和单例DataBase类，通过共享连接+手动事务控制实现多表批量插入的原子性：所有操作在同一事务中执行，任一表插入失败则全量回滚。

1. 定义批量插入元数据结构 #

先封装单表批量插入的SQL与参数，统一处理多表数据：

// 封装单表批量插入的必要信息
class BatchInsertData {
    String insertSql;          // 带占位符的插入SQL（如"INSERT INTO user(name, age) VALUES (?, ?)"）
    List<Object[]> paramsList; // 批量参数集合，每个数组对应一行数据的占位符参数

    public BatchInsertData(String insertSql, List<Object[]> paramsList) {
        this.insertSql = insertSql;
        this.paramsList = paramsList;
    }
}

2. 修复DataBase单例类的事务与批量插入逻辑 #

替换原setData方法，实现事务下的多表批量处理：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

public class DataBase {
    // 单例实现（按需调整为懒汉式/枚举式）
    private static final DataBase INSTANCE = new DataBase();
    private final DataBaseManager dbManager;

    private DataBase() {
        this.dbManager = new DataBaseManager(); // 假设DataBaseManager已初始化连接池/数据源
    }

    public static DataBase getInstance() {
        return INSTANCE;
    }

    /**
     * 单事务内完成多表批量插入，失败自动全量回滚
     * @param batchDataList 多表批量插入数据集合
     * @return 成功插入的总行数
     * @throws SQLException 执行异常时抛出，交由上层处理
     */
    public int batchInsertInTransaction(List<BatchInsertData> batchDataList) throws SQLException {
        Connection conn = null;
        int totalInsertedRows = 0;

        try {
            // 从DataBaseManager获取连接，关闭自动提交开启事务
            conn = dbManager.getConnection();
            conn.setAutoCommit(false);

            // 遍历处理每个表的批量插入
            for (BatchInsertData batchData : batchDataList) {
                // try-with-resources自动释放PreparedStatement
                try (PreparedStatement pstmt = conn.prepareStatement(batchData.insertSql)) {
                    // 绑定批量参数（JDBC参数索引从1开始）
                    for (Object[] rowParams : batchData.paramsList) {
                        for (int i = 0; i < rowParams.length; i++) {
                            pstmt.setObject(i + 1, rowParams[i]);
                        }
                        pstmt.addBatch();
                    }
                    // 执行单表批量插入并累加行数
                    int[] rowCounts = pstmt.executeBatch();
                    for (int count : rowCounts) {
                        totalInsertedRows += count;
                    }
                }
            }

            // 所有表插入成功，提交事务
            conn.commit();
            return totalInsertedRows;
        } catch (SQLException e) {
            // 发生异常时强制回滚事务
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace(); // 替换为项目日志框架记录
                }
            }
            throw e; // 抛出异常，让上层感知失败状态
        } finally {
            // 释放连接前恢复自动提交，避免影响连接池复用
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    dbManager.releaseConnection(conn); // 假设DataBaseManager提供连接释放方法
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3. 关键修复点说明 #

• 参数绑定修正：明确JDBC参数索引从1开始，循环绑定每行参数，避免索引越界或参数错位问题。
• 事务原子性保障：
  • 所有表操作共享同一个数据库连接，确保在同一事务上下文。
  • 关闭自动提交，手动控制事务的提交/回滚。
  • 任何步骤抛出SQLException，立即回滚整个事务。
• 资源安全：使用try-with-resources自动关闭PreparedStatement，finally块中恢复连接状态并释放回连接池，防止资源泄漏。

4. 使用示例 #

public class Demo {
    public static void main(String[] args) {
        // 准备用户表批量数据
        List<Object[]> userParams = List.of(
            new Object[]{"Alice", 26},
            new Object[]{"Bob", 31}
        );
        BatchInsertData userBatch = new BatchInsertData(
            "INSERT INTO user(name, age) VALUES (?, ?)",
            userParams
        );

        // 准备订单表批量数据
        List<Object[]> orderParams = List.of(
            new Object[]{1, "2024-05-22", 150.5},
            new Object[]{2, "2024-05-23", 300.0}
        );
        BatchInsertData orderBatch = new BatchInsertData(
            "INSERT INTO `order`(user_id, order_date, amount) VALUES (?, ?, ?)",
            orderParams
        );

        try {
            int total = DataBase.getInstance().batchInsertInTransaction(List.of(userBatch, orderBatch));
            System.out.println("成功插入 " + total + " 条数据");
        } catch (SQLException e) {
            System.out.println("批量插入失败，已全量回滚：" + e.getMessage());
            // 此处可添加业务降级逻辑
        }
    }
}

内容的提问来源于stack exchange，提问作者user10771503