测试代码
List<BaseOrg> baseOrgList = new ArrayList<>();
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
OrgMapper um = session.getMapper(OrgMapper.class);
long startTime = System.currentTimeMillis();
for (int i = 0; i < 50000; i++) {
BaseOrg baseOrg = new BaseOrg();
baseOrg.setOrgid(String.valueOf(i));
baseOrg.setJb(0);
baseOrg.setOrgcode(i + "1");
baseOrg.setOrgname(i + "1");
baseOrg.setOrgpid(i + "1");
baseOrg.setIOrder(i + "1");
baseOrg.setMinistriesname(i + "1");
baseOrg.setOrgRootid(i + "1");
baseOrg.setOrgtype(i + "1");
baseOrg.setIsLeaf("1");
baseOrg.setOrgnature(i + "1");
baseOrg.setKeyTrades("");
baseOrg.setProvince(i + "1");
baseOrg.setOrgGuid(i + "1");
baseOrg.setCreateUserid(i + "1");
baseOrg.setCreateTime(new Timestamp(System.currentTimeMillis()));
baseOrg.setUpdateUserid(i + "1");
baseOrg.setUpdateTime(new Timestamp(System.currentTimeMillis()));
baseOrg.setCreateUserorg(i + "1");
baseOrg.setUpdateUserorg(i + "1");
baseOrgList.add(baseOrg);
}
// 一:将baseOrgList拆分1000条执行一次 55981ms
if (baseOrgList.size() < 1000) {
dBOrgMapper.insertOrgVOList(baseOrgList);
} else {
int insertTimes = (int) Math.ceil(baseOrgList.size() * 1.0 / 1000);
for (int i = 0; i < insertTimes; i++) {
dBOrgMapper.insertOrgVOList(baseOrgList.subList(1000 * i, Math.min(1000 * (i + 1), baseOrgList.size())));
}
}
// 二:使用google的guava工具包下的Lists.partion方法,分片,分批次1000条执行,其实和上边那个一样 89949ms
List<List<BaseOrg>> partitionList = Lists.partition(baseOrgList, 1000);
for (List<BaseOrg> orgList : partitionList) {
dBOrgMapper.insertOrgVOList(orgList);
}
// 数据库配置添加 &rewriteBatchedStatements=true,使用ExecutorType.BATCH,这样前前后后就只用这一个 SqlSession 4604ms
for (BaseOrg baseOrg : baseOrgList) {
um.insertOrgVO(baseOrg);
}
session.commit();
long endTime = System.currentTimeMillis();
logger.info("一条条插入 SQL 耗费时间 {}", (endTime - startTime));
方式一:
// 一:将baseOrgList拆分1000条执行一次 55981ms
if (baseOrgList.size() < 1000) {
dBOrgMapper.insertOrgVOList(baseOrgList);
} else {
int insertTimes = (int) Math.ceil(baseOrgList.size() * 1.0 / 1000);
for (int i = 0; i < insertTimes; i++) {
dBOrgMapper.insertOrgVOList(baseOrgList.subList(1000 * i, Math.min(1000 * (i + 1), baseOrgList.size())));
}
}
方式二:
// 二:使用google的guava工具包下的Lists.partion方法,分片,分批次1000条执行,其实和上边那个一样 89949ms
List<List<BaseOrg>> partitionList = Lists.partition(baseOrgList, 1000);
for (List<BaseOrg> orgList : partitionList) {
dBOrgMapper.insertOrgVOList(orgList);
}
方式三:
// 开启数据库配置&rewriteBatchedStatements=true
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
OrgMapper um = session.getMapper(OrgMapper.class);
// 数据库配置添加 &rewriteBatchedStatements=true,使用ExecutorType.BATCH,这样前前后后就只用这一个 SqlSession 4604ms
for (BaseOrg baseOrg : baseOrgList) {
um.insertOrgVO(baseOrg);
}
session.commit();