• Mybatis的三种批量插入方式


    1. 循环插入

    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>
    </mapper>
    

    mapper接口:

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

    测试代码:

    @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);
    }
    

    测试代码:

    @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. 批处理

    测试代码:

    @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/> <!-- lookup parent from repository -->
    	</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>
    
    

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

    插入方式 10条 100条 500条 1000条
    循环插入 496ms 3330ms 15584ms 33755ms
    foreach标签 268ms 366ms 392ms 684ms
    批处理 222ms 244ms 364ms 426ms

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

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

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

  • 相关阅读:
    .NET Core 使用NPOI读取Excel返回泛型List集合
    C# 判别系统版本以及Win10的识别办法
    WPF 程序员休息数字时钟
    分享一个淘宝/天猫/京东/阿里 图片抓取工具
    记一次数据库同步经历(sql server 2008)
    datagridview 如何显示记载中
    关于如何解决bootstrap table 列 切换 刷新 高度不一样
    js 中 函数的返回值问题
    winform 实现定位
    winform 里 如何实现文件上传
  • 原文地址:https://www.cnblogs.com/seve/p/14654104.html
Copyright © 2020-2023  润新知