多线程导入数据（目前数据量在上百万左右）

多线程导出excel

公司项目需求，需要生成大量数据存入数据库，刚开始使用mybatis 批量插入，但是后来通过查资料发现，相对有spring 的jdbcTemplate处理速度，mybatis还是有些慢，后来就自己采用jdbcTemplate，并采用多线程分批插入

配置线程池

项目使用springboot框架，所以线程池也是用springboot配置

@Configuration
@EnableAsync
public class TaskPoolConfig {

    @Bean("taskExecutor")
    public Executor taskExecutro(){
        int i = Runtime.getRuntime().availableProcessors();
        System.out.println("系统最大线程数  ： "+i);
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        taskExecutor.setCorePoolSize(i);
        taskExecutor.setMaxPoolSize(i);
        taskExecutor.setQueueCapacity(99999);
        taskExecutor.setKeepAliveSeconds(60);
        taskExecutor.setThreadNamePrefix("taskExecutor--");
        taskExecutor.setWaitForTasksToCompleteOnShutdown(true);
        taskExecutor.setAwaitTerminationSeconds(60);
        return taskExecutor;
    }
}

定义jdbcTemplate批量插入

@Repository
public class CodeRepository {
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public void batchSave ( List<Code> list){

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());

        String sql=" insert into two_dimensional_code_service.code(" +
                "id, code, create_time, status, type, brand_number, code_apply_id, code_apply_detail_id)" +
                " values (:id, :code, :createTime,:status, :type,:brandNumber, :codeApplyId, :codeApplyDetailId)";
        jdbcTemplate.batchUpdate(sql, (batch));

    }

    public void batchUpdate ( List<Code> list ,String applyDetailsId){

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());

        String sql=" UPDATE two_dimensional_code_service.code SET status=1 , factory_plan_id= '"+applyDetailsId+"' where id=:id" ;

        jdbcTemplate.batchUpdate(sql, (batch));
    }

}

定义异步执行方法

 @Component
@Slf4j
public class AsyncTaskService {
    @Autowired
    private CodeRepository codeRepository;

    /**
     * 
     * @param map 要处理的批次数据分页信息
     * @param cdl
     */
    @Async("taskExecutor")
    public void generateCode(Map<String, Object> map , CountDownLatch cdl) {
        long start = System.currentTimeMillis();
        // 导出文件路径
        List<Code> list = new ArrayList<>();
        int pageSize = (int) map.get("pageSize");
        int type = (int) map.get("type");
        CodeApplyDetails codeApplyDetail = (CodeApplyDetails)map.get("codeApplyDetail");
        try {
            for (int i = 0; i < pageSize; i++) {
                Code code = new Code();
                code.setId(UUIDUtils.getUUID());
                code.setCode(UUIDUtils.getUUID());
                code.setCreateTime(new Date());
                code.setBrandNumber(codeApplyDetail.getTobaccoName());
                code.setCodeApplyDetailId(codeApplyDetail.getId());
                code.setCodeApplyId(codeApplyDetail.getApplyId());
                code.setStatus(0);
                code.setType(type);
                code.setCycle(codeApplyDetail.getCycle());
                list.add(code);
            }
            codeRepository.batchSave(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        log.info("线程：" + Thread.currentThread().getName() + "第   " + map.get("page") + "   批次 ， 插入   " + map.get("pageSize") + "   条成功 ，耗时 ：" + (end - start));
        list.clear();
        cdl.countDown();
    }
}

service层

这里使用mybatis操作数据库，根据总的数据量处理每批次的分页信息存入队列，然后每从队列取出一个批次信息开启一个线程，调用异步导出方法，

@Service("codeReceptionListService")
@Slf4j
public class CodeServiceImpl implements CodeService {
    @Resource
    private CodeDao codeDao;
    @Autowired
    private AsyncTaskService asyncTaskService;

    @Resource
    private CodeApplyDao codeApplyDao;


    public static Queue<Map<String, Object>> queue;//Queue是java自己的队列，具体可看API，是同步安全的

    static {
        queue = new ConcurrentLinkedQueue<Map<String, Object>>();
    }

    @Resource
    private CodeApplyDetailsDao codeApplyDetailsDao;

    /**
     * 生成编码
     *
     * @param applyId 申请表id
     */
    @Override
    @Transactional
    public void generateCode(String applyId)  {
        //获取申请表明细
        HashMap<String, Object> map = new HashMap<>();
        map.put("applyId", applyId);
        map.put("limit", 9999);
        map.put("page", 1);
        PageUtil.pageUtil(map);
        List<CodeApplyDetails> codeApplyDetails = codeApplyDetailsDao.queryAllByLimit(map);

        for (CodeApplyDetails codeApplyDetail : codeApplyDetails) {
            initQueue(codeApplyDetail, 0);
            initQueue(codeApplyDetail, 1);
            initQueue(codeApplyDetail, 2);
            Integer pieceNumber = codeApplyDetail.getPieceNumber();
            Integer barNumber = codeApplyDetail.getBarNumber();
            Integer boxNumber = codeApplyDetail.getBoxNumber();
            log.info("总件数 ：  " + pieceNumber.toString());
            log.info("总条数 ：  " + barNumber.toString());
            log.info("总包数 ：  " + boxNumber.toString());

        }
        long start = System.currentTimeMillis();
        int codeingStatus=0;
        try {
        CountDownLatch cdl = new CountDownLatch(queue.size());
        while (queue.size() > 0) {
            asyncTaskService.generateCode(queue.poll(), cdl);
        }
            cdl.await();
        } catch (Exception e) {
            e.printStackTrace();
            codeingStatus=2;
        }finally {
            CodeApply codeApply = new CodeApply();
            codeApply.setId(applyId);
            codeApply.setCodeingStatus(codeingStatus);
            codeApplyDao.update(codeApply);
        }
        long end = System.currentTimeMillis();
        log.info("任务执行完毕       共消耗   ：  "+(end-start)/1000+"  秒");
        //明细表分别生成件 、条、包对应的码


    }

    /**
     * 初始化队列
     */
    public void initQueue(CodeApplyDetails codeApplyDetail, int type) {
        List<Code> codes = new ArrayList<>();
        if (codeApplyDetail.getIsCodeing() > 0 && codeApplyDetail.getIsCodeingPiece() > 0) {
            int pageSize = 10000;
            Integer count = 0;
            if (type == 0) {
                count = codeApplyDetail.getPieceNumber();
            }
            if (type == 1) {
                count = codeApplyDetail.getBarNumber();
            }
            if (type == 2) {
                count = codeApplyDetail.getBoxNumber();
            }
            int pages = count / pageSize + (count % pageSize > 0 ? 1 : 0);
            //int pages = pieceNumber / pageSize;

            for (int i = 1; i <= pages; i++) {
                List<Map<String, Object>> list = new ArrayList<>(4);
                Map<String, Object> map = new HashMap<>();
                //判断是否是最后一页
                if (i == pages) {
                    pageSize = count - (pageSize * (pages - 1));
                    //每页多上条
                }
                map.put("pageSize", pageSize);
                map.put("page", i);
                map.put("type", type);
                map.put("codeApplyDetail", codeApplyDetail);
                //添加元素
                queue.offer(map);
            }
        }
    }
}