//package com.whyc.util;
|
//
|
//import com.github.pagehelper.PageInfo;
|
//import com.whyc.constant.DevAlmEnum;
|
//import com.whyc.dto.DevA200AlarmDto;
|
//import com.whyc.factory.ThreadPoolExecutorFactory;
|
//import com.whyc.mapper.CommonMapper;
|
//import com.whyc.pojo.db_alarm.BattAlarmHistory;
|
//import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear;
|
//import com.whyc.pojo.db_lithium_ram_db.DevLithiumInf;
|
//import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
|
//import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
|
//import com.whyc.pojo.db_power_history.BattStationTempHistory;
|
//import com.whyc.pojo.db_power_history.PowerRealTimeDataHistory;
|
//import com.whyc.pojo.db_user.UserInf;
|
//import com.whyc.service.DevLithiumInfService;
|
//import com.whyc.service.SubTableService;
|
//import com.whyc.service.UserInfService;
|
//import org.springframework.beans.BeanUtils;
|
//import org.springframework.beans.factory.annotation.Autowired;
|
//import org.springframework.stereotype.Component;
|
//
|
//import java.text.ParseException;
|
//import java.util.*;
|
//import java.util.concurrent.CountDownLatch;
|
//import java.util.concurrent.ThreadPoolExecutor;
|
//import java.util.regex.Pattern;
|
//import java.util.stream.Collectors;
|
//
|
///**
|
// * 数据量极大的表,按照年份分表,分页查询时获取数据
|
// */
|
//@Component
|
//public class SubTablePageInfoUtil {
|
// @Autowired(required = false)
|
// private CommonMapper commonMapper;
|
//
|
// @Autowired
|
// private SubTableService service;
|
//
|
// @Autowired
|
// private DevLithiumInfService devLithiumInfService;
|
//
|
// @Autowired
|
// private UserInfService userInfService;
|
//
|
// /**按年份表分页查询*/
|
// 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.getQueryTimeForSubTablesDescWithOutDefault(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 = tablePrefix+"_"+tableYear;
|
// boolean existTableName = commonMapper.existTable(dbName, tableName);
|
// if(!existTableName){
|
// continue;
|
// }
|
// //====== 根据不同类型类型对象对应调整 ======
|
// if(pojo instanceof BattAlarmHistory) {
|
// BattAlarmHistory battAlarmHistory = new BattAlarmHistory();
|
// BeanUtils.copyProperties(pojo, battAlarmHistory);
|
// battAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// battAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// battAlarmHistory.setRecordYear(tableName);
|
// int currentCount = service.getBattHisCount(battAlarmHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
// else if(pojo instanceof PowerAlarmHistory) {
|
// PowerAlarmHistory powerAlarmHistory = new PowerAlarmHistory();
|
// BeanUtils.copyProperties(pojo, powerAlarmHistory);
|
// powerAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// powerAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// powerAlarmHistory.setRecordYear(tableName);
|
// int currentCount = service.getPowerHisCount(powerAlarmHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
// else if (pojo instanceof DevA200AlarmDto) {
|
// DevA200AlarmDto dto = (DevA200AlarmDto) pojo;
|
// dto.setStartTime(queryTime.get(0));
|
// dto.setEndTime(queryTime.get(1));
|
// dto.setTableName(tableName);
|
// int currentCount = service.getCountForDevAlm(dto);
|
// 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 = tablePrefix + "_" + key;
|
// if(pojo instanceof BattAlarmHistory) {
|
// BattAlarmHistory battAlarmHistory = new BattAlarmHistory();
|
// BeanUtils.copyProperties(pojo, battAlarmHistory);
|
// battAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// battAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// battAlarmHistory.setRecordYear(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// battAlarmHistory.setLimitStart(limitList.get(0));
|
// battAlarmHistory.setLimitEnd(limitList.get(1));
|
// List<BattAlarmHistory> list = service.getBattHisList(battAlarmHistory);
|
// dataList.addAll(list);
|
// }
|
// else if(pojo instanceof PowerAlarmHistory) {
|
// PowerAlarmHistory powerAlarmHistory = new PowerAlarmHistory();
|
// BeanUtils.copyProperties(pojo, powerAlarmHistory);
|
// powerAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// powerAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// powerAlarmHistory.setRecordYear(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// powerAlarmHistory.setLimitStart(limitList.get(0));
|
// powerAlarmHistory.setLimitEnd(limitList.get(1));
|
// List<PowerAlarmHistory> list = service.getPowerHisList(powerAlarmHistory);
|
// dataList.addAll(list);
|
// }
|
// else if (pojo instanceof DevA200AlarmDto) {
|
// if(!key.equals("default")){
|
// DevA200AlarmDto dto = (DevA200AlarmDto) pojo;
|
// dto.setStartTime(queryTime.get(0));
|
// dto.setEndTime(queryTime.get(1));
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// dto.setLimitStart(limitList.get(0));
|
// dto.setLimitEnd(limitList.get(1));
|
// dto.setTableName(recordYear);
|
// List<DevLithiumAlarmDataYear> list = service.getListDevAlm(dto);
|
// for (DevLithiumAlarmDataYear year:list) {
|
// int devId=year.getDevId();
|
// int uid=year.getConfirmedUid();
|
// DevLithiumInf dinf=devLithiumInfService.getDinfByDevId(devId);
|
// UserInf uinf= userInfService.getUinfByUId(uid);
|
// int almId=year.getAlmId();
|
// String almName= DevAlmEnum.getValue(almId);
|
// year.setAlmName(almName!=null?almName:"");
|
// year.setDevInf(dinf);
|
// year.setConfirmedUname(uinf!=null?uinf.getUname():"");
|
// }
|
// dataList.addAll(list);
|
// }
|
// }
|
// }
|
// pageInfo.setList(dataList);
|
// return pageInfo;
|
// }
|
//
|
// /**按月分表,分页查询*/
|
// public PageInfo<Object> getPageInfoByMonthTable(int pageNum, int pageSize,
|
// Date startTime, Date endTime,
|
// String dbName, String tablePrefix,
|
// Object pojo) throws ParseException, InterruptedException {
|
// Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesByMonthDesc2(startTime, endTime);
|
// //查询分表是否存在,存在则查询结果
|
// Map<String,Integer> queryCountMap = new LinkedHashMap<>();
|
// Set<String> tableYearKeySet = queryTimeForSubTables.keySet();
|
// //优化1-查询所有的符合月份规则的表和主表
|
// List<String> tableNameListDB = getTableList(dbName, tablePrefix, tablePrefix + "(_[0-9]{4}_[0-9]{2})?");
|
// List<String> tableYearListInDB = new LinkedList<>(); //查询时间段内的年月分表集合且在数据库中存在
|
// //过滤掉时间段内数据库中不存在的年月分表
|
// for (String tableYear : tableYearKeySet) {
|
// String tableName;
|
// if(!tableYear.equals("default")){
|
// //数值
|
// tableName = tablePrefix+"_"+tableYear;
|
// //String existTableName = commonMapper.existTable(dbName, tableName);
|
// if(!tableNameListDB.contains(tableName)){ //优化1-数据库中不存在,去除多次查表,一次查出
|
// continue;
|
// }
|
// }
|
// tableYearListInDB.add(tableYear);
|
// }
|
// //查询年月分表的记录数
|
// //优化2:多线程读取
|
// ThreadPoolExecutor pool = ThreadPoolExecutorFactory.getPoolExecutor();
|
// CountDownLatch latch = new CountDownLatch(tableYearListInDB.size());
|
// for (String tableYear : tableYearListInDB) {
|
// List<Date> queryTime = queryTimeForSubTables.get(tableYear);
|
//
|
// String tableName;
|
// if(!tableYear.equals("default")){
|
// //数值
|
// tableName = tablePrefix+"_"+tableYear;
|
// }else{
|
// tableName = tablePrefix;
|
// }
|
// pool.execute(()-> {
|
// //====== 根据不同类型类型对象对应调整 ======
|
// if (pojo instanceof BattAlarmHistory) {
|
// BattAlarmHistory battAlarmHistory = new BattAlarmHistory();
|
// BeanUtils.copyProperties(pojo, battAlarmHistory);
|
// battAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// battAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// battAlarmHistory.setRecordYear(tableName);
|
// int currentCount = service.getBattHisCount(battAlarmHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
// else if (pojo instanceof BattStationTempHistory) {
|
// BattStationTempHistory tempHistory = new BattStationTempHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(tableName);
|
// int currentCount = service.getBattStationTempHisCount(tempHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
// else if (pojo instanceof BattRealTimeDataHistory) {
|
// BattRealTimeDataHistory tempHistory = new BattRealTimeDataHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(tableName);
|
// int currentCount = service.getBattRealTimeDataHisCount(tempHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
// else if (pojo instanceof PowerRealTimeDataHistory) {
|
// PowerRealTimeDataHistory tempHistory = new PowerRealTimeDataHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(tableName);
|
// int currentCount = service.getPowerRealTimeDataHisCount(tempHistory);
|
// queryCountMap.put(tableYear, currentCount);
|
// }
|
//
|
// latch.countDown();
|
// });
|
// }
|
// latch.await();
|
// //因为多线程后无序,需要重排序
|
// List<String> keySetDesc = queryCountMap.keySet().stream().sorted(Comparator.reverseOrder()).collect(Collectors.toList());
|
// Map<String,Integer> queryCountMapDesc = new LinkedHashMap<>();
|
// for (String key : keySetDesc) {
|
// Integer count = queryCountMap.get(key);
|
// queryCountMapDesc.put(key,count);
|
// }
|
// //分页信息
|
// //确认总页数,总记录数
|
// 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,queryCountMapDesc);
|
// 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 BattAlarmHistory) {
|
// BattAlarmHistory battAlarmHistory = new BattAlarmHistory();
|
// BeanUtils.copyProperties(pojo, battAlarmHistory);
|
// battAlarmHistory.setAlmStartTime(queryTime.get(0));
|
// battAlarmHistory.setAlmEndTime(queryTime.get(1));
|
// battAlarmHistory.setRecordYear(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// battAlarmHistory.setLimitStart(limitList.get(0));
|
// battAlarmHistory.setLimitEnd(limitList.get(1));
|
// List<BattAlarmHistory> list = service.getBattHisList(battAlarmHistory);
|
// dataList.addAll(list);
|
// }
|
// else if (pojo instanceof BattStationTempHistory){
|
// BattStationTempHistory tempHistory = new BattStationTempHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// tempHistory.setLimitStart(limitList.get(0));
|
// tempHistory.setLimitEnd(limitList.get(1));
|
// List<BattStationTempHistory> list = service.getBattStationTempHisList(tempHistory);
|
// dataList.addAll(list);
|
// }
|
// else if (pojo instanceof BattRealTimeDataHistory){
|
// BattRealTimeDataHistory tempHistory = new BattRealTimeDataHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// tempHistory.setLimitStart(limitList.get(0));
|
// tempHistory.setLimitEnd(limitList.get(1));
|
// List<BattRealTimeDataHistory> list = service.getBattRealTimeDataHisList(tempHistory);
|
// dataList.addAll(list);
|
// }
|
// else if (pojo instanceof PowerRealTimeDataHistory){
|
// PowerRealTimeDataHistory tempHistory = new PowerRealTimeDataHistory();
|
// tempHistory.setStartTime(queryTime.get(0));
|
// tempHistory.setEndTime(queryTime.get(1));
|
// tempHistory.setRecordYearMonth(recordYear);
|
// List<Integer> limitList = tableAndLimitMap.get(key);
|
// tempHistory.setLimitStart(limitList.get(0));
|
// tempHistory.setLimitEnd(limitList.get(1));
|
// List<PowerRealTimeDataHistory> list = service.getPowerRealTimeDataHisList(tempHistory);
|
// 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;
|
// }
|
//}
|
