用Mybatis 的 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);