首次提交

826220679@qq.com

2025-08-07 4d6cd980c5c69e4d9d150669c89734642297e0cd

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
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;
 
public class TrackTableManager {
 
    // ÅäÖòÎÊý
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/your_database?useSSL=false";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";
    private static final int SAVE_DAYS = 30; // ¹ì¼£Êý¾Ý±£´æÌìÊý
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final String TABLE_PREFIX = "tb_track_";
 
    public static void main(String[] args) {
        // ³õʼ»¯Ê±Á¢¼´Ö´ÐÐÒ»´Î
        manageTables();
        
        // ´´½¨¶¨Ê±ÈÎÎñ£¨Ã¿ÌìÁ賿1µãÖ´ÐУ©
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(
            TrackTableManager::manageTables,
            getInitialDelay(),
            24 * 60 * 60, // 24СʱÖÜÆÚ
            TimeUnit.SECONDS
        );
    }
 
    // Ö÷¹ÜÀíÂß¼­
    private static void manageTables() {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // 1. ´´½¨Ã÷ÌìµÄ±í
            createTomorrowTable(conn);
            
            // 2. É¾³ý¹ýÆÚ±í
            deleteExpiredTables(conn);
            
        } catch (SQLException e) {
            System.err.println("Êý¾Ý¿â²Ù×÷ʧ°Ü: " + e.getMessage());
        }
    }
 
    // ´´½¨Ã÷ÌìµÄ±í
    private static void createTomorrowTable(Connection conn) throws SQLException {
        String tomorrow = LocalDate.now().plusDays(1).format(DATE_FORMATTER);
        String tableName = TABLE_PREFIX + tomorrow;
        
        if (!tableExists(conn, 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='¹ì¼£¼Ç¼±í'";
            
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(createSQL);
                System.out.println("±í´´½¨³É¹¦: " + tableName);
            }
        } else {
            System.out.println("±íÒÑ´æÔÚ: " + tableName);
        }
    }
 
    // É¾³ý¹ýÆÚ±í
    private static void deleteExpiredTables(Connection conn) throws SQLException {
        LocalDate thresholdDate = LocalDate.now().minusDays(SAVE_DAYS);
        String threshold = thresholdDate.format(DATE_FORMATTER);
        
        for (String table : getAllTrackTables(conn)) {
            String datePart = table.substring(TABLE_PREFIX.length());
            if (datePart.compareTo(threshold) < 0) {
                try (Statement stmt = conn.createStatement()) {
                    stmt.executeUpdate("DROP TABLE " + table);
                    System.out.println("ÒÑɾ³ý¹ýÆÚ±í: " + table);
                }
            }
        }
    }
 
    // ¼ì²é±íÊÇ·ñ´æÔÚ
    private static boolean tableExists(Connection conn, String tableName) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null)) {
            return rs.next();
        }
    }
 
    // »ñÈ¡ËùÓй켣±í
    private static List<String> getAllTrackTables(Connection conn) throws SQLException {
        List<String> tables = new ArrayList<>();
        try (ResultSet rs = conn.getMetaData().getTables(null, null, TABLE_PREFIX + "%", null)) {
            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;
    }
}