MyBatis与MyBatis-Plus批量插入实战指南

在实际项目中，批量插入数据是一个常见且重要的需求。本文将详细总结MyBatis和MyBatis-Plus常用的几种批量插入方式，并通过实际代码示例和运行结果，帮助大家更好地理解和应用。

一、准备工作

1. 导入pom.xml依赖

   <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <scope>runtime</scope>
   </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>
   
   <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <optional>true</optional>
   </dependency>
   

2. 配置yml文件

   server:
     port: 8080
   
   spring:
     datasource:
       username: mysql用户名
       password: mysql密码
       url: jdbc:mysql://localhost:3306/数据库名字?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");
       }
   }
   

2. 编写UserMapper接口

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

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
           <foreach collection="userList" item="user" separator=",">
               (#{user.username}, #{user.password})
           </foreach>
       </insert>
   </mapper>
   

4. 输出结果

   一万条数据总耗时：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