0824

826220679@qq.com

23 小时以前 ea135161eff1dd7c71c159be948e93b50fd1db81

 src/scheduled_task/TrackTableManager.java

@@ -7,21 +7,18 @@
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 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 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 main(String[] args) {
    
    public static void Start() {
        // 初始化时立即执行一次
        manageTables();
        
        manageTables();        
        // 创建定时任务（每天凌晨1点执行）
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(
@@ -34,85 +31,97 @@

    // 主管理逻辑
    private static void manageTables() {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // 1. 创建明天的表
            createTomorrowTable(conn);
        try {
            // 0. 创建当天的表（如果不存在）
            createTodayTableIfNotExists();
            
            // 1. 创建未来3天的表
            createFutureTables();
            
            // 2. 删除过期表
            deleteExpiredTables(conn);
            deleteExpiredTables();
            
        } 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 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(Connection conn) throws SQLException {
    private static void deleteExpiredTables() throws SQLException {
        LocalDate thresholdDate = LocalDate.now().minusDays(SAVE_DAYS);
        String threshold = thresholdDate.format(DATE_FORMATTER);
        
        for (String table : getAllTrackTables(conn)) {
        for (String table : getAllTrackTables()) {
            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);
                }
                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(Connection conn, String tableName) throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null)) {
            return rs.next();
    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(Connection conn) throws SQLException {
    private static List<String> getAllTrackTables() throws SQLException {
        List<String> tables = new ArrayList<>();
        try (ResultSet rs = conn.getMetaData().getTables(null, null, TABLE_PREFIX + "%", null)) {
        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"));
                tables.add(rs.getString("table_name"));
            }
        }
        return tables;