获取近一小时内数据访问量/近一周数据访问量/近一天数据访问量绑定图表

绑定图表的时候使用的是ECharts插件，此示例只有后端代码，绑定的是折线图，类似下图

代码是做项目的时候写的，翻看代码的时候突然看到，想记录一下

众所周知，这种情况最重要的就是写对sql语句，所以我们把建表语句和xml贴上来

DDL

create table cap_api_access_log
(
  api_access_log_id  bigint auto_incrementcomment '日志id' primary key,
  api_service_id     bigint not null comment '访问服务id',
  api_access_name    varchar(36) not null comment '访问示例名，直接填入uuid即可',
  api_result_status  varchar(50) null comment '执行结果状态码',
  api_result_info    text null comment '执行结果信息',
  api_param_info     text null comment '参数信息',
  access_addr        varchar(50) not null comment '访问地址',
  access_start_time  timestamp default CURRENT_TIMESTAMP not null comment '访问开始时间',
  response_time      int  null comment '响应时间',
  api_release_version varchar(36) null, comment '版本号'
  authorize_app_name  varchar(100) null comment '被授权应用名'
);

mapper.xml

<?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.hiynn.share.service.gateway.register.mapper.ApiAccessLogMapper">

    <resultMap id="apiAccessLog" type="com.hiynn.share.service.api.gateway.register.entity.ApiAccessLog">
        <id column="api_access_log_id" property="apiAccessLogId"/>
        <result column="api_service_id" property="apiServiceId"/>
        <result column="api_access_name" property="apiAccessName"/>
        <result column="api_result_status" property="apiResultStatus"/>
        <result column="api_result_info" property="apiResultInfo"/>
        <result column="api_param_info" property="apiParamInfo"/>
        <result column="access_addr" property="accessAddress"/>
        <result column="access_start_time" property="accessStartTime"/>
        <result column="response_time" property="responseTime"/>
        <result column="api_release_version" property="apiReleaseVersion"/>
        <result column="authorize_app_name" property="authorizeAppName"/>
    </resultMap>

    <resultMap id="apiAccessLogDto" type="com.hiynn.share.service.api.gateway.register.dto.ApiAccessLogDto"
               extends="apiAccessLog">
        <result column="api_service_name" property="apiServiceName"/>
    </resultMap>

    <resultMap id="accessData" type="com.hiynn.share.service.api.gateway.register.entity.AccessData">
        <result column="times" property="times" javaType="java.lang.String"/>
        <result column="num" property="num" javaType="java.lang.Integer"/>
    </resultMap>

    <sql id="baseColumns">
        api_access_log_id,api_service_id,api_access_name,api_result_status,api_result_info,api_param_info,access_addr,access_start_time,response_time,api_release_version,authorize_app_name
    </sql>




    <!--近一个小时内的数据，统计每十分钟访问次数-->
    <select id="getCountByHour" resultMap="accessData">
        SELECT datas.ti as times,count(*) as num FROM
        (SELECT *,DATE_FORMAT(concat( DATE( access_start_time ), ' ', HOUR ( access_start_time ), ':',
        FLOOR(MINUTE(access_start_time)/10)*10 +MINUTE (now())%10),'%Y-%m-%d %H:%i:00' ) ti FROM cap_api_access_log
        <where>
            <if test="apiServiceId!=null">
                AND api_service_id = #{apiServiceId}
            </if>
            <if test="apiReleaseVersion!=null and apiReleaseVersion!=''">
                AND api_release_version = #{apiReleaseVersion}
            </if>
            <!--若只需要获取异常数据则isError为true-->
            <if test="isError">
                AND api_result_status = '200'
            </if>
            AND access_start_time>DATE_SUB(now() , INTERVAL 1 HOUR)
        </where>
        ) datas GROUP BY datas.ti
    </select>

    <!--近一天的数据-->
    <select id="getCountByDay" resultMap="accessData">
        SELECT datas.ti as times,count(*) as num FROM
        (SELECT *,DATE_FORMAT(CONCAT( DATE(access_start_time) ,' ',HOUR(access_start_time)),'%Y-%m-%d %H:00:00') ti
        FROM cap_api_access_log
        <where>
            <if test="apiServiceId!=null">
                AND api_service_id = #{apiServiceId}
            </if>
            <if test="apiReleaseVersion!=null and apiReleaseVersion!=''">
                AND api_release_version = #{apiReleaseVersion}
            </if>
            <if test="isError">
                AND api_result_status != '200'
            </if>
            AND access_start_time>DATE_SUB(CURRENT_DATE , INTERVAL 1 DAY)
        </where>
        ) datas GROUP BY datas.ti
    </select>

    <!--近一周的数据-->
    <select id="getCountByWeek" resultMap="accessData">
        SELECT datas.times,count(*) as num FROM
        (SELECT *,DATE_FORMAT(access_start_time,'%Y-%m-%d') times FROM cap_api_access_log
        <where>
            <if test="apiServiceId!=null">
                AND api_service_id = #{apiServiceId}
            </if>
            <if test="apiReleaseVersion!=null and apiReleaseVersion!=''">
                AND api_release_version = #{apiReleaseVersion}
            </if>
            <if test="isError">
                AND api_result_status = '200'
            </if>
            AND access_start_time>DATE_SUB(CURRENT_DATE ,INTERVAL 1 WEEK)
        </where>
        ) as datas GROUP BY datas.times ORDER BY datas.times ASC
    </select>


