MyBatisPlus 批量插入问题-Toy模板网

这篇具有很好参考价值的文章主要介绍了MyBatisPlus 批量插入问题。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

MyBatisPlus 批量插入问题

业务类 service接口继承 com.baomidou.mybatisplus.extension.service 包下的 IService 接口

public interface TUserService extends IService<TUser> {}

当使用在controller中或测试类总注入 TUserService，调用批量插入方法

boolean result = service.saveBatch(tUsers);

最终调用的是 IService的 com.baomidou.mybatisplus.extension.service.impl包下的 ServiceImpl 实现类

@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
    String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
    return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
}

可以一步一步点进去看到最终的实现

 public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
        Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
        return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {
            int size = list.size();
            int i = 1;
            for (E element : list) {
                consumer.accept(sqlSession, element);
                if ((i % batchSize == 0) || i == size) {
                    sqlSession.flushStatements();
                }
                i++;
            }
        });
    }

将集合遍历，组装成一个个的Insert into 的语句，遍历结束后 flush，一次性将所有的insert into语句执行；

相当于还是遍历单条语句插入；

控制台可以看到打印出的sql

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@68c286] will be managed by Spring
==>  Preparing: INSERT INTO t_user ( user_name, nick_name, email, create_time, update_time, deleted_flag ) VALUES ( ?, ?, ?, ?, ?, ? )
==> Parameters: name_0(String), name_0nickName(String), name_0email(String), 2022-09-30T13:59:48.668(LocalDateTime), 2022-09-30T13:59:48.668(LocalDateTime), 0(Integer)
==> Parameters: name_1(String), name_1nickName(String), name_1email(String), 2022-09-30T13:59:48.668(LocalDateTime), 2022-09-30T13:59:48.668(LocalDateTime), 0(Integer)
==> Parameters: name_2(String), name_2nickName(String), name_2email(String), 2022-09-30T13:59:48.668(LocalDateTime), 2022-09-30T13:59:48.668(LocalDateTime), 0(Integer)
==> Parameters: name_3(String), name_3nickName(String), name_3email(String), 2022-09-30T13:59:48.668(LocalDateTime), 2022-09-30T13:59:48.668(LocalDateTime), 0(Integer)
==> Parameters: name_4(String), name_4nickName(String), name_4email(String), 2022-09-30T13:59:48.668(LocalDateTime), 2022-09-30T13:59:48.668(LocalDateTime), 0(Integer)

相当于一个单条插入语句作为模板，用for循环遍历集合，将元素的值进行填充，来执行的批量插入；

呐如何实现数据库的一条语句插入多行数据呢？

INSERT INTO t_user (user_name,nick_name,email,create_time,update_time,deleted_flag) VALUES (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?)

方式一

使用xml的自定义sql，foreach标签来拼接sql；

方式二

重写配置批量方法，增加方法；

参考链接： MyBatis-plus 自定义通用方法及其实现原理

新增SQL注入器，
在配置类中将SQL注入器注入到bean容器
新增自定义的Mapper接口，继承BaseMapper,其他mapper接口直接继承自定义的Mapper接口即可使用新方法；

MyBatis-plus 提供了真正的批量插入方法 InsertBatchSomeColumn，只不过这个方法只在 MySQL 数据库下测试过，所以没有将其作为默认通用方法添加到 SqlMethod

这个方法的源码

其实就是提供了一个使用 foreach 标签的 SQL 脚本

