Merge branch 'master' of http://118.89.139.230:10101/r/fg_v2.0

lxw

2023-06-17 d2ef5afff8240c78616b2b4732d1bcd0eb8a248a

Merge branch 'master' of http://118.89.139.230:10101/r/fg_v2.0

 src/main/java/com/whyc/controller/UserLogController.java

@@ -12,7 +12,9 @@

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;
import java.util.List;
import java.util.Objects;

/**
 * 用户操作日志
@@ -27,10 +29,10 @@

    @ApiOperation(value = "分页")
    @PostMapping("page")
    public Response<PageInfo<UserLog>> getPage(@RequestParam int pageNum, @RequestParam int pageSize, @RequestBody UserLog userLog){
    public Response<PageInfo<Object>> getPage(@RequestParam int pageNum, @RequestParam int pageSize, @RequestBody UserLog userLog) throws ParseException {
        //PageInfo<UserLog> pageInfo = service.getPage(pageNum,pageSize,userLog);
        PageInfo<UserLog> pageInfo = service.getPage2(pageNum,pageSize,userLog);
        return new Response<PageInfo<UserLog>>().set(1,pageInfo);
        PageInfo<Object> pageInfo = service.getPage2(pageNum,pageSize,userLog);
        return new Response<PageInfo<Object>>().set(1,pageInfo);
    }

    @PostMapping("exportExcel")

 src/main/java/com/whyc/mapper/UserLogMapper.java

@@ -10,6 +10,12 @@

    List<UserLog> getList(UserLog userLog);

    /**动态传入表名*/
    Integer getCount(UserLog userLog);

    /**动态传入表名*/
    List<UserLog> getList2(UserLog userLog);

    List<UserLog> getDataBeforeRetentionMonth(@Param("retentionTime") Date retentionTime);

}

 src/main/java/com/whyc/pojo/UserLog.java

@@ -44,6 +44,12 @@
    @TableField(exist = false)
    private String recordYear;

    @TableField(exist = false)
    private Integer limitStart;

    @TableField(exist = false)
    private Integer limitEnd;

    public String getOperationTypeStr() {
        return operationTypeStr;
    }
@@ -163,4 +169,20 @@
    public void setOperationDetailEnUs(String operationDetailEnUs) {
        this.operationDetailEnUs = operationDetailEnUs;
    }

    public Integer getLimitStart() {
        return limitStart;
    }

    public void setLimitStart(Integer limitStart) {
        this.limitStart = limitStart;
    }

    public Integer getLimitEnd() {
        return limitEnd;
    }

    public void setLimitEnd(Integer limitEnd) {
        this.limitEnd = limitEnd;
    }
}

 src/main/java/com/whyc/service/UserLogService.java

@@ -5,25 +5,30 @@
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.whyc.constant.UserOperation;
import com.whyc.mapper.CommonMapper;
import com.whyc.mapper.UserLogMapper;
import com.whyc.pojo.UserLog;
import com.whyc.util.ActionUtil;
import com.whyc.util.DateUtil;
import com.whyc.util.ExcelUtil;
import com.whyc.util.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Date;
import java.util.List;
import java.text.ParseException;
import java.util.*;

@Service
public class UserLogService {

    @Resource
    private UserLogMapper mapper;

    @Resource
    private CommonMapper commonMapper;

    @Resource
    private SubTablePageInfoUtils subTablePageInfoUtils;

    public PageInfo<UserLog>getPage(int pageNum, int pageSize, UserLog userLog) {
        PageHelper.startPage(pageNum, pageSize, true);
@@ -124,14 +129,78 @@
    }

