From 39b74c6fb4a50bed412bbebdd38440d384f47733 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期日, 30 三月 2025 14:40:18 +0800
Subject: [PATCH] 完善温度告警
---
src/main/java/com/whyc/mapper/CommonMapper.java | 2
src/main/java/com/whyc/service/JdbcSqlExecuteService.java | 206 +++++++++++++++++++++++++++++++++++++++++
src/main/java/com/whyc/pojo/db_power_history/BattStationTempHistory.java | 26 +++++
src/main/java/com/whyc/mapper/BattStationTempHisMapper.java | 1
src/main/resources/mapper/CommonMapper.xml | 12 +-
src/main/java/com/whyc/schedule/BattStationTempScheduleService.java | 22 +++
src/main/java/com/whyc/service/BattStationTempHisService.java | 21 +++
src/main/java/com/whyc/util/SubTablePageInfoUtil.java | 4
8 files changed, 280 insertions(+), 14 deletions(-)
diff --git a/src/main/java/com/whyc/mapper/BattStationTempHisMapper.java b/src/main/java/com/whyc/mapper/BattStationTempHisMapper.java
index e4ecd25..32d8445 100644
--- a/src/main/java/com/whyc/mapper/BattStationTempHisMapper.java
+++ b/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);
}
diff --git a/src/main/java/com/whyc/mapper/CommonMapper.java b/src/main/java/com/whyc/mapper/CommonMapper.java
index ec8f2da..daca304 100644
--- a/src/main/java/com/whyc/mapper/CommonMapper.java
+++ b/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);
diff --git a/src/main/java/com/whyc/pojo/db_power_history/BattStationTempHistory.java b/src/main/java/com/whyc/pojo/db_power_history/BattStationTempHistory.java
index da4f0ab..328b287 100644
--- a/src/main/java/com/whyc/pojo/db_power_history/BattStationTempHistory.java
+++ b/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;
}
diff --git a/src/main/java/com/whyc/schedule/BattStationTempScheduleService.java b/src/main/java/com/whyc/schedule/BattStationTempScheduleService.java
index c7efb8b..49a82ca 100644
--- a/src/main/java/com/whyc/schedule/BattStationTempScheduleService.java
+++ b/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);
diff --git a/src/main/java/com/whyc/service/BattStationTempHisService.java b/src/main/java/com/whyc/service/BattStationTempHisService.java
index 0dd3395..2109e8f 100644
--- a/src/main/java/com/whyc/service/BattStationTempHisService.java
+++ b/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());
+ }
}
diff --git a/src/main/java/com/whyc/service/JdbcSqlExecuteService.java b/src/main/java/com/whyc/service/JdbcSqlExecuteService.java
new file mode 100644
index 0000000..5a45cb4
--- /dev/null
+++ b/src/main/java/com/whyc/service/JdbcSqlExecuteService.java
@@ -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;
+
+ // 鑷畾涔夋墽琛孲QL
+ 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;
+ }
+
+ // 鑷畾涔夋墽琛孲QL
+ 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;
+ }
+}
diff --git a/src/main/java/com/whyc/util/SubTablePageInfoUtil.java b/src/main/java/com/whyc/util/SubTablePageInfoUtil.java
index 0b78efc..c436eca 100644
--- a/src/main/java/com/whyc/util/SubTablePageInfoUtil.java
+++ b/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;
}
//====== 鏍规嵁涓嶅悓绫诲瀷绫诲瀷瀵硅薄瀵瑰簲璋冩暣 ======
diff --git a/src/main/resources/mapper/CommonMapper.xml b/src/main/resources/mapper/CommonMapper.xml
index 03e827e..ee5558e 100644
--- a/src/main/resources/mapper/CommonMapper.xml
+++ b/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
--
Gitblit v1.9.1