建立五分钟数据表以及读取一分钟表数据计算平均值到五分钟表的接口

kaiyu

2020-10-16 2973f298e49fd8dff95065ef88d2997fe4eed338

建立五分钟数据表以及读取一分钟表数据计算平均值到五分钟表的接口

 src/main/java/com/moral/mapper/HistoryFiveMinutelyMapper.java

New file
@@ -0,0 +1,15 @@
package com.moral.mapper;

import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface HistoryFiveMinutelyMapper {

    List<Map<String, Object>> getFiveMinutesSensorData(Map<String, Object> map);

    int insertHistoryFiveMinutely(@Param("list") List<Map<String, Object>> list, @Param("yearAndMonth") String yearAndMonth);

    void createHistoryFiveMinutely(@Param("yearAndMonth") String yearAndMonth);
}

 src/main/java/com/moral/service/HistoryFiveMinutelyService.java

New file
@@ -0,0 +1,12 @@
package com.moral.service;

import java.util.List;
import java.util.Map;

public interface HistoryFiveMinutelyService {
    List<Map<String,Object>> getFiveMinutesSensorData(Map<String, Object> map);

    int insertHistoryFiveMinutely(List<Map<String, Object>> list, String yearAndMonth);

    void createHistoryFiveMinutelyTable(String yearAndMonth);
}

 src/main/java/com/moral/service/impl/HistoryFiveMinutelyServiceImpl.java

New file
@@ -0,0 +1,31 @@
package com.moral.service.impl;

import com.moral.mapper.HistoryFiveMinutelyMapper;
import com.moral.service.HistoryFiveMinutelyService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Service
public class HistoryFiveMinutelyServiceImpl implements HistoryFiveMinutelyService{

    @Resource
    private HistoryFiveMinutelyMapper historyFiveMinutelyMapper;

    @Override
    public List<Map<String, Object>> getFiveMinutesSensorData(Map<String, Object> map) {
        return historyFiveMinutelyMapper.getFiveMinutesSensorData(map);
    }

    @Override
    public int insertHistoryFiveMinutely(List<Map<String, Object>> list, String yearAndMonth) {
        return historyFiveMinutelyMapper.insertHistoryFiveMinutely(list,yearAndMonth);
    }

    @Override
    public void createHistoryFiveMinutelyTable(String yearAndMonth) {
        historyFiveMinutelyMapper.createHistoryFiveMinutely(yearAndMonth);
    }
}

 src/main/java/com/moral/util/DateUtil.java

@@ -1,9 +1,172 @@
package com.moral.util;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateUtil {
    /**
            * 英文简写（默认）如：2010-12-01
            */
    public static String FORMAT_SHORT = "yyyy-MM-dd";
    /**
     * 英文全称  如：2010-12-01 23:15:06
     */
    public static String FORMAT_LONG = "yyyy-MM-dd HH:mm:ss";
    /**
     * 精确到毫秒的完整时间    如：yyyy-MM-dd HH:mm:ss.S
     */
    public static String FORMAT_FULL = "yyyy-MM-dd HH:mm:ss.S";
    /**
     * 中文简写  如：2010年12月01日
     */
    public static String FORMAT_SHORT_CN = "yyyy年MM月dd";
    /**
     * 中文全称  如：2010年12月01日  23时15分06秒
     */
    public static String FORMAT_LONG_CN = "yyyy年MM月dd日  HH时mm分ss秒";
    /**
     * 精确到毫秒的完整中文时间
     */
    public static String FORMAT_FULL_CN = "yyyy年MM月dd日  HH时mm分ss秒SSS毫秒";


    public static void main(String[] args) {
        Date date = rollMinute(new Date(), -2);
        System.out.println(date);

    }



    /**
     * 获取当前时间
     */
    public static String getTimeString() {
        SimpleDateFormat df = new SimpleDateFormat(FORMAT_SHORT_CN);
        Calendar calendar = Calendar.getInstance();
        return df.format(calendar.getTime());
    }

    /**
     * 获取日期年份
     * @param date 日期
     * @return
     */
    public static String getYear(Date date) {
        SimpleDateFormat df = new SimpleDateFormat(FORMAT_FULL);
        return df.format(date).substring(0, 4);
    }

    /**
     * 功能描述：返回月
     *
     * @param date
     *            Date 日期
     * @return 返回月份
     */
    public static String getMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        int Month = calendar.get(Calendar.MONTH);
        Month++;
        String str = String.valueOf(Month);
        return str = str.length()==1?"0".concat(str):str;
    }

    /**
     * 功能描述：返回日期
     *
     * @param date
     *            Date 日期
     * @return 返回日份
     */
    public static int getDay(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.DAY_OF_MONTH);
    }

    /**
     * 功能描述：返回小时
     *
     * @param date
     *            日期
     * @return 返回小时
     */
    public static int getHour(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.HOUR_OF_DAY);
    }

    /**
     * 功能描述：返回分
     *
     * @param date
     *            日期
     * @return 返回分钟
     */
    public static String getMinute(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        int minute = calendar.get(Calendar.MINUTE);
        String str = String.valueOf(minute);
        return str = str.length()==1?"0".concat(str):str;
    }

    /**
     * 返回秒钟
     *
     * @param date
     *            Date 日期
     * @return 返回秒钟
     */
    public static int getSecond(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.SECOND);
    }

    /**
     * 功能描述：返回毫
     *
     * @param date
     *            日期
     * @return 返回毫
     */
    public static long getMillis(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.getTimeInMillis();
    }

    /**
     * 功能描述：对时间进行分钟的加减
     *
     * @param
     *
     * @return 返回日期类型
     */
    public static Date rollMinute(Date d, int minute) {
        return new Date(d.getTime() + minute * 60 * 1000);
    }


    /**
     * 前/后?月
     *
     * @param d
     * @param mon
     * @return
     */
    public static Date rollMon(Date d, int mon) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(d);
        cal.add(Calendar.MONTH, mon);
        return cal.getTime();
    }

    //获得过去i天的年月日
    public static String getOldTime(int i){
        Long time=System.currentTimeMillis();

 src/main/resources/mapper/HistoryFiveMinutelyMapper.xml

New file
@@ -0,0 +1,35 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.HistoryFiveMinutelyMapper">
    <select id="getFiveMinutesSensorData" resultType="java.util.LinkedHashMap">
        select
        h.mac
        <foreach collection="sensorKeys" open="," separator="," item="sensorKey">
            ROUND(AVG(json->'$.${sensorKey}[0]'),3) AS 'AVG${sensorKey}',
            ROUND(MIN(json->'$.${sensorKey}[1]'),3) AS 'MIN${sensorKey}',
            ROUND(MAX(json->'$.${sensorKey}[2]'),3) AS 'MAX${sensorKey}'
        </foreach>
        FROM
        history_minutely_${yearAndMonth} h
        where  h.time<![CDATA[>=]]>#{start} and h.time <![CDATA[<=]]>  #{end}
        group by h.mac
    </select>

    <insert id="insertHistoryFiveMinutely">
        insert into
        history_five_minutely_${yearAndMonth}
        values
        <foreach collection="list" item="map" separator=",">
            (#{map.mac},#{map.time},#{map.json})
        </foreach>
    </insert>

    <update id="createHistoryFiveMinutely" parameterType="String">
        CREATE TABLE  IF NOT EXISTS `history_five_minutely_${yearAndMonth}` (
        `mac` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
        `time` datetime DEFAULT NULL,
        `json` json DEFAULT NULL
        ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    </update>

</mapper>