    /**根据查询优化分页查询方式*/
    public PageInfo<UserLog>getPage2(int pageNum, int pageSize, UserLog userLog) {
        PageHelper.startPage(pageNum,pageSize,true);
        //查询出符合要求的各分表记录数
    public PageInfo<Object>getPage2(int pageNum, int pageSize, UserLog userLog) throws ParseException {
        //PageHelper.startPage(pageNum,pageSize,true);
        //根据传入的查询时间,获取分表及默认表的拆分查询
        Date startTime = userLog.getOperationTime();
        Date endTime = userLog.getOperationTime2();
        /*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);
            userLog.setOperationTime(queryTime.get(0));
            userLog.setOperationTime2(queryTime.get(1));
            String tableName;
            if(!tableYear.equals("default")){
                //数值
                tableName = "tb_user_log_"+tableYear;
                String existTableName = commonMapper.existTable("db_user", tableName);
                if(existTableName == null){
                    continue;
                }
            }else{
                tableName = "tb_user_log";
            }
            userLog.setRecordYear(tableName);
            int currentCount = mapper.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> userLogList = new LinkedList<>();
        for (String key : keySet) {
            List<Date> queryTime = queryTimeForSubTables.get(key);
            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(key.equals("default")?"tb_user_log":"tb_user_log_"+key);
            List<UserLog> list = mapper.getList2(userLog);
            userLogList.addAll(list);
        }*/
        PageInfo<Object> pageInfo = subTablePageInfoUtils.getPageInfo(pageNum, pageSize, startTime, endTime, "db_user", "tb_user_log", userLog);
        List<Object> userLogList = pageInfo.getList();

        //这个接口要兼容新老审计日志记录的查询
        List<UserLog> userLogList = mapper.getList(userLog);
        userLogList.stream().forEach(userLog1 -> {
        //List<UserLog> userLogList = mapper.getList(userLog);
        userLogList.stream().forEach(userLogTemp -> {
            UserLog userLog1 = (UserLog)userLogTemp;
            switch (userLog1.getOperationType()){
                case 1: userLog1.setOperationTypeStr(UserOperation.TYPE_LOGIN.getTypeName());break;
                case 2: userLog1.setOperationTypeStr(UserOperation.TYPE_LOGOUT.getTypeName());break;
@@ -147,7 +216,8 @@
                default:userLog1.setOperationTypeStr(UserOperation.TYPE_UNRECOGNIZED.getTypeName());
            }
        });
        PageInfo<UserLog> pageInfo = new PageInfo<>(userLogList);
        pageInfo.setList(userLogList);
        //PageInfo<UserLog> pageInfo = new PageInfo<>(userLogList);
        return pageInfo;

    }

 src/main/java/com/whyc/util/DateUtil.java

@@ -68,23 +68,76 @@
     * <p>
     * 根据传入的起止时间,按照年切割成不同时间段
     *
     * @param startTimeStr 2021-01-01 10:00:00
     * @param endTimeStr   2023-05-01 10:10:10
     * @param startTime 2021-01-01 10:00:00
     * @param endTime   2023-05-01 10:10:10
     * @return 示例[2021, 2022, 2023]
     * 使用方法:
     * 根据返回的第一个数据,如果存在表,则查询筛选条件为>=输入的起始时间;不存在则弃用输入的起始时间
     * 根据返回的最后个数据,如果表存在,则查询筛选条件为<=输入的终止时间;不存在则弃用输入的终止时间
     * 返回的非第一最后数据,查询全表
     */
    public static List<Integer> getYearList(String startTimeStr,String endTimeStr) throws ParseException {
        Integer startYear = Integer.valueOf(startTimeStr.split("-")[0]);
        Integer endYear = Integer.valueOf(endTimeStr.split("-")[0]);
        List<Integer> yearList = new LinkedList<>();
        while (startYear<=endYear){
            yearList.add(startYear);
            startYear++;
    public static Map<String,List<Date>> getYearList(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 = startYear; i <= endYear; 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 yearList;
        return result;
    }

    /**
@@ -125,7 +178,27 @@
        }
        return yearMonthList;
    }
    public static List<List<Date>> getQueryTimeForSubTables(Date startTimeDate,Date endTimeDate) throws ParseException {

    /**
     *  查询使用时,需要处理 默认表查询的问题
     *  示例:
     *  <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>> getQueryTimeForSubTables(Date startTimeDate,Date endTimeDate) throws ParseException {
        //根据筛选的时间段,查询出符合要求的各分表记录数
        //首先,如果当前时间超过7月,默认表中只有本年度的;如果没超过7月,默认表还存在上一年度的记录
        Calendar instance = Calendar.getInstance();
@@ -138,38 +211,223 @@
        Date beforeYearTime = instance.getTime();
        instance.setTime(endTimeDate);
        int endTimeYear = instance.get(Calendar.YEAR);
        List<List<Date>> monthTime;
        if(nowYear == beforeYear){ //默认表只有本年度的
            monthTime = DateUtil.getMonthTime(startTimeDate, endTimeDate);

        Calendar startTime = Calendar.getInstance();
        startTime.setTime(startTimeDate);

        Map<String,List<Date>> yearTimeMap = DateUtil.getYearList(startTimeDate, endTimeDate);;
        if(nowYear == beforeYear){ //默认表只有本年度的(超过了半年,默认表记录全部只有本年度的,同时会生成了当前年度的分表)
            //如果查询时间范围包含当前年度,则需要查询默认表
            if(startTimeDate.getYear() >= nowYear && endTimeDate.getYear()<= nowYear){
                //表
                //monthTime.add();
            if(startTime.get(Calendar.YEAR) >= nowYear && endTimeYear <= nowYear){
                //开始时间设置年度初始时间
                Calendar defaultTableStartTime = Calendar.getInstance();
                defaultTableStartTime.set(Calendar.YEAR,nowYear);
                defaultTableStartTime.set(Calendar.MONTH,defaultTableStartTime.getActualMinimum(Calendar.MONTH));
                defaultTableStartTime.set(Calendar.DAY_OF_MONTH,defaultTableStartTime.getActualMinimum(Calendar.DAY_OF_MONTH));
                defaultTableStartTime.set(Calendar.HOUR_OF_DAY,defaultTableStartTime.getActualMinimum(Calendar.HOUR_OF_DAY));
                defaultTableStartTime.set(Calendar.MINUTE,defaultTableStartTime.getActualMinimum(Calendar.MINUTE));
                defaultTableStartTime.set(Calendar.SECOND,defaultTableStartTime.getActualMinimum(Calendar.SECOND));
                //终止时间设置为当前时间
                List<Date> defaultDateList = new LinkedList<>();
                defaultDateList.add(defaultTableStartTime.getTime());
                defaultDateList.add(endTimeDate);

                yearTimeMap.put("default",defaultDateList);

            }

        }else{ //默认表中存在上一年度的记录.上一年的最早时间为beforeYearTime
        }else{ //查询时间范围不在当前年度
            //默认表中存在上一年度的记录.上一年的最早时间为beforeYearTime-上一年的年末
            //判断查询时间范围是否包含默认表中上一年度的时间
            if(instance.getTime().compareTo(beforeYearTime)>0){ //查询时间大于默认表中上一年度时间,说明查询记录也包含在默认表中
                //起始时间
                //终止时间
                List<Date> defaultDateList = new LinkedList<>();
                defaultDateList.add(beforeYearTime);
                defaultDateList.add(endTimeDate);

                yearTimeMap.put("default",defaultDateList);
            }
        }
        return yearTimeMap;
    }

    /**
     * 在分表的情况下使用,按年分表,查询需要切割查询使用 时间倒序
     * <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());

            }
            //如果查询的范围,
            monthTime = DateUtil.getMonthTime(startTimeDate, endTimeDate);
            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 instance = Calendar.getInstance();
        if(endTimeDate.compareTo(instance.getTime())>0){ //校验优化最大查询时间
            endTimeDate = instance.getTime();
        }
        int nowYear = instance.get(Calendar.YEAR);
        instance.add(Calendar.MONTH,-6);
        int beforeYear = instance.get(Calendar.YEAR);
        Date beforeYearTime = instance.getTime();
        instance.setTime(endTimeDate);
        int endTimeYear = instance.get(Calendar.YEAR);

        Calendar startTime = Calendar.getInstance();
        startTime.setTime(startTimeDate);

        LinkedHashMap<String,List<Date>> yearTimeMap = (LinkedHashMap<String, List<Date>>) DateUtil.getYearListDesc(startTimeDate, endTimeDate);;
        Map<String,List<Date>> yearTimeDescMap = new LinkedHashMap<>();
        boolean existDefaultYear = false;
        if(nowYear == beforeYear){ //默认表只有本年度的(超过了半年,默认表记录全部只有本年度的,同时会生成了当前年度的分表)
            //如果查询时间范围包含当前年度,则需要查询默认表
            if(startTime.get(Calendar.YEAR) >= nowYear && endTimeYear <= nowYear){
                //开始时间设置年度初始时间
                Calendar defaultTableStartTime = Calendar.getInstance();
                defaultTableStartTime.set(Calendar.YEAR,nowYear);
                defaultTableStartTime.set(Calendar.MONTH,defaultTableStartTime.getActualMinimum(Calendar.MONTH));
                defaultTableStartTime.set(Calendar.DAY_OF_MONTH,defaultTableStartTime.getActualMinimum(Calendar.DAY_OF_MONTH));
                defaultTableStartTime.set(Calendar.HOUR_OF_DAY,defaultTableStartTime.getActualMinimum(Calendar.HOUR_OF_DAY));
                defaultTableStartTime.set(Calendar.MINUTE,defaultTableStartTime.getActualMinimum(Calendar.MINUTE));
                defaultTableStartTime.set(Calendar.SECOND,defaultTableStartTime.getActualMinimum(Calendar.SECOND));
                //终止时间设置为当前时间
                List<Date> defaultDateList = new LinkedList<>();
                defaultDateList.add(defaultTableStartTime.getTime());
                defaultDateList.add(endTimeDate);

                yearTimeMap.put("default",defaultDateList);
                existDefaultYear = true;

            }

        }else{ //查询时间范围不在当前年度
            //默认表中存在上一年度的记录.上一年的最早时间为beforeYearTime-上一年的年末
            //判断查询时间范围是否包含默认表中上一年度的时间
            if(instance.getTime().compareTo(beforeYearTime)>0){ //查询时间大于默认表中上一年度时间,说明查询记录也包含在默认表中
                //起始时间
                //终止时间
                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 null;
        return yearTimeDescMap;
    }

    public static void main(String[] args) throws ParseException {
        Date d1 = YYYY_MM_DD_HH_MM_SS.parse("2020-12-25 14:12:22");
        Date d2 = YYYY_MM_DD_HH_MM_SS.parse("2022-01-10 09:12:22");
        List<List<Date>> monthTime = getMonthTime(d1, d2);
        System.out.println(monthTime);
        /*String dateStr = "2023-07-01 00:00:01";
        Date date = YYYY_MM_DD_HH_MM_SS.parse(dateStr);
        Calendar instance = Calendar.getInstance();
        instance.setTime(date);
        instance.add(Calendar.MONTH,-6);
        System.out.println(YYYY_MM_DD_HH_MM_SS.format(instance.getTime()));*/
        Date d2 = YYYY_MM_DD_HH_MM_SS.parse("2022-12-29 09:12:22");
        Map<String, List<Date>> yearListForSubTables = getQueryTimeForSubTablesDesc(d1, d2);
        Set<String> strings = yearListForSubTables.keySet();
        Set<Map.Entry<String, List<Date>>> entries = yearListForSubTables.entrySet();
        for (Map.Entry<String, List<Date>> entry : entries) {
            System.out.println(entry);
        }
    }

}

 src/main/java/com/whyc/util/MathUtil.java

@@ -6,10 +6,7 @@
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.Arrays;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;
import java.util.*;

/**
 * 计算工具类
@@ -210,5 +207,92 @@
    }


    /**
     *
     * @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);
                } else { //不是终止年,属于中间年
                    LinkedList<Integer> limitList = new LinkedList<>();
                    limitList.add(0);
                    limitList.add(queryCount);
                    resultMap.put(queryKey, limitList);
                }
            }
        }
        return resultMap;

    }
}

 src/main/java/com/whyc/util/SubTablePageInfoUtils.java

New file
@@ -0,0 +1,106 @@
package com.whyc.util;

import com.github.pagehelper.PageInfo;
import com.whyc.constant.Com;
import com.whyc.mapper.BattalarmDataHistoryMapper;
import com.whyc.mapper.CommonMapper;
import com.whyc.mapper.UserLogMapper;
import com.whyc.pojo.UserLog;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.*;

/**
 * 数据量极大的表,按照年份分表,分页查询时获取数据
 */
@Component
public class SubTablePageInfoUtils {

    @Resource
    private CommonMapper commonMapper;

    @Resource
    private UserLogMapper userLogMapper;

    @Resource
    private BattalarmDataHistoryMapper battAlarmDataHistoryMapper;

    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);
                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);
            //====== 根据不同类型类型对象对应调整 ======
            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(key.equals("default") ? tablePrefix : tablePrefix + "_" + key);
                List<UserLog> list = userLogMapper.getList2(userLog);
                dataList.addAll(list);
            }
        }
        pageInfo.setList(dataList);
        return pageInfo;
    }
}

 src/main/resources/mapper/UserLogMapper.xml

