0824

826220679@qq.com

15 小时以前 ea135161eff1dd7c71c159be948e93b50fd1db81

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
126
127
128
129
130
131
132
133
134
135
136
137
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 {
            // 0. ´´½¨µ±ÌìµÄ±í£¨Èç¹û²»´æÔÚ£©
            createTodayTableIfNotExists();
            
            // 1. ´´½¨Î´À´3ÌìµÄ±í
            createFutureTables();
            
            // 2. É¾³ý¹ýÆÚ±í
            deleteExpiredTables();
            
        } catch (SQLException e) {
            System.err.println("Êý¾Ý¿â²Ù×÷ʧ°Ü: " + e.getMessage());
        }
    }
 
    // ´´½¨µ±ÌìµÄ±í£¨Èç¹û²»´æÔÚ£©
    private static void createTodayTableIfNotExists() throws SQLException {
        String today = LocalDate.now().format(DATE_FORMATTER);
        String tableName = TABLE_PREFIX + today;
        if (!tableExists(tableName)) {
            DBConnector.executeUpdate(getcreateSQL(tableName));
        }
    }
 
    // ´´½¨Î´À´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;
    }
}