java实现本地数据与阿里云MySQL数据同步：动态表创建与数据更新

在开发应用程序时，经常需要将数据从一个数据源（如API、外部数据库等）同步到本地数据库中。这可能涉及到不同的表结构和数据模式。
在这种情况下，一个主要的挑战是，如果本地数据库中的表结构与源数据不匹配，应该如何自动适应这些变化并确保数据同步的顺利进行。

解决方案：动态表创建与数据更新

为了实现数据同步，并根据需要创建表并更新数据，我们可以采用以下步骤：

1. 检查表是否存在： 在同步数据之前，首先检查目标表是否已存在。可以通过查询数据库的信息模式（INFORMATION_SCHEMA）来确定表是否存在。

2. 动态创建表： 如果目标表不存在，根据源数据的结构动态地创建一个匹配的表。需要解析源数据的结构，并在本地数据库中使用相应的列和数据类型来创建表。

3. 数据同步： 一旦表存在（无论是已经存在还是刚刚创建），将数据从源数据源同步到目标表中。通过适当的ETL（提取、转换、加载）过程来实现，根据数据的结构进行相应的转换和映射。

4. 数据更新： 在进行数据同步时，根据需求采取适当的更新策略。包括插入新数据、更新现有数据或删除不再存在于源数据中的数据。

5. 定期同步： 数据同步是一个动态的过程，因此最好设置定期的同步任务，以确保数据保持最新。可以使用定时任务、触发器或其他调度机制来实现定期同步。

 

代码实现：

