作者：@heiheipp 、@Gin

一、数据加载调优项

调整数据加载程序的 jdbc 连接串：JDBC:mysql://{TiDBIP}:{TiDBPort}/{DBName}?characterEncoding=utf8&useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10000000&useConfigs=maxPerformance&rewriteBatchedStatements=true&defaultfetchsize=-2147483648

将单行 insert 改为 batch insert 方式写入数据，形如 insert into table values(),(),(),()......,();

增加配置参数用于灵活调整 batch insert 语句的 batch size，最佳实践的 batch size 为 100~300 之间，也就是每个 insert 语句写入 100~300 行记录，每个 insert 作为一个事务，自动提交。

batch insert 的并发可以调整到 64 或更高，同样需要增加配置参数用于灵活调整并发数。

并发调高了，但在 TiDB 上观察连接数不够，可能是连接池设置问题，参考本文调整连接池最大连接数：专栏 - 使用 TiDB 时的连接池和负载均衡器配置策略 | TiDB 社区

经常需要进行大量数据加载的表，需要设置为非聚簇索引表，并在表结构上增加 SHARD_ROW_ID_BITS 和 PRE_SPLIT_REGIONS 以避免写入热点，参考官方文档进行调整 TiDB 热点问题处理 | PingCAP 文档中心

先创建索引，再加载数据。

二、MyBatis 的 batch insert 示例代码

1. 配置信息

程序基于 Sprint Boot 框架，在工程 resources 目录下的 application.yml 或 properties 文件中指定可被 spring context 获取的batch 提交参数，该参数用于程序中控制每一批提交的行数，如下图所示：

图1

2. 程序内容

2.1 获取配置

通过 spring 的 @Value或 @ConfigurationProperties 注解获取上述文件中的控制参数，本示例采用的是后者，即通过单独的参数实体类进行的数据封装，如下图所示：

图2

参考类文件 CtiqTransMainTableConfigModel.java：

package com.heiheipp.dataprepare.model; import com.heiheipp.common.config.AbstractConfigModel; import lombok.Data; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; /*** @author heiheipp * @version 1.0 * @className CtiqTransMainTableConfigModel * @desc TODO * @date 2022/3/16 17:05 */ @Data @Component("ctiqTransMainTableConfigModel") @ConfigurationProperties(prefix = "ctiq.config.transmaintable") public class CtiqTransMainTableConfigModel extends AbstractConfigModel { /** * 客户数量 */ private int custNum; /** * 每个客户的卡号数量 */ private int perCustCardNums; /** * 客户每日交易笔数 */ private int custTransNumEveryday; /** * 交易日期跨度 */ private int days; /** * 交易起始日期 */ private String startDay; /** * 交易终止日期 */ private String endDay; /** * 数据库单批次提交数量 */ private int batchNum; /** * 客户类型 */ private String custType; /** * 个人客户号前缀 */ private String personalCustIdPrefix; /** * 对公客户号前缀 */ private String companyCustIdPrefix; /** * 客户号长度 */ private int custIdLength; /** * 卡号前缀 */ private String cardBin; /** * 卡号长度 */ private int cardLength; /** * 账号前缀 */ private String accountPrefix; /** * 账号长度 */ private int accountLength; /** * 获取配置模型描述 * @return */ @Override public String getConfigModelDesc() { return "真实场景交易基础信息主表"; } /** * 获取总处理数量 * @return */ @Override public int getTotalNums() { return getCustNum(); } /** * 获取表头文件名 * @return */ @Override public String getFileHeaderName() { return "test_table_1_header.csv"; } /** * 获取表文件名 * @return */ @Override public String getTargetFileName() { return "test_table_1.csv"; } }

2.2 程序控制

在另外的造数程序中，通过获取上述类的控制参数，通过循环控制的方式进行定制化批次大小的触发，即先循环构建每一条待插入的记录，并缓存到list对象中，再由数据库写入方法按照批次大小判断是否提交：

图3

图4

参考类文件 CtiqTransMainTableFutureTask.java：

