使用CompletableFuture减少批量定时任务留存率5万多条数据从1个小时优化到8分钟,解决oracle批量插入问题

  private Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private OrderMapper orderMapper;
    @Value("${GIFT_TYPE_ID}")
    private String[] GIFT_TYPE_ID; // 产品类型ID
    @Value("${INSERT_TOTAL}")
    private Integer insertTotal;

    //@Scheduled(cron = "${retentionRate.job.cron}")
    @XxlJob("orderRetentionRateJob")
    public ReturnT<String> orderRetentionRateJob(String params) {
        logger.info("orderRetentionRateJob执行开始");
        long startTime = System.currentTimeMillis();
        //根据产品类型查询出所有的产品
        List<GiftTypeInfoVo> giftInfoVoList = orderMapper.queryAllGiftByType(Arrays.asList(GIFT_TYPE_ID));
        Map<String, GiftTypeInfoVo> giftIdMap = giftInfoVoList.stream().collect(Collectors.toMap(GiftTypeInfoVo::getGiftId, Function.identity()));
        //1.查询所有的分支机构
        List<OrgInfoVo> orgInfoVoList = orderMapper.queryOrgInfo();
        List<String> orgList = orgInfoVoList.stream().map(OrgInfoVo::getValue).collect(Collectors.toList());
        //清空临时表
        orderMapper.truncateOrderRetentionRates();
        //生成时间
        //1:近一个月,2:近三个月,3:近六个月4:本年度
        Date now = new Date();
        DateTime oneStartTime = DateUtil.offsetMonth(now, -1);
        DateTime threeStartTime = DateUtil.offsetMonth(now, -3);

        DateTime sixthStartTime = DateUtil.offsetMonth(now, -6);
        //本年度开始时间
        DateTime yearStartTime = DateUtil.beginOfYear(now);
        List<OrderRetentionRates> retentionRatesList = new ArrayList<>();
        //1:近一个月
        orderCount(orgList, null, "1", giftIdMap, DateUtil.format(oneStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //2:近三个月
        orderCount(orgList, null, "2", giftIdMap, DateUtil.format(threeStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //3:近六个月
        orderCount(orgList, null, "3", giftIdMap, DateUtil.format(sixthStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //4:本年度
        orderCount(orgList, null, "4", giftIdMap, DateUtil.format(yearStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //2.查询所有的营业部
        List<YYBInfoVo> yybInfoVoList = orderMapper.queryAllYYBInfo(orgList);
        List<String> yybIdList = yybInfoVoList.stream().map(YYBInfoVo::getYybId).collect(Collectors.toList());
        //1:近一个月
        orderCount(null, yybIdList, "1", giftIdMap, DateUtil.format(oneStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //2:近三个月
        orderCount(null, yybIdList, "2", giftIdMap, DateUtil.format(threeStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //3:近六个月
        orderCount(null, yybIdList, "3", giftIdMap, DateUtil.format(sixthStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        //4:本年度
        orderCount(null, yybIdList, "4", giftIdMap, DateUtil.format(yearStartTime, "yyyy-MM-dd"), DateUtil.format(now, "yyyy-MM-dd"), Arrays.asList(GIFT_TYPE_ID), retentionRatesList);
        List<List<OrderRetentionRates>> listList = ListUtil.split(retentionRatesList, insertTotal);
        for (List<OrderRetentionRates> orderRetentionRatesList : listList) {
            orderMapper.insertBatchOrderRetention(orderRetentionRatesList);
        }
        long endTime = System.currentTimeMillis();
        logger.info("orderRetentionRateJob执行结束,耗时:{}", endTime - startTime);
        return ReturnT.SUCCESS;
    }


    private void orderCount(List<String> orgIdList, List<String> yybIdList, String dateType, Map<String, GiftTypeInfoVo> giftIdMap, String startTime, String endTime, List<String> giftTypeList, List<OrderRetentionRates> retentionRatesList) {
        RetentionRateTaskVo retentionRateTaskVo = new RetentionRateTaskVo();
        //按照分支机构统计
        if (CollectionUtil.isNotEmpty(orgIdList)) {
            CompletableFuture<Void> signOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> signOrderGiftList = orderMapper.signOrderTotalCountByOrgId(startTime, endTime, giftTypeList);
                retentionRateTaskVo.setSignOrderGiftList(signOrderGiftList);
            });
            CompletableFuture<Void> retentionOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> retentionOrderGiftList = orderMapper.retentionOrderCountByOrgId(startTime, endTime, giftTypeList);
                retentionRateTaskVo.setRetentionOrderGiftList(retentionOrderGiftList);
            });
            CompletableFuture<Void> freeDeadDateOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> freeDeadDateOrderGiftList = orderMapper.freeDeadDateOrderCountByOrgId(startTime, endTime, giftTypeList);
                retentionRateTaskVo.setFreeDeadDateOrderGiftList(freeDeadDateOrderGiftList);
            });
            CompletableFuture.allOf(signOrderCompletableFuture, retentionOrderCompletableFuture, freeDeadDateOrderCompletableFuture).join();
            List<OrderRetentionRates> signOrderGiftList = retentionRateTaskVo.getSignOrderGiftList();
            List<OrderRetentionRates> retentionOrderGiftList = retentionRateTaskVo.getRetentionOrderGiftList();
            List<OrderRetentionRates> freeDeadDateOrderGiftList = retentionRateTaskVo.getFreeDeadDateOrderGiftList();
            if (CollectionUtil.isNotEmpty(signOrderGiftList)) {
                for (OrderRetentionRates signOrder : signOrderGiftList) {
                    OrderRetentionRates orderRetentionRates = new OrderRetentionRates();
                    if (CollectionUtil.isNotEmpty(retentionOrderGiftList)) {
                        //留存率
                        OrderRetentionRates retentionOrder = retentionOrderGiftList.stream().filter(retentionOrderGift ->
                                        retentionOrderGift.getGiftId().equals(signOrder.getGiftId()) && retentionOrderGift.getKhwdId().equals(signOrder.getKhwdId()))
                                .findFirst().orElse(null);
                        computeRetentionRate(signOrder, orderRetentionRates, retentionOrder);
                    } else {
                        orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
                        orderRetentionRates.setRetentionOrderCount(0);
                        orderRetentionRates.setRetentionRate(0 + "%");
                    }
                    //已过免佣留存率
                    if (CollectionUtil.isNotEmpty(freeDeadDateOrderGiftList)) {
                        OrderRetentionRates freeDeadDateOrder = freeDeadDateOrderGiftList.stream().filter(freeDeadDateOrderGift ->
                                        freeDeadDateOrderGift.getGiftId().equals(signOrder.getGiftId()) && freeDeadDateOrderGift.getKhwdId().equals(signOrder.getKhwdId()))
                                .findFirst().orElse(null);
                        computeFreeDateRetentionRate(signOrder, orderRetentionRates, freeDeadDateOrder);
                    } else {
                        orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
                        orderRetentionRates.setFreeDeadDateOrderCount(0);
                        orderRetentionRates.setFreeDeadDateRetentionRate(0 + "%");
                    }
                    orderRetentionRates.setCreateDate(new Date());
                    orderRetentionRates.setKhwdId(signOrder.getKhwdId());
                    orderRetentionRates.setKhwdName(signOrder.getKhwdName());
                    orderRetentionRates.setGiftId(signOrder.getGiftId());
                    if (Objects.nonNull(giftIdMap.get(signOrder.getGiftId()))) {
                        orderRetentionRates.setGiftCode(giftIdMap.get(signOrder.getGiftId()).getGiftCode());
                    }
                    orderRetentionRates.setGiftName(signOrder.getGiftName());
                    orderRetentionRates.setDateType(dateType);
                    logger.info("分支机构留存率记录:{}", orderRetentionRates);
                    retentionRatesList.add(orderRetentionRates);
                }
            }

        } else {//按照营业部
            CompletableFuture<Void> signOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> signOrderGiftList = orderMapper.signOrderTotalCountByYybId(yybIdList, startTime, endTime, giftTypeList);
                retentionRateTaskVo.setSignOrderGiftList(signOrderGiftList);
            });
            CompletableFuture<Void> retentionOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> retentionOrderGiftList = orderMapper.retentionOrderCountByYybId(yybIdList, startTime, endTime, giftTypeList);
                retentionRateTaskVo.setRetentionOrderGiftList(retentionOrderGiftList);
            });
            CompletableFuture<Void> freeDeadDateOrderCompletableFuture = CompletableFuture.runAsync(() -> {
                List<OrderRetentionRates> freeDeadDateOrderGiftList = orderMapper.freeDeadDateOrderCountByYybId(yybIdList, startTime, endTime, giftTypeList);
                retentionRateTaskVo.setFreeDeadDateOrderGiftList(freeDeadDateOrderGiftList);
            });
            CompletableFuture.allOf(signOrderCompletableFuture, retentionOrderCompletableFuture, freeDeadDateOrderCompletableFuture).join();
            List<OrderRetentionRates> signOrderGiftList = retentionRateTaskVo.getSignOrderGiftList();
            List<OrderRetentionRates> retentionOrderGiftList = retentionRateTaskVo.getRetentionOrderGiftList();
            List<OrderRetentionRates> freeDeadDateOrderGiftList = retentionRateTaskVo.getFreeDeadDateOrderGiftList();
            if (CollectionUtil.isNotEmpty(signOrderGiftList)) {
                for (OrderRetentionRates signOrder : signOrderGiftList) {
                    OrderRetentionRates orderRetentionRates = new OrderRetentionRates();
                    //留存率
                    if (CollectionUtil.isNotEmpty(retentionOrderGiftList)) {
                        OrderRetentionRates retentionOrder = retentionOrderGiftList.stream().filter(retentionOrderGift ->
                                        retentionOrderGift.getGiftId().equals(signOrder.getGiftId()) && retentionOrderGift.getYybId().equals(signOrder.getYybId()))
                                .findFirst().orElse(null);
                        computeRetentionRate(signOrder, orderRetentionRates, retentionOrder);
                    } else {
                        orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
                        orderRetentionRates.setRetentionOrderCount(0);
                        orderRetentionRates.setRetentionRate(0 + "%");
                    }
                    //已过免佣留存率
                    if (CollectionUtil.isNotEmpty(freeDeadDateOrderGiftList)) {
                        OrderRetentionRates freeDeadDateOrder = freeDeadDateOrderGiftList.stream().filter(freeDeadDateOrderGift ->
                                        freeDeadDateOrderGift.getGiftId().equals(signOrder.getGiftId()) && freeDeadDateOrderGift.getYybId().equals(signOrder.getYybId()))
                                .findFirst().orElse(null);
                        computeFreeDateRetentionRate(signOrder, orderRetentionRates, freeDeadDateOrder);
                    } else {
                        orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
                        orderRetentionRates.setFreeDeadDateOrderCount(0);
                        orderRetentionRates.setFreeDeadDateRetentionRate(0 + "%");
                    }
                    orderRetentionRates.setCreateDate(new Date());
                    orderRetentionRates.setYybId(signOrder.getYybId());
                    orderRetentionRates.setYybName(signOrder.getYybName());
                    orderRetentionRates.setGiftId(signOrder.getGiftId());
                    if (Objects.nonNull(giftIdMap.get(signOrder.getGiftId()))) {
                        orderRetentionRates.setGiftCode(giftIdMap.get(signOrder.getGiftId()).getGiftCode());
                    }
                    orderRetentionRates.setGiftName(signOrder.getGiftName());
                    orderRetentionRates.setDateType(dateType);
                    logger.info("营业部留存率记录:{}", orderRetentionRates);
                    retentionRatesList.add(orderRetentionRates);
                }
            }
        }

    }

    /**
     * 计算留存率(已过免佣期留存订单)
     *
     * @param signOrder
     * @param orderRetentionRates
     * @param freeDeadDateOrder
     */

    private void computeFreeDateRetentionRate(OrderRetentionRates signOrder, OrderRetentionRates orderRetentionRates, OrderRetentionRates freeDeadDateOrder) {
        if (Objects.nonNull(freeDeadDateOrder)) {
            int freeDeadDateRetentionRate = NumberUtil.div(freeDeadDateOrder.getFreeDeadDateOrderCount(), signOrder.getSignOrderTotal(), 2).multiply(BigDecimal.valueOf(100)).intValue();
            orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
            orderRetentionRates.setFreeDeadDateOrderCount(freeDeadDateOrder.getFreeDeadDateOrderCount());
            orderRetentionRates.setFreeDeadDateRetentionRate(freeDeadDateRetentionRate + "%");
        } else {
            orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
            orderRetentionRates.setFreeDeadDateOrderCount(0);
            orderRetentionRates.setFreeDeadDateRetentionRate(0 + "%");
        }
    }

    /**
     * 计算留存率
     *
     * @param signOrder
     * @param orderRetentionRates
     * @param retentionOrder
     */
    private void computeRetentionRate(OrderRetentionRates signOrder, OrderRetentionRates orderRetentionRates, OrderRetentionRates retentionOrder) {
        if (Objects.nonNull(retentionOrder)) {
            int retentionRate = NumberUtil.div(retentionOrder.getRetentionOrderCount(), signOrder.getSignOrderTotal(), 2).multiply(BigDecimal.valueOf(100)).intValue();
            orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
            orderRetentionRates.setRetentionOrderCount(retentionOrder.getRetentionOrderCount());
            orderRetentionRates.setRetentionRate(retentionRate + "%");
        } else {
            orderRetentionRates.setSignOrderTotal(signOrder.getSignOrderTotal());
            orderRetentionRates.setRetentionOrderCount(0);
            orderRetentionRates.setRetentionRate(0 + "%");
        }
    }
}

批量插入oracle

<insert id="insertBatchOrderRetention">
		INSERT INTO ORDER_RETENTION_RATES(
		GIFT_ID,
		GIFT_CODE,
		GIFT_NAME,
		KHWD_ID,
		KHWD_NAME,
		YYB_ID,
		YYB_NAME,
		SIGN_ORDER_TOTAL,
		RETENTION_ORDER_COUNT,
		FREE_DEAD_DATE_ORDER_COUNT,
		RETENTION_RATE,
		FREE_DEAD_DATE_RETENTION_RATE,
		DATE_TYPE,
		CREATE_DATE
		)
		select m.* from (
		<foreach collection="list" index="" item="map" separator="union all">
			select
			#{map.giftId,jdbcType=VARCHAR},
			#{map.giftCode,jdbcType=VARCHAR},
			#{map.giftName,jdbcType=VARCHAR},
			#{map.khwdId,jdbcType=VARCHAR},
			#{map.khwdName,jdbcType=VARCHAR},
			#{map.yybId,jdbcType=VARCHAR},
			#{map.yybName,jdbcType=VARCHAR},
			#{map.signOrderTotal,jdbcType=INTEGER},
			#{map.retentionOrderCount,jdbcType=INTEGER},
			#{map.freeDeadDateOrderCount,jdbcType=INTEGER},
			#{map.retentionRate,jdbcType=VARCHAR},
			#{map.freeDeadDateRetentionRate,jdbcType=VARCHAR},
			#{map.dateType,jdbcType=VARCHAR},
			sysdate
			from dual
		</foreach>
		) m
	</insert>

遇到的问题
org.springframework.jdbc.BadSqlGrammarException:

Error updating database. Cause: java.sql.SQLException:ORA-0660:第33644行,第4列:PLS-00183:绑定变量太多

优化每次插入到oracle 2000条数据

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值