电池站点温度历史,按摄像头id,月分表

whycxzp

2025-03-31 a127882c8f15593783fcf0a1489835c16db70682

电池站点温度历史,按摄像头id,月分表

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

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

import com.whyc.dto.Response;
import com.whyc.pojo.db_power_history.BattStationTempHistory;
import com.whyc.pojo.db_power_rt.BattStationTemp;
import com.whyc.service.BattStationTempHisService;
import com.whyc.service.BattStationTempService;
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;
import java.util.List;

@RestController
@Api(tags = "电池机房温度历史")
@RequestMapping("battStationTempHis")
public class BattStationTempHisController {

    @Autowired
    private BattStationTempHisService service;

    @ApiOperation("查询列表")
    @GetMapping("getPageByCameraId")
    public Response getPageByCameraId(@RequestParam String cameraId,@RequestParam int pageNum, @RequestParam int pageSize,
                                                                    @RequestParam String startTime,@RequestParam String endTime
    ) throws ParseException, InterruptedException {
        return service.getPageByCameraId(pageNum,pageSize,cameraId,startTime,endTime);
    }


}

 src/main/java/com/whyc/pojo/db_power_history/BattStationTempHistory.java

@@ -1,5 +1,6 @@
package com.whyc.pojo.db_power_history;

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.ToString;
@@ -27,4 +28,20 @@

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date recordTime;

    @TableField(exist = false)
    private Date startTime;

    @TableField(exist = false)
    private Date endTime;

    @TableField(exist = false)
    private String recordYearMonth;

    @TableField(exist = false)
    private Integer limitStart;

    @TableField(exist = false)
    private Integer limitEnd;

}

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

@@ -1,12 +1,18 @@
package com.whyc.service;

import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.mapper.BattStationTempHisMapper;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_power_history.BattStationTempHistory;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

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

@Service
public class BattStationTempHisService {
@@ -20,6 +26,9 @@
    @Autowired
    private JdbcSqlExecuteService jdbcSqlExecuteService;

    @Autowired
    private SubTablePageInfoUtil pageInfoUtil;

    public void createTableByMonth(Integer battGroupId,String yearMonth) {
        jdbcSqlExecuteService.execute("CREATE TABLE IF NOT EXISTS `db_power_history`.`db_batt_station_temp_history_"+yearMonth+"` LIKE `db_power_history`.`db_batt_station_temp_history`");
    }
@@ -32,4 +41,9 @@
        jdbcSqlExecuteService.execute("INSERT INTO `db_power_history`.`db_batt_station_temp_history_"+battGroupId+"_"+yearMonth+"` (`batt_group_id`, `station_id`, `pixel_x`, `pixel_y`, `max_temp`, `min_temp`, `min_temp_point`, `max_temp_point`, `camera_id`, `record_time`)" +
                "\n values("+tempHistory.getBattGroupId()+","+tempHistory.getStationId()+","+tempHistory.getPixelX()+","+tempHistory.getPixelY()+","+tempHistory.getMaxTemp()+","+tempHistory.getMinTemp()+",'"+tempHistory.getMinTempPoint()+"','"+tempHistory.getMaxTempPoint()+"','"+tempHistory.getCameraId()+"','"+tempHistory.getRecordTime());
    }

    public Response getPageByCameraId(int pageNum, int pageSize, String cameraId, String startTime, String endTime) throws ParseException, InterruptedException {
        PageInfo<Object> pageInfo = pageInfoUtil.getPageInfoByMonthTable(pageNum, pageSize, ThreadLocalUtil.parse(startTime, 1), ThreadLocalUtil.parse(endTime, 1), "db_power_history", "tb_batt_station_temp_history_" + cameraId, new BattStationTempHistory());
        return new Response().set(1, pageInfo);
    }
}

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

