MyBatis批量插入的五种方式

最新推荐文章于 2025-05-06 10:27:14 发布

北执南念

最新推荐文章于 2025-05-06 10:27:14 发布

阅读量1.6w

点赞数 34

分类专栏： MyBatis 文章标签： mybatis

本文链接：https://blog.youkuaiyun.com/qq_45525848/article/details/137725872

版权

MyBatis 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍了在SpringBoot项目中，使用MyBatis和MyBatis-Plus的五种方法进行批量插入，包括For循环、手动提交、集合方式以及MyBatis-Plus的SaveBatch和InsertBatchSomeColumn，对比了不同方式的性能和适用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MyBatis批量插入的五种方式:

在这里插入图片描述

一、准备工作

1、导入pom.xml依赖

    <dependencies>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <!-- MySQL驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
 
        <!--Mybatis依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
 
        <!--Mybatis-Plus依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
 
        <!-- Lombok依赖 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
 
    </dependencies>

2、配置yml文件

server:
  port: 8080
 
spring:
  datasource:
    username: root(用户名)
    password: root(密码)
    url: jdbc:mysql://localhost:3306/test(数据库名字)?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
 
mybatis:
  mapper-locations: classpath:mapping/*.xml

3、公用的User类

@Data
public class User {
 
    private int id;
    private String username;
    private String password;
}

二、MyBatis利用For循环批量插入

1、编写UserService服务类，测试一万条数据耗时情况

@Service
public class UserService {
 
    @Resource
    private UserMapper userMapper;
 
    public void InsertUsers(){
        long start = System.currentTimeMillis();
        for(int i = 0 ;i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userMapper.insertUsers(user);
        }
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
    }
 
}

2、编写UserMapper接口

@Mapper
public interface UserMapper {
 
    Integer insertUsers(User user);
}

3、编写UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ithuang.demo.mapper.UserMapper">
    <insert id="insertUsers">
        INSERT INTO user (username, password)
        VALUES(#{username}, #{password})
    </insert>
</mapper>

4、进行单元测试

@SpringBootTest
class DemoApplicationTests {
 
    @Resource
    private UserService userService;
 
    @Test
    public void insert(){
        userService.InsertUsers();
    }
 
}

5、结果输出

一万条数据总耗时：26348ms

三、MyBatis的手动批量提交

1、其他保持不变，Service层作稍微的变化

@Service
public class UserService {
 
    @Resource
    private UserMapper userMapper;
 
    @Resource
    private SqlSessionTemplate sqlSessionTemplate;
 
    public void InsertUsers(){
        //关闭自动提交
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        long start = System.currentTimeMillis();
        for(int i = 0 ;i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userMapper.insertUsers(user);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
    }
 
}

2、结果输出

一万条数据总耗时：24516ms

四、MyBatis以集合方式批量新增

1、编写UserService服务类

@Service
public class UserService {
 
    @Resource
    private UserMapper userMapper;
 
    public void InsertUsers(){
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        User user;
        for(int i = 0 ;i < 10000; i++) {
            user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        userMapper.insertUsers(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
    }
 
}

3、编写UserMapper接口

@Mapper
public interface UserMapper {
 
    Integer insertUsers(List<User> userList);
}

4、编写UserMapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ithuang.demo.mapper.UserMapper">
    <insert id="insertUsers">
        INSERT INTO user (username, password)
        VALUES
        <foreach collection ="userList" item="user" separator =",">
            (#{user.username}, #{user.password})
        </foreach>
    </insert>
</mapper>

以下完整且规范的写法，注：如果需要MySQL支持批量操作需要在yml的url配置中新增allowMultiQueries=true，支持以;分隔批量执行SQL。

例如：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?allowMultiQueries=true&useSSL=false
    username: 
    password:

完整代码：

<insert id="insertUserBatch">
        <foreach collection ="list" item="item" separator =";">
            INSERT INTO user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="item.id != null and item.id != ''">id,</if>
                <if test="item.username != null and item.username != ''">username,</if>
                <if test="item.password != null and item.password != ''">password,</if>
                <if test="item.sex != null and item.sex != ''">sex,</if>
                <if test="item.age != null">age,</if>
                <if test="item.address != null and item.address != ''">address,</if>
                <if test="item.createTime != null">create_time,</if>
            </trim>
            VALUES
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="item.id != null and item.id != ''">#{item.id},</if>
                <if test="item.username != null and item.username != ''">#{item.username},</if>
                <if test="item.password != null and item.password != ''">#{item.password},</if>
                <if test="item.sex != null and item.sex != ''">#{item.sex},</if>
                <if test="item.age != null">#{item.age},</if>
                <if test="item.address != null and item.address != ''">#{item.address},</if>
                <if test="item.createTime != null">#{item.createTime},</if>
            </trim>
        </foreach>
    </insert>

5、输出结果

一万条数据总耗时：521ms

五、MyBatis-Plus提供的SaveBatch方法

1、编写UserService服务

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> {
 
    public void InsertUsers(){
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        User user;
        for(int i = 0 ;i < 10000; i++) {
            user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        saveBatch(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
    }
}

2、编写UserMapper接口

@Mapper
public interface UserMapper extends BaseMapper<User> {
 
}

3、单元测试结果

一万条数据总耗时：24674ms

注：

这边我没有开启rewriteBatchedStatements=true，所以会非常慢，开启的话我实测过，耗时为707ms，这个参数的作用是启用批处理语句的重写功能，这对提高使用JDBC批量更新语句的性能有很大帮助。

MyBatis-Plus的SaveBatch方法默认使用JDBC的addBatch()和executeBatch()方法实现批量插入。但是部分数据库的JDBC驱动并不支持addBatch()，这样每次插入都会发送一条SQL语句，严重影响了批量插入的性能。设置rewriteBatchedStatements=true后，MyBatis-Plus会重写插入语句，将其合并为一条SQL语句，从而减少网络交互次数，提高批量插入的效率。

需要加在yml配置文件中：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useSSL=false
    username: root
    password: root

六、MyBatis-Plus提供的InsertBatchSomeColumn方法

1、编写EasySqlInjector 自定义类

public class EasySqlInjector extends DefaultSqlInjector {
 
 
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        // 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
 
}

2、定义核心配置类注入此Bean

@Configuration
public class MybatisPlusConfig {
 
    @Bean
    public EasySqlInjector sqlInjector() {
        return new EasySqlInjector();
    }
}

3、编写UserService服务类

public class UserService{
 
    @Resource
    private UserMapper userMapper;
    public void InsertUsers(){
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        User user;
        for(int i = 0 ;i < 10000; i++) {
            user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        userMapper.insertBatchSomeColumn(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
    }
}

4、编写EasyBaseMapper接口

public interface EasyBaseMapper<T> extends BaseMapper<T> {
    /**
     * 批量插入 仅适用于mysql
     *
     * @param entityList 实体列表
     * @return 影响行数
     */
    Integer insertBatchSomeColumn(Collection<T> entityList);
}

5、编写UserMapper接口

@Mapper
public interface UserMapper<T> extends EasyBaseMapper<User> {
    
}

6、单元测试结果

一万条数据总耗时：575ms