本文介绍了在Java中如何利用夜间服务器低负载时段执行耗时任务，如生成报表。主要内容包括创建运行报表的TaskThread线程类，定义报表执行逻辑，以及启动所有报表线程的实现方式。报表类通过遍历公司并调用存储过程来生成销售商品分类占比等报表。
白天时服务器一般收到用户的请求较多，执行比较多的任务。晚上收到用户的请求少，压力小，可以在夜间执行一些耗时的操作，如生成报表。本文将介绍，在多公司，一个公司对应一个数据库的情景下，如何在夜间执行任务，生成各个公司的报表。本文将介绍以下内容：“创建运行报表的相关类”、“报表类的执行逻辑”、“启动所有报表线程”。
1、创建运行报表的相关类。
TaskThread继承于Thread，是一个线程类。所有类型的报表继承于TaskThread类，TaskThread存放公共属性和方法：
@Component("taskThread")
@Scope("prototype")
public class TaskThread extends Thread
TaskThread的run方法
@Override
public void run() {
while (atomicInteger.get() != SIGNAL_ThreadExit) {
doTask();
try {
if (isMockingMVC) {
timeSpanToCheckTaskStartTime = 50; // 在测试环境下，检查频繁一点，以缩短报表测试的运行时间
} else {
timeSpanToCheckTaskStartTime = 10000; // 检查疏一点，以免占用太多CPU时间
}
Thread.sleep(timeSpanToCheckTaskStartTime);
} catch (InterruptedException e) {
logger.error("夜间检查任务【" + name + "】定时任务线程异常" + e.getMessage());
return;
}
}
}Run方法调用了doTask方法，
doTask方法执行的逻辑：
(1)遍历所有公司；
(2)判断是否已经到了执行该任务的时间dateToRunReport；
(3)如果到了执行任务的时间，那么设置下一次执行任务的时间是24小时候dateToRunNextTime；
(4)开始执行doTaskForEveryCompany：
protected void doTask() {
List<BaseModel> list = CacheManager.getCache(BaseAction.DBName_Public, EnumCacheType.ECT_Company).readN(false, false);
for (BaseModel bm : list) {
Company company = (Company) bm;
if (!company.getDbName().equals(BaseAction.DBName_Public)) {// 公共DB没有夜间任务
if (company.getIncumbent() == EnumCompanyCreationStatus.ECCS_Incumbent.getIndex() && company.getStatus() == EnumCompanyCreationStatus.ECCS_Incumbent.getIndex()) {// 表示创建公司时是否已经创建完成
Date dateToRunReport = (getReportDateForTest() == null ? new Date() : getReportDateForTest());
if (mapDatetimeForNextRun.get(company.getDbName()) == null) {
mapDatetimeForNextRun.put(company.getDbName(), getStartDatetime(company.getDbName()));// 有的公司是后来创建的
}
// 如果时间已经超过本夜间任务设定的运行时间，则设置此时间为24小时后再跑一次，然后才跑夜间任务。子类中，可以自定义夜间任务跑或不跑、如何跑。
if (DatetimeUtil.isAfterDate(dateToRunReport, mapDatetimeForNextRun.get(company.getDbName()), 0) || canRunOnceForTest()) {
logger.info("定时任务执行时间段已到，开始运行任务, \t本线程hashcode=" + this.hashCode());
Date dateToRunNextTime = mapDatetimeForNextRun.get(company.getDbName());
dateToRunNextTime = DatetimeUtil.getDate(dateToRunNextTime, 24 * 3600);
mapDatetimeForNextRun.put(company.getDbName(), dateToRunNextTime);
doTaskForEveryCompany(company);
taskStatusForTest.incrementAndGet();
}
}
}
}
}2、报表类的执行逻辑。
以销售商品分类占比报表为例，介绍报表的生成过程。
类名为RetailTradeDailyReportByCategoryParentTaskThread，继承了TaskThread：
@Component("retailTradeDailyReportByCategoryParentTaskThread")
@Scope("prototype")
public class RetailTradeDailyReportByCategoryParentTaskThread extends TaskThread因为它是TaskThread的子类，所以它的关键方法为doTaskForEveryCompany：
@Override
protected void doTaskForEveryCompany(Company company) {
// logger.debug(this.getName() + "--正在检查是否到达定时任务执行时期段(DB名称=" + dbName + ")...");
String dbName = company.getDbName();
logger.info("定时任务执行时间段已到，开始运行任务：查询当天" + name + "并推送 \t本线程hashcode=" + this.hashCode() + "\t 执行的dbName=" + dbName);
RetailTradeDailyReportByCategoryParent retailTradeDailyReportByCategoryParent = new RetailTradeDailyReportByCategoryParent();
// reportDateForTest==null时获取当前时间的前一天，reportDateForTest!=null时，生成reportDateForTest当天报表
Date targetDate = (reportDateForTest == null ? DatetimeUtil.getDays(new Date(), -1) : DatetimeUtil.getDays(reportDateForTest, -1));
// 获取所有门店信息，循环生成所有门店的日报表
List<BaseModel> shopList = CacheManager.getCache(company.getDbName(), EnumCacheType.ECT_Shop).readN(true, false); // 从缓存中拿到所有门店
for(BaseModel bm : shopList) {
Shop shop = (Shop) bm;
retailTradeDailyReportByCategoryParent.setShopID(shop.getID());
retailTradeDailyReportByCategoryParent.setSaleDatetime(targetDate);
DataSourceContextHolder.setDbName(dbName);
List<List<BaseModel>> bmList = retailTradeDailyReportByCategoryParentBO.createObjectEx(BaseBO.SYSTEM, BaseBO.INVALID_CASE_ID, retailTradeDailyReportByCategoryParent);
if (retailTradeDailyReportByCategoryParentBO.getLastErrorCode() == EnumErrorCode.EC_NoError) {
// TODO 大类是否需要发送微信消息
logger.info("生成【"+ name + "】成功：" + bmList);
} else {
if (retailTradeDailyReportByCategoryParentBO.getLastErrorCode() == EnumErrorCode.EC_BusinessLogicNotDefined) {
logger.info("生成【"+ name + "】失败(其实不算失败):" + retailTradeDailyReportByCategoryParentBO.printErrorInfo(dbName, retailTradeDailyReportByCategoryParent));
} else { //其它的错误是3，需要报警
if (BaseAction.ENV != EnumEnv.DEV) {
logger.error("生成【"+ name + "】失败！错误码：" + retailTradeDailyReportByCategoryParentBO.printErrorInfo(dbName, retailTradeDailyReportByCategoryParent));
}
}
return;
}
}
}doTaskForEveryCompany方法执行的逻辑：
- 获取该公司所有门店，遍历门店；
- 调用存储过程，生成门店的销售商品分类占比的报表。
生成销售商品分类占比的存储过程如下：
查看代码DROP PROCEDURE IF EXISTS `SP_RetailTradeDailyReportByCategoryParent_Create`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_RetailTradeDailyReportByCategoryParent_Create`(
OUT iErrorCode INT,
OUT sErrorMsg VARCHAR(64),
IN iShopID INT,
IN dSaleDatetime DATETIME, -- 销售日期
IN deleteOldData INT -- 仅用于测试，测试时传入该值判断是否需要删除前一个数据 为1时删除；
)
BEGIN
DECLARE isSameDatetime INT;
DECLARE returnAmount DECIMAL(20, 6); -- 退货额
DECLARE retailAmount DECIMAL(20, 6); -- 销售额
DECLARE categoryparentID INT; -- 商品大类ID
DECLARE totalNO INT;
DECLARE done INT DEFAULT false;
DECLARE tempCategoryparentID DECIMAL(20, 6); -- 临时分类ID，用在游标的遍历
DECLARE tempAmount DECIMAL(20, 6) DEFAULT 0; -- 临时总额，用在游标的遍历
-- 查询出这一天所有的分类ID
DECLARE list3 CURSOR FOR(
SELECT distinct F_ID FROM t_categoryparent WHERE F_ID IN (SELECT F_ParentID FROM t_category WHERE F_ID IN (
SELECT F_CategoryID FROM t_commodity WHERE F_ID IN (
SELECT F_CommodityID FROM t_retailtradecommodity rtc WHERE F_TradeID IN (
SELECT F_ID FROM t_retailtrade rt WHERE datediff(F_SaleDatetime, dSaleDatetime) = 0 AND F_ShopID = iShopID
)
)
)
)
);
-- 查询出这一天所有进行过零售的分类，并算出各分类的销售总额
DECLARE list2 CURSOR FOR(
SELECT cp.F_ID AS categoryparentID, sum(rtc.F_PriceReturn * rtc.F_NO) AS amount
FROM t_retailtrade rt,t_retailtradecommodity rtc,t_commodity cd,t_category c,t_categoryparent cp
WHERE rt.F_ID = rtc.F_TradeID
AND cd.F_ID = rtc.F_CommodityID
AND c.F_ID = cd.F_CategoryID
AND cp.F_ID = c.F_ParentID
AND datediff(rt.F_SaleDatetime, dSaleDatetime) = 0
AND rt.F_SourceID = -1
AND rt.F_ShopID = iShopID
GROUP BY cp.F_ID
);
-- 查询出这一天所有进行过退货的分类，并算出各分类的退货总额
DECLARE list1 CURSOR FOR(
SELECT cp.F_ID AS categoryparentID, sum(rtc.F_PriceReturn * rtc.F_NO) AS amount
FROM t_retailtrade rt,t_retailtradecommodity rtc,t_commodity cd,t_category c,t_categoryparent cp
WHERE rt.F_ID = rtc.F_TradeID
AND cd.F_ID = rtc.F_CommodityID
AND c.F_ID = cd.F_CategoryID
AND cp.F_ID = c.F_ParentID
AND datediff(rt.F_SaleDatetime, dSaleDatetime) = 0
AND rt.F_SourceID <> -1
AND rt.F_ShopID = iShopID
GROUP BY cp.F_ID
);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET iErrorCode := 3, sErrorMsg := '数据库错误';
ROLLBACK;
END;
START TRANSACTION;
SET iErrorCode := 0;
SET sErrorMsg := '';
-- 仅用于测试，保证测试通过
IF deleteOldData = 1 THEN
DELETE FROM t_retailtradedailyreportbycategoryparent WHERE F_Datetime = DATE_FORMAT(dSaleDatetime,'%Y-%m-%d') AND F_ShopID = iShopID;
END IF;
-- 计算销售笔数(零售单数)
SELECT NO INTO totalNO
FROM
(
SELECT count(F_ID) AS NO
FROM t_retailtrade
WHERE datediff(F_SaleDatetime,dSaleDatetime) = 0 -- datediff(date1,date2) 返回两个date之间的相隔天数
AND F_ShopID = iShopID
) AS tmp1;
-- 查询数据库已有数据，是否包含插入时间
SELECT NO INTO isSameDatetime
FROM
(
SELECT count(F_Datetime) AS NO
FROM t_retailtradedailyreportbycategoryparent
WHERE datediff(F_Datetime,dSaleDatetime) = 0 -- datediff(date1,date2) 返回两个date之间的相隔天数
AND F_ShopID = iShopID
) AS tmp2;
IF totalNO > 0 THEN
IF isSameDatetime <> 0 THEN
SET iErrorCode := 7;
SET sErrorMsg := '不能插入数据库已有日期';
ELSE
OPEN list3;-- 存储了当天所有分类的游标
read_loop: LOOP
FETCH list3 INTO categoryparentID;
IF done THEN
LEAVE read_loop;
END IF;
SET retailAmount := 0;
SET returnAmount := 0;
OPEN list2; -- 存储了当天所有进行过零售的分类ID,分类对应的销售总额
read_loop2: LOOP
FETCH list2 INTO tempCategoryparentID, tempAmount;
IF done THEN
LEAVE read_loop2;
END IF;
IF tempCategoryparentID = categoryparentID THEN
SET retailAmount := tempAmount;
LEAVE read_loop2;
END IF;
END LOOP read_loop2;
CLOSE list2;
SET done = false;
OPEN list1;-- 存储了当天所有进行过退货的分类ID,分类对应的退货总额
read_loop1: LOOP
FETCH list1 INTO tempCategoryparentID, tempAmount;
IF done THEN
LEAVE read_loop1;
END IF;
IF tempCategoryparentID = categoryparentID THEN
SET returnAmount := tempAmount;
LEAVE read_loop1;
END IF;
END LOOP read_loop1;
CLOSE list1;
SET done := false;
INSERT INTO t_retailtradedailyreportbycategoryparent (
F_ShopID,
F_Datetime,
F_CategoryParentID,
F_TotalAmount)
VALUES (
iShopID,
DATE_FORMAT(dSaleDatetime,'%Y-%m-%d'),
categoryparentID,
retailAmount - returnAmount
);
END LOOP read_loop;
CLOSE list3;
SELECT F_ID,
F_ShopID,
F_Datetime,
F_CategoryParentID,
F_TotalAmount,
F_CreateDatetime,
F_UpdateDatetime
FROM t_retailtradedailyreportbycategoryparent
WHERE F_Datetime=DATE_FORMAT(dSaleDatetime,'%Y-%m-%d') AND F_ShopID = iShopID;
SET iErrorCode := 0;
SET sErrorMsg := '';
END IF;
ELSE
SET iErrorCode := 7;
SET sErrorMsg := '当天零售笔数为0';
END IF;
COMMIT;
END;3、启动所有报表线程。
在@PostConstruct注解的方法里启动：
@Resource
private TaskScheduler ts;
@PostConstruct
private void load() {
resolveCurrentEnvAndDomain();
……
// 启动夜间任务的所有线程
ts.start();TaskScheduler类的start方法负责启动线程：
查看代码public void start() {
TaskThread tt = TaskManager.getCache(EnumTaskType.ETT_PurchasingTimeout);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_PurchasingTimeout, ptt);
ptt.setAtomicInteger(aiThreadSignal);
ptt.setName("采购超时定时检查线程");
ptt.start();
logger.info("线程" + ptt.getName() + "已经启动。Hashcode=" + ptt.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_ShelfLifeTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_ShelfLifeTaskThread, stt);
stt.setAtomicInteger(aiThreadSignal);
stt.setName("商品保质期定时检查线程");
stt.start();
logger.info("线程" + stt.getName() + "已经启动。Hashcode=" + stt.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_UnsalableCommodity);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_UnsalableCommodity, uc);
uc.setAtomicInteger(aiThreadSignal);
uc.setName("商品滞销定时检查线程");
uc.start();
logger.info("线程" + uc.getName() + "已经启动。Hashcode=" + uc.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_RetailTradeDailyReportSummaryTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_RetailTradeDailyReportSummaryTaskThread, dr);
dr.setAtomicInteger(aiThreadSignal);
dr.setName("日报表定时推送检查线程");
dr.start();
logger.info("线程" + dr.getName() + "已经启动。Hashcode=" + dr.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_RetailTradeMonthlyReportSummaryTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_RetailTradeMonthlyReportSummaryTaskThread, dm);
dm.setAtomicInteger(aiThreadSignal);
dm.setName("月报表定时推送检查线程");
dm.start();
logger.info("线程" + dm.getName() + "已经启动。Hashcode=" + dm.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_RetailTradeDailyReportByCategoryParentTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_RetailTradeDailyReportByCategoryParentTaskThread, drc);
drc.setAtomicInteger(aiThreadSignal);
drc.setName("日大类报表定时推送检查线程");
drc.start();
logger.info("线程" + drc.getName() + "已经启动。Hashcode=" + drc.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_RetailTradeDailyReportByStaffTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_RetailTradeDailyReportByStaffTaskThread, ds);
ds.setAtomicInteger(aiThreadSignal);
ds.setName("员工业绩报表定时推送检查线程");
ds.start();
logger.info("线程" + ds.getName() + "已经启动。Hashcode=" + ds.hashCode());
}
tt = TaskManager.getCache(EnumTaskType.ETT_BonusTaskThread);
if (tt != null && tt.isAlive() && tt.getState() != State.TERMINATED) {
logger.info("旧线程" + tt.getName() + "仍未退出！Hashcode=" + tt.hashCode());
} else {
TaskManager.register(EnumTaskType.ETT_BonusTaskThread, btt);
btt.setAtomicInteger(aiThreadSignal);
btt.setName("积分清零检查线程");
btt.start();
logger.info("线程" + btt.getName() + "已经启动。Hashcode=" + btt.hashCode());
}
TaskManager.setTaskScheduler(this);
}
扫一扫
286
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