package com.heiheipp.dataprepare.executor; import cn.hutool.core.date.DateUnit; import cn.hutool.core.date.DateUtil; import cn.hutool.core.date.SystemClock; import cn.hutool.core.util.IdUtil; import cn.hutool.core.util.NumberUtil; import cn.hutool.core.util.RandomUtil; import cn.hutool.core.util.StrUtil; import com.github.javafaker.Faker; import com.heiheipp.common.constant.ConfigConstant; import com.heiheipp.common.context.RuntimeContext; import com.heiheipp.common.context.SpringContextUtil; import com.heiheipp.common.executor.AbstractFutureTask; import com.heiheipp.common.mbg.model.TestTable1; import com.heiheipp.common.service.TaskLogService; import com.heiheipp.common.service.TestTable1Service; import com.heiheipp.common.util.DataBuildUtil; import com.heiheipp.common.util.DateTimeUtil; import com.heiheipp.common.constant.DataModelConstant; import com.heiheipp.dataprepare.model.CtiqTransMainTableConfigModel; import com.heiheipp.dataprepare.service.impl.CTIQDataPrepareServiceImpl; import java.io.File; import java.math.BigDecimal; import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.UUID; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.atomic.AtomicLong; import lombok.extern.slf4j.Slf4j; /*** @author heiheipp * @version 1.0 * @className CtiqTransMainTableFutureTask * @desc TODO * @date 2022/3/16 16:58 */ @Slf4j public class CtiqTransMainTableFutureTask extends AbstractFutureTask<String> { private int targetType; private String fileLocation; private int threadCustNums; private long threadTotalNums; private int batchNums; private TaskLogService taskLogService; private TestTable1Service testTable1Service; private Map<String, Object> runtimeDatas = new ConcurrentHashMap<>(); private String subThreadId; private AtomicLong processedNums = new AtomicLong(0L); private int committedRows = 0; private int startOffset; private CtiqTransMainTableConfigModel configModel; private Faker faker = new Faker(Locale.CHINA); private boolean isFirstLine; private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); private Date start; private Date end; private long dayBetween = 1L; private boolean isRegisterDBLog; /** * 构造函数 * * @param parentThreadId * @param threadCustNums * @param threadOrder * @param configModel */ public CtiqTransMainTableFutureTask(long parentThreadId, int threadCustNums, int threadOrder, CtiqTransMainTableConfigModel configModel) { this.parentThreadId = String.valueOf(parentThreadId); this.threadCustNums = threadCustNums; this.taskLogService = SpringContextUtil.getBean(TaskLogService.class); this.testTable1Service = SpringContextUtil.getBean(TestTable1Service.class); this.configModel = configModel; this.batchNums = this.configModel.getBatchNum(); // 参数计算 this.startOffset = this.threadCustNums * configModel.getPerCustCardNums() * threadOrder + 1; this.targetType = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getTargetType(); this.fileLocation = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getFileLocation(); this.isFirstLine = !SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).isFileMerge(); this.isRegisterDBLog = SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).isRegisterDBLog(); // 以startDay和endDay计算要循环的天数 if (!StrUtil.isEmpty(configModel.getStartDay()) && !StrUtil.isEmpty(configModel.getEndDay())) { try { this.start = this.dateFormat.parse(configModel.getStartDay()); this.end = this.dateFormat.parse(configModel.getEndDay()); // 日期校验 if (this.start.compareTo(this.end) > 0) { log.error("起始日期不能大于终止日期"); throw new RuntimeException("起始日期不能大于终止日期"); } // 计算时间差 this.dayBetween = DateUtil.between(this.start, this.end, DateUnit.DAY) + 1L; } catch (ParseException e) { log.error("子线程[{}]解析起始、终止时间异常", this.subThreadId); e.printStackTrace(); throw new RuntimeException("解析起始、终止时间异常"); } } else if (configModel.getDays() > 0) { this.dayBetween = configModel.getDays(); } // 计算当前子线程要处理的总记录数 this.threadTotalNums = (threadCustNums * configModel.getCustTransNumEveryday()) * this.dayBetween; } /** * 任务执行方法 * * @return */ @Override protected String submit(long subThreadId) { String result = ""; this.subThreadId = String.valueOf(subThreadId); long startTime = System.currentTimeMillis(); log.info("Sub thread[{}] start, threadTotalNums is {}, custNums is {}, duration is {} days, batchNums is {}, startOffset is {}, isRegisterDBLog is {}.", this.subThreadId, this.threadTotalNums, this.threadCustNums, this.dayBetween, this.batchNums, this.startOffset, this.isRegisterDBLog); // 计算公共变量 String fileName = null; if (this.targetType == 2) { fileName = "boc_poc.test_table_1.0" + this.subThreadId + ".csv"; this.fileLocation = this.fileLocation.equalsIgnoreCase("default") ? new File(".").getAbsolutePath() : this.fileLocation; this.fileLocation += File.separator + fileName; log.info("Sub thread[{}] write data to file[{}].", this.subThreadId, this.fileLocation); // 将文件名写入主线程 SpringContextUtil.getBean(CTIQDataPrepareServiceImpl.class).getTmpFileNameList().add(this.fileLocation); } // 处理业务公共信息 int custIdFillLength; String custIdPrefix = null; switch (this.configModel.getCustType()) { case "Personal": custIdFillLength = this.configModel.getCustIdLength() - this.configModel.getPersonalCustIdPrefix().length() - DataModelConstant.PROVINCE_LENGTH; custIdPrefix = this.configModel.getPersonalCustIdPrefix(); break; case "Company": custIdFillLength = this.configModel.getCustIdLength() - this.configModel.getCompanyCustIdPrefix().length() - DataModelConstant.PROVINCE_LENGTH; custIdPrefix = this.configModel.getCompanyCustIdPrefix(); break; default: custIdFillLength = 0; custIdPrefix = ""; break; } int cardNoFillLength = this.configModel.getCardLength() - this.configModel.getCardBin().length(); String cardNoPrefix = this.configModel.getCardBin(); int accountFillLength = this.configModel.getAccountLength() - this.configModel.getAccountPrefix().length(); String accountPrefix = this.configModel.getAccountPrefix(); // 登记任务开始日志 if (isRegisterDBLog) { recordTask(ConfigConstant.TaskStatusEnum.START); } // 制造数据 List<TestTable1> testTable1s = new ArrayList<>(); TestTable1 testTable1 = null; int currentPercentage = 0, prevPercentage = 0; try { for (int i = 0; i < this.threadCustNums; i++) { // 按单客户每次交易笔数制造数据 buildMultiTestTable1(testTable1s, testTable1, custIdPrefix, cardNoPrefix, accountPrefix, i, custIdFillLength, cardNoFillLength, accountFillLength); // 数据写入操作 if (this.processedNums.get() < this.threadTotalNums) { writeData(testTable1s, false); } else { writeData(testTable1s, true); } // 登记任务处理中日志 prevPercentage = currentPercentage; currentPercentage = DataBuildUtil.getPercentge(this.processedNums.get(), this.threadTotalNums); if (DataBuildUtil.getPercentageWtihTens(this.processedNums.get(), this.threadTotalNums) && prevPercentage < currentPercentage) { if (isRegisterDBLog) { recordTask(ConfigConstant.TaskStatusEnum.PROCESSING); } log.info("Sub thread {} has been processing {}%, processedNums is {}, totalNums is {}, execution time is {}.", this.subThreadId, currentPercentage, this.processedNums.get(), this.threadTotalNums, SystemClock.now() - startTime); } } // 补提交剩余部分 writeData(testTable1s, true); } catch (Exception e) { log.error("Sub thread {} error, and execution time is {}ms.", this.subThreadId, System.currentTimeMillis() - startTime); e.printStackTrace(); // 登记任务失败信息 if (isRegisterDBLog) { recordTask(ConfigConstant.TaskStatusEnum.ERROR); } } finally { log.info("Sub thread {} finish, and execution time is {}ms.", this.subThreadId, System.currentTimeMillis() - startTime); // 登记任务结束信息 if (isRegisterDBLog) { recordTask(ConfigConstant.TaskStatusEnum.SUCCESS); } // 清理线程上下文 RuntimeContext.clearRuntimeData(); // 计数器操作 CTIQDataPrepareServiceImpl.getCountDownLatch().countDown(); } return result; } /** * 构建任务日志数据 */ private void buildRuntimeDatas(ConfigConstant.TaskStatusEnum taskStatusEnum, Object... args) { switch (taskStatusEnum) { case START: // 登记开始任务 runtimeDatas.put(ConfigConstant.PARENT_THREAD_ID_KEY, this.parentThreadId); runtimeDatas.put(ConfigConstant.SUB_THREAD_ID_KEY, subThreadId); runtimeDatas.put(ConfigConstant.TASK_TYPE_KEY, ConfigConstant.TaskTypeEnum.DATA_PREPARE.getTypeDesc()); runtimeDatas.put(ConfigConstant.TASK_CONTENT_KEY, getTaskContent()); runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.START.getStatus()); runtimeDatas.put(ConfigConstant.TASK_TOTAL_NUMS_KEY, this.threadTotalNums); runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, 0L); runtimeDatas.put(ConfigConstant.TASK_CREATE_TIME_KEY, SystemClock.now()); runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now()); runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.START); break; case PROCESSING: // 更新处理中任务 runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.PROCESSING.getStatus()); runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get()); runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.PROCESSING); runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now()); break; case ERROR: // 任务异常 runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.ERROR.getStatus()); runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get()); runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.ERROR); runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now()); break; case SUCCESS: // 任务成功结束 runtimeDatas.put(ConfigConstant.TASK_STATUS_KEY, ConfigConstant.TaskStatusEnum.SUCCESS.getStatus()); runtimeDatas.put(ConfigConstant.TASK_PROCESSED_NUMS_KEY, processedNums.get()); runtimeDatas.put(ConfigConstant.TASK_STATUS_ENUM_KEY, ConfigConstant.TaskStatusEnum.SUCCESS); runtimeDatas.put(ConfigConstant.TASK_UPDATE_TIME_KEY, SystemClock.now()); break; default: break; } } /** * 获取任务类型 * * @return */ private String getTaskContent() { return "TEST_TABLE_1表数据准备"; } /** * 登记任务日志 * * @param taskStatusEnum */ private void recordTask(ConfigConstant.TaskStatusEnum taskStatusEnum) { switch (taskStatusEnum) { case START: // 登记任务开始日志 buildRuntimeDatas(ConfigConstant.TaskStatusEnum.START); break; case PROCESSING: // 登记任务处理中日志 buildRuntimeDatas(ConfigConstant.TaskStatusEnum.PROCESSING); break; case ERROR: // 登记任务失败信息 buildRuntimeDatas(ConfigConstant.TaskStatusEnum.ERROR); break; case SUCCESS: // 登记任务结束信息 buildRuntimeDatas(ConfigConstant.TaskStatusEnum.SUCCESS); break; default: break; } RuntimeContext.setRuntimeDatas(runtimeDatas); try { taskLogService.recordTaskLog(); } catch (Exception e) { e.printStackTrace(); } } /** * 构建TestTable1对象 * @param testTable1 * @param custId * @param cardNoArray * @param accountArray * @param custName * @param startLongTime * @param endLongTime */ private void buildTestTable1(TestTable1 testTable1, String custId, String[] cardNoArray, String[] accountArray, String custName, long startLongTime, long endLongTime) { int num = DataBuildUtil.getIntegerRandom(this.configModel.getPerCustCardNums()); testTable1.setColumn1(new BigDecimal(IdUtil.getSnowflakeNextIdStr())); testTable1.setColumn2(DataBuildUtil.getRandomWithLength(3)); //testTable1.setColumn3(DataBuildUtil.getRandomWithLength(5)); testTable1.setColumn3(String.valueOf(RandomUtil.randomInt(1, 10000))); testTable1.setColumn4(accountArray[num]); testTable1.setColumn5(new Timestamp(DateTimeUtil.randomLongTime(startLongTime, endLongTime))); testTable1.setColumn6(DataBuildUtil.getRandomWithLength(8)); testTable1.setColumn7(DataBuildUtil.getRandomWithLength(8)); testTable1.setColumn8(DataBuildUtil.getRandomWithLength(5)); testTable1.setColumn9(testTable1.getColumn4()); testTable1.setColumn10("0001"); //testTable1.setColumn11(Integer.valueOf(DataBuildUtil.getRandomWithLength(3))); testTable1.setColumn11(RandomUtil.randomInt(1, 999)); testTable1.setColumn12(Integer.valueOf(DataBuildUtil.getRandomWithLength(2))); testTable1.setColumn13(DataBuildUtil.getRandomWithLength(12)); testTable1.setColumn14(UUID.randomUUID().toString().replaceAll("-", "")); testTable1.setColumn15(DataBuildUtil.getRandomWithLength(4)); testTable1.setColumn16(DataBuildUtil.getRandomWithLength(4)); testTable1.setColumn17(DataBuildUtil.generateMsg("某", 12, "产品")); testTable1.setColumn18(DataBuildUtil.generateMsg("某", 16, "文书合同")); testTable1.setColumn19("1"); testTable1.setColumn20("01"); testTable1.setColumn21("001"); testTable1.setColumn22(cardNoArray[num]); testTable1.setColumn23("01"); testTable1.setColumn24("1"); testTable1.setColumn25(DataBuildUtil.getTranType()); testTable1.setColumn26(DataBuildUtil.getTranTypeDesc(testTable1.getColumn25())); testTable1.setColumn27(DateUtil.date(testTable1.getColumn5().getTime()).toTimeStr()); testTable1.setColumn28(DataBuildUtil.getRandomWithLength(7)); testTable1.setColumn29(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn30(DataModelConstant.DEFAULT_BRANCH_NAME); testTable1.setColumn31(DateUtil.parse("2021-05-01", "yyyy-MM-dd")); testTable1.setColumn32("0"); testTable1.setColumn33("112233"); testTable1.setColumn34("112233"); testTable1.setColumn35("01"); testTable1.setColumn36("提示码"); testTable1.setColumn37(DataBuildUtil.getChannel()); testTable1.setColumn38(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate()); testTable1.setColumn39(testTable1.getColumn13()); testTable1.setColumn40("156"); testTable1.setColumn41("CNY"); testTable1.setColumn42(NumberUtil.round("1.00", 2)); testTable1.setColumn43(DataBuildUtil.getDebitOrCreditFlag()); testTable1.setColumn44(DataBuildUtil.getChongzhengFlag(testTable1.getColumn43())); testTable1.setColumn45(DataBuildUtil.getRandomBigDecimal(10000, 2)); testTable1.setColumn46(testTable1.getColumn45().add(new BigDecimal(100))); testTable1.setColumn47(testTable1.getColumn46()); testTable1.setColumn48(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn49(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn50(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn51(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn52(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn53(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn54(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn55(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn56(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn57(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn58(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn59(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn60(DataBuildUtil.generateMsg("某", 30, "备注")); testTable1.setColumn61(DataBuildUtil.generateMsg("某", 15, "用途")); testTable1.setColumn62(DataBuildUtil.generateMsg("某", 30, "附言")); testTable1.setColumn63(DataBuildUtil.generateMsg("某", 30, "摘要")); testTable1.setColumn64("1"); testTable1.setColumn65("现钞"); testTable1.setColumn66(DataModelConstant.DEFAULT_PAYER_NAME); testTable1.setColumn67("1234567890123456789"); testTable1.setColumn68("1234567890123456789"); testTable1.setColumn69("1111"); testTable1.setColumn70(Integer.valueOf(DataBuildUtil.getRandomWithLength(3))); testTable1.setColumn71(Integer.valueOf(DataBuildUtil.getRandomWithLength(2))); testTable1.setColumn72(DataModelConstant.DEFAULT_BRANCH_NAME); testTable1.setColumn73(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn74(DataModelConstant.DEFAULT_PROVINCE_LH_NO); // 付款人姓名 if ("D".equalsIgnoreCase(testTable1.getColumn43())) { testTable1.setColumn75(custName); } else { testTable1.setColumn75(DataModelConstant.DEFAULT_PAYER_NAME); } testTable1.setColumn76("1234567890123456789"); testTable1.setColumn77("12345678901234567890001"); testTable1.setColumn78("1111"); testTable1.setColumn79(Integer.valueOf(DataBuildUtil.getRandomWithLength(3))); testTable1.setColumn80(Integer.valueOf(DataBuildUtil.getRandomWithLength(2))); testTable1.setColumn81(DataModelConstant.DEFAULT_BRANCH_NAME); testTable1.setColumn82(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn83(DataModelConstant.DEFAULT_PROVINCE_LH_NO); testTable1.setColumn84(DataModelConstant.DEFAULT_RECEIVER_NAME); testTable1.setColumn85("9876543210987654321"); testTable1.setColumn86("12345678901234567890001"); testTable1.setColumn87("1111"); testTable1.setColumn88(Integer.valueOf(DataBuildUtil.getRandomWithLength(3))); testTable1.setColumn89(Integer.valueOf(DataBuildUtil.getRandomWithLength(2))); testTable1.setColumn90(DataModelConstant.DEFAULT_BRANCH_NAME); testTable1.setColumn91(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn92(DataModelConstant.DEFAULT_PROVINCE_LH_NO); // 收款人姓名 if ("C".equalsIgnoreCase(testTable1.getColumn43())) { testTable1.setColumn93(custName); } else { testTable1.setColumn93(DataModelConstant.DEFAULT_RECEIVER_NAME); } testTable1.setColumn94("9876543210987654321"); testTable1.setColumn95("98765432109876543210001"); testTable1.setColumn96("1111"); testTable1.setColumn97(Integer.valueOf(DataBuildUtil.getRandomWithLength(3))); testTable1.setColumn98(Integer.valueOf(DataBuildUtil.getRandomWithLength(2))); testTable1.setColumn99(DataModelConstant.DEFAULT_BRANCH_NAME); testTable1.setColumn100(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn101(DataModelConstant.DEFAULT_PROVINCE_LH_NO); testTable1.setColumn102("2222"); testTable1.setColumn103(DataBuildUtil.generateMsg("某", 2, "凭证")); testTable1.setColumn104("112233445566778899"); testTable1.setColumn105("2222"); testTable1.setColumn106(DataBuildUtil.generateMsg("某", 2, "产生凭证")); testTable1.setColumn107("112233445566778899"); testTable1.setColumn108("998877665544332211"); testTable1.setColumn109("1111"); testTable1.setColumn110(Integer.valueOf(4)); testTable1.setColumn111(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn112(DataModelConstant.ZERO_AMOUNT); testTable1.setColumn113(DataModelConstant.DEFAULT_ADDRESS_PHONE); testTable1.setColumn114(DataModelConstant.DEFAULT_ADDRESS_PHONE); testTable1.setColumn115("1122334455"); testTable1.setColumn116("1122334455"); testTable1.setColumn117("1122334455"); testTable1.setColumn118("1122334455"); testTable1.setColumn119("1122334455"); testTable1.setColumn120("1122334455"); testTable1.setColumn121(DataBuildUtil.generateMsg("某", 1, "系统")); testTable1.setColumn122("11111"); testTable1.setColumn123(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate()); testTable1.setColumn124("1111"); testTable1.setColumn125("11111"); testTable1.setColumn126("1"); testTable1.setColumn127("1"); testTable1.setColumn128("11112222"); testTable1.setColumn129(DataModelConstant.DEFAULT_BRANCH_NO); testTable1.setColumn130("1"); testTable1.setColumn131("1"); testTable1.setColumn132("1"); testTable1.setColumn133("1"); testTable1.setColumn134(DateUtil.date(testTable1.getColumn5().getTime()).toSqlDate()); testTable1.setColumn135("1"); testTable1.setColumn136("1"); testTable1.setColumn137("1"); testTable1.setColumn138("1"); testTable1.setColumn139("1"); testTable1.setColumn140("1"); testTable1.setColumn141("1"); testTable1.setColumn142("1"); testTable1.setColumn143("1"); testTable1.setColumn144(custId); testTable1.setColumn145("1"); testTable1.setColumn146("1"); // 生成json字符串 testTable1.setColumn151(DataModelConstant.DEFAULT_JSON_STRING); } /** * 构建TestTable1列表 * @param testTable1s * @param testTable1 * @param custIdPrefix * @param cardNoPrefix * @param accountPrefix * @param currentOffset * @param custIdFillLength * @param cardNoFillLength * @param accountFillLength */ private void buildMultiTestTable1(List<TestTable1> testTable1s, TestTable1 testTable1, String custIdPrefix, String cardNoPrefix, String accountPrefix, int currentOffset, int custIdFillLength, int cardNoFillLength, int accountFillLength) { // 公共信息 int offset = currentOffset * this.configModel.getPerCustCardNums(); String custId = DataBuildUtil.generateCustId(custIdPrefix, this.startOffset, offset, custIdFillLength); String custName = this.faker.name().fullName(); String[] cardNoArray = new String[this.configModel.getPerCustCardNums()]; String[] accountArray = new String[this.configModel.getPerCustCardNums()]; for (int k = 0; k < this.configModel.getPerCustCardNums(); k++) { cardNoArray[k] = DataBuildUtil.generateCardNo(cardNoPrefix, this.startOffset, offset + k, cardNoFillLength); accountArray[k] = DataBuildUtil.generateAccount(accountPrefix, this.startOffset, offset + k, accountFillLength); } // 外层按照日期循环 for (int i = 0; i < (new Long(this.dayBetween)).intValue(); i++) { long startLongTime = DateUtil.offsetDay(this.start, i).getTime(); long endLongTime = DateUtil.offsetDay(this.start, i + 1).getTime() - 1L; // 内层按照每日交易数循环 for (int j = 0; j < this.configModel.getCustTransNumEveryday(); j++) { testTable1 = new TestTable1(); buildTestTable1(testTable1, custId, cardNoArray, accountArray, custName, startLongTime, endLongTime); testTable1s.add(testTable1); // 递增总生成数量和待提交数量 this.processedNums.incrementAndGet(); this.committedRows++; } } } /** * 写文件操作 * @param testTable1s * @param flag */ private void writeData(List<TestTable1> testTable1s, boolean flag) { if (testTable1s.size() > 0) { if (this.targetType == 1) { //写入数据库 if (this.committedRows >= this.batchNums) { this.testTable1Service.batchInsert(testTable1s); testTable1s.clear(); this.committedRows = 0; } else if (flag) { this.testTable1Service.batchInsert(testTable1s); testTable1s.clear(); this.committedRows = 0; } } else if (this.targetType == 2) { //写入csv文件 this.testTable1Service.writeCsv(this.fileLocation, testTable1s, this.isFirstLine); testTable1s.clear(); this.isFirstLine = false; //末尾追加空行 if (flag) { this.testTable1Service.writeCsv(); } } } } }

2.3 DAO 相关操作

本工程使用的是 MyBatis ORM 框架，在 mapper xml 文件中增加了 batchInsert sql，通过 foreach 方式进行的批量 insert，如下图：

图5

图6

对应的 mapper interface 类如下，红线部分主要是为了 mapper xml 中的参数定位：

图7

对应的 service 类通过 spring 事务的方式、以 PROPAGATION_REQUIRES_NEW 的事务传播方式进行的事务控制：

图8