Mybatis-Plus大批量插入数据到MySQL

embedded/2024/12/22 15:18:58/

MyBatis-Plus的saveBatch方法

@GetMapping("/save1")
public void save1() {// 数据准备List<MallOrder> orderList = getMallOrderList();// mybatis-pluslong start = System.currentTimeMillis();mallOrderService.saveBatch(orderList);System.out.println("mybatis-plus的【savaBatch】插入数据，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

MyBatis-Plus的xml方式

@GetMapping("/save2")
public void save2() {// 数据准备List<MallOrder> orderList = getMallOrderList();// xmllong start = System.currentTimeMillis();mallOrderService.saveBatchXml(orderList);System.out.println("mybatis-plus的【xml拼接sql】插入数据，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

<insert id="saveBatch">insert into mall_order (order_id,customer_id,order_status,payment_method,total_amount,shipping_fee,coupon_discount,order_date,payment_time,shipping_address,receiver_name,receiver_phone)values<foreach collection="orderList" item="item" separator=",">(#{item.orderId},#{item.customerId},#{item.orderStatus},#{item.paymentMethod},#{item.totalAmount},#{item.shippingFee},#{item.couponDiscount},#{item.orderDate},#{item.paymentTime},#{item.shippingAddress},#{item.receiverName},#{item.receiverPhone})</foreach>
</insert>

MyBatis-Plus的批量插入器

@GetMapping("/save3")
public void save3() {// 数据准备List<MallOrder> orderList = getMallOrderList();// insertBatchSomeColumnlong start = System.currentTimeMillis();mallOrderService.insertBatchSomeColumn(orderList);System.out.println("mybatis-plus的【insertBatchSomeColumn】插入数据，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

package com.qiangesoft.batchsave.config;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;/*** sql注入器** @author qiangesoft* @date 2024-04-11*/
public class InsertBatchSqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {// super.getMethodList() 保留 Mybatis Plus 自带的方法List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);// 添加自定义方法：批量插入，方法名为 insertBatchSomeColumn// bean mapper中的方法名也是insertBatchSomeColumn 须和内部定义好的方法名保持一致。methodList.add(new InsertBatchSomeColumn());return methodList;}}

package com.qiangesoft.batchsave.config;import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** mybatis-plus配置** @author qiangesoft* @date 2024-04-11*/
@Configuration
public class MybatisPlusConfig {@Beanpublic InsertBatchSqlInjector insertBatchSqlInjector() {return new InsertBatchSqlInjector();}}
package com.qiangesoft.batchsave.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qiangesoft.batchsave.entity.MallOrder;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** <p>* 订单信息表 Mapper 接口* </p>** @author qiangesoft* @since 2024-05-07*/
public interface MallOrderMapper extends BaseMapper<MallOrder> {/*** 批量插入 仅适用于mysql** @param orderList*/Integer insertBatchSomeColumn(List<MallOrder> orderList);
}

SqlSession手动提交

@GetMapping("/save4")
public void save4() {// 数据准备List<MallOrder> orderList = getMallOrderList();// 手动提交long start = System.currentTimeMillis();mallOrderService.manualCommit(orderList);System.out.println("sqlSession的【手动提交】插入数据100000条，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

@Autowired
private SqlSessionFactory sqlSessionFactory;@Override
public void manualCommit(List<MallOrder> orderList) {// 关闭自动提交SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);MallOrderMapper mallOrderMapper = sqlSession.getMapper(MallOrderMapper.class);for (MallOrder mallOrder : orderList) {mallOrderMapper.insert(mallOrder);}sqlSession.commit();sqlSession.clearCache();sqlSession.close();
}

循环执行MyBatis-Plus的save方法

@GetMapping("/save5")
public void save5() {// 数据准备List<MallOrder> orderList = getMallOrderList();// 循环插入long start = System.currentTimeMillis();for (MallOrder mallOrder : orderList) {mallOrderService.save(mallOrder);}System.out.println("mybatis-plus的【循环】插入数据100000条，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

Statement批量执行

@GetMapping("/save6")
public void save6() {// 数据准备List<MallOrder> orderList = getMallOrderList();// statement.executeBatchlong start = System.currentTimeMillis();mallOrderService.executeBatch(orderList);System.out.println("statement的【executeBatch】插入数据100000条，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

@Override
public void executeBatch(List<MallOrder> orderList) {String sql = "insert into mall_order (" +"customer_id," +"order_status," +"payment_method," +"total_amount," +"shipping_fee," +"coupon_discount," +"order_date," +"payment_time," +"shipping_address," +"receiver_name," +"receiver_phone) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";SqlSession sqlSession = null;Connection connection = null;PreparedStatement statement = null;try {sqlSession = sqlSessionFactory.openSession();connection = sqlSession.getConnection();connection.setAutoCommit(false);statement = connection.prepareStatement(sql);for (MallOrder mallOrder : orderList) {// 主键自增不设置
//                statement.setLong(0, mallOrder.getOrderId());statement.setLong(1, mallOrder.getCustomerId());statement.setInt(2, mallOrder.getOrderStatus());statement.setInt(3, mallOrder.getPaymentMethod());statement.setBigDecimal(4, mallOrder.getTotalAmount());statement.setBigDecimal(5, mallOrder.getShippingFee());statement.setBigDecimal(6, mallOrder.getCouponDiscount());statement.setObject(7, mallOrder.getOrderDate());statement.setObject(8, mallOrder.getPaymentTime());statement.setString(9, mallOrder.getShippingAddress());statement.setString(10, mallOrder.getReceiverName());statement.setString(11, mallOrder.getReceiverPhone());statement.addBatch();}statement.executeBatch();connection.commit();} catch (SQLException e) {throw new RuntimeException(e);} finally {if (statement != null) {try {statement.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (connection != null) {try {connection.close();} catch (SQLException e) {throw new RuntimeException(e);}}if (sqlSession != null) {sqlSession.close();}}
}

MyBatis-Plus的saveBatch方法+异步任务

@GetMapping("/save7")
public void save7() {// 数据准备List<MallOrder> orderList = getMallOrderList();// 异步任务处理long start = System.currentTimeMillis();mallOrderService.saveBatchAsync(orderList);System.out.println("mybatis-plus的【异步任务处理】插入数据100000条，耗时：" + (System.currentTimeMillis() - start) + "ms");
}

@Autowired
private ThreadPoolTaskExecutor threadPoolTaskExecutor;
@Autowired
private PlatformTransactionManager transactionManager;@Override
public void saveBatchAsync(List<MallOrder> orderList) {int count = orderList.size();// 每批次插入的数据量int pageSize = 1000;// 线程数int threadNum = count % pageSize == 0 ? (count / pageSize) : (count / pageSize + 1);CountDownLatch countDownLatch = new CountDownLatch(threadNum);for (int i = 0; i < threadNum; i++) {int startIndex = i * pageSize;int endIndex = Math.min(count, (i + 1) * pageSize);List<MallOrder> subList = orderList.subList(startIndex, endIndex);threadPoolTaskExecutor.execute(() -> {DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();TransactionStatus status = transactionManager.getTransaction(transactionDefinition);try {// 保存数据this.saveBatch(subList);transactionManager.commit(status);} catch (Exception exception) {transactionManager.rollback(status);throw exception;} finally {countDownLatch.countDown();}});}try {countDownLatch.await();} catch (InterruptedException e) {throw new RuntimeException(e);}
}

测试

CREATE TABLE `mall_order`
(`order_id`         BIGINT         NOT NULL AUTO_INCREMENT COMMENT '订单ID（主键）',`customer_id`      BIGINT         NOT NULL COMMENT '客户ID（关联customer表）',`order_status`     tinyint(4)     NOT NULL DEFAULT 1 COMMENT '订单状态 1-待支付 2-已支付 3-待发货 4-已发货 5-已完成 6-已取消',`payment_method`   tinyint(4)     NULL     DEFAULT null COMMENT '支付方式; 1-现金 2-支付宝 3-微信 4-银行卡',`total_amount`     DECIMAL(10, 2) NOT NULL COMMENT '订单总金额',`shipping_fee`     DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '运费',`coupon_discount`  DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT '优惠券减免金额',`order_date`       DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单日期',`payment_time`     DATETIME                DEFAULT NULL COMMENT '支付时间',`shipping_address` VARCHAR(255)   NULL COMMENT '收货地址',`receiver_name`    VARCHAR(50)    NULL COMMENT '收货人姓名',`receiver_phone`   VARCHAR(20)    NULL COMMENT '收货人电话',PRIMARY KEY (`order_id`)
) ENGINE = InnoDBDEFAULT CHARSET = utf8mb4 COMMENT ='订单信息表';

package com.qiangesoft.batchsave.entity;import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;/*** <p>* 订单信息表* </p>** @author qiangesoft* @since 2024-05-07*/
@Getter
@Setter
@Accessors(chain = true)
@TableName("mall_order")
public class MallOrder implements Serializable {private static final long serialVersionUID = 1L;/*** 订单ID（主键）*/@TableId(value = "order_id", type = IdType.INPUT)private Long orderId;/*** 客户ID（关联customer表）*/private Long customerId;/*** 订单状态 1-待支付 2-已支付 3-待发货 4-已发货 5-已完成 6-已取消*/private Integer orderStatus;/*** 支付方式; 1-现金 2-支付宝 3-微信 4-银行卡*/private Integer paymentMethod;/*** 订单总金额*/private BigDecimal totalAmount;/*** 运费*/private BigDecimal shippingFee;/*** 优惠券减免金额*/private BigDecimal couponDiscount;/*** 下单日期*/private LocalDateTime orderDate;/*** 支付时间*/private LocalDateTime paymentTime;/*** 收货地址*/private String shippingAddress;/*** 收货人姓名*/private String receiverName;/*** 收货人电话*/private String receiverPhone;}

1000条

/*** 构建数据** @return*/
private static List<MallOrder> getMallOrderList() {List<MallOrder> orderList = new ArrayList<>();for (int i = 0; i < 10000; i++) {MallOrder mallOrder = new MallOrder();mallOrder.setCustomerId(1L);mallOrder.setOrderStatus(1);mallOrder.setPaymentMethod(1);mallOrder.setTotalAmount(BigDecimal.valueOf(12));mallOrder.setShippingFee(BigDecimal.valueOf(1));mallOrder.setCouponDiscount(BigDecimal.valueOf(0));mallOrder.setOrderDate(LocalDateTime.now());mallOrder.setPaymentTime(LocalDateTime.now());mallOrder.setShippingAddress("哈哈");mallOrder.setReceiverName("暂时");mallOrder.setReceiverPhone("13211111111");orderList.add(mallOrder);}return orderList;
}

在这里插入图片描述

10000条

/*** 构建数据** @return*/
private static List<MallOrder> getMallOrderList() {List<MallOrder> orderList = new ArrayList<>();for (int i = 0; i < 1000; i++) {MallOrder mallOrder = new MallOrder();mallOrder.setCustomerId(1L);mallOrder.setOrderStatus(1);mallOrder.setPaymentMethod(1);mallOrder.setTotalAmount(BigDecimal.valueOf(12));mallOrder.setShippingFee(BigDecimal.valueOf(1));mallOrder.setCouponDiscount(BigDecimal.valueOf(0));mallOrder.setOrderDate(LocalDateTime.now());mallOrder.setPaymentTime(LocalDateTime.now());mallOrder.setShippingAddress("哈哈");mallOrder.setReceiverName("暂时");mallOrder.setReceiverPhone("13211111111");orderList.add(mallOrder);}return orderList;
}

在这里插入图片描述

总结

由此可见：
MyBatis-Plus的批量插入器、Statement批量执行、MyBatis-Plus的saveBatch方法+异步任务这三种方式较快。


http://www.ppmy.cn/embedded/37318.html

相关文章

本地运行AI大模型简单示例

本地运行AI大模型简单示例

一、引言 大模型LLM英文全称是Large Language Model&#xff0c;是指包含超大规模参数&#xff08;通常在十亿个以上&#xff09;的神经网络模型。2022年11月底&#xff0c;人工智能对话聊天机器人ChatGPT一经推出&#xff0c;人们利用ChatGPT这样的大模型帮助解决很多事情&am…
阅读更多...
Found multiple lockfiles

Found multiple lockfiles

当你遇到“发现多个锁定文件”的提示时&#xff0c;通常意味着你的项目中存在多于一个用来管理依赖关系的文件&#xff0c;而你正在使用的工具&#xff08;比如npm、yarn、pip或其他包管理器&#xff09;不确定应该使用哪一个。锁定文件&#xff0c;比如npm中的package-lock.js…
阅读更多...
《Fundamentals of Power Electronics》——示例：Buck-Boost转换器模型变为正则形式

《Fundamentals of Power Electronics》——示例：Buck-Boost转换器模型变为正则形式

为了说明正则电路模型推导的步骤&#xff0c;让我们将buck-boost转换器的等效电路操作成规范形式。buck-boost转换器的一个小信号交流等效电路如下图所示。 为了将上图所示网络转换成正则形式&#xff0c;需要将所有独立源d(t)转换到左侧&#xff0c;而将所有电感转换到右侧与变…
阅读更多...
车载打气泵pcba方案研发

车载打气泵pcba方案研发

车载打气泵是一种常见的充气工具&#xff0c;它在我们的日常生活中扮演着重要的角色。它主要用于给车辆、自行车、橡皮艇、游泳圈等充气&#xff0c;方便人们在各种场合使用。它简单方便的操作&#xff0c;快速高效的充气速度&#xff0c;以及便携的设计&#xff0c;让我们能够…
阅读更多...
微信小程序交互增强：实现上拉加载、下拉刷新与一键返回顶部【代码示例】

微信小程序交互增强：实现上拉加载、下拉刷新与一键返回顶部【代码示例】

微信小程序交互增强&#xff1a;实现上拉加载、下拉刷新与一键返回顶部【代码示例】 基础概念实现步骤与代码示例1. 下拉刷新2. 上拉加载更多3. 返回顶部 性能优化与安全考虑结语与讨论 在微信小程序的开发过程中&#xff0c;提供流畅的用户体验至关重要&#xff0c;其中上拉加…
阅读更多...
科技早报 | 微软将推出自研AI大模型；苹果折叠屏iPhone新专利获批 | 最新快讯

科技早报 | 微软将推出自研AI大模型；苹果折叠屏iPhone新专利获批 | 最新快讯

微软将推出自研AI大模型 5月6日消息&#xff0c;据The Information报道&#xff0c;微软正在公司内部训练一个新的人工智能模型&#xff0c;规模足以与谷歌、Anthropic&#xff0c;乃至OpenAI 自身的先进大模型相抗衡。 报道称&#xff0c;这个新模型内部代号为“MAI-1”&…
阅读更多...
frp内网穿透服务搭建与使用

frp内网穿透服务搭建与使用

frp内网穿透服务搭建与使用 1、frp简介 frp 是一个专注于内网穿透的高性能的反向代理应用&#xff0c;支持 TCP、UDP、HTTP、HTTPS 等多种协议。 可以将内网服务以安全、便捷的方式通过具有公网 IP 节点的中转暴露到公网。frp工作原理 服务端运行&#xff0c;监听一个主端口…
阅读更多...
研发效能 | Jacoco dump基于k8s的实现

研发效能 | Jacoco dump基于k8s的实现

问题描述 总所周知&#xff0c;jacoco的dump操作如果是使用server模式只需要使用以下命令就能获取到 exec 文件。 java -jar jacococli.jar dump --address 192.169.110.1 --port 6300 --destfile ./jacoco-demo.exec 如果是非 k8s 的集群&#xff0c;也只需要遍历执行这条命…
阅读更多...
最新文章

Copyright @ 2022~2023