SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据

2024-07-10 08:37| 来源: 网络整理| 查看: 265

在这里插入图片描述

文章目录摘要图片案例代码描述：代码没有重新中文整理思路，看着有点乱，敬请谅解整理思路：第一步：根据查询的范围类型优先构建list，包含 list初始化大小，x：时间字符串， y：数量条数，默认为0第二步：查询SQL，对应填充list，返回前端即可

摘要

1、今日数据：以2h为间隔，左间隔时间=< “条数” } /** * 检测一个字符串是否是时间格式 * @param str 请求字符串 * @author liudz * @date 2019/12/17 * @return 执行结果 **/ public static boolean isValidDate(String str) { boolean convertSuccess = true; // 指定日期格式为四位年/两位月份/两位日期，注意yyyy/MM/dd区分大小写；设置lenient为false. 否则SimpleDateFormat会比较宽松地验证日期，比如2007/02/29会被接受，并转换成2007/03/01 SimpleDateFormat format = new SimpleDateFormat("HH:mm"); try { format.setLenient(false); format.parse(str); } catch (Exception e) { convertSuccess = false; } return convertSuccess; } /** * accord( a dateStr and number and type) return a number ago of a newDateStr * @param dateStr dateStr * @param number number * @param type type * @author liudz * @date 2020/3/17 * @return newDateStr **/ public static String returnANumberAgoDate(String dateStr, Integer number, int type) { String newDateStr = ""; try { SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date; date = simpleDateFormat.parse(dateStr); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); if (type == ListEnum.ONE.getValue() || type == ListEnum.TWO.getValue() || type == ListEnum.THREE.getValue() || type == ListEnum.FOUR.getValue()) { calendar.add(Calendar.DATE, number); } else if (type == ListEnum.FIVE.getValue()) { calendar.add(Calendar.MONTH, number); } else if (type == ListEnum.SIX.getValue()) { calendar.add(Calendar.YEAR, number); calendar.add(Calendar.MONTH, 1); } date = calendar.getTime(); newDateStr = simpleDateFormat.format(date); } catch (ParseException e) { log.error(e.getMessage()); } return newDateStr; } /** * according a dateStr returns a map containing dateStrKey * @param type type * @author liudz * @date 2020/3/17 * @return map **/ public static List returnDateMap(int type) { List list = new ArrayList(); if (type == ListEnum.ONE.getValue()) { list = returnDateArrType1(list); } else if (type == ListEnum.TWO.getValue()) { list = returnDateArrType2(list); } else if (type == ListEnum.THREE.getValue()) { list = returnDateArrType3(list, type); } else if (type == ListEnum.FOUR.getValue()) { list = returnDateArrType4(list, type); } else if (type == ListEnum.FIVE.getValue()) { list = returnDateArrType5(list, type); } else { list = returnDateArrType6(list, type); } return list; } /** * 报表3-1，type=1,查询今天数据 * @param list list * @author liudz * @date 2020/3/24 * @return list **/ public static List returnDateArrType1(List list) { int count = 1; Date date = new Date(); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("HH"); String format = simpleDateFormat.format(date); int arrayLength = Integer.valueOf(format) / NumberDictionary.TWO + NumberDictionary.ONE; do { joinListParam(count, list); count++; } while (count case NumberDictionary.ONE: reportDto.setDateStr("00:00-02:00"); break; case NumberDictionary.TWO: reportDto.setDateStr("02:00-04:00"); break; case NumberDictionary.THREE: reportDto.setDateStr("04:00-06:00"); break; case NumberDictionary.FOUR: reportDto.setDateStr("06:00-08:00"); break; case NumberDictionary.FIVE: reportDto.setDateStr("08:00-10:00"); break; case NumberDictionary.SIX: reportDto.setDateStr("10:00-12:00"); break; case NumberDictionary.SEVEN: reportDto.setDateStr("12:00-14:00"); break; case NumberDictionary.EIGHT: reportDto.setDateStr("14:00-16:00"); break; case NumberDictionary.NINE: reportDto.setDateStr("16:00-18:00"); break; case NumberDictionary.TEN: reportDto.setDateStr("18:00-20:00"); break; case NumberDictionary.ELEVEN: reportDto.setDateStr("20:00-22:00"); break; case NumberDictionary.TWELVE: reportDto.setDateStr("22:00-23:59"); break; default: throw new RuntimeException("no suck this arrayLength!"); } list.add(reportDto); } /** * 报表3-1，type=2,查询昨天数据 * @param list list * @author liudz * @date 2020/3/24 * @return list **/ public static List returnDateArrType2(List list) { int count = 1; do { joinListParam(count, list); count++; } while (count try { ReportDto reportDto = new ReportDto(); reportDto.setDateStr(dateStr); list.add(reportDto); date = simpleDateFormat.parse(dateStr); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DATE, 1); date = calendar.getTime(); dateStr = simpleDateFormat.format(date); count++; } catch (ParseException e) { log.error(e.getMessage()); } } while (count try { date = simpleDateFormat.parse(dateStr); String oldDateStr = dateStr; Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DATE, ListEnum.FOUR.getValue()); date = calendar.getTime(); dateStr = simpleDateFormat.format(date); ReportDto reportDto = new ReportDto(); reportDto.setDateStr(oldDateStr + "~" + simpleDateFormat.format(date)); list.add(reportDto); date = simpleDateFormat.parse(dateStr); calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DATE, ListEnum.ONE.getValue()); date = calendar.getTime(); dateStr = simpleDateFormat.format(date); count++; } catch (ParseException e) { log.error(e.getMessage()); } } while (count try { ReportDto reportDto = new ReportDto(); reportDto.setDateStr(dateStr); list.add(reportDto); date = simpleDateFormat2.parse(dateStr); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.MONTH, 1); date = calendar.getTime(); dateStr = simpleDateFormat.format(date).substring(0, ListEnum.SEVEN.getValue()); count++; } catch (ParseException e) { log.error(e.getMessage()); } } while (count try { ReportDto reportDto = new ReportDto(); reportDto.setDateStr(dateStr); list.add(reportDto); date = simpleDateFormat2.parse(dateStr); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.MONTH, 1); date = calendar.getTime(); dateStr = simpleDateFormat.format(date).substring(0, ListEnum.SEVEN.getValue()); count++; } catch (ParseException e) { log.error(e.getMessage()); } } while (count List result = alarmMessageMapper.getReportDataPartThree1(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType1(list, result); } else if (reportParam.getType() == ListEnum.TWO.getValue()) { List result = alarmMessageMapper.getReportDataPartThree2(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType1(list, result); } else if (reportParam.getType() == ListEnum.THREE.getValue()) { List result = alarmMessageMapper.getReportDataPartThree3(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType3(list, result); } else if (reportParam.getType() == ListEnum.FOUR.getValue()) { List result = alarmMessageMapper.getReportDataPartThree4(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType4(list, result); } else if (reportParam.getType() == ListEnum.FIVE.getValue()) { List result = alarmMessageMapper.getReportDataPartThree5(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType5(list, result); } else if (reportParam.getType() == ListEnum.SIX.getValue()) { List result = alarmMessageMapper.getReportDataPartThree6(reportParam); list = DateTool.returnDateMap(reportParam.getType()); list = fillMapType6(list, result); } log.debug("--AlarmMessageServiceImpl--getReportDataPartThree--end--result:{}", reportParam); return list; } /** * 报表3-1，type=1,查询今天数据或报表3-2，type=2,查询昨天数据 * @param list list * @param result result * @author liudz * @date 2020/3/24 * @return fillMap **/ public List fillMapType1(List list, List result) { ReportDto reportDto; for (int i = 0; i String[] split = list.get(i).getDateStr().split("-"); if (result.get(j).getDateStr().compareTo(split[0]) >= 0 && result.get(j).getDateStr().compareTo(split[1]) for (ReportDto item : list) { for (ReportDataParamThree r : result) { if (item.getDateStr().equals(r.getDateStr())) { item.setCount(r.getCount()); list.set(list.indexOf(item), item); } } } return list; } /** * 报表3-4，type=4,查询过去30天数据 * @param list list * @param result result * @author liudz * @date 2020/3/24 * @return fillMap **/ public List fillMapType4(List list, List result) { ReportDto reportDto; for (int i = 0; i String[] split = list.get(i).getDateStr().split("~"); if (result.get(j).getDateStr().compareTo(split[0]) >= 0 && result.get(j).getDateStr().compareTo(split[1]) for (ReportDto item : list) { for (ReportDataParamThree r : result) { if (item.getDateStr().equals(r.getDateStr())) { item.setCount(r.getCount()); list.set(list.indexOf(item), item); } } } return list; } /** * 报表3-6，type=6, 查询过去一年数据 * @param list list * @param result result * @author liudz * @date 2020/3/24 * @return fillMap **/ public List fillMapType6(List list, List result) { for (ReportDto item : list) { for (ReportDataParamThree r : result) { if (item.getDateStr().equals(r.getDateStr())) { item.setCount(r.getCount()); list.set(list.indexOf(item), item); } } } return list; }

AlarmMessageSqlProvider

/** * 报表数据部分1，查询总览预警情况 * @param reportParam 请求参数 * @author liudz * @date 2019/12/2 * @return 执行结果 **/ public String getReportDataPartOne(ReportParam reportParam) { SQL sql = new SQL(); if (DictionaryEnum.ZERO.getFiledString().equals(reportParam.getCategory())) { sql.SELECT("device_key, category, COUNT(*)"); } else { sql.SELECT("task_name, category, COUNT(*), task_id"); } sql.FROM("ge_alarm_message"); sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}"); sql.WHERE("category = #{category,jdbcType=VARCHAR}"); sql.WHERE("date(ctime) = CURDATE()"); if (DictionaryEnum.ZERO.getFiledString().equals(reportParam.getCategory())) { sql.GROUP_BY("device_key"); } else { sql.GROUP_BY("task_name, task_id"); } return sql.toString(); } /** * 报表数据部分2，查询详细预警情况 * @param reportParam 请求参数 * @author liudz * @date 2019/12/2 * @return 执行结果 **/ public String getReportDataPartTwo(ReportParam reportParam) { SQL sql = new SQL(); sql.SELECT("id, task_name, task_id, content, time, product_key, device_key, ctime"); sql.FROM("ge_alarm_message"); sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}"); sql.WHERE("category = #{category,jdbcType=VARCHAR}"); sql.WHERE("date(ctime) = CURDATE()"); if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) { sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}"); } if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) { sql.WHERE("task_id = #{taskId}"); } sql.ORDER_BY("ctime desc"); return sql.toString(); } /** * 报表数据3-1：type=1,今日数据 * @param reportParam 请求参数 * @author liudz * @date 2019/12/2 * @return 执行结果 **/ public String getReportDataPartThree1(ReportParam reportParam) { SQL sql = new SQL(); sql.SELECT("concat(DATE_FORMAT(ctime, '%H'), ':00') as dateStr,count(*) as count"); sql.FROM("ge_alarm_message"); sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}"); sql.WHERE("category = #{category,jdbcType=VARCHAR}"); sql.WHERE("date(ctime) = CURDATE()"); if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) { sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}"); } if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) { sql.WHERE("task_id = #{taskId}"); } sql.GROUP_BY("concat(DATE_FORMAT(ctime, '%H'), ':00')"); return sql.toString(); } /** * 报表数据3-2：type=2,过去24小时数据 * @param reportParam 请求参数 * @author liudz * @date 2019/12/2 * @return 执行结果 **/ public String getReportDataPartThree2(ReportParam reportParam) { SQL sql = new SQL(); sql.SELECT("concat(DATE_FORMAT(ctime, '%H'), ':00') as dateStr,count(*) as count"); sql.FROM("ge_alarm_message"); sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}"); sql.WHERE("category = #{category,jdbcType=VARCHAR}"); sql.WHERE("TO_DAYS(NOW()) - TO_DAYS(ctime) = 1"); if (!StringUtils.isEmpty(reportParam.getDeviceKey()) && StringUtils.isEmpty(reportParam.getTaskId())) { sql.WHERE("device_key = #{deviceKey,jdbcType=VARCHAR}"); } if (!StringUtils.isEmpty(reportParam.getTaskId()) && StringUtils.isEmpty(reportParam.getDeviceKey())) { sql.WHERE("task_id = #{taskId}"); } sql.GROUP_BY("concat(DATE_FORMAT(ctime, '%H'), ':00')"); return sql.toString(); } /** * 报表数据3-3：type=3,过去7天数据 * @param reportParam 请求参数 * @author liudz * @date 2019/12/2 * @return 执行结果 **/ public String getReportDataPartThree3(ReportParam reportParam) { SQL sql = new SQL(); sql.SELECT("SUBSTRING(ctime,1,10) as dateStr,count(*) as count"); sql.FROM("ge_alarm_message"); sql.WHERE("cuser = #{cuser,jdbcType=BIGINT}"); sql.WHERE("category = #{category,jdbcType=VARCHAR}"); sql.WHERE("DATE_SUB(CURDATE(), INTERVAL 6 DAY)

【本文地址】

公司简介

联系我们