</mapper>

sql写好了，并且没有问题，但是我们绑定图表的时候有问题啊

比如sql查询近一小时数据的时候，10分钟的时候有数据，20分钟这个时间段没有数据，那么20分钟这个坐标就不会显示，所以我在java代码中处理了这种情况

以下为涉及到的实体类和枚举类（@Data为Lombok插件注解，代替了get,set等方法）

package com.hiynn.provider.dto;

import lombok.Data;

/**
 * @author lidai
 * @date 2019/6/12 11:08
 */
@Data
public class AccessData {

    /**
     * 访问时间
     */
    private String times;

    /**
     * 访问次数
     */
    private Integer num;

}

package com.hiynn.provider.entity;

import lombok.AllArgsConstructor;
import lombok.Data;

import java.util.List;

/**
 * @author lidai
 * @date 2019/6/11 14:05
 */
@Data
@AllArgsConstructor
public class ECharts {

    /**
     * 横轴
     */
    private Horizontal horizontal;

    /**
     * 数据
     */
    private List<Object> data;


}

package com.hiynn.provider.entity;

import com.hiynn.provider.enums.HorizontalUnit;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @author lidai
 * @date 2019/6/11 14:10
 * <p>
 * 横轴
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Horizontal {

    /**
     * 横轴数据
     */
    private List<String> names;

    /**
     * 横轴单位
     */
    private HorizontalUnit unit;
}

package com.hiynn.provider.enums;

/**
 * @author lidai
 * @date 2019/6/11 14:13
 */
public enum HorizontalUnit {

    MINUTE(1, "MINUTE"),
    HOUR(2, "HOUR"),
    DAY(3, "DAY");

    private int name;
    private String value;

    HorizontalUnit(int name, String value) {
        this.name = name;
        this.value = value;
    }

    public int getName() {
        return name;
    }

