Mybatis 三种批量插入数据方式

谁践踏了优雅 2023-01-13 05:50 82阅读 0赞

1. 循环插入

mapper.xml：

mapper接口：

public interface StudentMapper {
        int insert(Student student);
    }

测试代码：

//java项目www.fhadmin.org
    @SpringBootTest
    class DemoApplicationTests {
    	@Resource
    	private StudentMapper studentMapper;
    
    	@Test
    	public void testInsert(){
    		//数据生成
    		List<Student> studentList = createData(100);
    
    		//循环插入
    		long start = System.currentTimeMillis();
    		studentList.stream().forEach(student -> studentMapper.insert(student));
    		System.out.println(System.currentTimeMillis() - start);
    	}
    
    	private List<Student> createData(int size){
    		List<Student> studentList = new ArrayList<>();
    		Student student;
    		for(int i = 0; i < size; i++){
    			student = new Student();
    			student.setName("小王" + i);
    			student.setAge(18);
    			student.setClassId(1);
    			student.setPhone("1585xxxx669");
    			student.setAddress("未知");
    			studentList.add(student);
    		}
    
    		return studentList;
    	}
    }

2. foreach标签

mapper.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.buhe.demo.mapper.StudentMapper">
      <insert id="insert" parameterType="Student">
        INSERT INTO tb_student (name, age, phone, address, class_id) VALUES (#{name},#{age},#{phone},#{address},#{classId})
      </insert>
    
      
      <insert id="insertBatch">
        INSERT INTO tb_student (name, age, phone, address, class_id) VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.name},#{item.age},#{item.phone},#{item.address},#{item.classId})
        </foreach>
      </insert>
    </mapper>

mapper接口：

public interface StudentMapper {
        int insert(Student student);
    
        int insertBatch(List<Student> studentList);
    }

测试代码：

//java项目www.fhadmin.org
    @SpringBootTest
    class DemoApplicationTests {
    	@Resource
    	private StudentMapper studentMapper;
    
    	@Test
    	public void testInsertByForeachTag(){
    		//数据生成
    		List<Student> studentList = createData(100);
    
    		//使用foreach标签，拼接SQL插入
    		long start = System.currentTimeMillis();
    		studentMapper.insertBatch(studentList);
    		System.out.println(System.currentTimeMillis() - start);
    	}
    
    
    	private List<Student> createData(int size){
    		List<Student> studentList = new ArrayList<>();
    		Student student;
    		for(int i = 0; i < size; i++){
    			student = new Student();
    			student.setName("小王" + i);
    			student.setAge(18);
    			student.setClassId(1);
    			student.setPhone("1585xxxx669");
    			student.setAddress("未知");
    			studentList.add(student);
    		}
    
    		return studentList;
    	}
    }

3. 批处理

测试代码：

//java项目www.fhadmin.org
    @SpringBootTest
    class DemoApplicationTests {
    	@Autowired
    	private SqlSessionFactory sqlSessionFactory;
    
    	@Test
    	public void testInsertBatch(){
    		//数据生成
    		List<Student> studentList = createData(100);
    
                    //使用批处理
    		long start = System.currentTimeMillis();
    		SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
    		StudentMapper studentMapperNew = sqlSession.getMapper(StudentMapper.class);
    		studentList.stream().forEach(student -> studentMapperNew.insert(student));
    		sqlSession.commit();
    		sqlSession.clearCache();
    		System.out.println(System.currentTimeMillis() - start);
    	}
    
    	private List<Student> createData(int size){
    		List<Student> studentList = new ArrayList<>();
    		Student student;
    		for(int i = 0; i < size; i++){
    			student = new Student();
    			student.setName("小王" + i);
    			student.setAge(18);
    			student.setClassId(1);
    			student.setPhone("1585xxxx669");
    			student.setAddress("未知");
    			studentList.add(student);
    		}
    
    		return studentList;
    	}
    }

三种方式的对比

MySQL服务器版本：5.6.4

其他依赖版本如下：

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.4.4</version>
    		<relativePath/> 
    	</parent>
    	<groupId>com.buhe</groupId>
    	<artifactId>demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>demo</name>
    	<description>Demo project for Spring Boot</description>
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.41</version>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>1.3.1</version>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    
    		<resources>
    			<resource>
    				<directory>src/main/java</directory>
    				<includes>
    					<include>**/*.xml</include>
    				</includes>
    			</resource>
    		</resources>
    	</build>
    
    </project>

![wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==][wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw]

三种插入方式在不同数据量下的表现，测试结果：

<table> 
 <thead> 
  <tr> 
   <th>插入方式</th> 
   <th>10条</th> 
   <th>100条</th> 
   <th>500条</th> 
   <th>1000条</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>循环插入</td> 
   <td>496ms</td> 
   <td>3330ms</td> 
   <td>15584ms</td> 
   <td>33755ms</td> 
  </tr> 
  <tr> 
   <td>foreach标签</td> 
   <td>268ms</td> 
   <td>366ms</td> 
   <td>392ms</td> 
   <td>684ms</td> 
  </tr> 
  <tr> 
   <td>批处理</td> 
   <td>222ms</td> 
   <td>244ms</td> 
   <td>364ms</td> 
   <td>426ms</td> 
  </tr> 
 </tbody> 
</table>

三种方式中，批处理的方式效率是最高的，尤其是在数据量大的情况下尤为明显。

其次是foreach标签，foreach标签是通过拼接SQL语句的方式完成批量操作的。但是当拼接的SQL过多，导致SQL大小超过了MySQL服务器中**max\_allowed\_packet**变量的值时，会导致操作失败，抛出PacketTooBigException异常。

最后是循环插入的方式，这种方式在数据量小的时候可以使用，在数据量大的情况下效率要低很多。

[wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw]: