screen-job/src/main/java/com/moral/api/mapper/HistoryHourlyMapper.java
@@ -1,9 +1,9 @@ package com.moral.api.mapper; import java.util.List; import java.util.Map; import com.moral.api.entity.HistoryHourly; import com.baomidou.mybatisplus.core.mapper.BaseMapper; /** * <p> @@ -13,10 +13,16 @@ * @author moral * @since 2021-06-28 */ public interface HistoryHourlyMapper extends BaseMapper<HistoryHourly> { public interface HistoryHourlyMapper { void createTable(String timeUnits); void insertHistoryHourly(List<HistoryHourly> list); Integer selectCountByTime(Map<String, Object> params); List<Map<String, Object>> selectDailyData(Map<String, Object> params); }
@@ -1,7 +1,7 @@ package com.moral.api.service; import com.moral.api.entity.HistoryHourly; import com.baomidou.mybatisplus.extension.service.IService; import java.util.List; import java.util.Map; /** * <p> @@ -11,9 +11,15 @@ * @author moral * @since 2021-06-28 */ public interface HistoryHourlyService extends IService<HistoryHourly> { public interface HistoryHourlyService { //小时表创建 void createTable(String timeUnits); //缺失小时数据补充 void insertHistoryHourly(); //从分钟表统计天数据 List<Map<String, Object>> selectDailyData(Map<String, Object> params); } screen-job/src/main/java/com/moral/api/service/impl/HistoryDailyServiceImpl.java
@@ -3,7 +3,6 @@ import com.alibaba.fastjson.JSONObject; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.moral.api.entity.HistoryDaily; import com.moral.api.entity.HistoryHourly; import com.moral.api.entity.Sensor; import com.moral.api.mapper.HistoryDailyMapper; import com.moral.api.service.HistoryDailyService; @@ -63,9 +62,13 @@ List<Sensor> sensors = sensorService.list(sensorQueryWrapper); //获取所有设备小时数据 QueryWrapper<HistoryHourly> historyHourlyQueryWrapper = new QueryWrapper<>(); historyHourlyQueryWrapper.ge("time", DateUtils.dateToDateString(start)).le("time", DateUtils.dateToDateString(end)); List<Map<String, Object>> dailyData = historyHourlyService.listMaps(historyHourlyQueryWrapper); Map<String, Object> prop = new HashMap<>(); String timeUnits = DateUtils.dateToDateString(start, DateUtils.yyyyMM_EN); prop.put("timeUnits", timeUnits); prop.put("start", DateUtils.dateToDateString(start)); prop.put("end", DateUtils.dateToDateString(end)); List<Map<String, Object>> dailyData = historyHourlyService.selectDailyData(prop); if (dailyData.size() == 0) { return; } screen-job/src/main/java/com/moral/api/service/impl/HistoryHourlyServiceImpl.java
@@ -7,7 +7,6 @@ import com.moral.api.mapper.HistoryHourlyMapper; import com.moral.api.mapper.HistoryMinutelyMapper; import com.moral.api.service.HistoryHourlyService; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.moral.api.service.SensorService; import com.moral.constant.Constants; import com.moral.constant.RedisConstants; @@ -39,7 +38,7 @@ * @since 2021-06-28 */ @Service public class HistoryHourlyServiceImpl extends ServiceImpl<HistoryHourlyMapper, HistoryHourly> implements HistoryHourlyService { public class HistoryHourlyServiceImpl implements HistoryHourlyService { @Autowired private HistoryHourlyMapper historyHourlyMapper; @@ -54,6 +53,11 @@ private SensorService sensorService; @Override public void createTable(String timeUnits) { historyHourlyMapper.createTable(timeUnits); } @Override public void insertHistoryHourly() { //时间格式化：yyyy-MM-dd HH:mm String format = DateUtils.yyyy_MM_dd_HH_EN; @@ -64,16 +68,18 @@ Date now = new Date(); String time = DateUtils.dateToDateString(now, format) + ":00:00"; QueryWrapper<HistoryHourly> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("time", time); //查询数据库当前小时的条数，如果比macs小，说明需要数据补充 Integer count = historyHourlyMapper.selectCount(queryWrapper); if (macs.size() > count) { Map<String, Object> prop = new HashMap<>(); prop.put("timeUnits", DateUtils.getDateStringOfMon(0, DateUtils.yyyyMM_EN)); prop.put("time", time); Integer count = historyHourlyMapper.selectCountByTime(prop); if (macs.size() <= count) { return; } else { macs.removeIf(mac -> { queryWrapper.clear(); queryWrapper.eq("time", time); queryWrapper.eq("mac", mac); Integer num = historyHourlyMapper.selectCount(queryWrapper); prop.put("mac", mac); Integer num = historyHourlyMapper.selectCountByTime(prop); return num != 0; }); } @@ -213,4 +219,9 @@ //存入小时表 historyHourlyMapper.insertHistoryHourly(insertData); } @Override public List<Map<String, Object>> selectDailyData(Map<String, Object> params) { return historyHourlyMapper.selectDailyData(params); } } screen-job/src/main/java/com/moral/api/task/CreateTableTask.java
@@ -4,6 +4,7 @@ import org.springframework.stereotype.Component; import com.moral.api.service.HistoryFiveMinutelyService; import com.moral.api.service.HistoryHourlyService; import com.moral.api.service.HistoryMinutelyService; import com.moral.constant.Constants; import com.moral.util.DateUtils; @@ -19,6 +20,9 @@ @Autowired private HistoryMinutelyService historyMinutelyService; @Autowired private HistoryHourlyService historyHourlyService; //分钟表创建任务 @@ -49,4 +53,20 @@ } return ReturnT.SUCCESS; } //小时表创建任务 @XxlJob("createHistoryHourlyTable") public ReturnT createHistoryHourlyTable() { String timeUnits = DateUtils.getDateStringOfMon(1, DateUtils.yyyyMM_EN); try { //已校准小时表 historyHourlyService.createTable(timeUnits); //未校准小时表 historyHourlyService.createTable(timeUnits + "_" + Constants.UN_ADJUST); } catch (Exception e) { XxlJobHelper.log(e.getMessage()); return new ReturnT(ReturnT.FAIL_CODE, e.getMessage()); } return ReturnT.SUCCESS; } } screen-job/src/main/resources/mapper/HistoryHourlyMapper.xml
@@ -2,13 +2,14 @@ <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.moral.api.mapper.HistoryHourlyMapper"> <!-- 通用查询映射结果 --> <resultMap id="BaseResultMap" type="com.moral.api.entity.HistoryHourly"> <result column="mac" property="mac"/> <result column="time" property="time"/> <result column="value" property="value"/> <result column="version" property="version"/> </resultMap> <update id="createTable" parameterType="String"> CREATE TABLE IF NOT EXISTS `history_hourly_${timeUnits}` ( `mac` VARCHAR (20) DEFAULT NULL COMMENT '设备mac', `time` datetime DEFAULT NULL COMMENT '数据时间', `value` json DEFAULT NULL COMMENT '数据', KEY `idx_mac_time` (`mac`,`time`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '小时数据表' </update> <insert id="insertHistoryHourly"> INSERT INTO history_hourly VALUES @@ -16,4 +17,18 @@ (#{item.mac}, #{item.time}, #{item.value}, #{item.version}) </foreach> </insert> <select id="selectCountByTime" resultType="java.lang.Integer"> SELECT COUNT(1) FROM history_hourly_${timeUnits} WHERE `time` = #{time} <if test="mac != null"> AND mac = #{mac} </if> </select> <select id="selectDailyData" resultType="java.util.Map"> SELECT mac, `value` FROM history_hourly_${timeUnits} WHERE `time` <![CDATA[>=]]> #{start} AND `time` <![CDATA[<]]> #{end} </select> </mapper>
