关于Mybatis批量插入使用JDBC原生batch批处理以及mybatis的ExecutorType.BATCH的批处理和Mybatis的动态sql语句批处理

2023-08-05 05:29| 来源: 网络整理| 查看: 265

近期处理数据，总结一下

项目背景：从Mongo中定时转移前第7天文本到mysql数据库且生成文件,有各种不同的定时任务，有批次消息、发送明细消息等等很多的数据需要转移，也有老系统和新系统数据的转移，mysql使用的分区表；架构使用的ssm，定时任务使用的xxl-job分布式任务调度

上面都是废话；

1、JDBC原生batch处理400万条数据

/** * @Description: 批量新增发送明细数据一次性插入1000条 * @Param: * @return: * @Author: fanghuaiming * @Date: */ public void savebatchDtlBatch(List smsSendHistoryDetails) { LOG.info("Fuction:savebatchDtlBatch start insert into mysql batch 【 sms_msg_dtl 】"); Connection conn = DataSourceUtils.getConnection(dataSource1Config.testDataSource()); long before = System.currentTimeMillis(); int count = 0; LOG.info("Fuction:savebatchDtlBatch Connection has opend"); PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement("INSERT INTO sms_msg_dtl (unique_id,username,mtype,mobile,batch_id,channel_id,location,words,sub_succ,sub_fail,rpt_succ,rpt_fail,uprice,cprice,submit_time,_id,mdstr,extend) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); conn.setAutoCommit(false); } catch (SQLException e) { e.printStackTrace(); } for (int i = 0; i < smsSendHistoryDetails.size(); i++) { try { SmsSendHistoryDetail smsSendHistoryDetail = smsSendHistoryDetails.get(i); pstmt.setLong(1, null == smsSendHistoryDetail.getUniqueId() ? 0 : smsSendHistoryDetail.getUniqueId()); pstmt.setInt(2, null == smsSendHistoryDetail.getUserName() ? 0 : smsSendHistoryDetail.getUserName()); pstmt.setInt(3, null == smsSendHistoryDetail.getMtype() ? 0 : smsSendHistoryDetail.getMtype()); pstmt.setLong(4, null == smsSendHistoryDetail.getMobile() ? 0 : smsSendHistoryDetail.getMobile()); pstmt.setLong(5, null == smsSendHistoryDetail.getBatchId() ? 0 : smsSendHistoryDetail.getBatchId()); pstmt.setInt(6, null == smsSendHistoryDetail.getChannelId() ? 0 : smsSendHistoryDetail.getChannelId()); pstmt.setString(7, null == smsSendHistoryDetail.getLocation() ? "" : smsSendHistoryDetail.getLocation()); pstmt.setString(8, null == smsSendHistoryDetail.getWords() ? "" : smsSendHistoryDetail.getWords()); pstmt.setInt(9, null == smsSendHistoryDetail.getSubSucc() ? 0 : smsSendHistoryDetail.getSubSucc()); pstmt.setInt(10, null == smsSendHistoryDetail.getSubFail() ? 0 : smsSendHistoryDetail.getSubFail()); pstmt.setInt(11, null == smsSendHistoryDetail.getRptSucc() ? 0 : smsSendHistoryDetail.getRptSucc()); pstmt.setInt(12, null == smsSendHistoryDetail.getRptFail() ? 0 : smsSendHistoryDetail.getRptFail()); pstmt.setLong(13, null == smsSendHistoryDetail.getuPrice() ? 0 : smsSendHistoryDetail.getuPrice()); pstmt.setLong(14, null == smsSendHistoryDetail.getcPrice() ? 0 : smsSendHistoryDetail.getcPrice()); pstmt.setLong(15, null == smsSendHistoryDetail.getSubmitTime() ? 0 : smsSendHistoryDetail.getSubmitTime()); pstmt.setString(16, null == smsSendHistoryDetail.get_id() ? "" : smsSendHistoryDetail.get_id()); pstmt.setString(17, null == smsSendHistoryDetail.getMdstr() ? "" : smsSendHistoryDetail.getMdstr()); pstmt.setString(18, null == smsSendHistoryDetail.getExtend() || "".equals(smsSendHistoryDetail.getExtend()) ? "" : smsSendHistoryDetail.getExtend()); pstmt.addBatch(); int a = i + 1; if (a % 1000 == 0) { try { pstmt.executeBatch(); } catch (BatchUpdateException e) { int[] updateCounts = e.getUpdateCounts(); for (int j = 0; j < updateCounts.length; j++) { if(updateCounts[j]

【本文地址】

公司简介

联系我们