/**
 * 批量新增数据,自选字段 insert
 * <p> 不同的数据库支持度不一样!!!  只在 mysql 下测试过!!!  只在 mysql 下测试过!!!  只在 mysql 下测试过!!! </p>
 * <p> 除了主键是 <strong> 数据库自增的未测试 </strong> 外理论上都可以使用!!! </p>
 * <p> 如果你使用自增有报错或主键值无法回写到entity,就不要跑来问为什么了,因为我也不知道!!! </p>
 * <p>
 * 自己的通用 mapper 如下使用:
 * <pre>
 * int insertBatchSomeColumn(List<T> entityList);
 * </pre>
 * </p>
 *
 * <li> 注意: 这是自选字段 insert !!,如果个别字段在 entity 里为 null 但是数据库中有配置默认值, insert 后数据库字段是为 null 而不是默认值 </li>
 *
 * <p>
 * 常用的 {@link Predicate}:
 * </p>
 *
 * <li> 例1: t -> !t.isLogicDelete() , 表示不要逻辑删除字段 </li>
 * <li> 例2: t -> !t.getProperty().equals("version") , 表示不要字段名为 version 的字段 </li>
 * <li> 例3: t -> t.getFieldFill() != FieldFill.UPDATE) , 表示不要填充策略为 UPDATE 的字段 </li>
 *
 * @author miemie
 * @since 2018-11-29
 */
@NoArgsConstructor
@AllArgsConstructor
public class InsertBatchSomeColumn extends AbstractMethod {

    /**
     * 字段筛选条件
     */
    @Setter
    @Accessors(chain = true)
    private Predicate<TableFieldInfo> predicate;

    @SuppressWarnings("Duplicates")
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        KeyGenerator keyGenerator = new NoKeyGenerator();
        SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
        List<TableFieldInfo> fieldList = tableInfo.getFieldList();
        String insertSqlColumn = tableInfo.getKeyInsertSqlColumn(false) +
            this.filterTableFieldInfo(fieldList, predicate, TableFieldInfo::getInsertSqlColumn, EMPTY);
        String columnScript = LEFT_BRACKET + insertSqlColumn.substring(0, insertSqlColumn.length() - 1) + RIGHT_BRACKET;
        String insertSqlProperty = tableInfo.getKeyInsertSqlProperty(ENTITY_DOT, false) +
            this.filterTableFieldInfo(fieldList, predicate, i -> i.getInsertSqlProperty(ENTITY_DOT), EMPTY);
        insertSqlProperty = LEFT_BRACKET + insertSqlProperty.substring(0, insertSqlProperty.length() - 1) + RIGHT_BRACKET;
        String valuesScript = SqlScriptUtils.convertForeach(insertSqlProperty, "list", null, ENTITY, COMMA);
        String keyProperty = null;
        String keyColumn = null;
        // 表包含主键处理逻辑,如果不包含主键当普通字段处理
        if (StringUtils.isNotBlank(tableInfo.getKeyProperty())) {
            if (tableInfo.getIdType() == IdType.AUTO) {
                /* 自增主键 */
                keyGenerator = new Jdbc3KeyGenerator();
                keyProperty = tableInfo.getKeyProperty();
                keyColumn = tableInfo.getKeyColumn();
            } else {
                if (null != tableInfo.getKeySequence()) {
                    keyGenerator = TableInfoHelper.genKeyGenerator(getMethod(sqlMethod), tableInfo, builderAssistant);
                    keyProperty = tableInfo.getKeyProperty();
                    keyColumn = tableInfo.getKeyColumn();
                }
            }
        }
        String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
    }

    @Override
    public String getMethod(SqlMethod sqlMethod) {
        // 自定义 mapper 方法名
        return "insertBatchSomeColumn";
    }
}

新建一个自定义的sql注入器

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;

import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description: 自定义的sql 注入器
 */
public class EasysqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        //不要指定了update填充的字段 InsertBatchSomeColumn 这是批量新增数据,自选字段 insert 其实就是提供了一个使用 foreach 标签的 SQL 脚本
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
}

在配置类里将自定义注入器添加到bean容器

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description:
 */
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public EasysqlInjector easysqlInjector() {
        return new EasysqlInjector();
    }
}

需要导入依赖

		<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.3</version>
        </dependency>

自定义MyBaseMapper 接口，继承 BaseMapper接口，定义指定的方法 insertBatchSomeColumn

import cn.hutool.core.collection.CollUtil;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description:
 */
public interface MyBaseMapper<T> extends BaseMapper<T> {

