创建一个SpringBoot工程
<?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 http://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.3</version>
<relativePath/>
</parent>
<groupId>com.qbb</groupId>
<artifactId>mybatis_batch_insert</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>17</maven.compiler.source>
<maven.compiler.target>17</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--Mybatis依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>3.0.1</version>
</dependency>
<!--Mybatis-Plus依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
</project>
方式一：for循环的方式 编写bean、service、dao、测试类
service
void batchInsertByFor(User user);
service-impl
@Autowired
private UserMapper userMapper;
@Override
public void batchInsertByFor(User user) {
userMapper.batchInsertByFor(user);
}
dao
<insert id="batchInsertByFor">
insert into user
values (#{id}, #{username}, #{password})
</insert>
测试类
import com.qbb.Application;
import com.qbb.domain.User;
import com.qbb.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest(classes = Application.class)
public class BatchInsertByForTest {
@Autowired
private UserService userService;
@Test
public void forTest(){
long start = System.currentTimeMillis();
for(int i = 0 ;i < 10000; i++) {
User user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userService.batchInsertByFor(user);
}
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
}
结果：一万条数据总耗时 : 327582ms（我电脑比较垃圾.....,反正就是很耗时）
方式二：MyBatis以集合方式批量新增 编写bean、service、dao、测试类
service
void batchInsert(List<User> users);
service-impl
@Override
public void batchInsert(List<User> users) {
userMapper.batchInsert(users);
}
dao
<insert id="batchInsert">
INSERT INTO user (username, password)
VALUES
<foreach collection ="users" item="user" separator =",">
(#{user.username}, #{user.password})
</foreach>
</insert>
测试类
@Test
public void batchInsertTest() {
long start = System.currentTimeMillis();
List<User> userList = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
User user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userList.add(user);
}
userService.batchInsert(userList);
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时：" + (end - start) + "ms");
}
结果：一万条数据总耗时：2005ms（快了很多，推荐使用）
方式三：MyBatisPlus以集合方式批量新增 编写测试类
测试类
@Test
public void MPBatchInsertTest() {
long start = System.currentTimeMillis();
List<User> userList = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
User user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userList.add(user);
}
userService.saveBatch(userList);
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时：" + (end - start) + "ms");
}
结果：一万条数据总耗时：3693ms（快了很多，推荐使用）
方式四：MyBatis-Plus提供的InsertBatchSomeColumn方法 编写handle、config、测试类
handle
package com.qbb;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import java.util.List;
/**
* 批量插入处理器
*/
public class MPBatchHandle extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
// 注意：此SQL注入器继承了DefaultSqlInjector(默认注入器)，调用了DefaultSqlInjector的getMethodList方法，保留了mybatis-plus的自带方法
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
return methodList;
}
}
config
package com.qbb.config;
import com.qbb.MPBatchHandle;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisConfig {
@Bean
public MPBatchHandle mpBatchHandle(){
return new MPBatchHandle();
}
}
service
void insertBatchSomeColumn(List<User> userList);
service-impl
@Override
public void insertBatchSomeColumn(List<User> userList) {
userMapper.insertBatchSomeColumn(userList);
}
dao
public interface EasyBaseMapper<T> extends BaseMapper<T> {
/**
* 批量插入 仅适用于mysql
*
* @param entityList 实体列表
* @return 影响行数
*/
Integer insertBatchSomeColumn(Collection<T> entityList);
}
@Mapper
public interface UserMapper extends BaseMapper<User>,EasyBaseMapper<User> {
void batchInsert(@Param("users") List<User> users);
}
测试类
@Test
public void MPInsertBatchSomeColumnTest(){
long start = System.currentTimeMillis();
List<User> userList = new ArrayList<>();
User user;
for(int i = 0 ;i < 10000; i++) {
user = new User();
user.setUsername("name" + i);
user.setPassword("password" + i);
userList.add(user);
}
userService.insertBatchSomeColumn(userList);
long end = System.currentTimeMillis();
System.out.println("一万条数据总耗时：" + (end-start) + "ms" );
}
结果：一万条数据总耗时：2224ms（快了很多，推荐使用）
