mybatisplus添加真正的批量新增、批量更新的实现

下面我给您详细讲解一下“mybatisplus添加真正的批量新增、批量更新的实现”的完整攻略。

理解MyBatis-Plus

MyBatis-Plus是基于MyBatis的快速开发框架，提供一系列的增强功能，能够更加方便、快捷地开发数据库相关操作。其中，其批量操作功能得到了广泛的关注和应用。本文详细介绍了MyBatis-Plus批量新增、批量更新的实现方式。

MyBatis-Plus批量新增

MyBatis-Plus的批量新增操作需要使用MyBatis的批量操作接口Executor，通过该接口实现真正的批量插入。其实现流程主要如下：

1. 获取MyBatis的SqlSession对象。

SqlSession sqlSession = SqlHelper.sqlSession();

1. 通过SqlSession获取对应的Executor对象。

Executor batchExecutor = sqlSession.getConfiguration().newExecutor(TransactionIsolationLevel.SQL_REPEATABLE_READ, true);

1. 通过batchExecutor执行批量插入操作。需要注意的是，批量插入的时候，需要将SQL语句的占位符设置成(?, ?, ...)

List<T> list = new ArrayList<>();
// 添加数据到list中
String sql = "INSERT INTO table_name (col1, col2, ...) VALUES (?, ?, ...)";
PreparedStatement statement = null;
try {
    int batchSize = 1000; // 每批次插入的数量
    int index = 0;
    statement = batchExecutor.getTransaction().getConnection().prepareStatement(sql);
    for (T entity : list) {
        // 填充数据到占位符中
        statement.setObject(1, entity.getCol1());
        statement.setObject(2, entity.getCol2());
        // ...
        statement.addBatch();
        index++;
        if (index % batchSize == 0 || index == list.size()) {
            statement.executeBatch();
            statement.clearBatch();
        }
    }
    batchExecutor.getTransaction().commit();
} catch (SQLException e) {
    batchExecutor.getTransaction().rollback();
    throw new RuntimeException(e);
} finally {
    if (statement != null) {
        statement.close();
    }
}

MyBatis-Plus批量更新

MyBatis-Plus的批量更新操作与批量新增操作类似，同样需要使用MyBatis的批量操作接口Executor，通过该接口实现真正的批量更新。其实现流程主要如下：

1. 获取MyBatis的SqlSession对象。

SqlSession sqlSession = SqlHelper.sqlSession();

1. 通过SqlSession获取对应的Executor对象。

Executor batchExecutor = sqlSession.getConfiguration().newExecutor(TransactionIsolationLevel.SQL_REPEATABLE_READ, true);

1. 通过batchExecutor执行批量更新操作。需要注意的是，批量更新的时候，需要将SQL语句的占位符设置成(?, ?, ...)

List<T> list = new ArrayList<>();
// 添加数据到list中
String sql = "UPDATE table_name SET col1 = ?, col2 = ?, ... WHERE id = ?";
PreparedStatement statement = null;
try {
    int batchSize = 1000; // 每批次更新的数量
    int index = 0;
    statement = batchExecutor.getTransaction().getConnection().prepareStatement(sql);
    for (T entity : list) {
        // 填充数据到占位符中
        statement.setObject(1, entity.getCol1());
        statement.setObject(2, entity.getCol2());
        // ...
        statement.setObject(n + 1, entity.getId());
        statement.addBatch();
        index++;
        if (index % batchSize == 0 || index == list.size()) {
            statement.executeBatch();
            statement.clearBatch();
        }
    }
    batchExecutor.getTransaction().commit();
} catch (SQLException e) {
    batchExecutor.getTransaction().rollback();
    throw new RuntimeException(e);
} finally {
    if (statement != null) {
        statement.close();
    }
}

示例

以下是两个示例，分别展示了MyBatis-Plus的批量新增和批量更新。

示例1：批量新增

public void batchInsert(List<User> userList) {
    // 获取SqlSession对象
    SqlSession sqlSession = this.getSqlSession();
    // 获取Executor对象
    Executor batchExecutor = sqlSession.getConfiguration().newExecutor(TransactionIsolationLevel.SQL_REPEATABLE_READ, true);
    // 执行批量插入操作
    String sql = "INSERT INTO t_user (id, name, age) VALUES (?, ?, ?)";
    PreparedStatement statement = null;
    try {
        int batchSize = 1000; // 每批次插入的数量
        int index = 0;
        statement = batchExecutor.getTransaction().getConnection().prepareStatement(sql);
        for (User user : userList) {
            statement.setLong(1, user.getId());
            statement.setString(2, user.getName());
            statement.setInt(3, user.getAge());
            statement.addBatch();
            index++;
            if (index % batchSize == 0 || index == userList.size()) {
                statement.executeBatch();
                statement.clearBatch();
            }
        }
        batchExecutor.getTransaction().commit();
    } catch (SQLException e) {
        batchExecutor.getTransaction().rollback();
        throw new RuntimeException(e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

示例2：批量更新

public void batchUpdate(List<User> userList) {
    // 获取SqlSession对象
    SqlSession sqlSession = this.getSqlSession();
    // 获取Executor对象
    Executor batchExecutor = sqlSession.getConfiguration().newExecutor(TransactionIsolationLevel.SQL_REPEATABLE_READ, true);
    // 执行批量更新操作
    String sql = "UPDATE t_user SET name = ?, age = ? WHERE id = ?";
    PreparedStatement statement = null;
    try {
        int batchSize = 1000; // 每批次更新的数量
        int index = 0;
        statement = batchExecutor.getTransaction().getConnection().prepareStatement(sql);
        for (User user : userList) {
            statement.setString(1, user.getName());
            statement.setInt(2, user.getAge());
            statement.setLong(3, user.getId());
            statement.addBatch();
            index++;
            if (index % batchSize == 0 || index == userList.size()) {
                statement.executeBatch();
                statement.clearBatch();
            }
        }
        batchExecutor.getTransaction().commit();
    } catch (SQLException e) {
        batchExecutor.getTransaction().rollback();
        throw new RuntimeException(e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

希望以上内容对您有所帮助。