数据库对比脚本，java如何对比两个数据库的表字段的不同

因为有时候开发环境和 测试环境，有时候会有不同的数据库表，比如有些加字段了，所以这个脚本就实现了对比两个数据库连接的数据库到底哪里不一样，输出到控制台

package com.junfun.pms;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.*;
import java.util.*;

@Slf4j
public class CompareDB {

    public static void main(String[] args) throws SQLException {
        CompareDB.extracted();
    }

    private static void extracted() throws SQLException {

        String url1 = "jdbc:mysql://127.0.0.1:3306/ccgdc-pms";  // 客户展示数据库
        String user1 = "root";
        String password1 = "123456";
        String driverClass1 = "com.mysql.cj.jdbc.Driver";
        Connection conn1 = getConnection(url1, user1, password1, driverClass1);

        String url2 = "jdbc:mysql://192.168.0.66:3306/ccgdc-pms";      // 开发数据库
        String user2 = "root";
        String password2 = "Junfun1907!*%$slowkf";
        String driverClass2 = "com.mysql.cj.jdbc.Driver";
        Connection conn2 = getConnection(url2, user2, password2, driverClass2);

        compareTables(conn1, conn2);

        conn1.close();
        conn2.close();
    }

    private static Connection getConnection(String url, String user, String password, String driverClass) throws SQLException {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return DriverManager.getConnection(url, user, password);
    }

    private static void compareTables(Connection conn1, Connection conn2) throws SQLException {
        DatabaseMetaData meta1 = conn1.getMetaData();
        DatabaseMetaData meta2 = conn2.getMetaData();

        // 指定需要对比的数据库名称
        String specifiedDatabaseName = "ccgdc-pms";

        ResultSet tables1 = meta1.getTables(null, null, null, new String[]{"TABLE"});
        while (tables1.next()) {
            String tableName = tables1.getString("TABLE_NAME");

            // 获取表所属的数据库名称
            String databaseName = tables1.getString("TABLE_CAT");

            if (specifiedDatabaseName.equals(databaseName)) {

                List<String> columns1 = getColumnsForTable(meta1, tableName);
                List<String> columns2 = getColumnsForTable(meta2, tableName);

                if (!columns1.equals(columns2)) {
                    log.info("\u001B[31m表 " + tableName + " 有不同的字段:\u001B[0m");
                    log.info("\u001B[34mDatabase 1 columns: " + columns1 + "\u001B[0m");
                    log.info("\u001B[35mDatabase 2 columns: " + columns2 + "\u001B[0m");


                    List<String> extraColumns = new ArrayList<>(columns2);
                    extraColumns.removeAll(columns1);
                    if (!extraColumns.isEmpty()) {
//                    log.info("\u001B[31m多出的字段: " + extraColumns + "\u001B[0m");
                    }

                } else {
//                log.info("表 " + tableName + " 字段一致: " + columns1);
                }
            }
        }
    }

    private static List<String> getColumnsForTable(DatabaseMetaData meta, String tableName) throws SQLException {
        Set<String> columns = new LinkedHashSet<>();
        ResultSet columnsResult = meta.getColumns(null, null, tableName, null);
        while (columnsResult.next()) {
            String columnName = columnsResult.getString("COLUMN_NAME");
            columns.add(columnName);
        }
        //将columns 转成list
        List<String> columnsList = new ArrayList<>(columns);
        return columnsList;
    }
}

输出后的情况