    /**
     * 默认批次提交数量
     */
    int DEFAULT_BATCH_SIZE = 1000;

    /**
     * 批量新增数据,自选字段 insert. 自动按每批1000插入数据库
     * * 此填充不会填充 FieldFill.UPDATE 的字段。
     * * 注意数据库默认更新的字段也需要手工设置
     *
     * @param entityList
     * @return 插入的条数
     */
    @Transactional(rollbackFor = Exception.class)
    default Integer insertBatch(List<T> entityList) {
        return this.insertBatchSomeColumn(entityList, DEFAULT_BATCH_SIZE);
    }

    /**
     * 批量新增数据,自选字段 insert
     * 不会分批插入，需要分批请调用方法insertBatch或者 insertBatchSomeColumn(List<T> entityList, int size)
     * 此填充不会填充 FieldFill.UPDATE 的字段。
     * 注意数据库默认更新的字段也需要手工设置
     *
     * @param entityList 数据
     * @return 插入条数
     */
    int insertBatchSomeColumn(List<T> entityList);

    /**
     * 分批插入。每次插入
     *
     * @param entityList 原实体对象
     * @param size       分批大小
     * @return 总插入记录
     */
    @Transactional(rollbackFor = Exception.class)
    default int insertBatchSomeColumn(List<T> entityList, int size) {
        if (CollUtil.isEmpty(entityList)) {
            return 0;
        }
        List<List<T>> split = CollUtil.split(entityList, size);
        return split.stream().mapToInt(this::insertBatchSomeColumn).sum();
    }
}

需要使用的mapper接口以前是继承 BaseMapper的，现在继承 MyBaseMapper，就可以使用到配置的方法

@Mapper
public interface TUserMapper extends MyBaseMapper<TUser> {
}

业务类

import com.ung.mydb.entity.TUser;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

public interface TUserService extends IService<TUser> {

    boolean insertBatchSomeColumn(List<TUser> list);
}

业务实现类

import com.ung.mydb.entity.TUser;
import com.ung.mydb.mapper.TUserMapper;
import com.ung.mydb.service.TUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.util.List;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author ung
 * @since 2022-09-30
 */
@Transactional
@Service
public class TUserServiceImpl extends ServiceImpl<TUserMapper, TUser> implements TUserService {
    @Override
    public boolean insertBatchSomeColumn(List<TUser> list) {
        if (CollectionUtils.isEmpty(list)) {
            return false;
        }
        return baseMapper.insertBatchSomeColumn(list) == list.size();
    }
}

测试使用

package com.ung.mydb;

import cn.hutool.system.UserInfo;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ung.mydb.entity.TUser;
import com.ung.mydb.service.TUserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description:
 */
@SpringBootTest
public class MyDBApplicationTest {

    @Autowired
    TUserService service;

    @Test
    public void count() {
        int count = service.count();
        System.out.println(count);
    }

    @Test
    public void delete() {
        UpdateWrapper<TUser> tUserUpdateWrapper = new UpdateWrapper<>();
        tUserUpdateWrapper.isNotNull("id");
        boolean remove = service.remove(tUserUpdateWrapper);
        System.out.println(remove);
    }


    @Test
    public void insert() {
        ArrayList<TUser> tUsers = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            tUsers.add(new TUser("name_" + i));
        }
        long start = System.currentTimeMillis();
        //默认的方法
//        boolean result = service.saveBatch(tUsers);
        //mybatisplus配置的batchSave 方法
      boolean result = service.insertBatchSomeColumn(tUsers);

        System.out.println(result);
        long end = System.currentTimeMillis();
        System.out.println("time：" + (end - start));
    }
}

测试结果：

