springboot+mybatis3.5.2动态查询某一字段在某一段时间内的统计信息（折线图）

需求：
动态查询某一统计字段在一段时间内的统计折线图信息

1. controller层

    @ApiOperation(value = "getStatisticDetail", notes = "统计折线图")@GetMapping("/detail")@ResponseStatus(HttpStatus.OK)@AccessLogAnnotation(ignoreRequestArgs = "loginUser")@ApiImplicitParams({@ApiImplicitParam(name = "startTime", value = "开始时间", dataTypeClass = String.class),@ApiImplicitParam(name = "endTime", value = "结束时间", dataTypeClass = String.class),@ApiImplicitParam(name = "taskType", value = "查询类型", required = true, dataTypeClass = Enum.class)})public Result getStatisticDetail(@ApiIgnore @RequestAttribute(value = Constants.SESSION_USER) User loginUser,@RequestParam(value = "taskType", required = true) String taskType,@RequestParam(value = "startTime", required = true) String startTime,@RequestParam(value = "endTime", required = true) String endTime) {return statisticAnalysisService.getStatisticDetail(startTime, endTime, Long.valueOf(projectCode), taskType);}  

2. service层biz

    public Result<StatisticDetailResponse> getStatisticDetail( String startTime, String endTime String taskType) {Result<StatisticDetailResponse> result = new Result<>();StatisticDetailResponse statisticDetailResponse = new StatisticDetailResponse();//计算时间天数跨度String[] days = new String[getDayDiffer(startTime, endTime)];startTime = "'" + startTime + "'";endTime = "'" + endTime + "'";     StatisticDetailResponse responseList = getStatisticTasks(statisticDetailResponse, startTime, endTime,Objects.requireNonNull(StatisticDetailType.getStatisticDetailByType(taskType)), days);result.setData(responseList);putMsg(result, Status.SUCCESS);return result;}private StatisticDetailResponse getStatisticTasks(StatisticDetailResponse statisticDetailResponse, String startTime, String endTime, StatisticDetailType statisticDetailType, String[] days) {String filed = statisticDetailType.getFeildName();List<StatisticAnalysis> details = statisticAnalysisMapper.getStatisticDetail(startTime, endTime, filed, days);List<String> xAxisData = new ArrayList<>(12);List<StatisticTask> statisticTaskList = new ArrayList<>(12);if (ObjectUtils.isNotEmpty(statisticDetailResponse.getList())) {statisticTaskList = statisticDetailResponse.getList();}List data = new ArrayList<>(12);for (StatisticAnalysis detail : details) {String key = detail.getName();Object value = detail.getNameValue();xAxisData.add(key.replaceAll(String.valueOf(Constants.SUBTRACT_CHAR), ""));data.add(value);}StatisticTask statisticTask = new StatisticTask();statisticTask.setData(data);statisticTask.setName(statisticDetailType.getType());statisticTaskList.add(statisticTask);statisticDetailResponse.setXAxisData(xAxisData);statisticDetailResponse.setList(statisticTaskList);return statisticDetailResponse;}

2.1. 查询开始时间——结束时间的间隔天数

    /*** 计算开始时间，结束时间，间隔天数** @param startTime 开始时间* @param endTime   结束时间* @return 间隔天数*/public static int getDayDiffer(String startTime, String endTime) {SimpleDateFormat formatter = new SimpleDateFormat(Constants.DATE_PATTERN1);long ts2 = 0;try {Date date1 = null;Date date = formatter.parse(startTime);long ts = date.getTime();date1 = formatter.parse(endTime);long ts1 = date1.getTime();ts2 = ts1 - ts;} catch (ParseException e) {e.printStackTrace();}int totalTime = 0;totalTime = (int) (ts2 / (24 * 3600 * 1000) + 1);return totalTime;}

2.2. taskType枚举类型

public enum StatisticDetailType {/*** 当日存储剩余空间（单位P）*/freeSpace("freeSpace", "free_space"),/*** 当日空间使用比例（单位%）*/useRatio("useRatio", "use_ratio");private String type;private String feildName;StatisticDetailType() {}StatisticDetailType(String type, String feildName) {this.type = type;this.feildName = feildName;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getFeildName() {return feildName;}public void setFeildName(String feildName) {this.feildName = feildName;}/*** 根据枚举值返回枚举对象** @param key* @return*/public static StatisticDetailType getStatisticDetailByType(String key) {for (StatisticDetailType statisticDetailType : values()) {if (Objects.equals(statisticDetailType.type, key)) {return statisticDetailType;}}return null;}
}

3. Mapper

    /*** @param startTime   结束时间* @param endTime     开始时间* @param filed       查询字段* @param days        查询天数* @return 查询结果*/List<StatisticAnalysis> getStatisticDetail(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("filed") String filed, @Param("days") String[] days);

4. Mapper.xml

    <select id="getStatisticDetail" resultType="entity.StatisticAnalysis">select d.name, IFNULL(T.${filed},0) nameValuefrom (select date_add(${startTime},interval @i:=@i+1 day) as namefrom (select 1<foreach item="index" collection="days">union all select 1</foreach>) as tmp,(select @i:= -1) t) dleft join (select ${filed},DATE_FORMAT(statistic_date,'%Y-%m-%d') statistic_datefrom s_statistic_analysiswhere  statistic_date &gt;= ${startTime} and statistic_date &lt;=${endTime})T on T.statistic_date = d.name</select>

5. entity

public class StatisticDetailResponse {private List<String> xAxisData;private List<StatisticTask> list;}public class StatisticTask {private String name;private List data;}

6. 结果

{"code": 0,"msg": "success","data": {"list": [{"name": "freeSpace","data": ["0.00","0.00","0.00","0.00","0.00"]}],"xaxisData": ["20231023","20231024","20231025","20231026","20231027"]},"success": true,"failed": false
}