java与sql解决某段时间数据排序并反馈前端

本例子补充：
用来说明 笔者本人某篇 日期往前推或后退一段时间

/**
	 * 根据筛选条件  获取路名拥堵次数的排名
	 * @param latestDays 近三天，近七天，一个月，三个月
	 * @param peakTime "早高峰【07:00-09:00】"
	 * @param ydjbcode "1"-畅通,"2"-缓行,"3"-拥挤,"4"-严重拥堵
	 */
	public static Map<String, Object> getTop10Ydqk(String latestDays,String peakTime,String ydjbcode,Integer topNum){
		if(StringUtils.isEmpty(latestDays)||StringUtils.isEmpty(ydjbcode)) return null;	
		if(topNum == null) topNum=10;
//		拼接 cjsj条件
		String dateTimeSql=buildFilterIfForCjsj(latestDays, peakTime,null);
		String sql="select lm,count(lm) count from ycl_jtlljc where ydjb='"+ydjbcode+"'";
//		筛选条件  有时间段
		if(dateTimeSql.length()!=0) {
			sql+=" and ("+dateTimeSql+")";
		}
		sql+=" group by lm order by count desc";
		Table tab=SQL.select(sql, null, dataModel);
		Iterator<Row> iterator=tab.iterator();
		int count=0;
		Map<String, Object> resultMap=new HashMap<String, Object>();
		List<String> roadList=new ArrayList<String>(),trafficCountList=new ArrayList<String>();
		while(iterator.hasNext() && count<topNum) {
			Row row=iterator.next();
			roadList.add(row.getString("LM"));
			trafficCountList.add(row.getDecimal("COUNT").toString());
			count++;
		}
		resultMap.put("roadList", roadList) ;
		resultMap.put("trafficCountList", trafficCountList);
		
		return resultMap;
	}
	
	public static String buildFilterIfForCjsj(String latestDays,String peakTime,String isKSQL) {
		String startTime="00:00:00",endTime="23:59:59";
		if(StringUtils.isNotEmpty(peakTime)&&peakTime.indexOf("-")>4) {
			int index=peakTime.indexOf("-");
			startTime=peakTime.substring(index-5,index);
			endTime=peakTime.substring(index+1,index+6);
		}
//		获取筛选的 日期
		Calendar cal=Calendar.getInstance();
		Date endDate=cal.getTime();
		switch(latestDays) {
		case "近三天":
			cal.add(Calendar.DATE, -2);//当天包含在内共显示3天数据
			break;
		case "近七天":
			cal.add(Calendar.DATE, -6);
			break;
		case "近一个月":
			cal.add(Calendar.MONTH, -1);
			break;
		case "近三个月":
			cal.add(Calendar.MONTH, -3);
			break;
		}
		/*拼接日期 时间sql条件  
		目标格式 cjsj >= to_date('2020/12/26 15:36:13','yyyy/mm/dd hh24:mi:ss') and 
		cjsj <= to_date('2021/12/20 00:00:00','yyyy/mm/dd hh24:mi:ss')*/
		String dateTimeSql="";
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");
		while(cal.getTime().compareTo(endDate)<=0) {//日期在截至日期前或当天
			String tempStartTime=sdf.format(cal.getTime())+" "+startTime;
			String tempEndTime=sdf.format(cal.getTime())+" "+endTime;
			if(dateTimeSql.length()==0) {
				dateTimeSql="cjsj >= to_date('"+tempStartTime+"','yyyy/mm/dd hh24:mi:ss')" +
						" and cjsj <= to_date('"+tempEndTime+"','yyyy/mm/dd hh24:mi:ss')";
				if(StringUtils.isNotEmpty(isKSQL)) {
					dateTimeSql="YCL_JTLLJC.CJSJ >= stringToDateTime('"+tempStartTime+"')" +
							" and YCL_JTLLJC.CJSJ <= stringToDateTime('"+tempEndTime+"')";
				}
			} else {
				if(StringUtils.isNotEmpty(isKSQL)) {
					dateTimeSql+="OR YCL_JTLLJC.CJSJ >= stringToDateTime('"+tempStartTime+"')" +
							" and YCL_JTLLJC.CJSJ <= stringToDateTime('"+tempEndTime+"')";
				} else {
					dateTimeSql+="or cjsj >= to_date('"+tempStartTime+"','yyyy/mm/dd hh24:mi:ss')" +
							" and cjsj <= to_date('"+tempEndTime+"','yyyy/mm/dd hh24:mi:ss')";
				}
			}
			cal.add(Calendar.DATE, 1);//日期后推一天
		}
		return dateTimeSql;
	}
	```