需求:每隔五分钟把一张数据表里的数据读取到另一张表中
yml配置文件:
定时任务表读取到的数据要添加到data_census_detail表中
用data_census表记录定时任务的状态,0代表开始,1代表成功,2代表失败,定时任务开始时在data_census中插入开始时间并返回插入数据所生成的主键id并返回,定时任务完成之后更新data_census的状态和结束时间
@Component
@EnableScheduling
public class DataCensusScheduler {
private final Logger logger = LoggerFactory.getLogger(DataCensusScheduler.class);
@Autowired
private DataCensusService dataCensusService;
@Autowired
private DataCensusMapper dataCensusMapper;
@Scheduled(cron = "${dataCensus.schedule.cron}")
public void dataCensus(){
long censusId = 0;
try{
censusId = addDataCensus();
dataCensusService.dataCensusTask(censusId);
updateDataCensus(censusId,1);
}catch (Exception e){
e.printStackTrace();
updateDataCensus(censusId,2);
}
}
public Long addDataCensus(){
logger.info("数据统计定时任务开始:{}",new Date());
DataCensus dc = new DataCensus();
dc.setCensusStatus(0);
dc.setStartTime(new Date());
dataCensusMapper.addDataCensus(dc);
return dc.getCensusId();
}
public void updateDataCensus(long censusId,int status){
logger.info("数据统计定时任务结束:{}",new Date());
DataCensus dc = new DataCensus();
dc.setCensusId(censusId);
dc.setCensusStatus(status);
dc.setEndTime(new Date());
dataCensusMapper.updateDataCensus(dc);
}
}
DataCensusMapper.xml
<mapper namespace="com.rsi.rc.bs.module.datacensus.dao.DataCensusMapper">
<resultMap id="BaseResultMap" type="com.rsi.rc.bs.module.datacensus.dao.DataCensus">
<id column="census_id" property="censusId" jdbcType="BIGINT"/>
<result column="census_status" property="censusStatus" jdbcType="INTEGER"/>
<result column="start_time" property="startTime" jdbcType="TIMESTAMP"/>
<result column="end_time" property="endTime" jdbcType="TIMESTAMP"/>
</resultMap>
<sql id="Base_Column_List">
census_id,census_status,start_time,end_time
</sql>
<insert id="addDataCensus" parameterType="com.rsi.rc.bs.module.datacensus.dao.DataCensus">
<selectKey resultType="java.lang.Long" order="AFTER" keyProperty="censusId">
SELECT LAST_INSERT_ID()
</selectKey>
insert into bs_data_census_t
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="censusId != null" >
census_id,
</if>
<if test="censusStatus != null" >
census_status,
</if>
<if test="startTime != null" >
start_time,
</if>
<if test="endTime != null" >
end_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="censusId != null" >
#{censusId,jdbcType=BIGINT},
</if>
<if test="censusStatus != null" >
#{censusStatus,jdbcType=INTEGER},
</if>
<if test="startTime != null" >
#{startTime,jdbcType=TIMESTAMP},
</if>
<if test="endTime != null" >
#{endTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateDataCensus" parameterType="com.rsi.rc.bs.module.datacensus.dao.DataCensus">
update bs_data_census_t
<set >
<if test="censusStatus != null" >
census_status = #{censusStatus,jdbcType=INTEGER},
</if>
<if test="startTime != null" >
start_time = #{startTime,jdbcType=TIMESTAMP},
</if>
<if test="endTime != null" >
end_time = #{endTime,jdbcType=TIMESTAMP},
</if>
</set>
where census_id = #{censusId,jdbcType=BIGINT}
</update>
将数据统计参数和数据统计参数详情表和指标表的数据写入data_census_detail表
@Slf4j
@Service
@Component
public class DataCensusServiceImpl implements DataCensusService {
@Autowired
private MetricDataClient metricDataClient;
@Autowired
private DataCensusParamMapper dataCensusParamMapper;
@Autowired
private DataCensusParamDetailMapper dataCensusParamDetailMapper;
@Autowired
private DataCensusDetailMapper dataCensusDetailMapper;
@Override
@Transactional
public void dataCensusTask(long censusId) {
String shanghaiTime = DateTimeUtil.getShanghaiTime();
String shanghaiTimeBeforeMin = DateTimeUtil.getShanghaiTimeBeforeMin(5);
// shanghaiTime = "2021-02-26 09:50:00";
// shanghaiTimeBeforeMin = "2021-02-26 09:45:00";
//查询所有统计参数
List<DataCensusParamVO> paramList = dataCensusParamMapper.getCensusParamListAll();
List<DataCensusParamDetailVO> paramDetailList = dataCensusParamDetailMapper.getCensusParamDetailedListAll();
// 设备的MetricId
List<Long> metricIds = new ArrayList<Long>();
// 统计参数list
List<DataCensusVO> voList = new ArrayList<>();
DataCensusVO vo = null;
for (DataCensusParamVO dataCensusParamVO : paramList) {
long sewageId = dataCensusParamVO.getSewageId();
long workshopId = dataCensusParamVO.getWorkshopId();
long paramId = dataCensusParamVO.getParamId();
for (DataCensusParamDetailVO dataCensusParamDetailVO : paramDetailList) {
long paramId2 = dataCensusParamDetailVO.getParamId();
long deviceId = dataCensusParamDetailVO.getDeviceId();
long metricId = dataCensusParamDetailVO.getMetricId();
if(paramId == paramId2){
vo = new DataCensusVO();
vo.setSewageId(sewageId);
vo.setWorkshopId(workshopId);
vo.setDeviceId(deviceId);
vo.setMetricId(metricId);
vo.setValues(new ArrayList<>());
metricIds.add(metricId);
voList.add(vo);
}
}
}
//查询历史数据
if(metricIds !=null && metricIds.size()>0){
GetDataByMetricAndTimeReq metricreq = new GetDataByMetricAndTimeReq();
metricreq.setMetricIds(metricIds);
metricreq.setStartTime(shanghaiTimeBeforeMin);
metricreq.setEndTime(shanghaiTime);
List<MetricDataVO> dataByMetricIdsAndTimeList = metricDataClient.getDataByMetricIdsAndTimeList(metricreq);
if(dataByMetricIdsAndTimeList!=null && dataByMetricIdsAndTimeList.size() > 0){
for (MetricDataVO metricDataVO : dataByMetricIdsAndTimeList) {
long metricId = Long.parseLong(metricDataVO.getMetricId());
long deviceId = metricDataVO.getDeviceId();
double value = metricDataVO.getValue();
for (DataCensusVO dataCensusVO : voList) {
long metricId2 = dataCensusVO.getMetricId();
List<Double> values = dataCensusVO.getValues();
if(metricId == metricId2){
values.add(value);
break;
}
}
}
for (DataCensusVO dataCensusVO : voList) {
long sewageId = dataCensusVO.getSewageId();
long workshopId = dataCensusVO.getWorkshopId();
long metricId = dataCensusVO.getMetricId();
long deviceId = dataCensusVO.getDeviceId();
//采集值
double value = 0;
double allValue = 0;
//采集平均值
double averageValue = 0;
//采集最小值
double minValue = 999999;
//采集最大值
double maxValue = -999999;
//采集差值
double diffValue = 0;
//采集累加值
double sumValue = 0;
//采集次数
int valueNumber = 0;
//采集时间间隔
int timeInterval = 0;
//时间间隔数据
double timeVale = 0;
List<Double> values = dataCensusVO.getValues();
for (Double val : values) {
value = val;
allValue = allValue + val;
if(val <= minValue){
minValue = val;
}
if(val >= maxValue){
maxValue = val;
}
}
averageValue = allValue / values.size();
diffValue = maxValue - minValue;
sumValue = allValue;
valueNumber = values.size();
timeInterval = 5;
timeVale = averageValue / 60 * 5;
DataCensusDetail dataCensusDetail = new DataCensusDetail();
dataCensusDetail.setCensusId(censusId);
dataCensusDetail.setSewageId(sewageId);
dataCensusDetail.setWorkshopId(workshopId);
dataCensusDetail.setDeviceId(deviceId);
dataCensusDetail.setMetricId(metricId);
dataCensusDetail.setTime(new Date());
dataCensusDetail.setValue(value);
dataCensusDetail.setAverageValue(averageValue);
dataCensusDetail.setMinValue(minValue);
dataCensusDetail.setMaxValue(maxValue);
dataCensusDetail.setDiffValue(diffValue);
dataCensusDetail.setSumValue(sumValue);
dataCensusDetail.setValueNumber(valueNumber);
dataCensusDetail.setTimeInterval(timeInterval);
dataCensusDetail.setTimeVale(timeVale);
dataCensusDetailMapper.addDataCensusDetail(dataCensusDetail);
}
}
}
}
}