SpringBoot+MyBatis批量插入数据的三种方式

原文链接：SpringBoot+MyBatis批量插入数据的三种方式

1. 背景介绍

在开发过程中，我们经常会遇到往数据库表中插入大量数据的场景，比如excel批量导入数据。那么该如何快速地插入数据呢？

我们可以考虑使用批量插入来实现，实测100000条数据添加，后附具体实现代码。

2. 方案介绍

2.1 第一种方案，用 for语句循环插入（不推荐）

用一个 for 循环，把数据一条一条地插入。

insert into t_user values (?, ?, ?, ?, ?)
1

/**
* 第一种方案，用 for语句循环插入 10万 条数据
*/
@GetMapping("/test1")
public String test1(int count) {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    for (int i = 0; i < count; i++) {
        User user = new User();
        user.setName("方案1测试" + i);
        user.setGender("男");
        user.setUsername("方案1测试");
        user.setPassword("方案1测试");
        user.setRemark("方案1测试");
        userService.saveInfo(user);
    }
    stopWatch.stop();
    System.out.println("第一种方案，用 for语句循环插入耗时：" + stopWatch.getTotalTimeMillis());
    return "操作完成";
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

优势：JDBC 中的 PreparedStatement 有预编译功能，预编译之后会缓存起来。

之后SQL执行会比较快，且 JDBC可以开启批处理，这个批处理执行非常给力。

劣势：这种方式插入大量数据时，效率非常底下，不推荐。很多时候我们的 SQL 服务器和应用服务器可能并不是同一台，所以必须要考虑网络 IO。

如果网络 IO 比较费时间的话，那么可能会拖慢 SQL 执行的速度。

2.2 第二种方案，利用mybatis的foreach来实现循环插入（不推荐）

insert into t_user values (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?) , (?, ?, ?, ?, ?)
1

/**
* 第二种方案，利用mybatis的foreach来实现循环插入 10万 条数据
*/
@GetMapping("/test2")
public String test2(int count) {
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    List<User> list = new ArrayList<>();
    for (int i = 0; i < count; i++) {
        User user = new User();
        user.setName("方案2测试" + i);
        user.setGender("男");
        user.setUsername("方案2测试");
        user.setPassword("方案2测试");
        user.setRemark("方案2测试");
        list.add(user);
    }
    userService.saveList(list);
    stopWatch.stop();
    System.out.println("第二种方案，利用mybatis的foreach来实现循环插入耗时：" + stopWatch.getTotalTimeMillis());
    return "操作完成";
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<insert id="saveList" parameterType="list">
    insert into t_user values
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.gender}, #{item.username}, #{item.password}, #{item.remark})
    </foreach>
</insert>
1
2
3
4
5
6

优势：不用频繁访问数据库，一条sql搞定，效率比较高。

劣势：一当数据量太大时，会出现拼接的sql语句超长而执行失败，所以当数据量太大时，也不推荐。

二是 SQL 太长了，甚至可能需要分片后批量处理。

三是无法充分发挥 PreparedStatement 预编译的优势，SQL 要重新解析且无法复用

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,879,714 > 4,194,304).
    You can change this value on the server by setting the 'max_allowed_packet' variable.
1
2

2.3 第三种方案，使用sqlSessionFactory实现批量插入（推荐）

@Resource
private SqlSessionFactory sqlSessionFactory;
1
2

// 关闭session的自动提交
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    list.stream().forEach(user -> userMapper.saveInfo(user));
    // 提交数据
    sqlSession.commit();
} catch (Exception e) {
    sqlSession.rollback();
} finally {
    sqlSession.close();
}
1
2
3
4
5
6
7
8
9
10
11
12

优势：这种方式可以说是集第一种和第二种方式的优点于一身，既可以提高运行效率，又可以保证大数据量时执行成功，大数据量时推荐使用这种方式。

3. 创建Springboot项目

3.1 选择Spring Initializr(springboot项目)

3.2 配置属性，完成点击next

3.3 项目启动类

3.4 Pom文件添加依赖

<?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.7.5</version>
        <relativePath/>
    </parent>

    <groupId>com.liyh</groupId>
    <artifactId>springboot_mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot_mybatis</name>
    <description>springboot_mybatis</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!--jdbc 数据库连接-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--引入阿里数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.6</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

3.5 配置application.yml文件

# 配置端口
server:
  port: 8091

spring:
  # 配置数据源
  datasource:
    url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

