Mybatis的Batch Insert Support批量插入
狄狄呀 人气:0Batch 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 左右
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容