锁的历史状态

whyclxw

2025-01-16 c967a290ba1865e4859900e1f85ab583fcc5bf58

锁的历史状态

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

New file
@@ -0,0 +1,28 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.service.LockHisService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.text.ParseException;

@RestController
@Api(tags = "锁的历史状态")
@RequestMapping("lockHis")
public class LockHisController {
    @Autowired
    private LockHisService service;
    @ApiOperation("查询锁的历史状态")
    @GetMapping("getLockHis")
    public Response getLockHis(@RequestParam int lockId, @RequestParam String startTime, @RequestParam String endTime
            , @RequestParam int pageNum, @RequestParam int pageSize) throws ParseException, InterruptedException {
        Response res=service.getLockHis(lockId,startTime,endTime,pageNum,pageSize);
        return res;
    }
}

 src/main/java/com/whyc/pojo/db_lock_his/LockHis.java

New file
@@ -0,0 +1,63 @@
package com.whyc.pojo.db_lock_his;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.util.Date;

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="LockHis对象", description="锁具2024年状态更新表")
public class LockHis {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "自增主键")
    @TableId(value = "num", type = IdType.AUTO)
    private Integer num;

    @ApiModelProperty(value = "锁具ID号")
    private Integer lockId;

    @ApiModelProperty(value = "记录时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date recordTime;

    @ApiModelProperty(value = "锁具状态[0-闭锁  1-开锁]")
    private Integer lockState;

    @ApiModelProperty(value = "上一次开锁方式[0-485开锁 1-刷卡开锁 2-蓝牙开锁 3-DI开锁]")
    private Integer unlockType;

    @ApiModelProperty(value = "上一次刷开开锁卡号[仅刷ID卡开锁时有效]")
    private String unlockId;

    @TableField(exist = false)
    @ApiModelProperty("表名字拼接")
    private String recordYear;

    @TableField(exist = false)
    @ApiModelProperty("分页开始位置")
    private Integer limitStart;

    @TableField(exist = false)
    @ApiModelProperty("分页结束位置")
    private Integer limitEnd;

    @TableField(exist = false)
    @ApiModelProperty("开始时间")
    private Date startTime;

    @TableField(exist = false)
    @ApiModelProperty("结束时间")
    private Date endTime;

}

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

New file
@@ -0,0 +1,25 @@
package com.whyc.service;

import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.pojo.db_lock_his.LockHis;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;

@Service
public class LockHisService {
    @Autowired
    private SubTablePageInfoUtil util;
    //查询锁的历史状态
    public Response getLockHis(int lockId, String startTime, String endTime, int pageNum, int pageSize) throws ParseException {
        LockHis his=new LockHis();
        his.setLockId(lockId);
        PageInfo pageInfo=util.getPageInfo(pageNum,pageSize, ThreadLocalUtil.parse(startTime,1),ThreadLocalUtil.parse(endTime,1)
                ,"db_lock_his","tb_lock_his",his);
        return new Response().setII(1,pageInfo.getList()!=null,pageInfo,"查询锁的历史状态");
    }
}

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

New file
@@ -0,0 +1,142 @@
package com.whyc.service;

import com.whyc.mapper.CallBack;
import com.whyc.util.ThreadLocalUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;

@Service
public class MybatisSqlExecuteService {
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    //    自定义执行SQL
    public JSONArray executeQuery(String sql){
        PreparedStatement ps = null;
        ResultSet rs = null;
        SqlSession sqlSession = openSession();
        JSONArray jsonArray = new JSONArray();
        try {
            ps=sqlSession.getConnection().prepareStatement(sql);
            rs=ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int colCnt = metaData.getColumnCount();
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                for(int i=1;i<=colCnt;i++){
                    String property=metaData.getColumnName(i);
                    Object obj=rs.getObject(i);
                    if(obj instanceof java.util.Date ){
                        //jsonObject.put(property, ActionUtil.sdf.format(obj));
                        jsonObject.put(property, ThreadLocalUtil.format((java.util.Date) obj,1));
                    }else{
                        jsonObject.put(property,obj);
                    }
                }
                JSONObject.toBean(jsonObject,Object.class);
                jsonArray.add(jsonObject);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeSession(rs,ps,sqlSession);
        }
        return jsonArray;
    }

    //    自定义执行SQL
    public List executeQuery_call(String sql, CallBack call){
        PreparedStatement ps = null;
        ResultSet rs = null;
        SqlSession sqlSession = openSession();
        try {
            ps=sqlSession.getConnection().prepareStatement(sql);
            rs=ps.executeQuery();
            return call.getResults(rs);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeSession(rs,ps,sqlSession);
        }
        return null;
    }

    /**
     * 封装所有更新的操作(添加,删除,修改)
     * @param sql
     * @param values
     * @return
     */
    public  int executeUpdate(String sql, Object[] values){
        PreparedStatement ps = null;
        ResultSet rs = null;
        SqlSession sqlSession = openSession();
        int flag=0;
        try {
            ps = sqlSession.getConnection().prepareStatement(sql);
            if(values != null){
                for (int i = 0; i < values.length; i++) {
                    ps.setObject(i+1, values[i]);
                }
            }
            flag =  ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            closeSession(rs,ps,sqlSession);
        }
        return flag;
    }
    //执行sql语句
    public  boolean execute(String sql){
        PreparedStatement ps = null;
        SqlSession sqlSession = openSession();
        boolean rest = true;
        try{
                ps = sqlSession.getConnection().prepareStatement(sql);
                ps.execute();

        }catch(SQLException ex) {
            ex.printStackTrace();
            rest = false;
        }finally {
            closeSession(null,ps,sqlSession);
        }

        return rest;
    }
    //    开启链接
    private SqlSession openSession() {
        SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
        return sqlSessionFactory.openSession();
    }