@@ -6,6 +6,7 @@
import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear;
import com.whyc.pojo.db_dis_batt.BattTestInfData;
import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
import com.whyc.pojo.db_power_history.BattStationTempHistory;
import com.whyc.util.ActionUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
@@ -124,6 +125,67 @@
        return list;
    }

    public int getBattStationTempHisCount(BattStationTempHistory tempHistory) {
        String sql="select count(id) as number from db_power_history."+tempHistory.getRecordYearMonth()+" where 1=1 ";
        if(tempHistory.getStartTime()!=null){
            sql+=" and record_time  >='"+ ThreadLocalUtil.format(tempHistory.getStartTime(),1)+"' ";
        }
        if(tempHistory.getEndTime()!=null){
            sql+=" and record_time  <='"+ThreadLocalUtil.format(tempHistory.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<BattStationTempHistory> getBattStationTempHisList(BattStationTempHistory tempHistory) {
        String sql="select * from db_power_history."+tempHistory.getRecordYearMonth()+" where 1=1 ";
        if(tempHistory.getStartTime()!=null){
            sql+=" and record_time  >='"+ ThreadLocalUtil.format(tempHistory.getStartTime(),1)+"' ";
        }
        if(tempHistory.getEndTime()!=null){
            sql+=" and record_time  <='"+ThreadLocalUtil.format(tempHistory.getEndTime(),1)+"' ";
        }
        sql+="  ORDER BY record_time desc  limit "+ tempHistory.getLimitStart()+","+ tempHistory.getLimitEnd()+" ";
        List<BattStationTempHistory> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<BattStationTempHistory> list=new ArrayList();
                while (rs.next()){
                    BattStationTempHistory data=new BattStationTempHistory();
                    data.setId(rs.getInt("id"));
                    data.setBattGroupId(rs.getInt("batt_group_id"));
                    data.setStationId(rs.getInt("station_id"));
                    data.setCameraId(rs.getString("camera_id"));
                    data.setPixelX(rs.getInt("pixel_x"));
                    data.setPixelY(rs.getInt("pixel_y"));
                    data.setMinTemp(rs.getFloat("min_temp"));
                    data.setMaxTemp(rs.getFloat("max_temp"));
                    data.setMinTempPoint(rs.getString("min_temp_point"));
                    data.setMaxTempPoint(rs.getString("max_temp_point"));
                    data.setRecordTime(rs.getTimestamp("record_time"));
                    list.add(data);
                }
                return list;
            }
        });
        return list;
    }
    //电源告警历史实时计算每张表查询总数
    public int getPowerHisCount(PowerAlarmHistory powerAlarmHistory) {
        String sql="SELECT  count(*) as number FROM db_power_alarm."+ powerAlarmHistory.getRecordYear()+" history " +

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

@@ -664,6 +664,45 @@
        return yearTimeDescMap;
    }

    /**
     *  按月划分且,不存在默认表,都是分表
     * @param startTimeDate
     * @param endTimeDate
     * @return
     * @throws ParseException
     */
    public static Map<String,List<Date>> getQueryTimeForSubTablesByMonthDesc2(Date startTimeDate,Date endTimeDate) throws ParseException {
        //根据筛选的时间段,查询出符合要求的各分表记录数
        //首先,如果当前时间超过7月,默认表中只有本年度的;如果没超过7月,默认表还存在上一年度的记录
        Calendar now = Calendar.getInstance();
        if(endTimeDate.compareTo(now.getTime())>0){ //校验优化最大查询时间
            endTimeDate = now.getTime();
        }
        LinkedHashMap<String,List<Date>> yearTimeMap = (LinkedHashMap<String, List<Date>>) DateUtil.getMonthListDesc(startTimeDate, endTimeDate);
        Map<String,List<Date>> yearTimeDescMap = new LinkedHashMap<>();
        boolean existDefaultYear = false;
        //查询的结束时间
        //if(DateUtil.YYYY_MM_TABLE.format(endTimeDate).equals(DateUtil.YYYY_MM_TABLE.format(now.getTime()))){
        if(ThreadLocalUtil.format(endTimeDate,2).equals(ThreadLocalUtil.format(now.getTime(),2))){
            List<Date> defaultDateList = new LinkedList<>();
            defaultDateList.add(startTimeDate);
            defaultDateList.add(endTimeDate);
        }
        //调整下顺序,将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;
    }


    public static void main(String[] args) throws ParseException {
        //Date d1 = YYYY_MM_DD_HH_MM_SS.parse("2023-01-22 14:12:22");

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

@@ -9,6 +9,7 @@
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.BattStationTempHistory;
import com.whyc.pojo.db_user.UserInf;
import com.whyc.service.DevLithiumInfService;
import com.whyc.service.SubTableService;
@@ -175,7 +176,7 @@
                                                    Date startTime, Date endTime,
                                                    String dbName, String tablePrefix,
                                                    Object pojo) throws ParseException, InterruptedException {
        Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesByMonthDesc(startTime, endTime);
        Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesByMonthDesc2(startTime, endTime);
        //查询分表是否存在,存在则查询结果
        Map<String,Integer> queryCountMap = new LinkedHashMap<>();
        Set<String> tableYearKeySet = queryTimeForSubTables.keySet();
@@ -220,6 +221,14 @@
                    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);
                }
                latch.countDown();
            });
        }