# mybatis相关配置
mybatis:
  mapper-locations: classpath*:mapper/*.xml   #指定mapper映射文件路径
  type-aliases-package: com.liyh.mybatis.entity  # 别名
  configuration:
    map-underscore-to-camel-case: true

#打印sql，保存到文件
logging:
  level:
    com.liyh.mybatis.mapper: debug
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.6 导入数据库

CREATE TABLE `t_user`  (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '批量导入用户表' ROW_FORMAT = COMPACT;
1
2
3
4
5
6
7

3.7 项目结构

4. 方案测试

4.1 测试第一种方案，用 for语句循环插入 10万 条数据

1. 通过postman调用接口

2. 查看耗时情况（20万条数据需要10几分钟！！！）

3. 数据库数据

4.2 测试第二种方案，用 for语句循环插入 10万 条数据

1. 通过postman调用接口

2. 查看耗时情况（测试2000，10000，50000，100000条数据）

当数据量达到5万条时，报错了！！！（单批次少量数据效率还可以）

### Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,238,915 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
; Packet for query is too large (5,238,915 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.; nested exception is com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,238,915 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.] with root cause

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (5,238,915 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:107) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354) ~[mysql-connector-j-8.0.31.jar:8.0.31]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_201]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_201]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_201]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_201]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.9.jar:3.5.9]
	at com.sun.proxy.$Proxy72.execute(Unknown Source) ~[na:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.9.jar:3.5.9]
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.9.jar:3.5.9]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.3 测试第三种方案，使用sqlSessionFactory实现批量插入 20万 条数据

1. 通过postman调用接口

2. 查看耗时情况（20万条数据大概17秒）

3. 数据库数据

6. 部分代码

6.1 UserController

package com.liyh.mybatis.controller;

import com.liyh.mybatis.entity.User;
import com.liyh.mybatis.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StopWatch;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * 测试接口
 *
 * @Author: liyh
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    UserService userService;

    /**
     * 第一种方案，用 for语句循环插入 10万 条数据
     */
    @GetMapping("/test1")
    public String test1(int count) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setName("方案1测试" + i);
            user.setGender("男");
            user.setUsername("方案1测试");
            user.setPassword("方案1测试");
            user.setRemark("方案1测试");
            userService.saveInfo(user);
        }
        stopWatch.stop();
        System.out.println("第一种方案，用 for语句循环插入耗时：" + stopWatch.getTotalTimeMillis());
        return "操作完成";
    }

    /**
     * 第二种方案，利用mybatis的foreach来实现循环插入 10万 条数据
     */
    @GetMapping("/test2")
    public String test2(int count) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<User> list = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setName("方案2测试" + i);
            user.setGender("男");
            user.setUsername("方案2测试");
            user.setPassword("方案2测试");
            user.setRemark("方案2测试");
            list.add(user);
        }
        userService.saveList(list);
        stopWatch.stop();
        System.out.println("第二种方案，利用mybatis的foreach来实现循环插入耗时：" + stopWatch.getTotalTimeMillis());
        return "操作完成";
    }

    /**
     * 第三种方案，使用sqlSessionFactory实现批量插入 10万 条数据
     */
    @GetMapping("/test3")
    public String test3(int count) {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<User> list = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            User user = new User();
            user.setName("方案3测试" + i);
            user.setGender("男");
            user.setUsername("方案3测试");
            user.setPassword("方案3测试");
            user.setRemark("方案3测试");
            list.add(user);
        }
        userService.saveBeach(list);
        stopWatch.stop();
        System.out.println("第三种方案，使用sqlSessionFactory实现批量插入：" + stopWatch.getTotalTimeMillis());
        return "操作完成";
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

6.2 UserServiceImpl

package com.liyh.mybatis.service.impl;

import com.liyh.mybatis.entity.User;
import com.liyh.mybatis.mapper.UserMapper;
import com.liyh.mybatis.service.UserService;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

/**
 * 用户业务实现类
 *
 * @Author: liyh
 */
@Service
@Transactional
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public void saveInfo(User user) {
        userMapper.saveInfo(user);
    }

    @Override
    public void saveList(List<User> list) {
        userMapper.saveList(list);
    }

    @Override
    public void saveBeach(List<User> list) {
        // ExecutorType.SIMPLE: 这个执行器类型不做特殊的事情。它为每个语句的执行创建一个新的预处理语句。
        // ExecutorType.REUSE: 这个执行器类型会复用预处理语句。
        // ExecutorType.BATCH: 这个执行器会批量执行所有更新语句,如果 SELECT 在它们中间执行还会标定它们是 必须的,来保证一个简单并易于理解的行为。

        // 关闭session的自动提交
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            list.stream().forEach(user -> userMapper.saveInfo(user));
            // 提交数据
            sqlSession.commit();
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

6.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.liyh.mybatis.mapper.UserMapper">

    <insert id="saveInfo">
        insert into t_user
        values (#{name}, #{gender}, #{username}, #{password}, #{remark})
    </insert>

    <insert id="saveList" parameterType="list">
        insert into t_user values
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.gender}, #{item.username}, #{item.password}, #{item.remark})
        </foreach>
    </insert>


</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18