您现在的位置是:首页 > 编程语言学习 > 后端编程语言 > 文章正文 后端编程语言
使用Mybatis的Batch Insert Support 实现批量插入
2022-07-11 10:17:00 后端编程语言
简介这篇文章主要介绍了使用Mybatis的Batch Insert Support 实现批量插入。具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全...
这篇文章主要介绍了使用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 左右
上一篇:java文件操作输入输出结构详解
下一篇:最后一页