java对比两个库之间差异

package com.ruoyi.shht;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

/**
 * @Title: CompareUtil
 * @Author cxf
 * @Package java.com.ruoyi.shht
 * @Date 2024/7/19 11:31
 * @description: 对比数据库
 */
public class CompareUtils {

    static String driver = "com.mysql.cj.jdbc.Driver";
    static String url = "jdbc:mysql://HOST:3306/DATABASE?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true";

    static class Config {
        String host;
        String userName;
        String password;
        String dbSchema;

        public Config(String host, String userName, String password, String dbSchema) {
            this.host = host;
            this.userName = userName;
            this.password = password;
            this.dbSchema = dbSchema;
        }
    }


    static class Table {
        //表名称
        public String tableName;
        //字段名称+字段类型
        public HashMap<String,String> column;
        //字段名称+字段注解
        public HashMap<String,String> columnComment;

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public HashMap<String, String> getColumn() {
            return column;
        }

        public void setColumn(HashMap<String, String> column) {
            this.column = column;
        }

        public HashMap<String, String> getColumnComment() {
            return columnComment;
        }

        public void setColumnComment(HashMap<String, String> columnComment) {
            this.columnComment = columnComment;
        }
    }

    private static Config getConfig(String name){
        Map<String, Config> map = new HashMap<>();
        
        Config local = new Config("localhost", "root", "mysql","cxcmp");
        Config local_test = new Config("localhost", "root", "mysql","cxcmp_test");
        
        map.put("local",local);
        map.put("local_test",local_test);
        
        return map.get(name);
    }

    public static void main(String[] args) throws Exception {
        compareTables(getConfig("local"),getConfig("local_test"));
        writeFile();
    }

    public static Connection getTransaction(Config config) throws Exception {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(
                url.replace("HOST", config.host).replace("DATABASE", config.dbSchema), config.userName, config.password);
        if (conn != null)System.out.println("主机：" + config.host + " 数据库加载成功！");
        return conn;
    }

    public static StringBuffer[] sb = {new StringBuffer(), new StringBuffer(), new StringBuffer(),
            new StringBuffer(), new StringBuffer(), new StringBuffer(),new StringBuffer()};

    /**
     * 初始化 比对文件提示信息
     */
    public static void initSbString(Config c1, Config c2){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sb[0].append("对比时间：").append(sdf.format(new Date())).append(" 对比库：")
                .append(c1.host).append(":").append(c1.dbSchema).append(" -> ").append(c2.host).append(":").append(c2.dbSchema)
                .append("\r\n");
        sb[1].append("1、").append(c1.host).append("存在，").append(c2.host).append("不存在的表：\r\n");
        sb[2].append("2、").append(c1.host).append("不存在，").append(c2.host).append("存在的表：\r\n");
        sb[3].append("3、").append(c1.host).append("存在，").append(c2.host).append("不存在的字段：\r\n");
        sb[4].append("4、").append(c1.host).append("不存在，").append(c2.host).append("存在的字段：\r\n");
        sb[5].append("5、表和字段都相同，但字段类型不同的内容：\r\n");
        sb[6].append("6、表和字段、字段类型都相同，但字段注解不同的内容：\r\n");
    }


    /**
     * 比对两个库的数据表，包括表名、字段名、字段类型、字段注解
     * @param c1
     * @param c2
     * @throws Exception
     */
    public static void compareTables(Config c1, Config c2) throws Exception {

        // 初始化SubString[]
        initSbString(c1,c2);

        // 生产数据库连接
        Connection connection_product = getTransaction(c1);
        Map<String, Table> map_product = getTables(connection_product,c1.dbSchema);

        // 开发数据库连接
        Connection connection_develop = getTransaction(c2);
        Map<String, Table> map_develop = getTables(connection_develop,c2.dbSchema);

        System.out.println("START----开始比对"+ c1.host +" 库");
        // 遍历开发库Map
        for (Iterator<String> iter_table = map_develop.keySet().iterator(); iter_table
                .hasNext();) {
            String key_table = (String) iter_table.next();
            Table table_develop = map_develop.get(key_table);// 获得开发库中的表
            Table table_product = map_product.get(key_table);// 尝试从生产库中获得同名表
            if (table_product == null) { // 如果获得表为空，说明开发存在，生产不存在
                append(table_develop, null, 2);
            } else { // 表相同，判断字段、字段类型、字段注解
                for (Iterator<String> column_develop = table_develop.column.keySet().iterator(); column_develop
                        .hasNext();) {
                    String key_column = (String) column_develop.next();
                    String value_develop = table_develop.column.get(key_column);// 获得开发库中的列
                    String value_column = table_product.column.get(key_column);// 尝试从生产库中获得同名列
                    if (value_column == null) {// 如果列名为空，说明开发存在，生产不存在
                        append(table_develop, key_column, 4);
                    } else {// 说明两者都存在
                        if (!value_column.equals(value_develop))// 字段类型不一致
                            append(table_develop, key_column, 5);

                        String comment_product = table_product.columnComment.get(key_column);// 获得生产库中的字段注解
                        String develop_product = table_develop.columnComment.get(key_column);// 尝试从开发库中获得同名字段注解
                        if (!comment_product.equals(develop_product))// 字段注解不一致
                            append(table_develop, key_column, 6);
                    }
                }
            }
        }
        System.out.println("END----结束比对"+ c1.host +"库");

        System.out.println("START----开始比对"+ c2.host +"库");
        // 遍历生产库Map
        for (Iterator<String> iter_table = map_product.keySet().iterator(); iter_table
                .hasNext();) {
            String key_table = (String) iter_table.next();
            Table table_product = map_product.get(key_table);// 从生产库中获得同名表
            Table table_develop = map_develop.get(key_table);// 尝试获得开发库中的表
            if (table_develop == null) { // 如果获得表为空，说明生产存在，开发不存在
                append(table_product, null, 1);
            } else { // 表相同，判断字段、字段类型、字段注解
                for (Iterator<String> column_product = table_product.getColumn().keySet().iterator(); column_product
                        .hasNext();) {
                    String key_column = (String) column_product.next();
                    String value_column = table_product.column.get(key_column);// 获得生产库中的列
                    String value_develop = table_develop.column.get(key_column);// 尝试从开发库中获得同名列
                    if (value_develop == null) {// 如果列名为空，说明生产存在，开发不存在
                        append(table_develop, key_column, 3);
                    }

                    // 字段相等 类型或者注解不相等上一步已经比对过，这里无需重复比对。
                }
            }
        }
        System.out.println("END----结束比对"+ c2.host +"库");
    }

