import java.sql.*;
import java.time.LocalDate;

public class DataSync {
    public static void main(String[] args) {
        // JDBC连接信息
        String url = "jdbc:mysql://localhost:3306/database";
        String username = "root";
        String password = "password";

        // 当前时间
        LocalDate currentDate = LocalDate.now();

        // 查询TRAVEL_REPORT_ALL_DAILY表是否为空
        boolean isEmpty = checkIfTableEmpty(url, username, password, "TRAVEL_REPORT_ALL_DAILY");

        if (isEmpty) {
            // 同步T_CC_TRAIN全量数据
            syncFullData(url, username, password, "T_CC_TRAIN", "TRAVEL_REPORT_ALL_DAILY");
        } else {
            // 删除TRAVEL_REPORT_ALL_DAILY表中从当前时间往前13个月的数据
            deleteOldData(url, username, password, "TRAVEL_REPORT_ALL_DAILY", currentDate.minusMonths(13));

            // 获取T_CC_TRAIN表中从当前时间往前13个月内的数据
            ResultSet resultSet = getMonthlyData(url, username, password, "T_CC_TRAIN", currentDate.minusMonths(13), currentDate);

            try {
                // 向TRAVEL_REPORT_ALL_DAILY表中插入每个月的数据
                while (resultSet.next()) {
                    // 插入数据操作
                    insertData(url, username, password, "TRAVEL_REPORT_ALL_DAILY", resultSet);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    // 查询表是否为空
    private static boolean checkIfTableEmpty(String url, String username, String password, String tableName) {
        boolean isEmpty = false;
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM " + tableName);
            resultSet.next();
            int count = resultSet.getInt(1);
            isEmpty = (count == 0);
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return isEmpty;
    }

    // 同步全量数据
    private static void syncFullData(String url, String username, String password, String sourceTable, String destinationTable) {
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            statement.execute("TRUNCATE TABLE " + destinationTable); // 清空目标表数据
            statement.execute("INSERT INTO " + destinationTable + " SELECT * FROM " + sourceTable); // 插入全量数据
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除旧数据
    private static void deleteOldData(String url, String username, String password, String tableName, LocalDate endDate) {
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            statement.execute("DELETE FROM " + tableName + " WHERE createtime < '" + endDate + "'"); // 删除指定日期前的数据
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取每月数据
    private static ResultSet getMonthlyData(String url, String username, String password, String tableName, LocalDate startDate, LocalDate endDate) {
        ResultSet resultSet = null;
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM " + tableName + " WHERE createtime >= '" + startDate + "' AND createtime < '" + endDate + "'"); // 查询指定日期范围内的数据
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    // 插入数据
    private static void insertData(String url, String username, String password, String tableName, ResultSet resultSet) {
        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            // 从resultSet读取数据并插入到目标表中
            // statement.execute("INSERT INTO " + tableName + " VALUES (...)"); // 插入数据操作
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

上述代码实现了根据需求进行数据同步的功能。首先通过checkIfTableEmpty方法判断TRAVEL_REPORT_ALL_DAILY表是否为空,如果为空则执行syncFullData方法将T_CC_TRAIN表的全量数据插入到TRAVEL_REPORT_ALL_DAILY表中。如果不为空,则通过deleteOldData方法删除TRAVEL_REPORT_ALL_DAILY表中从当前时间往前13个月的数据,然后通过getMonthlyData方法从T_CC_TRAIN表中获取从当前时间往前13个月内的数据,并通过insertData方法将每个月的数据插入到TRAVEL_REPORT_ALL_DAILY表中。

请注意,代码中的数据库连接信息需要根据实际情况进行修改。另外,由于具体的数据表结构和插入操作未提供,需要根据实际情况进行实现

写一段java代码并完整注释需求如下:如果TRAVEL_REPORT_ALL_DAILY数据为空则同步T_CC_TRAIN全量数据否则先删除TRAVEL_REPORT_ALL_DAILY表中从当前时间往前13个月的数据从T_CC_TRAIN表中获取从当前时间往前13个月内的数据T_CC_TRAIN的创建时间字段为createtime并根据每个月的数据向TRAVEL_REPORT_ALL_DAILY

原文地址: http://www.cveoy.top/t/topic/hyUt 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录