java postgres单体库迁移postgres集群库java

package com.slsl.digital.twin.manage.controller.project;

import com.google.common.collect.Lists;
import com.slsl.digital.twin.common.utils.CollectionUtils;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

public class Test {

    private static final String defaultProjectCode = "qd";
    private static final String defaultTenantCode = "dg";

    private static final String sourceDbUrl = "";
    private static final String sourceUser = "postgres";
    private static final String sourcePassword = "postgres";
    private static final String targetDbUrl = "";
    private static final String targetUser = "postgres";
    private static final String targetPassword = "123456";
    //指定固定的表同步
    private static final List<String> tables = Lists.newArrayList();

    public static void main(String[] args) {


        try (Connection sourceConnection = DriverManager.getConnection(sourceDbUrl, sourceUser, sourcePassword);
             Connection targetConnection = DriverManager.getConnection(targetDbUrl, targetUser, targetPassword)) {
            Set<String> sourceTables = fetchTables(sourceConnection);
            Set<String> targetTables = fetchTables(targetConnection); // 获取目标数据库的表
            sourceTables.retainAll(targetTables); // 只保留两个集合都有的表
            List<String> tablesInOrder;
            if (CollectionUtils.isNotEmpty(tables)) {
                tablesInOrder = tables;
            } else {
                Map<String, Set<String>> tableDependencies = fetchTableDependencies(sourceConnection, sourceTables);
                tablesInOrder = topologicalSort(tableDependencies, sourceTables);
            }

            for (String tableName : tablesInOrder) {
                migrateTable(sourceConnection, targetConnection, tableName);
            }
        } catch (SQLException e) {
            System.err.println("迁移过程中发生错误：" + e.getMessage());
            e.printStackTrace();
        }
    }

    private static List<String> topologicalSort(Map<String, Set<String>> dependencies, Set<String> filterTables) {
        List<String> orderedTables = new ArrayList<>();
        Set<String> visited = new HashSet<>();
        Set<String> tempMarks = new HashSet<>();

        for (String table : filterTables) { // 只考虑filterTables中的表
            if (dependencies.containsKey(table)) {
                visit(table, dependencies, visited, tempMarks, orderedTables, filterTables);
            }
        }
        Collections.reverse(orderedTables); // 反转列表以获得正确的顺序
        return orderedTables;
    }

    private static void visit(String table, Map<String, Set<String>> dependencies, Set<String> visited, Set<String> tempMarks, List<String> orderedTables, Set<String> filterTables) {
        if (!filterTables.contains(table)) {
            return; // 如果表不在过滤列表中，直接返回
        }
        if (tempMarks.contains(table)) {
            throw new RuntimeException("发现循环依赖：" + table);
        }
        if (!visited.contains(table)) {
            tempMarks.add(table);
            for (String dependentTable : dependencies.getOrDefault(table, Collections.emptySet())) {
                visit(dependentTable, dependencies, visited, tempMarks, orderedTables, filterTables);
            }
            tempMarks.remove(table);
            visited.add(table);
            orderedTables.add(table);
        }
    }

    private static Map<String, Integer> getColumnDataTypes(Connection connection, String tableName) throws SQLException {
        Map<String, Integer> columnDataTypes = new HashMap<>();

        // 获取数据库元数据
        DatabaseMetaData metaData = connection.getMetaData();

        // 查询指定表的列信息。参数说明：
        // catalog - 数据库名，可以为 null 表示不限定数据库名
        // schemaPattern - 数据库模式名模式，可以为 null 表示不限定模式名
        // tableNamePattern - 表名模式，这里使用表名获取该表的列信息
        // columnNamePattern - 列名模式，设置为 null 表示获取所有列
        try (ResultSet resultSet = metaData.getColumns(null, null, tableName, null)) {
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                int dataType = resultSet.getInt("DATA_TYPE");

                // 将列名和对应的数据类型存储在映射中
                columnDataTypes.put(columnName, dataType);
            }
        }