    public void setName(int name) {
        this.name = name;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

dao层 Mapper.java

package com.hiynn.provider.dao;

import com.hiynn.provider.dto.AccessData;
import com.hiynn.provider.dto.ApiAccessLogDto;
import com.hiynn.provider.entity.ApiAccessLog;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author lidai
 * @date 2019/1/25 10:37
 */
@Mapper
public interface ApiAccessLogMapper {

    /**
     * 获取近一小时的访问量，每十分钟为一组
     */
    List<AccessData> getCountByHour(@Param("apiReleaseVersion") String apiReleaseVersion, @Param("apiServiceId") Integer apiServiceId, @Param("isError") Boolean isError);

    /**
     * 获取近一天的访问量，每一个小时为一组
     */
    List<AccessData> getCountByDay(@Param("apiReleaseVersion") String apiReleaseVersion, @Param("apiServiceId") Integer apiServiceId, @Param("isError") Boolean isError);

    /**
     * 获取近一周的访问量，每天为一组
     */
    List<AccessData> getCountByWeek(@Param("apiReleaseVersion") String apiReleaseVersion, @Param("apiServiceId") Integer apiServiceId, @Param("isError") Boolean isError);

}

service

package com.hiynn.provider.service.impl;

import com.hiynn.common.entity.Result;
import com.hiynn.provider.dao.ApiAccessLogMapper;
import com.hiynn.provider.dto.AccessData;
import com.hiynn.provider.dto.ApiAccessLogDto;
import com.hiynn.provider.entity.ECharts;
import com.hiynn.provider.entity.Horizontal;
import com.hiynn.provider.enums.HorizontalUnit;
import com.hiynn.provider.service.ApiAccessLogService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lidai
 * @date 2019/1/25 10:54
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class ApiAccessLogServiceImpl implements ApiAccessLogService {

    @Autowired
    private ApiAccessLogMapper apiAccessLogMapper;


    /**
     * 获取近一小时的访问量
     *
     * @param apiReleaseVersion
     * @return
     */
    public Result getCountByHour(String apiReleaseVersion, Integer apiServiceId, Boolean isError) {
        List<String> horizontalNames = new ArrayList<>();
        List<AccessData> accessDatas = apiAccessLogMapper.getCountByHour(apiReleaseVersion, apiServiceId, isError);
        //获取当前时间和前一小时的时间
        LocalDateTime oldHourTime = LocalDateTime.now().minusHours(1);
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:00");
        while (oldHourTime.isBefore(currentTime) || oldHourTime.equals(currentTime)) {
            horizontalNames.add(oldHourTime.format(formatter));
            oldHourTime = oldHourTime.plusMinutes(10);
        }
        Map<String, Integer> map = accessData2Map(accessDatas);
        List<Object> data = getAccessData(horizontalNames, map);
        //横轴
        Horizontal horizontal = new Horizontal(horizontalNames, HorizontalUnit.MINUTE);
        //横轴和数据
        ECharts echarts = new ECharts(horizontal, data);
        return Result.build().success("获取成功", echarts);
    }

    /**
     * 获取近一天的访问量
     *
     * @param apiReleaseVersion
     * @return
     */
    public Result getCountByDay(String apiReleaseVersion, Integer apiServiceId, Boolean isError) {
        List<String> horizontalNames = new ArrayList<>();
        List<AccessData> accessDatas = apiAccessLogMapper.getCountByDay(apiReleaseVersion, apiServiceId, isError);
        //获取今天的日期和昨天的日期，填充横轴坐标
        LocalDateTime yesterdayTime = LocalDateTime.now().minusDays(1).plusHours(1);
        LocalDateTime currentTime = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:00:00");
        while (yesterdayTime.isBefore(currentTime) || yesterdayTime.equals(currentTime)) {
            horizontalNames.add(yesterdayTime.format(formatter));
            yesterdayTime = yesterdayTime.plusHours(1);
        }
        Horizontal horizontal = new Horizontal(horizontalNames, HorizontalUnit.HOUR);
        //将accessData转为一个map，时间戳为key
        Map<String, Integer> map = accessData2Map(accessDatas);
        List<Object> data = getAccessData(horizontalNames, map);
        ECharts echarts = new ECharts(horizontal, data);
        return Result.build().success("获取成功", echarts);
    }

    /**
     * 获取近一周的访问量
     *
     * @param apiReleaseVersion
     * @return
     */
    public Result getCountByWeek(String apiReleaseVersion, Integer apiServiceId, Boolean isError) {
        List<String> horizontalNames = new ArrayList<>();
        List<AccessData> accessDatas = apiAccessLogMapper.getCountByWeek(apiReleaseVersion, apiServiceId, isError);
        //获取今天的日期和七天前的日期
        LocalDateTime currentTime = LocalDateTime.now();
        LocalDateTime oneWeekAgoTime = LocalDateTime.now().minusWeeks(1).plusDays(1);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        while (oneWeekAgoTime.isBefore(currentTime) || oneWeekAgoTime.equals(currentTime)) {
            horizontalNames.add(oneWeekAgoTime.format(formatter));
            oneWeekAgoTime = oneWeekAgoTime.plusDays(1);
        }
        Horizontal horizontal = new Horizontal(horizontalNames, HorizontalUnit.DAY);
        //将accessData转为一个map，时间戳为key
        Map<String, Integer> map = accessData2Map(accessDatas);
        List<Object> data = getAccessData(horizontalNames, map);
        ECharts echarts = new ECharts(horizontal, data);
        return Result.build().success("获取成功", echarts);
    }

    /**
     * 获取访问量数据
     *
     * @param apiReleaseVersion 访问api版本号
     * @param type              访问类型 0：近一小时    1：近一天   2：近一周
     * @param apiServiceId      访问api主键
     * @param isError           是否为出错统计
     * @return
     */
    @Override
    public Result getCountBySelective(String apiReleaseVersion, Integer type, Integer apiServiceId, Boolean isError) {
        switch (type) {
            case 0:
                return getCountByHour(apiReleaseVersion, apiServiceId, isError);
            case 1:
                return getCountByDay(apiReleaseVersion, apiServiceId, isError);
            case 2:
                return getCountByWeek(apiReleaseVersion, apiServiceId, isError);
            default:
                throw new IllegalArgumentException();
        }
    }

    /**
     * 将{@link AccessData} 转为Map,以时间为主键
     *
     * @param accessDatas
     * @return
     */
    public Map<String, Integer> accessData2Map(List<AccessData> accessDatas) {
        if (null != accessDatas && !accessDatas.isEmpty()) {
            Map<String, Integer> result = new HashMap<>(8);
            for (AccessData accessData : accessDatas) {
                result.put(accessData.getTimes(), accessData.getNum());
            }
            return result;
        }
        return null;
    }

    /**
     * 获取访问数据量
     *
     * @param horizontalNames
     * @param map
     * @return
     */
    public List<Object> getAccessData(List<String> horizontalNames, Map<String, Integer> map) {
        List<Object> result = new ArrayList<>();
        if (null != map && !map.isEmpty()) {
            for (String times : horizontalNames) {
                if (map.containsKey(times))
                    result.add(map.get(times));
                else
                    result.add("");
            }
        }
        return result;
    }
}

controller

package com.hiynn.provider.controller;

import com.hiynn.common.entity.Result;
import com.hiynn.provider.service.ApiAccessLogService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @author lidai
 * @date 2019/1/25 10:46
 */
@RestController
@RequestMapping("/accessLog")
public class ApiAccessLogController {

    @Autowired
    private ApiAccessLogService apiAccessLogService;


    @GetMapping("/accessCount")
    public Result getCountBySelective(@RequestParam("apiReleaseVersion") String apiReleaseVersion,
                                      @RequestParam("type") Integer type,
                                      @RequestParam("apiServiceId") Integer apiServiceId,
                                      @RequestParam("isError") Boolean isError) {
        try {
            return apiAccessLogService.getCountBySelective(apiReleaseVersion, type, apiServiceId, isError);
        } catch (Exception e) {
            e.printStackTrace();
            return Result.build().fail(e.getMessage());
        }
    }

}

获取近一小时数据的结果如下图

 如上图所示，只有最近10分钟有数据，horizontal代码横轴坐标，unit为单位，data对应横轴坐标的数据