    /**
     * 封装TABLE数据
     * @param connection
     * @param tableSchema
     * @return
     * @throws Exception
     */
    public static Map<String, Table> getTables(Connection connection, String tableSchema)
            throws Exception {

        String sSql = "SELECT \n" +
                "  table_name AS tableName,\n" +
                "  GROUP_CONCAT(column_name) AS columnName,\n" +
                "  GROUP_CONCAT(column_type) AS columnType,\n" +
                "  GROUP_CONCAT(IF(column_comment=\"\",\"无\",column_comment)) AS columnComment\n" +
                "FROM\n" +
                "  information_schema.`COLUMNS` \n" +
                "WHERE table_schema = '"+tableSchema+"' \n" +
                "GROUP BY tableName\n" +
                "ORDER BY tableName;";

        Statement statement = connection.createStatement();//创建Statement对象
        ResultSet rs = statement.executeQuery(sSql);

        System.out.println("sql: "+sSql);
        Map<String, Table> map = new HashMap<String, Table>();
        while (rs.next()) {
            Table table = new Table();
            HashMap<String,String> columnMap = new HashMap<>();
            HashMap<String,String> columnCommentMap = new HashMap<>();

            table.setTableName(rs.getString("tableName"));
            if("cardinfo".equals(table.getTableName())){
                // 表太大，跳过
                continue;
            }
            //System.out.println("tableName: "+table.getTableName());
            String columnName = rs.getString("columnName");
            String columnType = rs.getString("columnType");
            String columnComment = rs.getString("columnComment");

            String columnName_ [] = columnName.split(",");
            String columnType_ [] = columnType.split(",");
            String columnComment_ [] = columnComment.split(",");
            for(int i=0;i<columnName_.length;i++){
                columnMap.put(columnName_[i],columnType_[i]);
                columnCommentMap.put(columnName_[i],columnComment_[i]);
            }
            table.setColumn(columnMap);
            table.setColumnComment(columnCommentMap);
            map.put(rs.getString("tableName"), table);
        }
        if (rs!=null)
            rs.close();
        connection.close();
        return map;
    }

    /**
     * 封装提示信息 （追加到满足条件的StringBuffer）
     * @param table
     * @param column
     * @param flag
     * @throws Exception
     */
    public static void append(Table table, String column, int flag)
            throws Exception {
        switch (flag) {
            case 1:
                sb[1].append(table.getTableName() + "\r\n");
                break;
            case 2:
                sb[2].append(table.getTableName() + "\r\n");
                break;
            case 3:
                sb[3].append(table.getTableName() + "["+ column+"]\r\n");
                break;
            case 4:
                sb[4].append(table.getTableName() + "["+ column+"]\r\n");
                break;
            case 5:
                sb[5].append(table.getTableName() + "["+ column+"]["+table.column.get(column)+"]\r\n");
                break;
            case 6:
                sb[6].append(table.getTableName() + "["+ column+"]["+table.columnComment.get(column)+"]\r\n");
                break;
        }
    }

    /**
     * 写出比对结果到文件
     * 将StringBuffer中的值写入文件中
     * @throws Exception
     */
    public static void writeFile() throws Exception {
        // 合并输出到txt
        StringBuffer rs = new StringBuffer();
        rs.append(sb[0]+ "\r\n\r\n").append(sb[1]+ "\r\n\r\n").append(sb[2]+ "\r\n\r\n").append(sb[3]+ "\r\n\r\n")
                .append(sb[4]+ "\r\n\r\n").append(sb[5]+ "\r\n\r\n").append(sb[6]+ "\r\n\r\n");

        File file = new File("C:\\Users\\27183\\Desktop\\数据库对比结果.txt");
        OutputStream os = new FileOutputStream(file);
        os.write(rs.toString().getBytes());
        os.flush();
        os.close();
    }

}

对比结果将输出到.txt文件中