        return columnDataTypes;
    }

    // 实际迁移表的方法，您需要在这里插入之前的迁移逻辑
    private static void migrateTable(Connection sourceConnection, Connection targetConnection, String tableName) throws SQLException {
        System.out.println("准备迁移表：" + tableName);

        // 获取源表和目标表的列及其数据类型
        Map<String, Integer> sourceColumnDataTypes = getColumnDataTypes(sourceConnection, tableName);
        Set<String> targetTableColumns = getTargetTableColumns(targetConnection, tableName).stream().map(String::toLowerCase).collect(Collectors.toSet());

        // 检查目标表是否包含特定的字段
        boolean hasProjectCode = targetTableColumns.contains("project_code");
        boolean hasTenantCode = targetTableColumns.contains("tenant_code");

        // 构建SQL语句的列名部分和参数占位符部分
        StringBuilder columnNames = new StringBuilder();
        StringBuilder questionMarks = new StringBuilder();
        List<String> columnList = new ArrayList<>();
        boolean isFirstColumn = true;
        for (Map.Entry<String, Integer> entry : sourceColumnDataTypes.entrySet()) {
            String columnName = entry.getKey();
            // 转换为小写进行比较，确保大小写不敏感性
            if (!targetTableColumns.contains(columnName.toLowerCase())) {
                continue; // 如果目标表中不存在此列，则忽略
            }
            if (columnName.equalsIgnoreCase("project_code") && !hasProjectCode) {
                continue; // 如果目标表中不存在project_code列，则跳过
            }
            if (columnName.equalsIgnoreCase("tenant_code") && !hasTenantCode) {
                continue; // 如果目标表中不存在tenant_code列，则跳过
            }
            if (isFirstColumn) {
                isFirstColumn = false;
            } else {
                columnNames.append(", ");
                questionMarks.append(", ");
            }
            columnNames.append(columnName);
            questionMarks.append("?");
            columnList.add(columnName);
        }
        // 检查并为 project_code 和 tenant_code 添加默认值处理

        if (targetTableColumns.contains("project_code") && !columnList.contains("project_code")) {
            columnNames.append(", project_code");
            questionMarks.append(", ?");
            columnList.add("project_code");
        }
        if (targetTableColumns.contains("tenant_code") && !columnList.contains("tenant_code")) {
            columnNames.append(", tenant_code");
            questionMarks.append(", ?");
            columnList.add("tenant_code");
        }

        String selectSql = "SELECT " + String.join(", ", columnList) + " FROM " + tableName + (sourceColumnDataTypes.containsKey("project_code") ? "" : ", CAST(NULL AS VARCHAR) AS project_code") + (sourceColumnDataTypes.containsKey("tenant_code") ? "" : ", CAST(NULL AS VARCHAR) AS tenant_code");
        System.out.println("执行SQL查询: " + selectSql);

        String insertSql = "INSERT INTO " + tableName + " (" + columnNames + ") VALUES (" + questionMarks + ") ON CONFLICT DO NOTHING";
        System.out.println("准备执行插入: " + insertSql);

        try (PreparedStatement targetStatement = targetConnection.prepareStatement(insertSql);
             Statement sourceStatement = sourceConnection.createStatement();
             ResultSet rs = sourceStatement.executeQuery(selectSql)) {
            targetConnection.setAutoCommit(false);
            while (rs.next()) {
                int columnIndex = 1;
                for (String columnName : columnList) {
                    // 特殊处理 project_code 和 tenant_code
                    if ("project_code".equals(columnName)) {
                        String projectCodeValue = rs.getString(columnName);
                        if (projectCodeValue == null) {
                            projectCodeValue = defaultProjectCode;
                        }
                        targetStatement.setString(columnIndex, projectCodeValue);
                    } else if ("tenant_code".equals(columnName)) {
                        String tenantCodeValue = rs.getString(columnName);
                        if (tenantCodeValue == null) {
                            tenantCodeValue = defaultTenantCode;
                        }
                        targetStatement.setString(columnIndex, tenantCodeValue);
                    } else {
                        // 处理其他列
                        Integer dataType = sourceColumnDataTypes.get(columnName);
                        switch (dataType) {
                            case Types.BIGINT:
                                targetStatement.setLong(columnIndex, rs.getLong(columnName));
                                break;
                            case Types.NUMERIC:
                            case Types.DECIMAL:
                                targetStatement.setBigDecimal(columnIndex, rs.getBigDecimal(columnName));
                                break;
                            case Types.BOOLEAN:
                                targetStatement.setBoolean(columnIndex, rs.getBoolean(columnName));
                                break;
                            case Types.VARCHAR:
                                targetStatement.setString(columnIndex, rs.getString(columnName));
                                break;
                            // 根据需要处理其他数据类型
                            default:
                                targetStatement.setObject(columnIndex, rs.getObject(columnName));
                                break;
                        }
                    }
                    columnIndex++;
                }
                targetStatement.addBatch();
            }
            targetStatement.executeBatch();
            targetConnection.commit();
            System.out.println("表 " + tableName + " 迁移完成。");
        } catch (SQLException e) {
            targetConnection.rollback();
            System.err.println("在迁移表 " + tableName + " 时发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static Set<String> fetchTables(Connection connection) throws SQLException {
        Set<String> tables = new HashSet<>();
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME").toLowerCase());
            }
        }
        return tables;
    }

    private static Set<String> getTargetTableColumns(Connection connection, String tableName) throws SQLException {
        Set<String> columns = new HashSet<>();
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet resultSet = metaData.getColumns(null, null, tableName, null)) {
            while (resultSet.next()) {
                columns.add(resultSet.getString("COLUMN_NAME").toLowerCase());
            }
        }
        return columns;
    }

    private static Map<String, Set<String>> fetchTableDependencies(Connection connection, Set<String> filterTables) throws SQLException {
        Map<String, Set<String>> dependencies = new HashMap<>();
        DatabaseMetaData metaData = connection.getMetaData();
        for (String tableName : filterTables) {
            dependencies.putIfAbsent(tableName, new HashSet<>());

            try (ResultSet foreignKeys = metaData.getImportedKeys(null, null, tableName)) {
                while (foreignKeys.next()) {
                    String fkTableName = foreignKeys.getString("PKTABLE_NAME").toLowerCase();
                    if (filterTables.contains(fkTableName)) { // 确保外键表也在过滤列表中
                        dependencies.get(tableName).add(fkTableName);
                    }
                }
            }
        }
        return dependencies;
    }
}