一、准备工作 测试环境:SpringBoot项目+mybatisPlus框架+Mysql数据库+Lombok 二、导入依赖 org.springframework.boot spring-
<dependency> <groupId>org.springframework.bootgroupId> <artifactId>spring-boot-starter-WEBartifactId> <version>2.5.3version> dependency> <dependency> <groupId>mysqlgroupId> <artifactId>mysql-connector-javaartifactId> <version>8.0.20version> dependency> <dependency> <groupId>org.projectlombokgroupId> <artifactId>lombokartifactId> <version>1.18.22version> dependency> <dependency> <groupId>com.baomidougroupId> <artifactId>mybatis-plus-boot-starterartifactId> <version>3.5.1version> dependency>
server: port: 8081spring: 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@NoArgsConstructorpublic class User implements Serializable { private Integer id; private String username; private String password;}
@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); }
@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); }
@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://blog.csdn.net/qq_59837950/article/details/127740560
--结束END--
本文标题: 数据库批量插入数据的三种方法
本文链接: https://www.lsjlt.com/news/376516.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-29
2024-04-28
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0