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

标签：SpringBoot 批量 mybatis user import MyBatis org com stopWatch

1. 背景介绍

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

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

2. 方案介绍

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

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

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

/**
* 第一种方案，用 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 "操作完成";
}

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

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

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

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

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

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

/**
* 第二种方案，利用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 "操作完成";
}

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

优势：不用频繁访问数据库，一条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.

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

@Resource
private SqlSessionFactory sqlSessionFactory;

// 关闭session的自动提交
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    list.stream().forEach(user -> userMapper.saveInfo(user));
    // 提交数据
    sqlSession.commit();
    sqlSession.rollback();
} catch (Exception e) {
    sqlSession.rollback();
} finally {
    sqlSession.close();
}

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

4. 创建Springboot项目

4.1 选择Spring Initializr(springboot项目)

4.2 配置属性，完成点击next

4.3 项目启动类

4.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>

4.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

4.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;

4.7 项目结构

5. 方案测试

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

1. 通过postman调用接口

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

3. 数据库数据

5.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]

5.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 "操作完成";
    }

}

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();
            sqlSession.rollback();
        } catch (Exception e) {
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}

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>

7. 完整项目地址（Gitee）

标签：SpringBoot,批量,mybatis,user,import,MyBatis,org,com,stopWatch
From： https://www.cnblogs.com/liyhbk/p/17218503.html

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

1. 背景介绍

2. 方案介绍

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

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

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

4. 创建Springboot项目

4.1 选择Spring Initializr(springboot项目)

4.2 配置属性，完成点击next

4.3 项目启动类

4.4 Pom文件添加依赖

4.5 配置application.yml文件

4.6 导入数据库

4.7 项目结构

5. 方案测试

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

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

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

6. 部分代码

6.1 UserController

6.2 UserServiceImpl

6.3 UserMapper.xml

7. 完整项目地址（Gitee）

相关文章

赞助商

阅读排行

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

1. 背景介绍

2. 方案介绍

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

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

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

4. 创建Springboot项目

4.1 选择Spring Initializr(springboot项目)

4.2 配置属性，完成点击next

4.3 项目启动类

4.4 Pom文件添加依赖

4.5 配置application.yml文件

4.6 导入数据库

4.7 项目结构

5. 方案测试

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

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

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

6. 部分代码

6.1 UserController

6.2 UserServiceImpl

6.3 UserMapper.xml

7. 完整项目地址（Gitee）

相关文章

赞助商

阅读排行

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

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

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