一、准备工作
测试环境:SpringBoot项目+MybatisPlus框架+MySQL数据库+Lombok文章来源:https://www.toymoban.com/news/detail-627116.html
二、导入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.5.3</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
三、yml配置文件
server:
port: 8081
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
username: root
password: 123456
url: jdbc:mysql://localhost:3306/mysql?useSSL=false&characterEncoding=utf-8&useUnicode=true&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
##rewriteBatchedStatements=true 开启批处理模式
四、实体类(数据库表要有对应字段)
package com.cy.model;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User implements Serializable {
private Integer id;
private String username;
private String password;
}
五、测试
- 方法一:for循环插入(单条)(总耗时:n分钟,每次都要获取连接Connection、释放连接和关闭资源等操作,比较耗时,这里就没测了)
@RequestMapping("/test3") //单个插入(慢)
public String test3(){
long startTime=System.currentTimeMillis();
for(int i=0;i<100000;i++){
userService.save(new User(i+1,"张三","123456"));
}
long endTime=System.currentTimeMillis();
long result=endTime-startTime;
return "总耗时:"+(result/1000);
}
- 方法二:批量插入saveBatch(4~7秒,这里用到了MybatisPLus的saveBatch批量插入方法,实际也是for循环单条插入,只不过它是利用分片处理batchSize=1000和分批提交事务,从而提高了性能,不用在Connection上消费性能了)(推荐)
@RequestMapping("/test2") //批量插入saveBatch(分片处理batchSize=1000,分批提交事务) (次快)
public String test2(){
List<User> userList=new ArrayList<>();
long startTime=System.currentTimeMillis();
for(int i=0;i<100000;i++){
userList.add(new User(i+1,"张三","123456"));
}
userService.saveBatch(userList);
long endTime=System.currentTimeMillis();
long result=endTime-startTime;
return "总耗时:"+(result/1000);
}
- 方法三:循环插入+开启批处理模式(4~7秒,开启批处理模式,关闭自动提交事务,共同用一个sqlsession,单个插入性能得到提升,由于用同一个sqlsession,极大的减少了对资源操作和对事务处理的时间,很好地提高了性能)(推荐)
@Resource
private SqlSessionFactory sqlSessionFactory;
@RequestMapping("/test1") //批量插入(最快)---循环插入+批处理模式 ( 开启批处理模式 rewriteBatchedStatements=true )
public String test1(){
//开启批处理模式,关闭自动提交事务
SqlSession sqlSession= sqlSessionFactory.openSession(ExecutorType.BATCH,false);
long startTime=System.currentTimeMillis();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
for(int i=0;i<100000;i++){
userMapper.insert(new User(i+1,"张三","123456"));
}
sqlSession.commit();//一次性提交事务
sqlSession.close();//关闭资源
long endTime=System.currentTimeMillis();
long result=endTime-startTime;
return "总耗时:"+(result/1000);
}
以上就是全部内容,希望对大家有帮助!文章来源地址https://www.toymoban.com/news/detail-627116.html
到了这里,关于数据库批量插入数据的三种方法的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!