成功执行批量插入，可以自行测试对比速度

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@a898b0]
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@16185fe] will be managed by Spring
==>  Preparing: INSERT INTO t_user (user_name,nick_name,email,create_time,update_time,deleted_flag) VALUES (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?) , (?,?,?,?,?,?)
==> Parameters: name_0(String), name_0nickName(String), name_0email(String), 2022-09-30T15:30:12.407(LocalDateTime), 2022-09-30T15:30:12.407(LocalDateTime), 0(Integer), name_1(String), name_1nickName(String), name_1email(String), 2022-09-30T15:30:12.407(LocalDateTime), 2022-09-30T15:30:12.407(LocalDateTime), 0(Integer), name_2(String), name_2nickName(String), name_2email(String), 2022-09-30T15:30:12.407(LocalDateTime), 2022-09-30T15:30:12.407(LocalDateTime), 0(Integer), name_3(String), name_3nickName(String), name_3email(String), 2022-09-30T15:30:12.407(LocalDateTime), 2022-09-30T15:30:12.407(LocalDateTime), 0(Integer), name_4(String), name_4nickName(String), name_4email(String), 2022-09-30T15:30:12.407(LocalDateTime), 2022-09-30T15:30:12.407(LocalDateTime), 0(Integer)
<==    Updates: 5

自定义批量插入和修改方法

定义批量插入

新建自定义批量插入类 MyInsertBatchMethod 继承 AbstractMethod类

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description: 自定义批量插入方法
 */
public class MyInsertBatchMethod extends AbstractMethod {

    /**
     * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18);
     <script>
     insert into user(id, name, age) values
     <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
     #{item.id}, #{item.name}, #{item.age}
     </foreach>
     </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        final String sql = "<script>insert into %s %s values %s</script>";
        final String fieldSql = prepareFieldSql(tableInfo);
        final String valueSql = prepareValuesSql(tableInfo);
        final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
        //log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        //第三个参数 myInsertBatch  就是自定义的方法名 myInsertBatch
        return this.addInsertMappedStatement(mapperClass, modelClass, "myInsertBatch", sqlSource, new NoKeyGenerator(), null, null);
    }

    private String prepareFieldSql(TableInfo tableInfo) {
        StringBuilder fieldSql = new StringBuilder();
        fieldSql.append(tableInfo.getKeyColumn()).append(",");
        tableInfo.getFieldList().forEach(x -> fieldSql.append(x.getColumn()).append(","));
        fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
        fieldSql.insert(0, "(");
        fieldSql.append(")");
        return fieldSql.toString();
    }

    private String prepareValuesSql(TableInfo tableInfo) {
        final StringBuilder valueSql = new StringBuilder();
        valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
        valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
        tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
        valueSql.delete(valueSql.length() - 1, valueSql.length());
        valueSql.append("</foreach>");
        return valueSql.toString();
    }
}

自定义批量修改类

新建自定义批量插入类 MyUpdateBatchMethod继承 AbstractMethod类

import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description: 批量更新方法实现，条件为主键，选择性更新
 */
public class MyUpdateBatchMethod extends AbstractMethod {

    /**
     * update user set name = "a", age = 17 where id = 1;
     * update user set name = "b", age = 18 where id = 2;
     * <script>
     * <foreach collection="list" item="item" separator=";">
     * update user
     * <set>
     * <if test="item.name != null and item.name != ''">
     * name = #{item.name,jdbcType=VARCHAR},
     * </if>
     * <if test="item.age != null">
     * age = #{item.age,jdbcType=INTEGER},
     * </if>
     * </set>
     * where id = #{item.id,jdbcType=INTEGER}
     * </foreach>
     * </script>
     */
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";
        String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);
        String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
        String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);
        //log.debug("sqlResult----->{}", sqlResult);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
        //第三个参数 myUpdateBatchById  就是自定义的方法名 myInsertBatch
        return this.addUpdateMappedStatement(mapperClass, modelClass, "myUpdateBatchById", sqlSource);
    }
}

在自定义sql注入器中设置

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;

import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description: 自定义的sql 注入器
 */
