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条数据