如何用Mybatis的Batch Insert Support实现批量插入

Batch Insert Support 批量插入

在开发中如果遇到需要批量insert的需求，可以使用Mybatis 的 Batch Insert Support 提高插入效率。

代码实例（开发的项目中截取的片段）

@Autowired
private SqlSessionTemplate sqlSessionTemplate;
    
public int insertFolder(ListIpsCatalogFolderDetail>
 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)){
    
            ListString>
     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);
    
  ListSimpleTableRecord>
     records = getRecordsToInsert();
     // not shown

  BatchInsertSimpleTableRecord>
     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 左右

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！