public class EasysqlInjector extends DefaultSqlInjector {

    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        //不要指定了update填充的字段 InsertBatchSomeColumn 这是批量新增数据,自选字段 insert 其实就是提供了一个使用 foreach 标签的 SQL 脚本
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        //自定义批量插入
        methodList.add(new MyInsertBatchMethod());
        //自定义批量修改
        methodList.add(new MyUpdateBatchMethod());
        return methodList;
    }
}

自定义的Mapper接口定义方法

注意：不论是否自定义还是不是自定义的批量插入，都需要指定默认一次性最大容量；

如果数据太多，会导致 java.lang.OutOfMemoryError: Java heap space内容溢出；

可以参考 IService 接口实现逻辑

package com.ung.mydb.mapper;

import cn.hutool.core.collection.CollUtil;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description:
 */
public interface MyBaseMapper<T> extends BaseMapper<T> {

    /**
     * 默认批次提交数量
      如果不指定一次最大执行的容量，呐一次性拼接数据太多会导致问题
      java.lang.OutOfMemoryError: Java heap space
     */
    int DEFAULT_BATCH_SIZE = 1000;

    /**
     * 批量新增数据,自选字段 insert. 自动按每批1000插入数据库
     * * 此填充不会填充 FieldFill.UPDATE 的字段。
     * * 注意数据库默认更新的字段也需要手工设置
     *
     * @param entityList
     * @return 插入的条数
     */
    @Transactional(rollbackFor = Exception.class)
    default Integer insertBatch(List<T> entityList) {
        return this.insertBatchSomeColumn(entityList, DEFAULT_BATCH_SIZE);
    }

    /**
     * 批量新增数据,自选字段 insert
     * 不会分批插入，需要分批请调用方法insertBatch或者 insertBatchSomeColumn(List<T> entityList, int size)
     * 此填充不会填充 FieldFill.UPDATE 的字段。
     * 注意数据库默认更新的字段也需要手工设置
     *
     * @param entityList 数据
     * @return 插入条数
     */
    int insertBatchSomeColumn(List<T> entityList);

    /**
     * 分批插入。每次插入
     *
     * @param entityList 原实体对象
     * @param size       分批大小
     * @return 总插入记录
     */
    @Transactional(rollbackFor = Exception.class)
    default int insertBatchSomeColumn(List<T> entityList, int size) {
        if (CollUtil.isEmpty(entityList)) {
            return 0;
        }
        List<List<T>> split = CollUtil.split(entityList, size);
        return split.stream().mapToInt(this::insertBatchSomeColumn).sum();
    }


    /**
     * 自定义的插入方法
     */

    int myInsertBatch(@Param("list") List<T> entityList);


    /**
     * 自定义的修改方法 byId
     */
    int myUpdateBatchById(@Param("list") List<T> entityList);

}

业务接口

public interface TUserService extends IService<TUser> {

    boolean insertBatchSomeColumn(List<TUser> list);


    boolean myInsertBatch(List<TUser> list);


    boolean myUpdateBatchById(List<TUser> list);
}

实现类

import com.ung.mydb.entity.TUser;
import com.ung.mydb.mapper.TUserMapper;
import com.ung.mydb.service.TUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import java.util.List;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author ung
 * @since 2022-09-30
 */
@Transactional
@Service
public class TUserServiceImpl extends ServiceImpl<TUserMapper, TUser> implements TUserService {


    @Override
    public boolean insertBatchSomeColumn(List<TUser> list) {
        if (CollectionUtils.isEmpty(list)) {
            return false;
        }
        return baseMapper.insertBatchSomeColumn(list) == list.size();
    }

    @Override
    public boolean myInsertBatch(List<TUser> list) {
        if (CollectionUtils.isEmpty(list)) {
            return false;
        }
        return baseMapper.myInsertBatch(list) == list.size();
    }

    @Override
    public boolean myUpdateBatchById(List<TUser> list) {
        if (CollectionUtils.isEmpty(list)) {
            return false;
        }
        return baseMapper.myUpdateBatchById(list) == list.size();
    }
}

最后直接测试

package com.ung.mydb;

