package com.whyc.util;
|
|
import com.github.pagehelper.PageInfo;
|
import com.whyc.dto.BreakAlarmDto;
|
import com.whyc.dto.DevAlarmHisDto;
|
import com.whyc.mapper.CKPowerDevAlarmHistoryMapper;
|
import com.whyc.mapper.CommonMapper;
|
import com.whyc.pojo.db_ckpwrdev_alarm.CKPowerDevAlarmHistory;
|
import com.whyc.pojo.db_ckpwrdev_break_alarm.CKPowerDevBreakAlarmHistory;
|
import com.whyc.service.SubTablePageInfoService;
|
import org.springframework.beans.factory.annotation.Autowired;
|
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;
|
|
@Autowired
|
private SubTablePageInfoService subService;
|
|
/**按年份表分页查询,按时间查询*/
|
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 DevAlarmHisDto) {
|
//devalarmHis不存在初始表，跳过
|
if(tableYear.equals("default")){
|
continue;
|
}
|
DevAlarmHisDto alm= (DevAlarmHisDto) pojo;
|
alm.setAlmStartTime(queryTime.get(0));
|
alm.setAlmEndTime(queryTime.get(1));
|
alm.setRecordYear(tableName);
|
int currentCount = subService.getCountForDevAlm(alm);
|
queryCountMap.put(tableYear,currentCount);
|
}
|
if(pojo instanceof BreakAlarmDto) {
|
//devalarmHis不存在初始表，跳过
|
if(tableYear.equals("default")){
|
continue;
|
}
|
BreakAlarmDto alm= (BreakAlarmDto) pojo;
|
alm.setAlmStarttime(queryTime.get(0));
|
alm.setAlmEndTime(queryTime.get(1));
|
alm.setRecordYear(tableName);
|
int currentCount = subService.getCountForBreakAlm(alm);
|
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 DevAlarmHisDto) {
|
DevAlarmHisDto data= (DevAlarmHisDto) pojo;
|
data.setAlmStartTime(queryTime.get(0));
|
data.setAlmEndTime(queryTime.get(1));
|
List<Integer> limitList = tableAndLimitMap.get(key);
|
data.setLimitStart(limitList.get(0));
|
data.setLimitEnd(limitList.get(1));
|
data.setRecordYear(recordYear);
|
List<CKPowerDevAlarmHistory> list = subService.getListDevAlm(data);
|
dataList.addAll(list);
|
}
|
if(pojo instanceof BreakAlarmDto) {
|
BreakAlarmDto data= (BreakAlarmDto) pojo;
|
data.setAlmStarttime(queryTime.get(0));
|
data.setAlmEndTime(queryTime.get(1));
|
List<Integer> limitList = tableAndLimitMap.get(key);
|
data.setLimitStart(limitList.get(0));
|
data.setLimitEnd(limitList.get(1));
|
data.setRecordYear(recordYear);
|
List<CKPowerDevBreakAlarmHistory> list = subService.getListBreakAlm(data);
|
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;
|
}
|
|
}
|
