MyBatis和Spring结合的批处理方案

 MyBatis和Spring结合的批处理方案

参考网址：

http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html

https://blog.csdn.net/zxy_9264_ang/article/details/93844849

https://blog.csdn.net/artaganan8/article/details/88624402

https://blog.csdn.net/huanghanqian/article/details/83177178

https://blog.csdn.net/qq_15003505/article/details/80428249

1、创建 SqlSession

SqlSessionFactory 是通过Spring管理的，除了设置 ExecutorType.BATCH，还要取消自动提交，通过 SqlSessionFactory的openSession 实现

2、设置rewriteBatchedStatements

拼接在jdbc地址上 jdbc:mysql://127.0.0.1:3306/project?rewriteBatchedStatements=true 

3、分批处理

如果不要一次循环直接跑完，多缓冲几次

4、获取处理结果

通过 sqlSession.flushStatements() 可以拿到返回的结果集

5、关闭sqlSession

package cn.xxx.partner.maintainsite.dao;

import cn.xxx.partner.maintainsite.entity.MaintainSiteBasicEntity;
import cn.xxx.partner.maintainsite.mapper.MaintainSiteUpdateMapper;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.annotation.PostConstruct;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author GaoMingBo
 * @since 2019-09-02 16:19
 */
@Repository
public class MaintainSiteBatchDao {

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    private SqlSession sqlSession;
    
    @PostConstruct
    public void init() {
        // 初始化批量处理的sql session，取消自动提交
        sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    }
    
    public String batchUpdate(String statementMapper, List<Map<String, Object>> params, Integer group) {
        List<BatchResult> batchResults = new ArrayList<>();
        // 默认每组1000个，不能太大，防止内存溢出
        group = (group == null || group == 0) ? 1000 : group;
        int batchSize = 0;
        for (Map<String, Object> param : params) {
            sqlSession.update(statementMapper, param);
            batchSize++;
            if (batchSize == group) {
                batchResults.addAll(sqlSession.flushStatements());
                batchSize = 0;
            }
        }
        if (batchSize > 0) {
            batchResults.addAll(sqlSession.flushStatements());
        }
        return assembleBatchResults(batchResults);
    }

    /**
     * 批量更新网点
     *
     * @param entityList 更新参数
     * @param group      分组数量--批量操作时的分组
     * @return 批量操作结果
     */
    public String batchUpdate(List<MaintainSiteBasicEntity> entityList, Integer group) {
        // 初始化Mapper
        MaintainSiteUpdateMapper siteMapper =     sqlSession.getMapper(MaintainSiteUpdateMapper.class);
        List<BatchResult> results = new ArrayList<>();
        // 默认每组1000个，不能太大，防止内存溢出
        group = (group == null || group == 0) ? 1000 : group;
        int batchSize = 0;
        try {
            for (MaintainSiteBasicEntity entity : entityList) {
                siteMapper.updateByPrimaryKeySelective(entity);
                batchSize++;
                if (batchSize == group) {
                    // 缓冲SQL语句
                    results.addAll(sqlSession.flushStatements());
                    batchSize = 0;
                }
            }
            if (batchSize > 0) {
                // 缓冲SQL语句
                results.addAll(sqlSession.flushStatements());
            }
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 回滚事务
            sqlSession.rollback();
        } finally {
            // 关闭SqlSession
            sqlSession.close();
        }
        // 返回组装后的处理结果
        return assembleBatchResults(results);
    }

    /**
     * 组装批量操作结果
     *
     * @param results 批量结果集
     * @return 批量操作结果
     */
    private String assembleBatchResults(List<BatchResult> results) {
        if (results.size() > 0) {
            StringBuilder sb = new StringBuilder();
            sb.append("批量操作记录:\n");
            for (int i = 0; i < results.size(); i++) {
                BatchResult result = results.get(i);
                sb.append("第 ").append(i + 1).append(" 批:\t");
                sb.append(result.getSql().replaceAll("\n", "")
                        .replaceAll("\\s+", " ")).append("\t");
                sb.append("更新行数: ").append(findSumWithoutUsingStream(result.getUpdateCounts())).append("\n");
            }
            return sb.toString();
        } else {
            return null;
        }
    }

    /**
     * 计算数组之和
     *
     * @param array int数组
     * @return 数组之和
     */
    private static int findSumWithoutUsingStream(int[] array) {
        int sum = 0;
        for (int value : array) {
            sum += value;
        }
        return sum;
    }
}