From ea135161eff1dd7c71c159be948e93b50fd1db81 Mon Sep 17 00:00:00 2001
From: 826220679@qq.com <826220679@qq.com>
Date: 星期日, 24 八月 2025 22:40:16 +0800
Subject: [PATCH] 0824
---
src/scheduled_task/TrackTableManager.java | 131 +++++++++++++++++++++++--------------------
1 files changed, 70 insertions(+), 61 deletions(-)
diff --git a/src/scheduled_task/TrackTableManager.java b/src/scheduled_task/TrackTableManager.java
index 9107b6e..2605294 100644
--- a/src/scheduled_task/TrackTableManager.java
+++ b/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;
--
Gitblit v1.9.3