@@ -269,6 +278,16 @@
                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);
            }
        }
        pageInfo.setList(dataList);

 src/main/java/com/whyc/webSocket/BattStationTempSocket.java

New file
@@ -0,0 +1,98 @@
package com.whyc.webSocket;

import com.whyc.config.WebSocketConfig;
import com.whyc.dto.PageAlarm;
import com.whyc.dto.Response;
import com.whyc.service.BattAlarmService;
import com.whyc.service.BattStationTempService;
import com.whyc.util.ActionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpSession;
import javax.websocket.*;
import javax.websocket.server.ServerEndpoint;
import java.util.HashMap;
import java.util.Map;


/**
 * 电池告警
 */
@Component
@ServerEndpoint(value = "/battStationTemp",encoders = WebSocketEncoder.class,configurator = WebSocketConfig.class)
public class BattStationTempSocket {
    private Session session;

    private Thread thread;

    private static BattStationTempService service;

    private volatile boolean runFlag = true;

    private volatile Map<String, Thread> threadMap = new HashMap<>();

    private volatile Map<Long,Boolean> threadFlagMap = new HashMap<>();

    @Autowired
    public void setService(BattStationTempService service) {
        BattStationTempSocket.service = service;
    }

    @OnOpen
    public void onOpen(Session session, EndpointConfig config) {
        this.session = session;
        thread = new Thread("Thread_BattStationTempSocket") {
            @Override
            public void run() {
                while (runFlag && !isInterrupted()) {
                    Thread thread = currentThread();
                    threadFlagMap.put(thread.getId(), true);
                    try {
                        if (session.isOpen()) {
                            //推送信息
                            synchronized (session) {
                                session.getBasicRemote().sendObject(service.getList());
                            }
                            threadFlagMap.put(thread.getId(), false);
                        }
                        sleep(4000);
                    } catch (Exception e) {
                        interrupt();
                    }
                }
            }
        };
        thread.start();
        threadFlagMap.put(thread.getId(),true);
        //停止老的socket线程
        Thread threadBefore = threadMap.get(session.getId());
        if(threadBefore !=null && threadBefore.isAlive()){
            while (threadFlagMap.get(threadBefore.getId())){
            }
            threadBefore.interrupt();
        }
        //将线程存储,便于调用定位
        threadMap.put(session.getId(), this.thread);
    }


    @OnClose
    public void onClose(CloseReason closeReason){
        System.err.println("closeReason = " + closeReason);
        runFlag = false;
        if (thread != null && thread.isAlive()) {
            thread.interrupt();
        }
        threadMap.remove(session.getId());
    }

    @OnError
    public void onError(Throwable error) {
        error.printStackTrace();
        if (thread != null && thread.isAlive()) {
            thread.interrupt();
        }
        threadMap.remove(session.getId());
    }
}