使用Mybatis的Batch Insert Support 实现批量插入
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 左右
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
相关文章