　　1. DataSyncUtil 类

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DataSyncUtil {
    public static void main(String[] args) {
        // Local MySQL configuration
        String sourceUrl = "jdbc:mysql://localhost:3306/reggie?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true";
        String sourceUser = "root";
        String sourcePassword = "123456";


        // Aliyun MySQL configuration
        String targetUrl = "jdbc:mysql://localhost:3306/reggiet?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true";
        String targetUser = "root";
        String targetPassword = "123456";

        try (
                Connection sourceConnection = DriverManager.getConnection(sourceUrl, sourceUser, sourcePassword);
                Connection targetConnection = DriverManager.getConnection(targetUrl, targetUser, targetPassword)
        ) {
            // Get the list of table names in the source database
            List<String> tableNames = getTableNames(sourceConnection);

            // Iterate through each table and sync data
            for (String tableName : tableNames) {
                System.out.println("tableName:"+tableName);
                syncTableData(sourceConnection, targetConnection, tableName);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // Helper method to get a list of table names in the database
    public static List<String> getTableNames(Connection connection) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SHOW TABLES");

        while (resultSet.next()) {
            tableNames.add(resultSet.getString(1));
        }

        resultSet.close();
        statement.close();

        return tableNames;
    }

    public static void syncTableData(Connection sourceConnection, Connection targetConnection, String tableName) throws SQLException {
        Statement sourceStatement = sourceConnection.createStatement();
        Statement targetStatement = targetConnection.createStatement();

        ResultSet resultSet = null;
        ResultSetMetaData metaData = null;
        int columnCount = 0;
        try {
            String selectQuery = "SELECT * FROM " + tableName;
            resultSet = sourceStatement.executeQuery(selectQuery);
            metaData = resultSet.getMetaData();
            columnCount = metaData.getColumnCount();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // Re-check if the table exists after potential creation
        if (tableExists(targetConnection, tableName)) {

            while (resultSet.next()) {
                StringBuilder insertColumns = new StringBuilder();
                StringBuilder insertValues = new StringBuilder();

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = resultSet.getObject(i);

                    insertColumns.append(columnName);
                    insertValues.append(formatValue(columnValue));

                    if (i < columnCount) {
                        insertColumns.append(", ");
                        insertValues.append(", ");
                    }
                }

                // Check if the data already exists in the target table
                String primaryKeyColumnName = getPrimaryKeyColumnName(metaData, targetConnection);
                String primaryKeyValue = formatValue(resultSet.getObject(primaryKeyColumnName));
                if (!dataExistsInTarget(targetConnection, tableName, primaryKeyColumnName, primaryKeyValue)) {
                    String insertQuery = "INSERT INTO `" + tableName + "` (" + insertColumns + ") VALUES (" + insertValues + ")";
                    System.out.println("数据表内数据为空,进行插入同步:insertQuery:"+insertQuery);
                    targetStatement.executeUpdate(insertQuery);
                } else {
                    // Data already exists, handle update or skip logic here
                    System.out.println("数据存在,需要进行更新同步");
                    String updateQuery = "UPDATE `" + tableName + "` SET " + buildUpdateValues(metaData, resultSet) +
                            " WHERE " + primaryKeyColumnName + " = " + primaryKeyValue;
                    targetStatement.executeUpdate(updateQuery);
                    // Alternatively, you can skip the duplicate data
                }
            }
        } else {
            System.out.println("Table doesn't exist in the target, create it");
            createTable(targetConnection, tableName, metaData);
            System.out.println("Table creation failed for: " + tableName);
        }

        resultSet.close();
        sourceStatement.close();
        targetStatement.close();
    }

    private static String getPrimaryKeyColumnName(ResultSetMetaData metaData, Connection connection) throws SQLException {
        String tableName = metaData.getTableName(1); // Assuming the primary key is for the first column (you can adjust accordingly)
        DatabaseMetaData dbMetaData = connection.getMetaData();
        ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, null, tableName);

        while (primaryKeys.next()) {
            String columnName = primaryKeys.getString("COLUMN_NAME");
            return columnName;
        }

        throw new SQLException("Primary key not found for table: " + tableName);
    }

    private static String buildUpdateValues(ResultSetMetaData metaData, ResultSet resultSet) throws SQLException {
        StringBuilder updateValues = new StringBuilder();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            Object columnValue = resultSet.getObject(i);

            if (i > 1) {
                updateValues.append(", ");
            }
            updateValues.append(columnName).append(" = ").append(formatValue(columnValue));
        }

        return updateValues.toString();
    }

    private static boolean dataExistsInTarget(Connection connection, String tableName, String primaryKeyColumnName, String primaryKeyValue) throws SQLException {
        String query = "SELECT * FROM `" + tableName + "` WHERE `" + primaryKeyColumnName + "` = " + primaryKeyValue;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        boolean dataExists = resultSet.next();
        resultSet.close();
        statement.close();
        return dataExists;
    }

    // Helper method to check if a table exists in the target database
    private static boolean tableExists(Connection connection, String tableName) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            String query = "SELECT 1 FROM " + tableName + " LIMIT 1";
            statement.executeQuery(query);
            return true;
        } catch (SQLException e) {
            return false;
        }
    }


    // Helper method to create a table in the target database
    private static String getColumnAttributes(ResultSetMetaData metaData, int columnIndex, Connection connection) throws SQLException {
        StringBuilder attributes = new StringBuilder();

        // Check if the column is NOT NULL
        if (metaData.isNullable(columnIndex) == ResultSetMetaData.columnNoNulls) {
            attributes.append(" NOT NULL");
        }

        // Check if the column is part of the primary key
        if (isPartOfPrimaryKey(metaData, columnIndex, connection)) {
            attributes.append(" PRIMARY KEY");
        }

        // Check if the column is part of a unique constraint
        if (isPartOfUniqueConstraint(metaData, columnIndex, connection)) {
            attributes.append(" UNIQUE");
        }

        // You can add more column attributes here, like FOREIGN KEY constraints

        return attributes.toString();
    }

    private static void createTable(Connection connection, String tableName, ResultSetMetaData metaData) throws SQLException {
        StringBuilder createTableQuery = new StringBuilder("CREATE TABLE `" + tableName + "` (");
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            String columnType = metaData.getColumnTypeName(i);

            if (columnType.equalsIgnoreCase("CHAR") || columnType.equalsIgnoreCase("VARCHAR")) {
                int columnSize = metaData.getPrecision(i);
                columnType = columnType + "(" + columnSize + ")";
            } else if (columnType.equalsIgnoreCase("DECIMAL")) {
                int columnSize = metaData.getPrecision(i);
                int decimalDigits = metaData.getScale(i);
                columnType = columnType + "(" + columnSize + "," + decimalDigits + ")";
            } else if (columnType.equalsIgnoreCase("BIGINT")) {
                columnType = "BIGINT"; // Handle BIGINT data type
            }

            // Get column attributes like NULL/NOT NULL, primary key, etc.
            String columnAttributes = getColumnAttributes(metaData, i, connection);

            createTableQuery.append("`").append(columnName).append("` ").append(columnType).append(columnAttributes);

            if (i < columnCount) {
                createTableQuery.append(", ");
            }
        }

        createTableQuery.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
        System.out.println("建表语句:"+createTableQuery.toString());

        Statement statement = connection.createStatement();
        statement.executeUpdate(createTableQuery.toString());
        statement.close();
    }


    private static boolean isPartOfPrimaryKey(ResultSetMetaData metaData, int columnIndex, Connection connection) throws SQLException {
        DatabaseMetaData dbMetaData = connection.getMetaData();
        ResultSet primaryKeys = dbMetaData.getPrimaryKeys(null, null, metaData.getTableName(columnIndex));

        while (primaryKeys.next()) {
            String columnName = primaryKeys.getString("COLUMN_NAME");
            if (columnName.equalsIgnoreCase(metaData.getColumnName(columnIndex))) {
                return true; // Column is part of the primary key
            }
        }

        return false; // Column is not part of the primary key
    }


    private static boolean isPartOfUniqueConstraint(ResultSetMetaData metaData, int columnIndex, Connection connection) throws SQLException {
        DatabaseMetaData dbMetaData = connection.getMetaData();
        ResultSet indexes = dbMetaData.getIndexInfo(null, null, metaData.getTableName(columnIndex), false, true);

        while (indexes.next()) {
            String columnName = indexes.getString("COLUMN_NAME");
            if (columnName.equalsIgnoreCase(metaData.getColumnName(columnIndex))) {
                return true; // Column is part of a unique index
            }
        }

        return false; // Column is not part of a unique index
    }



    // Helper method to format column values for SQL query
    private static String formatValue(Object value) {
        if (value == null) {
            return "NULL";
        } else if (value instanceof Number) {
            return value.toString();
        } else if (value instanceof Boolean) {
            // Convert boolean to integer
            return ((Boolean) value) ? "1" : "0";
        } else {
            return "'" + value.toString() + "'";
        }
    }

}

　　2. DataSyncScheduler类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class DataSyncScheduler {
    public static void main(String[] args) {
        DataSyncUtil dataSyncUtil=new DataSyncUtil();

        // Local MySQL configuration
        String sourceUrl = "jdbc:mysql://localhost:3306/XX?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true";
        String sourceUser = "root";
        String sourcePassword = "XXX";

        // Aliyun MySQL configuration
        String targetUrl = "jdbc:mysql://localhost:3306/XXx?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true";
        String targetUser = "root";
        String targetPassword = "XXX";

        try (
                Connection sourceConnection = DriverManager.getConnection(sourceUrl, sourceUser, sourcePassword);
                Connection targetConnection = DriverManager.getConnection(targetUrl, targetUser, targetPassword)
        ) {
            // Get the list of table names in the source database

            List<String> tableNames = dataSyncUtil.getTableNames(sourceConnection);

            // Iterate through each table and sync data
            for (String tableName : tableNames) {
                System.out.println("tableName:"+tableName);
                dataSyncUtil.syncTableData(sourceConnection, targetConnection, tableName);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}