Batch Insert Support 批量插入
在开发中如果遇到需要批量insert的需求，可以使用Mybatis 的 Batch Insert Support 提高插入效率。
代码实例（开发的项目中截取的片段）
@Autowired
private SqlSessionTemplate sqlSessionTemplate;
public int insertFolder(List<IpsCatalogFolderDetail> ips) {
//获取sql会话
SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
//通过新的session获取mapper，而不是常规的spring管理注入
IipsCatalogFolderDetailDao folderDetailDao = session.getMapper(IipsCatalogFolderDetailDao.class);
int size = ips.size();
//如果有父类子类两层都需要批量插入也可
try {
//外层循环
for (int i = 0; i < size; i ) {
ips.get(i).setType("folder");
//用上面在session中获取的mapper进行插入操作
folderDetailDao.insertFolder(ips.get(i));
//内层循环
String cs = ips.get(i).getContentIds();
if (StringUtils.isNotBlank(cs)){
List<String> con = JSON.parseArray(cs,String.class);
if (cs != null && con.size() > 0) {
for (int j = 0; j < con.size(); j ) {
IpsCatalogFolderDetail ifd = new IpsCatalogFolderDetail();
ifd.setParentCode(ips.get(i).getCode());
ifd.setContentId(con.get(j));
ifd.setType("contents");
//同样用上面在session中获取的mapper进行插入操作
folderDetailDao.insertFolder(ifd);
}
}
}
//最后批量提交
if (i % 200 == 0 || i == size - 1) {
session.commit();//200个提交一次，手动提交，提交后无法回滚
session.clearCache(); //清理缓存，防止溢出
}
}
}catch (Exception e) {
System.out.println(e.toString());
session.rollback(); //没有提交的数据可以回滚
} finally {
session.close();
}
return 0;
}另外有时我们在插入的时候需要先查询数据是否已存在，如果也需要批量操作可将insert和update语句合并，然后就可以继续使用Batch Insert了
ORACLE数据库sql示例
@Insert("merge into ips_catalog_folder_detail fd "
"using(select #{code,jdbcType=VARCHAR} c from dual)t "
"on(fd.FOLDERID = t.c)"
"when matched then"
"update set "
...(省略）...
"where ..."
"when not matched then insert("
"fd.PROD_LINE,"
"fd.TYPE,"
"fd.PARENTFOLDERCODE,"
"fd.FOLDERID,"
"fd.FOLDERCODE,"
"fd.FOLDERNAME,"
"fd.COLUMN_SORTINDEX,"
"fd.DESCRIPTION,"
"fd.CONTENTID,"
"fd.CREATETIME"
")"
"VALUES"
"("
"#{prod_line},"
"#{type,jdbcType=VARCHAR},"
"#{parentCode,jdbcType=VARCHAR},"
"#{code,jdbcType=VARCHAR},"
"#{aliasCode,jdbcType=VARCHAR},"
"#{name,jdbcType=VARCHAR},"
"#{sortIndex,jdbcType=VARCHAR},"
"#{desc,jdbcType=VARCHAR},"
"#{contentId,jdbcType=VARCHAR},"
"#{createTime,jdbcType=VARCHAR}"
")")
int insertFolder(IpsCatalogFolderDetail fd);MYSQL示例：
REPLACE INTO users (id,name,age) VALUES(1, '张雨绮', 32);
批量插入几千条数据优化(foreach)
项目中有一个耗时较长的Job存在CPU占用过高的问题
经排查发现，主要时间消耗在往MyBatis中批量插入数据。mapper configuration是用foreach循环做的，差不多是这样。
<insert id="batchInsert" parameterType="java.util.List">
insert into USER (id, name) values
<foreach collection="list" item="model" index="index" separator=",">
(#{model.id}, #{model.name})
</foreach>
</insert>优化代码
可以看 http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html 中 Batch Insert Support 标题里的内容）
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
BatchInsert<SimpleTableRecord> batchInsert = insert(records)
.into(simpleTable)
.map(id).toProperty("id")
.map(firstName).toProperty("firstName")
.map(lastName).toProperty("lastName")
.map(birthDate).toProperty("birthDate")
.map(employed).toProperty("employed")
.map(occupation).toProperty("occupation")
.build()
.render(RenderingStrategy.MYBATIS3);
batchInsert.insertStatements().stream().forEach(mapper::insert);
session.commit();
} finally {
session.close();
}总结一下，如果MyBatis需要进行批量插入，推荐使用 ExecutorType.BATCH 的插入方式，如果非要使用 <foreach>的插入的话，需要将每次插入的记录控制在 20~50 左右
以上为个人经验，希望能给大家一个参考，也希望大家多多支持Devmax。