@@ -95,6 +95,166 @@
        </if>
        ORDER BY uOprateDay DESC
    </select>

    <select id="getCount" resultType="int">
        select count(*) from db_user.${recordYear} l,db_user.tb_user_inf u
        where l.uId = u.uId
        and uOprateDay between #{operationTime} and  #{operationTime2}
        <if test="uId !=null">
            and l.uId = #{uId}
        </if>
        <!--条件查询-非全部-->
        <if test="eventType !=null">
            <choose>
                <!--系统级别(1)-->
                <when test="eventType ==1">
                    <choose>
                        <when test="operationType ==1
                            or operationType ==2
                            or operationType ==20
                            or operationType ==21
                        ">
                            <choose>
                                <!--登录包括普通登录和临时凭证ID登录-->
                                <when test="operationType ==1">
                                    and uoprateType in (#{operationType},35)
                                </when>
                                <otherwise>
                                    and uoprateType= #{operationType}
                                </otherwise>
                            </choose>
                        </when>
                        <when test="operationType ==31
                            or operationType ==32
                            or operationType ==33
                            or operationType ==34">
                            and uoprateType= #{operationType}
                            <choose>
                                <when test="operationType ==31">
                                    or uOprateMsg  regexp '登录PC系统失败'
                                </when>
                                <when test="operationType ==32">
                                    or uOprateMsg  regexp '配置参数修改|导航配置'
                                </when>
                                <when test="operationType ==33">
                                    or uOprateMsg  regexp '密码' and uOprateMsg not regexp '更新密码复杂度规则'
                                </when>
                                <otherwise>
                                    or uOprateMsg  regexp 'LoginAction!login|imeout'
                                </otherwise>
                            </choose>
                        </when>
                        <when test="operationType == 3 or operationType == 4 or operationType == 5">
                            and uoprateType= #{operationType}
                            and uOprateMsg  regexp
                            '添加新用户|的用户信息|权限组|审计数据记录容量
                            |执行了User模块|执行了PermitGroup'
                        </when>
                    </choose>
                </when>
                <!--业务级别-机房(2)-->
                <when test="eventType ==2">
                    and uoprateType= #{operationType}
                    and uOprateMsg regexp '单体|电池组|组端|设备|Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D'
                    and uOprateMsg not regexp '电源|Pwrdev'
                </when>
                <!--业务级别-电源(3)-->
                <when test="eventType ==3">
                    and uoprateType= #{operationType}
                    and uOprateMsg regexp 'PowerInf|Pwr'
                </when>
                <!--业务级别-其他(4)-->
                <when test="eventType ==4">
                    and uoprateType= #{operationType}
                    and uOprateMsg not regexp '添加新用户|的用户信息|权限组|审计数据记录容量|配置参数修改|导航配置|密码|单体|电池组|组端|设备|电源|
                    |执行了User模块|执行了PermitGroup|
                    |Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D|
                    |PowerInf|Pwr'
                </when>
            </choose>
        </if>
        ORDER BY uOprateDay DESC
    </select>

    <select id="getList2" resultMap="Map_UserLog">
        select l.*,u.uName from db_user.${recordYear} l,db_user.tb_user_inf u
        where l.uId = u.uId
        and uOprateDay between #{operationTime} and  #{operationTime2}
        <if test="uId !=null">
            and l.uId = #{uId}
        </if>
        <!--条件查询-非全部-->
        <if test="eventType !=null">
            <choose>
                <!--系统级别(1)-->
                <when test="eventType ==1">
                    <choose>
                        <when test="operationType ==1
                            or operationType ==2
                            or operationType ==20
                            or operationType ==21
                        ">
                            <choose>
                                <!--登录包括普通登录和临时凭证ID登录-->
                                <when test="operationType ==1">
                                    and uoprateType in (#{operationType},35)
                                </when>
                                <otherwise>
                                    and uoprateType= #{operationType}
                                </otherwise>
                            </choose>
                        </when>
                        <when test="operationType ==31
                            or operationType ==32
                            or operationType ==33
                            or operationType ==34">
                            and uoprateType= #{operationType}
                            <choose>
                                <when test="operationType ==31">
                                    or uOprateMsg  regexp '登录PC系统失败'
                                </when>
                                <when test="operationType ==32">
                                    or uOprateMsg  regexp '配置参数修改|导航配置'
                                </when>
                                <when test="operationType ==33">
                                    or uOprateMsg  regexp '密码' and uOprateMsg not regexp '更新密码复杂度规则'
                                </when>
                                <otherwise>
                                    or uOprateMsg  regexp 'LoginAction!login|imeout'
                                </otherwise>
                            </choose>
                        </when>
                        <when test="operationType == 3 or operationType == 4 or operationType == 5">
                            and uoprateType= #{operationType}
                            and uOprateMsg  regexp
                            '添加新用户|的用户信息|权限组|审计数据记录容量
                            |执行了User模块|执行了PermitGroup'
                        </when>
                    </choose>
                </when>
                <!--业务级别-机房(2)-->
                <when test="eventType ==2">
                    and uoprateType= #{operationType}
                    and uOprateMsg regexp '单体|电池组|组端|设备|Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D'
                    and uOprateMsg not regexp '电源|Pwrdev'
                </when>
                <!--业务级别-电源(3)-->
                <when test="eventType ==3">
                    and uoprateType= #{operationType}
                    and uOprateMsg regexp 'PowerInf|Pwr'
                </when>
                <!--业务级别-其他(4)-->
                <when test="eventType ==4">
                    and uoprateType= #{operationType}
                    and uOprateMsg not regexp '添加新用户|的用户信息|权限组|审计数据记录容量|配置参数修改|导航配置|密码|单体|电池组|组端|设备|电源|
                    |执行了User模块|执行了PermitGroup|
                    |Batt|Dev|FBO|Fbs|Ld9|Li9130|Lithium|Station3D|
                    |PowerInf|Pwr'
                </when>
            </choose>
        </if>
        ORDER BY num DESC limit #{limitStart},#{limitEnd}
    </select>
    <select id="getDataBeforeRetentionMonth" resultMap="Map_UserLog">
        select *,DATE_FORMAT(uOprateDay,"%Y")  as recordYear from db_user.tb_user_log where uOprateDay &lt; #{retentionTime};
    </select>