New file |
| | |
| | | package com.whyc.controller; |
| | | |
| | | import com.whyc.dto.Response; |
| | | import com.whyc.pojo.db_ckpwrdev_alarm.CKPowerDevAlarm; |
| | | import com.whyc.service.CKPowerDevAlarmService; |
| | | import io.swagger.annotations.Api; |
| | | import io.swagger.annotations.ApiOperation; |
| | | import org.springframework.beans.factory.annotation.Autowired; |
| | | import org.springframework.web.bind.annotation.*; |
| | | |
| | | @RestController |
| | | @Api(tags = "测控电源告警") |
| | | @RequestMapping("ckPowerDevAlarm") |
| | | public class CKPowerDevAlarmController { |
| | | |
| | | @Autowired |
| | | private CKPowerDevAlarmService service; |
| | | |
| | | @PostMapping("getPage") |
| | | @ApiOperation("分页查询") |
| | | public Response getPage(@RequestBody CKPowerDevAlarm alarm,@RequestParam int pageNum,@RequestParam int pageSize){ |
| | | return service.getPage(alarm,pageNum,pageSize); |
| | | } |
| | | |
| | | } |
New file |
| | |
| | | package com.whyc.mapper; |
| | | |
| | | import com.whyc.pojo.db_ckpwrdev_alarm.CKPowerDevAlarm; |
| | | |
| | | public interface CKPowerDevAlarmMapper extends CustomMapper<CKPowerDevAlarm> { |
| | | } |
New file |
| | |
| | | package com.whyc.mapper; |
| | | |
| | | import java.util.List; |
| | | |
| | | /** |
| | | * 通用mapper,用于通用数据的查询 |
| | | */ |
| | | public interface CommonMapper { |
| | | |
| | | /** |
| | | * @param schema dbName |
| | | * @param table tableName |
| | | * @param field 这个字段对应的值类型为数值,则valuePrefix为null,如果不为数值,valuePrefix必须指定非数值的前缀 |
| | | //* @param valuePrefix null | String |
| | | * @return |
| | | */ |
| | | //Object getMaxValue(String schema,String table,String field,String valuePrefix); |
| | | Object getMaxValue(String schema, String table, String field); |
| | | |
| | | String existTable(String dbName, String tableName); |
| | | |
| | | |
| | | List<String> getTableListLike(String dbName, String tableLike); |
| | | |
| | | } |
New file |
| | |
| | | package com.whyc.pojo.db_ckpwrdev_alarm; |
| | | |
| | | import com.baomidou.mybatisplus.annotation.TableField; |
| | | import com.baomidou.mybatisplus.annotation.TableName; |
| | | import io.swagger.annotations.ApiModelProperty; |
| | | import io.swagger.annotations.ApiOperation; |
| | | import lombok.Data; |
| | | |
| | | import java.util.Date; |
| | | |
| | | @Data |
| | | @TableName(schema = "db_ckpwrdev_alarm",value = "tb_ckpowerdev_alarm") |
| | | public class CKPowerDevAlarm { |
| | | |
| | | private Long num; |
| | | private Integer powerDeviceId; |
| | | private Integer almId; |
| | | private String almName; |
| | | @ApiModelProperty("设备类型[1-交流配电柜 2-直流配电柜 3-高频开关电源柜]") |
| | | private Integer devType; |
| | | @ApiModelProperty("告警等级[1-一般 2-重大 3-紧急]") |
| | | private Integer almLevel; |
| | | @TableField("alm_starttime") |
| | | private Date almStartTime; |
| | | @TableField("alm_endtime") |
| | | private Date almEndTime; |
| | | private Integer almIsConfirmed; |
| | | private Date almConfirmedTime; |
| | | private Integer almClearedType; |
| | | |
| | | } |
New file |
| | |
| | | package com.whyc.pojo.db_ckpwrdev_alarm; |
| | | |
| | | import com.baomidou.mybatisplus.annotation.TableField; |
| | | import lombok.Data; |
| | | |
| | | import java.util.Date; |
| | | |
| | | @Data |
| | | public class CKPowerDevAlarmHistory { |
| | | |
| | | private Long num; |
| | | private Integer powerDeviceId; |
| | | private Integer almId; |
| | | private Integer devType; |
| | | private Integer almLevel; |
| | | @TableField("alm_starttime") |
| | | private Date almStartTime; |
| | | @TableField("alm_endtime") |
| | | private Date almEndTime; |
| | | private Integer almIsConfirmed; |
| | | private Date almConfirmedTime; |
| | | private Integer almClearedType; |
| | | |
| | | } |
New file |
| | |
| | | package com.whyc.service; |
| | | |
| | | import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; |
| | | import com.baomidou.mybatisplus.core.toolkit.Wrappers; |
| | | import com.github.pagehelper.PageHelper; |
| | | import com.github.pagehelper.PageInfo; |
| | | import com.whyc.dto.Response; |
| | | import com.whyc.mapper.CKPowerDevAlarmMapper; |
| | | import com.whyc.pojo.db_ckpwrdev_alarm.CKPowerDevAlarm; |
| | | import org.springframework.stereotype.Service; |
| | | |
| | | import javax.annotation.Resource; |
| | | import java.util.List; |
| | | |
| | | @Service |
| | | public class CKPowerDevAlarmService { |
| | | |
| | | @Resource |
| | | private CKPowerDevAlarmMapper mapper; |
| | | |
| | | public Response getPage(CKPowerDevAlarm alarm, int pageNum, int pageSize) { |
| | | PageHelper.startPage(pageNum,pageSize); |
| | | QueryWrapper<CKPowerDevAlarm> query = Wrappers.query(); |
| | | if(alarm.getDevType()!=0) { |
| | | query.eq("dev_type", alarm.getDevType()); |
| | | } |
| | | if(!alarm.getAlmName().equals("")) { |
| | | query.eq("alm_name", alarm.getAlmName()); |
| | | } |
| | | if(alarm.getAlmLevel()!=0) { |
| | | query.eq("alm_level", alarm.getAlmLevel()); |
| | | } |
| | | List<CKPowerDevAlarm> alarmList = mapper.selectList(query); |
| | | PageInfo<CKPowerDevAlarm> pageInfo = new PageInfo<>(alarmList); |
| | | return new Response().set(1,pageInfo); |
| | | } |
| | | } |
| | |
| | | package com.whyc.util; |
| | | |
| | | import java.text.ParseException; |
| | | import java.text.SimpleDateFormat; |
| | | import java.util.Calendar; |
| | | import java.util.Date; |
| | | import java.util.*; |
| | | |
| | | /** |
| | | * 日期处理工具类 |
| | |
| | | return c.getTime(); |
| | | } |
| | | |
| | | /*==========时间切割,按年==========*/ |
| | | /** |
| | | * 在分表的情况下使用,按年分表,查询需要切割查询使用 时间倒序 |
| | | * <p> |
| | | * 根据传入的起止时间,按照年切割成不同时间段 |
| | | * |
| | | * @param startTime 2021-01-01 10:00:00 |
| | | * @param endTime 2023-05-01 10:10:10 |
| | | * @return 示例 |
| | | * 使用方法: |
| | | * 根据返回的第一个数据,如果存在表,则查询筛选条件为>=输入的起始时间;不存在则弃用输入的起始时间 |
| | | * 根据返回的最后个数据,如果表存在,则查询筛选条件为<=输入的终止时间;不存在则弃用输入的终止时间 |
| | | * 返回的非第一最后数据,查询全表 |
| | | */ |
| | | public static Map<String, List<Date>> getYearListDesc(Date startTime, Date endTime) throws ParseException { |
| | | Map<String,List<Date>> result = new LinkedHashMap<>(); |
| | | Calendar startTimeC = Calendar.getInstance(); |
| | | startTimeC.setTime(startTime); |
| | | int startYear = startTimeC.get(Calendar.YEAR); |
| | | |
| | | Calendar endTimeC = Calendar.getInstance(); |
| | | endTimeC.setTime(endTime); |
| | | int endYear = endTimeC.get(Calendar.YEAR); |
| | | int yearSize = endYear - startYear + 1; |
| | | for (int i = endYear; i >= startYear; i--) { |
| | | List<Date> dateList = new LinkedList<>(); |
| | | if(yearSize == 1){ //起止时间没有跨年 |
| | | dateList.add(startTime); |
| | | dateList.add(endTime); |
| | | } |
| | | else if(i==startYear){ //第一年 |
| | | dateList.add(startTime); |
| | | |
| | | Calendar instance = Calendar.getInstance(); |
| | | instance.set(Calendar.YEAR,i); |
| | | instance.set(Calendar.MONTH,instance.getActualMaximum(Calendar.MONTH)); |
| | | instance.set(Calendar.DAY_OF_MONTH,instance.getActualMaximum(Calendar.DAY_OF_MONTH)); |
| | | instance.set(Calendar.HOUR_OF_DAY,instance.getActualMaximum(Calendar.HOUR_OF_DAY)); |
| | | instance.set(Calendar.MINUTE,instance.getActualMaximum(Calendar.MINUTE)); |
| | | instance.set(Calendar.SECOND,instance.getActualMaximum(Calendar.SECOND)); |
| | | dateList.add(instance.getTime()); |
| | | } |
| | | else if(i== endYear){ //尾年 |
| | | Calendar instance = Calendar.getInstance(); |
| | | instance.set(Calendar.YEAR,i); |
| | | instance.set(Calendar.MONTH,instance.getActualMinimum(Calendar.MONTH)); |
| | | instance.set(Calendar.DAY_OF_MONTH,instance.getActualMinimum(Calendar.DAY_OF_MONTH)); |
| | | instance.set(Calendar.HOUR_OF_DAY,instance.getActualMinimum(Calendar.HOUR_OF_DAY)); |
| | | instance.set(Calendar.MINUTE,instance.getActualMinimum(Calendar.MINUTE)); |
| | | instance.set(Calendar.SECOND,instance.getActualMinimum(Calendar.SECOND)); |
| | | dateList.add(instance.getTime()); |
| | | |
| | | dateList.add(endTime); |
| | | }else{ |
| | | Calendar instance = Calendar.getInstance(); |
| | | instance.set(Calendar.YEAR,i); |
| | | instance.set(Calendar.MONTH,instance.getActualMinimum(Calendar.MONTH)); |
| | | instance.set(Calendar.DAY_OF_MONTH,instance.getActualMinimum(Calendar.DAY_OF_MONTH)); |
| | | instance.set(Calendar.HOUR_OF_DAY,instance.getActualMinimum(Calendar.HOUR_OF_DAY)); |
| | | instance.set(Calendar.MINUTE,instance.getActualMinimum(Calendar.MINUTE)); |
| | | instance.set(Calendar.SECOND,instance.getActualMinimum(Calendar.SECOND)); |
| | | dateList.add(instance.getTime()); |
| | | |
| | | Calendar instance2 = Calendar.getInstance(); |
| | | instance2.set(Calendar.YEAR,i); |
| | | instance2.set(Calendar.MONTH,instance2.getActualMaximum(Calendar.MONTH)); |
| | | instance2.set(Calendar.DAY_OF_MONTH,instance2.getActualMaximum(Calendar.DAY_OF_MONTH)); |
| | | instance2.set(Calendar.HOUR_OF_DAY,instance2.getActualMaximum(Calendar.HOUR_OF_DAY)); |
| | | instance2.set(Calendar.MINUTE,instance2.getActualMaximum(Calendar.MINUTE)); |
| | | instance2.set(Calendar.SECOND,instance2.getActualMaximum(Calendar.SECOND)); |
| | | dateList.add(instance2.getTime()); |
| | | |
| | | } |
| | | result.put(String.valueOf(i),dateList); |
| | | } |
| | | return result; |
| | | } |
| | | |
| | | /** |
| | | * 查询使用时,需要处理 默认表查询的问题 倒序 |
| | | * 示例: |
| | | * <p> |
| | | * {2020=[Fri Dec 25 14:12:22 CST 2020, Thu Dec 31 23:59:59 CST 2020]} |
| | | * {2021=[Fri Jan 01 00:00:00 CST 2021, Fri Dec 31 23:59:59 CST 2021]} |
| | | * {2022=[Sat Jan 01 00:00:00 CST 2022, Tue Nov 29 09:12:22 CST 2022]} |
| | | * 或 |
| | | * {2020=[Fri Dec 25 14:12:22 CST 2020, Thu Dec 31 23:59:59 CST 2020]} |
| | | * {2021=[Fri Jan 01 00:00:00 CST 2021, Fri Dec 31 23:59:59 CST 2021]} |
| | | * {2022=[Sat Jan 01 00:00:00 CST 2022, Thu Dec 29 09:12:22 CST 2022]} |
| | | * {default=[Wed Dec 14 15:27:13 CST 2022, Thu Dec 29 09:12:22 CST 2022]} |
| | | * </p> |
| | | * key为数字,表示年度分表;default为默认表 |
| | | * @param startTimeDate 起始时间 |
| | | * @param endTimeDate 终止时间 |
| | | * @return 需要查询的表及查询时间 |
| | | * @throws ParseException |
| | | */ |
| | | public static Map<String,List<Date>> getQueryTimeForSubTablesDesc(Date startTimeDate,Date endTimeDate) throws ParseException { |
| | | //根据筛选的时间段,查询出符合要求的各分表记录数 |
| | | //首先,如果当前时间超过7月,默认表中只有本年度的;如果没超过7月,默认表还存在上一年度的记录 |
| | | Calendar now = Calendar.getInstance(); |
| | | if(endTimeDate.compareTo(now.getTime())>0){ //校验优化最大查询时间 |
| | | endTimeDate = now.getTime(); |
| | | } |
| | | Calendar timeBefore7Month = Calendar.getInstance(); |
| | | timeBefore7Month.add(Calendar.MONTH,-7); |
| | | |
| | | LinkedHashMap<String,List<Date>> yearTimeMap = (LinkedHashMap<String, List<Date>>) DateUtil.getYearListDesc(startTimeDate, endTimeDate); |
| | | Map<String,List<Date>> yearTimeDescMap = new LinkedHashMap<>(); |
| | | boolean existDefaultYear = false; |
| | | if(startTimeDate.before(now.getTime()) || endTimeDate.after(timeBefore7Month.getTime())){ |
| | | List<Date> defaultDateList = new LinkedList<>(); |
| | | defaultDateList.add(startTimeDate); |
| | | defaultDateList.add(endTimeDate); |
| | | |
| | | yearTimeMap.put("default",defaultDateList); |
| | | existDefaultYear = true; |
| | | } |
| | | //调整下顺序,将default调整到首位 |
| | | if(existDefaultYear){ |
| | | List<Date> defaultTimeList = yearTimeMap.get("default"); |
| | | yearTimeDescMap.put("default",defaultTimeList); |
| | | } |
| | | Set<String> yearSet = yearTimeMap.keySet(); |
| | | for (String year : yearSet) { |
| | | if(!year.equals("default")){ |
| | | yearTimeDescMap.put(year,yearTimeMap.get(year)); |
| | | } |
| | | } |
| | | |
| | | return yearTimeDescMap; |
| | | } |
| | | |
| | | |
| | | } |
New file |
| | |
| | | package com.whyc.util; |
| | | |
| | | import com.whyc.dto.Response; |
| | | |
| | | import java.math.BigDecimal; |
| | | import java.math.RoundingMode; |
| | | import java.text.DecimalFormat; |
| | | import java.util.*; |
| | | |
| | | /** |
| | | * 计算工具类 |
| | | */ |
| | | public class MathUtil { |
| | | |
| | | public static final Integer TYPE_FLOAT = 1; |
| | | public static final Integer TYPE_FLOAT_100 = 2; |
| | | public static final Integer TYPE_FLOAT_PERCENT = 3; |
| | | /** |
| | | * 提取公共方法,相除获取比例,返回可选String或者Float |
| | | * @param type 1表示小数,2表示除去%的比例,3表示%的比例 |
| | | * @return Object String或者Float类型 |
| | | * */ |
| | | public static Object divide(Object num,Object num2,Integer type){ |
| | | float res = 0; |
| | | double num2Double=Double.parseDouble(num2.toString()); |
| | | if(num2Double!=0){ |
| | | |
| | | if (num instanceof Integer) { |
| | | res = BigDecimal.valueOf((Integer) num).divide(BigDecimal.valueOf((Integer) num2), 2, RoundingMode.HALF_UP).floatValue(); |
| | | } |
| | | if (num instanceof Float) { |
| | | res = BigDecimal.valueOf((Float) num).divide(BigDecimal.valueOf((Float) num2), 2, RoundingMode.HALF_UP).floatValue(); |
| | | } |
| | | if (num instanceof Double) { |
| | | res = BigDecimal.valueOf((Double) num).divide(BigDecimal.valueOf((Double) num2), 2, RoundingMode.HALF_UP).floatValue(); |
| | | } |
| | | } |
| | | //0.05 |
| | | if (type == 1) { |
| | | return res; |
| | | } |
| | | //5 |
| | | else if (type == 2) { |
| | | return (int) (res * 100); |
| | | } |
| | | //5% |
| | | return (int) (res * 100) + "%"; |
| | | } |
| | | |
| | | /** |
| | | * 提取公共方法,相除获取比例,返回可选String或者Float |
| | | * @param type 1表示小数,2表示除去%的比例,3表示%的比例 |
| | | * @return Object String或者Float类型 |
| | | * */ |
| | | public static Object divide(Object num, Object num2, Integer type, Integer scale){ |
| | | Object res = 0; |
| | | double num2Double=Double.parseDouble(num2.toString()); |
| | | double numDouble=Double.parseDouble(num.toString()); |
| | | if(num2Double!=0){ |
| | | //0.05 |
| | | if (type == 1) { |
| | | res = BigDecimal.valueOf(numDouble).divide(BigDecimal.valueOf(num2Double), scale, RoundingMode.HALF_UP).floatValue(); |
| | | } |
| | | //5 |
| | | else { |
| | | BigDecimal multiply = BigDecimal.valueOf(numDouble).multiply(BigDecimal.valueOf(100)); |
| | | BigDecimal divide = multiply.divide(BigDecimal.valueOf(num2Double), scale, RoundingMode.HALF_UP); |
| | | if (type == 2) { |
| | | res = divide.floatValue()+""; |
| | | } |
| | | //5% |
| | | else{ |
| | | res = divide.floatValue()+"%"; |
| | | } |
| | | } |
| | | return res; |
| | | }else{ |
| | | if(type == 1){ |
| | | return 0.0; |
| | | }else{ |
| | | if(type==2){ |
| | | return "0"; |
| | | }else{ |
| | | return "0%"; |
| | | } |
| | | } |
| | | } |
| | | } |
| | | |
| | | public static float multiply(float num,float num2,int scale){ |
| | | return BigDecimal.valueOf(num).multiply(BigDecimal.valueOf(num2)).setScale(2,RoundingMode.HALF_UP).floatValue(); |
| | | } |
| | | |
| | | /** |
| | | * 求自变量X,因变量Y的线性回归 @PerryHsu |
| | | * y = ax+b |
| | | * |
| | | * a = ∑Yi(Xi-X的平均值)/∑(Xi^2)-1/n*(∑(Xi^2) |
| | | * |
| | | * b = 1/n*∑(Yi-aXi) |
| | | * |
| | | * */ |
| | | public static Response linearRegressionUnary(List<Double> listX, List<Double> listY, Double x) { |
| | | List<Double> resList = new LinkedList<>(); |
| | | |
| | | int sizeX = listX.size(); |
| | | int sizeY = listY.size(); |
| | | DecimalFormat df = new DecimalFormat("0.##"); |
| | | |
| | | if (sizeX != sizeY) { |
| | | return new Response<>().set(1,false,"分子分母数量不一致。"); |
| | | } |
| | | |
| | | Double sum = 0d; |
| | | for (Double xi : listX) { |
| | | sum += xi; |
| | | } |
| | | double avgX = sum/ sizeY; |
| | | |
| | | //a的分子 |
| | | double aMolecule = 0; |
| | | for (int i = 0; i < sizeX; i++) { |
| | | aMolecule += listY.get(i) * (listX.get(i) - avgX); |
| | | } |
| | | |
| | | //a的分母 |
| | | double aDenominator = 0; |
| | | int aDenominatorXi = 0; |
| | | for (int i = 0; i < sizeX; i++) { |
| | | aDenominator += Math.pow(listX.get(i), 2); |
| | | aDenominatorXi += listX.get(i); |
| | | } |
| | | aDenominator = aDenominator - (1.0 / sizeX) * (Math.pow(aDenominatorXi, 2)); |
| | | |
| | | //System.out.println("w_denominator:"+w_denominator+" w_denominator_xi:"+w_denominator_xi); |
| | | |
| | | double a = aMolecule / aDenominator; |
| | | |
| | | double b = 1.0 / sizeX; |
| | | double sumYAX = 0; |
| | | for (int i = 0; i < sizeX; i++) { |
| | | sumYAX += (listY.get(i) - a * listX.get(i)); |
| | | } |
| | | b = b * sumYAX; |
| | | |
| | | String symbol = "+"; |
| | | if (b < 0) { |
| | | symbol = ""; |
| | | } |
| | | //System.out.println("y=" + df.format(a) + "x" + symbol + df.format(b)); |
| | | |
| | | Double y = a*x + b; |
| | | resList.add(a); |
| | | resList.add(b); |
| | | resList.add(y); |
| | | return new Response().setII(1,true,resList,null); |
| | | } |
| | | |
| | | |
| | | /** |
| | | * |
| | | * @param startNum 起始数 -1 = limitStart ,因为limit是从0开始 |
| | | * @param endNum 终止数 |
| | | * @param pageSize 每页条数 |
| | | * @param queryCountMap key-年份,value-数量 |
| | | * @return {"2023",[2,20]} |
| | | * 表名,[limit 2,20] |
| | | */ |
| | | public static Map<String, List<Integer>> getQueryTableAndLimit(int startNum, int endNum, int pageSize, Map<String, Integer> queryCountMap) { |
| | | Map<String,List<Integer>> resultMap = new LinkedHashMap<>(); |
| | | //定位起始数,在哪个年份范围内,起始数值;终止数,在哪个年份范围内,终止数值 |
| | | Set<String> queryKeySet = queryCountMap.keySet(); |
| | | int sum = 0; |
| | | int lastSum = 0; |
| | | |
| | | String limitStartYear = null; |
| | | int limitStart = 0; |
| | | String limitEndYear = null; |
| | | int limitEndSize = pageSize; |
| | | boolean limitStartFinished = false; |
| | | for (String queryKey : queryKeySet) { |
| | | int queryCount = queryCountMap.get(queryKey); |
| | | sum+=queryCount; |
| | | if(!limitStartFinished) { |
| | | if (startNum <= sum) { |
| | | limitStart = startNum - lastSum; |
| | | limitStartYear = queryKey; |
| | | //完成了limit 起始值的获取,后续不需要继续执行 |
| | | limitStartFinished = true; |
| | | } |
| | | } |
| | | if(limitStartFinished) { //limit 起始值获取后,开始计算终止的记录数量 |
| | | if (endNum <= sum) { |
| | | limitEndYear = queryKey; |
| | | |
| | | break; |
| | | } |
| | | //首次,执行起始limit的时候, limitEndSize(记录预留的查询个数)需要减去limitStart占用的 |
| | | if(limitEndSize == pageSize){ |
| | | limitEndSize = pageSize - (sum - startNum + 1); |
| | | }else{ //如果还没达到 终止数值,需要减去本次查询消耗掉的数量 |
| | | limitEndSize = limitEndSize - queryCount; |
| | | } |
| | | } |
| | | |
| | | lastSum = sum; |
| | | } |
| | | |
| | | boolean limitStartCompareFlag = true; |
| | | boolean tableStartFlag = false; |
| | | for (String queryKey : queryKeySet) { |
| | | int queryCount = queryCountMap.get(queryKey); |
| | | if(queryKey.equals(limitStartYear) && limitStartCompareFlag){ |
| | | tableStartFlag = true; |
| | | //从这里开始,取记录数 |
| | | LinkedList<Integer> limitList = new LinkedList<>(); |
| | | if(queryKey.equals(limitEndYear)) { //同一年 |
| | | limitList.add(limitStart-1); |
| | | limitList.add(pageSize); |
| | | resultMap.put(queryKey,limitList); |
| | | break; |
| | | }else{ |
| | | limitList.add(limitStart-1); |
| | | limitList.add(queryCount); |
| | | resultMap.put(queryKey,limitList); |
| | | } |
| | | limitStartCompareFlag = false; |
| | | continue; |
| | | } |
| | | //不同年,继续 |
| | | if(tableStartFlag) { //已经经过了起始年,才可以取数据. 起始年前的数据无效 |
| | | if (queryKey.equals(limitEndYear)) { //找到终止年 |
| | | LinkedList<Integer> limitList = new LinkedList<>(); |
| | | limitList.add(0); |
| | | limitList.add(limitEndSize); |
| | | resultMap.put(queryKey, limitList); |
| | | break; |
| | | } else { //不是终止年,属于中间年 |
| | | LinkedList<Integer> limitList = new LinkedList<>(); |
| | | limitList.add(0); |
| | | limitList.add(queryCount); |
| | | resultMap.put(queryKey, limitList); |
| | | } |
| | | } |
| | | } |
| | | return resultMap; |
| | | |
| | | } |
| | | } |
New file |
| | |
| | | //package com.whyc.util; |
| | | // |
| | | //import com.github.pagehelper.PageInfo; |
| | | //import com.whyc.mapper.CommonMapper; |
| | | //import org.springframework.stereotype.Component; |
| | | // |
| | | //import javax.annotation.Resource; |
| | | //import java.text.ParseException; |
| | | //import java.util.*; |
| | | //import java.util.regex.Pattern; |
| | | // |
| | | ///** |
| | | // * 数据量极大的表,按照年份分表,分页查询时获取数据 |
| | | // */ |
| | | //@Component |
| | | //public class SubTablePageInfoUtils { |
| | | // |
| | | // @Resource |
| | | // private CommonMapper commonMapper; |
| | | // |
| | | // @Resource |
| | | // private UserLogMapper userLogMapper; |
| | | // |
| | | // /**按年份表分页查询*/ |
| | | // public PageInfo<Object> getPageInfo(int pageNum,int pageSize, |
| | | // Date startTime,Date endTime, |
| | | // String dbName,String tablePrefix, |
| | | // Object pojo) throws ParseException { |
| | | // Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesDesc(startTime, endTime); |
| | | // //查询分表是否存在,存在则查询结果 |
| | | // Map<String,Integer> queryCountMap = new LinkedHashMap<>(); |
| | | // Set<String> tableYearKeySet = queryTimeForSubTables.keySet(); |
| | | // for (String tableYear : tableYearKeySet) { |
| | | // List<Date> queryTime = queryTimeForSubTables.get(tableYear); |
| | | // |
| | | // String tableName; |
| | | // if(!tableYear.equals("default")){ |
| | | // //数值 |
| | | // tableName = tablePrefix+"_"+tableYear; |
| | | // String existTableName = commonMapper.existTable(dbName, tableName); |
| | | // if(existTableName == null){ |
| | | // continue; |
| | | // } |
| | | // }else{ |
| | | // tableName = tablePrefix; |
| | | // } |
| | | // //====== 根据不同类型类型对象对应调整 ====== |
| | | // if(pojo instanceof UserLog) { |
| | | // UserLog userLog = (UserLog) pojo; |
| | | // userLog.setOperationTime(queryTime.get(0)); |
| | | // userLog.setOperationTime2(queryTime.get(1)); |
| | | // userLog.setRecordYear(tableName); |
| | | // //int currentCount = userLogMapper.getCount(userLog); |
| | | // int currentCount = subService.getCount(userLog); |
| | | // queryCountMap.put(tableYear,currentCount); |
| | | // } |
| | | // } |
| | | // |
| | | // //分页信息 |
| | | // //确认总页数,总记录数 |
| | | // PageInfo<Object> pageInfo = new PageInfo<>(); |
| | | // |
| | | // int total = 0; |
| | | // Set<String> queryKeySet = queryCountMap.keySet(); |
| | | // for (String queryKey : queryKeySet) { |
| | | // int size = queryCountMap.get(queryKey); |
| | | // total+=size; |
| | | // } |
| | | // int pages = (int) Math.ceil(Float.parseFloat(String.valueOf(total))/pageSize); |
| | | // pageInfo.setTotal(total); |
| | | // pageInfo.setPages(pages); |
| | | // pageInfo.setPageNum(pageNum); |
| | | // pageInfo.setPageSize(pageSize); |
| | | // //根据当前页所需记录,查询当前页记录 |
| | | // int startNum = (pageNum-1)*pageSize+1; |
| | | // int endNum = pageNum*pageSize; |
| | | // |
| | | // //最后一个算法:上面不应该先查询所有记录,应该取count. 这后面定位到哪个表或哪几张表后,采取limit获取当前页记录数; |
| | | // //格式:{表名,[limit 2,20]} |
| | | // Map<String,List<Integer>> tableAndLimitMap = MathUtil.getQueryTableAndLimit(startNum,endNum,pageSize,queryCountMap); |
| | | // Set<String> keySet = tableAndLimitMap.keySet(); |
| | | // List<Object> dataList = new LinkedList<>(); |
| | | // for (String key : keySet) { |
| | | // List<Date> queryTime = queryTimeForSubTables.get(key); |
| | | // //====== 根据不同类型类型对象对应调整 ====== |
| | | // String recordYear = key.equals("default") ? tablePrefix : tablePrefix + "_" + key; |
| | | // if(pojo instanceof UserLog) { |
| | | // UserLog userLog = (UserLog) pojo; |
| | | // userLog.setOperationTime(queryTime.get(0)); |
| | | // userLog.setOperationTime2(queryTime.get(1)); |
| | | // |
| | | // List<Integer> limitList = tableAndLimitMap.get(key); |
| | | // userLog.setLimitStart(limitList.get(0)); |
| | | // userLog.setLimitEnd(limitList.get(1)); |
| | | // userLog.setRecordYear(recordYear); |
| | | // //List<UserLog> list = userLogMapper.getList2(userLog); |
| | | // List<UserLog> list = subService.getList2(userLog); |
| | | // dataList.addAll(list); |
| | | // } |
| | | // } |
| | | // pageInfo.setList(dataList); |
| | | // return pageInfo; |
| | | // } |
| | | // |
| | | // |
| | | // /**查询是否包含特定规则的表,存在,则返回表名*/ |
| | | // public List<String> getTableList(String dbName,String tableLike,String regex){ |
| | | // List<String> resultTableList = new LinkedList<>(); |
| | | // List<String> tableList = commonMapper.getTableListLike(dbName,tableLike); |
| | | // for (String tableName : tableList) { |
| | | // boolean matches = Pattern.matches(regex, tableName); |
| | | // if(matches){ |
| | | // resultTableList.add(tableName); |
| | | // } |
| | | // } |
| | | // return resultTableList; |
| | | // } |
| | | // |
| | | //} |
New file |
| | |
| | | <?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.whyc.mapper.CommonMapper" > |
| | | |
| | | |
| | | <select id="getMaxValue" resultType="java.lang.Object"> |
| | | select max(${field}) from ${schema}.${table} |
| | | </select> |
| | | <select id="existTable" resultType="java.lang.String"> |
| | | select |
| | | table_name |
| | | from `INFORMATION_SCHEMA`.`TABLES` |
| | | where table_name = #{tableName} |
| | | and TABLE_SCHEMA = #{dbName} |
| | | </select> |
| | | <select id="getTableListLike" resultType="java.lang.String"> |
| | | select |
| | | table_name |
| | | from `INFORMATION_SCHEMA`.`TABLES` |
| | | where TABLE_SCHEMA = #{dbName} |
| | | and table_name like concat(#{tableLike},'%') |
| | | </select> |
| | | |
| | | |
| | | </mapper> |