修改

826220679@qq.com

18 小时以前 1bda9524add969e315d870f284046ecf1097f956

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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
package scheduled_task;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import dell_targets.Dell_SystemConfiguration;
import databases.DBConnector;  // Ìí¼Óµ¼Èë
 
public class TrackTableManager {
    // ÒƳýÓ²±àÂëµÄÊý¾Ý¿âÁ¬½Ó²ÎÊý
    private static final int SAVE_DAYS = Dell_SystemConfiguration.TrajectoryRetentionDays;
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final String TABLE_PREFIX = "tb_track_";
    
    public static void Start() {
        // ³õʼ»¯Ê±Á¢¼´Ö´ÐÐÒ»´Î
        manageTables();        
        // ´´½¨¶¨Ê±ÈÎÎñ£¨Ã¿ÌìÁ賿1µãÖ´ÐУ©
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(
            TrackTableManager::manageTables,
            getInitialDelay(),
            24 * 60 * 60, // 24СʱÖÜÆÚ
            TimeUnit.SECONDS
        );
    }
 
    // Ö÷¹ÜÀíÂß¼­
    private static void manageTables() {
        try {
            // 1. ´´½¨Î´À´3ÌìµÄ±í
            createFutureTables();
            
            // 2. É¾³ý¹ýÆÚ±í
            deleteExpiredTables();
            
        } catch (SQLException e) {
            System.err.println("Êý¾Ý¿â²Ù×÷ʧ°Ü: " + e.getMessage());
        }
    }
 
    // ´´½¨Î´À´3ÌìµÄ±í£¨°üÀ¨Ã÷Ìì¡¢ºóÌìºÍ´óºóÌ죩
    private static void createFutureTables() throws SQLException {
        for (int i = 1; i <= 3; i++) {
            String futureDate = LocalDate.now().plusDays(i).format(DATE_FORMATTER);
            String tableName = TABLE_PREFIX + futureDate;            
            if (!tableExists(tableName)) {                
                DBConnector.executeUpdate(getcreateSQL(tableName));
            } 
        }
    }
 
    // É¾³ý¹ýÆÚ±í
    private static void deleteExpiredTables() throws SQLException {
        LocalDate thresholdDate = LocalDate.now().minusDays(SAVE_DAYS);
        String threshold = thresholdDate.format(DATE_FORMATTER);
        
        for (String table : getAllTrackTables()) {
            String datePart = table.substring(TABLE_PREFIX.length());
            if (datePart.compareTo(threshold) < 0) {
                DBConnector.executeUpdate("DROP TABLE " + table);
            }
        }
    }
    
    public static String getcreateSQL(String tableName) {
         String createSQL = "CREATE TABLE " + tableName + " (" +
                 "id INT(30) PRIMARY KEY AUTO_INCREMENT COMMENT 'ÐòºÅ'," +
                 "device_number VARCHAR(30) NOT NULL COMMENT 'É豸±àºÅ'," +
                 "device_name VARCHAR(30) COMMENT 'É豸Ãû³Æ'," +
                 "x_coordinate VARCHAR(30) COMMENT 'X×ø±ê'," +
                 "y_coordinate VARCHAR(30) COMMENT 'Y×ø±ê'," +
                 "z_coordinate VARCHAR(30) COMMENT 'Z×ø±ê'," +
                 "layer VARCHAR(30) COMMENT 'ËùÔÚ²ã'," +
                 "battery VARCHAR(30) COMMENT 'µçÁ¿'," +
                 "utc_times VARCHAR(30) COMMENT 'UTCʱ¼ä'," +
                 "latitude VARCHAR(30) COMMENT 'γ¶È'," +
                 "longitude VARCHAR(30) COMMENT '¾­¶È'," +
                 "positioning_quality VARCHAR(30) COMMENT '¶¨Î»ÖÊÁ¿'," +
                 "satellite_count VARCHAR(30) COMMENT 'ÎÀÐÇÊýÁ¿'," +
                 "hdop VARCHAR(30) COMMENT 'ˮƽ¾«¶ÈÒò×Ó'," +
                 "altitude VARCHAR(30) COMMENT 'º£°Î¸ß¶È'," +
                 "geoid_height VARCHAR(30) COMMENT '´óµØË®×¼Ãæ¸ß¶È'," +
                 "differential_time VARCHAR(30) COMMENT '²î·Öʱ¼ä'," +
                 "source VARCHAR(30) COMMENT '¶¨Î»½á¹ûÀ´Ô´(0=UWB,1=ÎÀÐÇ,3=À¶ÑÀ)'," +
                 "company VARCHAR(30) COMMENT 'ËùÊô¹«Ë¾'," +
                 "save_time VARCHAR(30) COMMENT '±£´æʱ¼ä'" +
                 ") ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='¹ì¼£¼Ç¼±í'";
        return createSQL;
    }
 
    // ¼ì²é±íÊÇ·ñ´æÔÚ
    private static boolean tableExists(String tableName) throws SQLException {
        try (ResultSet rs = DBConnector.executeQuery(
            "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?", 
            tableName)) {
            return rs.next() && rs.getInt(1) > 0;
        }
    }
 
    // »ñÈ¡ËùÓй켣±í
    private static List<String> getAllTrackTables() throws SQLException {
        List<String> tables = new ArrayList<>();
        try (ResultSet rs = DBConnector.executeQuery(
            "SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name LIKE ?", 
            TABLE_PREFIX + "%")) {
            while (rs.next()) {
                tables.add(rs.getString("table_name"));
            }
        }
        return tables;
    }
 
    // ¼ÆËãÊ×´ÎÖ´ÐÐÑÓ³Ù£¨µ½ÏÂÒ»¸öÁ賿1µãµÄÃëÊý£©
    private static long getInitialDelay() {
        long now = System.currentTimeMillis() / 1000;
        long oneDay = 24 * 60 * 60;
        long target = (now / oneDay * oneDay) + oneDay + 3600; // ÏÂÒ»ÌìÁ賿1µã
        return target - now;
    }
}