    //    关闭链接
    private void closeSession(ResultSet rs,PreparedStatement ps,SqlSession sqlSession) {
        try {
            if(rs!=null){
                rs.close();
            }
            if(ps!=null){
                ps.close();
            }
            if(sqlSession!=null){
                sqlSession.close();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

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

New file
@@ -0,0 +1,81 @@
package com.whyc.service;

import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_lock_his.LockHis;
import com.whyc.util.ActionUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

@Service
public class SubTableService {
    @Autowired
    private MybatisSqlExecuteService sqlExecuteService;
    //锁的历史记录总数
    public int getLockHisCount(LockHis his) {
        String sql="SELECT  count(*) as number FROM db_lock_his"+ his.getRecordYear()+" history " +
                " where history.lock_id="+ his.getLockId() ;
        if(his.getStartTime()!=null){
            sql+=" and record_time  >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
        }
        if(his.getEndTime()!=null){
            sql+=" and record_time  <='"+ThreadLocalUtil.format(his.getEndTime(),1)+"' ";
        }
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                LinkedList<Object> temp = new LinkedList<>();
                try {
                    while (rs.next())
                        temp.add(rs.getInt("number"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return temp;
            }
        });
        int num =0;
        if(list!=null){
            num= (int) list.get(0);
        }
        return num;
    }
    //锁的历史记录
    public List<LockHis> getBattHisList(LockHis his) {
        String sql="SELECT * FROM db_lock_his"+ his.getRecordYear()+" history " +
                " where history.lock_id="+  his.getLockId() ;
        if(his.getStartTime()!=null){
            sql+=" and record_time  >='"+ ThreadLocalUtil.format(his.getStartTime(),1)+"' ";
        }
        if(his.getEndTime()!=null){
            sql+=" and record_time  <='"+ThreadLocalUtil.format(his.getEndTime(),1)+"' ";
        }
        sql+="  ORDER BY record_time asc  limit "+ his.getLimitStart()+","+ his.getLimitEnd()+" ";
        List<LockHis> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List list=new ArrayList();
                while (rs.next()){
                    LockHis data=new LockHis();
                    data.setNum(rs.getInt("num"));
                    data.setLockId(rs.getInt("lock_id"));
                    data.setRecordTime(rs.getTimestamp("record_time"));
                    data.setLockState(rs.getInt("lock_state"));
                    data.setUnlockType(rs.getInt("unlock_type"));
                    data.setUnlockId(rs.getString("unlock_id"));
                    list.add(data);
                }
                return list;
            }
        });
        return list;
    }


}

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

@@ -545,6 +545,39 @@
    }

    /**
     *  查询使用时,需要处理 默认表查询的问题 倒序
     *  示例:
     *  <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>> getQueryTimeForSubTablesDescWithOutDefault(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);
        return yearTimeMap;
    }

    /**
     * 按年划分
     * 优化getQueryTimeForSubTablesDesc,待测试
     * 更新default表是否查询的逻辑,改||为&&

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

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

import com.github.pagehelper.PageInfo;
import com.whyc.factory.ThreadPoolExecutorFactory;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_lock_his.LockHis;
import com.whyc.service.SubTableService;
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;

    //按年月分表
    /**按年份表分页查询*/
    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;
            String existTableName = commonMapper.existTable(dbName, tableName);
            if(existTableName == null){
                continue;
            }
            //====== 根据不同类型类型对象对应调整 ======
            if(pojo instanceof LockHis) {
                LockHis his = new LockHis();
                BeanUtils.copyProperties(pojo, his);
                his.setStartTime(queryTime.get(0));
                his.setEndTime(queryTime.get(1));
                his.setRecordYear(tableName);
                int currentCount = service.getLockHisCount(his);
                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 LockHis) {
                LockHis his = new LockHis();
                BeanUtils.copyProperties(pojo, his);
                his.setStartTime(queryTime.get(0));
                his.setEndTime(queryTime.get(1));
                his.setRecordYear(recordYear);
                List<Integer> limitList = tableAndLimitMap.get(key);
                his.setLimitStart(limitList.get(0));
                his.setLimitEnd(limitList.get(1));
                List<LockHis> list =  service.getBattHisList(his);
                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.getQueryTimeForSubTablesByMonthDesc(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 LockHis) {
                    LockHis his = new LockHis();
                    BeanUtils.copyProperties(pojo, his);
                    his.setStartTime(queryTime.get(0));
                    his.setEndTime(queryTime.get(1));
                    his.setRecordYear(tableName);
                    int currentCount = service.getLockHisCount(his);
                    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 LockHis) {
                LockHis his = new LockHis();
                BeanUtils.copyProperties(pojo, his);
                his.setStartTime(queryTime.get(0));
                his.setEndTime(queryTime.get(1));
                his.setRecordYear(recordYear);
                List<Integer> limitList = tableAndLimitMap.get(key);
                his.setLimitStart(limitList.get(0));
                his.setLimitEnd(limitList.get(1));
                List<LockHis> list =  service.getBattHisList(his);
                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;
    }
}