import cn.hutool.system.UserInfo;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.ung.mydb.entity.TUser;
import com.ung.mydb.service.TUserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.ArrayList;
import java.util.List;

/**
 * @author: wenyi
 * @create: 2022/9/30
 * @Description:
 */
@SpringBootTest
public class MyDBApplicationTest {

    @Autowired
    TUserService service;

    @Test
    public void count() {
        int count = service.count();

        System.out.println(count);
    }

    @Test
    public void delete() {
        UpdateWrapper<TUser> tUserUpdateWrapper = new UpdateWrapper<>();
        tUserUpdateWrapper.isNotNull("id");
        boolean remove = service.remove(tUserUpdateWrapper);
        System.out.println(remove);
    }


    @Test
    public void insert() {
        ArrayList<TUser> tUsers = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            tUsers.add(new TUser("name_" + i));
        }
        long start = System.currentTimeMillis();
        //默认的方法
//        boolean result = service.saveBatch(tUsers);//3529
        //mybatisplus配置的batchSave 方法
        boolean result = service.insertBatchSomeColumn(tUsers);//1402

        //自定义的批量方法
//        boolean result = service.myInsertBatch(tUsers);

        System.out.println(result);
        long end = System.currentTimeMillis();
        System.out.println("time：" + (end - start));
    }


    @Test
    public void update() {
        List<TUser> list = service.list(Wrappers.<TUser>lambdaQuery().between(TUser::getId, 100001, 100010));
        list.forEach(tUser -> {
            tUser.setUserName("更新了！！！" + tUser.getUserName());
        });
        //默认自带的
        boolean result = service.updateBatchById(list);
        //自定义的
//        boolean result = service.myUpdateBatchById(list);
        System.out.println(result);
    }

}

测试批量修改

默认的方法日志，也是相当于一条修改sql的模板，遍历参数执行；

相当于执行很多次

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@abff6b] will be managed by Spring
==> Preparing: UPDATE t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? WHERE id=?
==> Parameters: 更新了！！！更新了！！！更新了！！！name_0(String), name_0nickName(String), name_0email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100001(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_1(String), name_1nickName(String), name_1email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100002(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_2(String), name_2nickName(String), name_2email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100003(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_3(String), name_3nickName(String), name_3email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100004(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_4(String), name_4nickName(String), name_4email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100005(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_5(String), name_5nickName(String), name_5email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100006(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_6(String), name_6nickName(String), name_6email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100007(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_7(String), name_7nickName(String), name_7email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100008(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_8(String), name_8nickName(String), name_8email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100009(Integer)
==> Parameters: 更新了！！！更新了！！！更新了！！！name_9(String), name_9nickName(String), name_9email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100010(Integer)

自定义的修改方法

一条sql，直接拼接参数，一次执行

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1050f2]
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@abff6b] will be managed by Spring
==> Preparing: update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=? ; update t_user SET user_name=?, nick_name=?, email=?, create_time=?, update_time=?, deleted_flag=? where id=?
> Parameters: 更新了！！！更新了！！！更新了！！！更新了！！！name_0(String), name_0nickName(String), name_0email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100001(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_1(String), name_1nickName(String), name_1email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100002(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_2(String), name_2nickName(String), name_2email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100003(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_3(String), name_3nickName(String), name_3email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100004(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_4(String), name_4nickName(String), name_4email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100005(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_5(String), name_5nickName(String), name_5email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100006(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_6(String), name_6nickName(String), name_6email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100007(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_7(String), name_7nickName(String), name_7email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100008(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_8(String), name_8nickName(String), name_8email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100009(Integer), 更新了！！！更新了！！！更新了！！！更新了！！！name_9(String), name_9nickName(String), name_9email(String), 2022-09-30T13:58:40(LocalDateTime), 2022-09-30T13:58:40(LocalDateTime), 0(Integer), 100010(Integer)
< Updates: 1文章来源地址https://www.toymoban.com/news/detail-449884.html

到了这里，关于MyBatisPlus 批量插入问题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！