完善温度告警

whycxzp

2025-03-30 39b74c6fb4a50bed412bbebdd38440d384f47733

完善温度告警

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

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

public interface BattStationTempHisMapper {
    void createTableByMonth(Integer battGroupId, String month);

}

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

@@ -17,7 +17,7 @@
    //Object getMaxValue(String schema,String table,String field,String valuePrefix);
    Object getMaxValue(String schema, String table, String field);

    String existTable(String dbName, String tableName);
    boolean existTable(String dbName, String tableName);

    //void truncate(String dbName, String tableName);


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

@@ -1,4 +1,30 @@
package com.whyc.pojo.db_power_history;

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.ToString;

import java.util.Date;

@Data
@ToString
public class BattStationTempHistory {

    private Integer id;

    private Integer pixelX;

    private Integer pixelY;

    private Float minTemp;
    private Float maxTemp;

    private String minTempPoint;
    private String maxTempPoint;
    private String cameraId;
    private Integer battGroupId;
    private Integer stationId;

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

 src/main/java/com/whyc/schedule/BattStationTempScheduleService.java

@@ -7,18 +7,23 @@
import com.whyc.pojo.db_alarm.BattStationTempAlarm;
import com.whyc.pojo.db_batt.BattCamera;
import com.whyc.pojo.db_param.PageParam;
import com.whyc.pojo.db_power_history.BattStationTempHistory;
import com.whyc.pojo.db_power_rt.BattStationTemp;
import com.whyc.service.BattStationTempAlarmService;
import com.whyc.service.BattStationTempHisService;
import com.whyc.service.BattStationTempService;
import com.whyc.service.PageParamService;
import com.whyc.util.DateUtil;
import com.whyc.util.HikTempUtil;
import com.whyc.util.ThreadLocalUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.List;
import java.util.Map;
@@ -91,9 +96,20 @@
                    battStationTemp.setStationId(stationId);

                    service.addOrUpdate(battStationTemp);
                    //温度历史 - 按电池组id,月度表


                    String yearMonth = ThreadLocalUtil.format(date, 2);
                    //温度历史 - 记录到 按电池组id,月度表
                    boolean exist = hisService.existTempHisTableByMonth(battGroupId,yearMonth);
                    //不存在则创建.插入记录
                    if(!exist){
                        hisService.createTableByMonth(battGroupId,yearMonth);
                    }
                    BattStationTempHistory tempHistory = new BattStationTempHistory();
                    try {
                        BeanUtils.copyProperties(tempHistory,battStationTemp);
                    } catch (IllegalAccessException | InvocationTargetException e) {
                        throw new RuntimeException(e);
                    }
                    hisService.add(battGroupId,yearMonth,tempHistory);
                    //温度告警判断
                    //查询温度告警表，如果存在
                    BattStationTempAlarm alarmInDB = alarmService.getByBattGroupId(battGroupId);

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

@@ -1,6 +1,9 @@
package com.whyc.service;

import com.whyc.mapper.BattStationTempHisMapper;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_power_history.BattStationTempHistory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
@@ -11,8 +14,22 @@
    @Resource
    private BattStationTempHisMapper mapper;

    public void createTableByMonth(Integer battGroupId,String month) {
        mapper.createTableByMonth(battGroupId,month);
    @Resource
    private CommonMapper commonMapper;

    @Autowired
    private JdbcSqlExecuteService jdbcSqlExecuteService;

    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`");
    }

    public boolean existTempHisTableByMonth(Integer battGroupId, String yearMonth) {
        return commonMapper.existTable("db_power_history","db_batt_station_temp_history_"+battGroupId+"_"+yearMonth);
    }

    public void add(Integer battGroupId, String yearMonth, BattStationTempHistory tempHistory) {
        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());
    }
}

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

New file
@@ -0,0 +1,206 @@
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.*;
import java.util.ArrayList;
import java.util.List;

@Service
public class JdbcSqlExecuteService {
    @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();
        }
    }

    public  boolean makeManualCommit( ArrayList<String> al_sql_strs){
        PreparedStatement ps = null;
        SqlSession sqlSession = openSession();
        Connection mysql_con=sqlSession.getConnection();
        boolean exe_res = true;
        try {
            mysql_con.setAutoCommit(false);

            for(int n=0; n<al_sql_strs.size(); n++) {
                if(true == exe_res) {
                    exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
                } else {
                    break;
                }
            }
            if(true == exe_res) {
                mysql_con.commit();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            exe_res = false;
        } finally {
            try {
                if(false == exe_res) {
                    mysql_con.rollback();
                }
                mysql_con.setAutoCommit(true);
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }finally {
                closeSession(null,ps,sqlSession);
            }
        }
        return exe_res;
    }

    //执行sql语句with no colse
    public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
    {
        PreparedStatement ps=null;

        boolean rest = true;
        try
        {
            ps = mysql_con.prepareStatement(sql_str);
            ps.setQueryTimeout(600);
            ps.execute();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
            rest = false;

        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return rest;
    }
}

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

@@ -55,8 +55,8 @@

            //数值
            String tableName = tablePrefix+"_"+tableYear;
            String existTableName = commonMapper.existTable(dbName, tableName);
            if(existTableName == null){
            boolean existTableName = commonMapper.existTable(dbName, tableName);
            if(!existTableName){
                continue;
            }
            //====== 根据不同类型类型对象对应调整 ======

 src/main/resources/mapper/CommonMapper.xml

@@ -164,11 +164,13 @@
    <select id="getMaxValue" resultType="java.lang.Object">
        select max(${field}) from ${schema}.${table}
    </select>
    <select id="existTable" resultType="java.lang.String">
        select distinct table_name
        from INFORMATION_SCHEMA.TABLES
        where table_name = #{tableName}
        and TABLE_SCHEMA = #{dbName}
    <select id="existTable" resultType="java.lang.Boolean">
        SELECT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE table_name = #{tableName}
        AND TABLE_SCHEMA = #{dbName}
        ) AS table_exists
    </select>
    <select id="getTableListLike" resultType="java.lang.String">
            select distinct table_name