Springboot+MyBatis批量插入数据的三种方式
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>
