更新建表

whycxzp

2025-03-31 6249ef6adf616e11aa7a9b601635c9f0e6ac0ba0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
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 {
 
    @Resource
    private BattStationTempHisMapper mapper;
 
    @Resource
    private CommonMapper commonMapper;
 
    @Autowired
    private JdbcSqlExecuteService jdbcSqlExecuteService;
 
    @Autowired
    private SubTablePageInfoUtil pageInfoUtil;
 
    public void createTableByMonth(Integer battGroupId,String yearMonth) {
        yearMonth = battGroupId+"_"+yearMonth;
        //创建自增序列
        String createSeq = "CREATE SEQUENCE db_power_history.\"tb_batt_station_temp_history_"+yearMonth+"_id_seq\"\n" +
                "INCREMENT 1\n" +
                "MINVALUE 1\n" +
                "MAXVALUE 9223372036854775807\n" +
                "START 1\n" +
                "CACHE 1;\n" +
                "\n" +
                "ALTER SEQUENCE db_power_history.\"tb_batt_station_temp_history_"+yearMonth+"_id_seq\" OWNER TO sysdba;\n" +
                "\n" +
                "SELECT setval('db_power_history.\"tb_batt_station_temp_history_"+yearMonth+"_id_seq\"',1,false);";
 
        String sqlTable = "CREATE TABLE db_power_history.tb_batt_station_temp_history_"+yearMonth+"(\n" +
                "\tid bigint NOT NULL DEFAULT nextval('db_power_history.tb_batt_station_temp_history_"+yearMonth+"_id_seq'::regclass),\n" +
                "\tpixel_x integer NOT NULL,\n" +
                "\tpixel_y integer NOT NULL,\n" +
                "\tmax_temp double precision NOT NULL,\n" +
                "\tmin_temp double precision NOT NULL,\n" +
                "\tmin_temp_point character varying NOT NULL COLLATE pg_catalog.\"default\",\n" +
                "\tmax_temp_point character varying NOT NULL COLLATE pg_catalog.\"default\",\n" +
                "\tcamera_id integer NOT NULL,\n" +
                "\trecord_time timestamp without time zone NOT NULL,\n" +
                "\tstation_id integer,\n" +
                "\tbatt_group_id integer,\n" +
                "\t PRIMARY KEY (id)\n" +
                ")\n" +
                "\n" +
                ";\n" +
                "\n" +
                "ALTER    TABLE db_power_history.tb_batt_station_temp_history_"+yearMonth+"\n" +
                "\tOWNER TO sysdba;\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".pixel_x IS '像素x轴';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".pixel_y IS '像素y轴';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".max_temp IS '最大温度';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".min_temp IS '最小温度';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".min_temp_point IS '最小温度坐标,格式为x*y,用逗号隔开';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".max_temp_point IS '最大温度坐标,格式为x*y,用逗号隔开';\n" +
                "\n" +
                "COMMENT ON COLUMN db_power_history.tb_batt_station_temp_history_"+yearMonth+".camera_id IS '摄像头id';";
        jdbcSqlExecuteService.execute(createSeq+sqlTable);
    }
 
    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());
    }
 
    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);
    }
}