位置：首页-资讯-数据库

java实现mysql两个数据库对比表结构是否一致

2023-09-11 06:45

短信预约 -IT技能 免费直播动态提醒

【使用场景】

本地开发完后，增加或者删除了字段，或者修改了字段属性。

如何判定现场环境和本地环境的数据表结构一致性？肉眼看的话，实在是一个大的工作量，所以开发了此工具。

【类存放路径】

CompareDbMain是主函数。

【具体代码】

public class ColumnEntity {    private String tableName;    private String columnName;    private String columnTypeName;    private Integer columnDisplaySize;    private Integer scale;    public String getTableName() {        return tableName;    }    public void setTableName(String tableName) {        this.tableName = tableName;    }    public String getColumnName() {        return columnName;    }    public void setColumnName(String columnName) {        this.columnName = columnName;    }    public String getColumnTypeName() {        return columnTypeName;    }    public void setColumnTypeName(String columnTypeName) {        this.columnTypeName = columnTypeName;    }    public Integer getColumnDisplaySize() {        return columnDisplaySize;    }    public void setColumnDisplaySize(Integer columnDisplaySize) {        this.columnDisplaySize = columnDisplaySize;    }    public Integer getScale() {        return scale;    }    public void setScale(Integer scale) {        this.scale = scale;    }}

public class TableEntity {    private String tableName; //表名    private String tableType; //表类型    private String tableCat; //表所属数据库    private String tableSchem; //表所属用户名    private String remarks; //表备注    public String getTableName() {        return tableName;    }    public void setTableName(String tableName) {        this.tableName = tableName;    }    public String getTableType() {        return tableType;    }    public void setTableType(String tableType) {        this.tableType = tableType;    }    public String getTableCat() {        return tableCat;    }    public void setTableCat(String tableCat) {        this.tableCat = tableCat;    }    public String getTableSchem() {        return tableSchem;    }    public void setTableSchem(String tableSchem) {        this.tableSchem = tableSchem;    }    public String getRemarks() {        return remarks;    }    public void setRemarks(String remarks) {        this.remarks = remarks;    }}

import java.sql.*;public class MysqlDBTools {    static Connection connection = null;    static PreparedStatement preparedStatement = null;    static ResultSet resultSet = null;    static String DB_URL = "";    static String DB_USER = "";    static String DB_PWD = "";    public MysqlDBTools(String dbUrl, String dbUser, String dbPwd){        this.DB_URL = dbUrl;        this.DB_USER = dbUser;        this.DB_PWD = dbPwd;    }    public static Connection getConnection(){        try{            Class.forName("com.mysql.cj.jdbc.Driver");            connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);        }catch (Exception e){            System.out.println("连接失败！");            e.printStackTrace();        }        return connection;    }    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){        if(resultSet != null){            try {                resultSet.close();            } catch (SQLException throwables) {                throwables.printStackTrace();            }        }        if(preparedStatement != null){            try {                preparedStatement.close();            } catch (SQLException throwables) {                throwables.printStackTrace();            }        }        if(connection != null){            try {                connection.close();            } catch (SQLException throwables) {                throwables.printStackTrace();            }        }    }}

import java.sql.*;import java.util.*;public class CompareDbMain {    private static String db_url1 = "jdbc:mysql://xx.xx.xx.xx:3306/udesign?useUnicode=true&characterEncoding=utf8&useSSL=false";    private static String db_user1 = "root";    private static String db_pwd1 = "xxx";    private static String db_url2 = "jdbc:mysql://xx.xx.xx.xx:3306/udesign-v2?useUnicode=true&characterEncoding=utf8&useSSL=false";    private static String db_user2 = "root";    private static String db_pwd2 = "xxx";    public static void main(String[] args) throws Exception{        System.out.println("[结果说明]");        System.out.println("1. ++表示A比B多，--表示A比B少，**表示有变化");        System.out.println("");        Connection conn1 = new MysqlDBTools(db_url1, db_user1, db_pwd1).getConnection();        Connection conn2 = new MysqlDBTools(db_url2, db_user2, db_pwd2).getConnection();        //对比表信息        List tables1 = getTables(conn1);        List tables2 = getTables(conn2);        StringBuffer tableRes = compareTable(tables1, tables2);        System.out.println("[表对比结果]");        System.out.println(conn1.getCatalog() + " vs " + conn2.getCatalog());        System.out.println(tableRes);        StringBuffer columnRes = compareColumn(conn1, conn2, tables1, tables2);        System.out.println("[表字段对比结果]");        System.out.println(columnRes);    }    public static StringBuffer compareColumn(Connection conn1, Connection conn2, List tables1, List table2){        StringBuffer sb = new StringBuffer();        for(TableEntity t1: tables1){            if(tableContains(table2, t1)){                sb.append("["+ t1.getTableName() +"]"  + "\r\n");                List columnEntities1 = getColumns(conn1, t1);                List columnEntities2 = getColumns(conn2, t1);                for(ColumnEntity c1: columnEntities1){                    if(columnContains(columnEntities2, c1)){                        ColumnEntity c2 = getColumnFromList(columnEntities2, c1);                        if(!c2.getColumnDisplaySize().equals(c1.getColumnDisplaySize())                        || !c2.getColumnTypeName().equals(c1.getColumnTypeName())                        || !c2.getScale().equals(c1.getScale())){sb.append("  **" + c2.getColumnName() + "\r\n");                        }                        //System.out.println("对比字段属性");                        continue;                    }                }                for(ColumnEntity c2: columnEntities2){                    if(!columnContains(columnEntities1, c2)){                        sb.append("  --" + c2.getColumnName() + "\r\n");                        continue;                    }                }                for(ColumnEntity c1: columnEntities1){                    if(!columnContains(columnEntities2, c1)){                        sb.append("  ++" + c1.getColumnName() + "\r\n");                        continue;                    }                }            }        }        return sb;    }    //对比表信息    public static StringBuffer compareTable(List tab1, List tab2){        StringBuffer sb = new StringBuffer();        for(TableEntity t1: tab1){            if(tableContains(tab2, t1)){                sb.append("    " + t1.getTableName() + "\r\n");                continue;            }        }        for(TableEntity t2: tab2){            if(!tableContains(tab1, t2)){                sb.append("  --" + t2.getTableName() + "\r\n");                continue;            }        }        for(TableEntity t1: tab1){            if(!tableContains(tab2, t1)){                sb.append("  ++" + t1.getTableName() + "\r\n");                continue;            }        }        return sb;    }    //获取字段信息    public static List getColumns(Connection conn, TableEntity table){        List columnEntities = new LinkedList<>();        String sql = "select * from " + table.getTableName();        try {            PreparedStatement ps = conn.prepareStatement(sql);            ResultSet rs = ps.executeQuery();            ResultSetMetaData meta = rs.getMetaData();            int columnCount = meta.getColumnCount();            for (int i = 1; i < columnCount + 1; i++) {                ColumnEntity columnEntity = new ColumnEntity();                columnEntity.setColumnName(meta.getColumnName(i).toLowerCase());                columnEntity.setColumnTypeName( meta.getColumnTypeName(i).toLowerCase());                columnEntity.setColumnDisplaySize(meta.getColumnDisplaySize(i));                columnEntity.setScale(meta.getScale(i));                columnEntity.setTableName(meta.getTableName(i).toLowerCase());                columnEntities.add(columnEntity);            }        } catch (SQLException e) {            e.printStackTrace();        }        return columnEntities;    }    //获取表信息    public static List getTables(Connection conn){        List tableEntities = new LinkedList<>();        try {            DatabaseMetaData dbMetaData = conn.getMetaData();            ResultSet rs = dbMetaData.getTables(conn.getCatalog(), null, null,new String[] { "TABLE" });            while (rs.next()) {// ///TABLE_TYPE/REMARKS                TableEntity table = new TableEntity();                table.setTableName(rs.getString("TABLE_NAME").toLowerCase());                table.setTableType(rs.getString("TABLE_TYPE").toLowerCase());                table.setTableCat(rs.getString("TABLE_CAT").toLowerCase());                table.setTableSchem(rs.getString("TABLE_SCHEM")==null? "": rs.getString("TABLE_SCHEM").toLowerCase());                table.setRemarks(rs.getString("REMARKS").toLowerCase());                tableEntities.add(table);            }        } catch (SQLException e) {            e.printStackTrace();        }        return tableEntities;    }    //从list里面获取    public static ColumnEntity getColumnFromList(List columnEntities, ColumnEntity column){        for(ColumnEntity c: columnEntities){            if(c.getColumnName().equals(column.getColumnName())                    && c.getTableName().equals(column.getTableName())){                return c;            }        }        return null;    }    public static boolean tableContains(List tableEntities, TableEntity table){        for(TableEntity tab: tableEntities){            if(tab.getTableName().equals(table.getTableName())){                return true;            }        }        return  false;    }    public static boolean columnContains(List columnEntities, ColumnEntity column){        for(ColumnEntity tab: columnEntities){            if(tab.getColumnName().equals(column.getColumnName())                    && tab.getTableName().equals(column.getTableName())){                return true;            }        }        return false;    }}

结果展示：

[结果说明]
1. ++表示A比B多，--表示A比B少，**表示有变化

[表对比结果]
udesign vs udesign-v2
b_busi_type
b_busi_type_l2
c_config_version
c_datasource_attr
c_datasource_attr_set
c_datasource_change_plan
c_datasource_corba
c_datasource_ftp
c_datasource_info
c_datasource_jdbc
c_datasource_kafka
c_datasource_pipe
c_datasource_sdtp
c_datasource_snmp
c_datasource_socket
c_datasource_subscribe
c_dict_data_version
c_dict_devicetype
c_dict_net_type
c_dict_protocol
c_dict_region
c_dict_specility
c_dict_specility_level
c_dict_vendor
c_image
c_image_env
c_kafka_send_log
c_omc_device_type
c_omc_info
c_omc_ne
c_omc_net_type
c_redis_monitor
c_specility_topic
collect_schedule_file_log
collect_schedule_ftp_log
collect_schedule_ftp_log_d
collect_schedule_ftp_wait
collect_schedule_time_log
collect_stage
collect_stage_log
collect_stage_rel
collect_task
collect_task_log
group_info
pars_data_class
s_protocoltype
sys_dict
sys_dict_item
ue_component
ue_component_class
ue_dir
ue_dir_type
ue_etl_task_publish
ue_stage
ue_stage_meta_rel
ue_task
ue_task_publish
ue_template
ue_template_stage
ue_workspace
++kafka

[表字段对比结果]
[b_busi_type]
[b_busi_type_l2]
[c_config_version]
[c_datasource_attr]
[c_datasource_attr_set]
[c_datasource_change_plan]
[c_datasource_corba]
[c_datasource_ftp]
[c_datasource_info]
[c_datasource_jdbc]
[c_datasource_kafka]
[c_datasource_pipe]
[c_datasource_sdtp]
[c_datasource_snmp]
[c_datasource_socket]
[c_datasource_subscribe]
[c_dict_data_version]
[c_dict_devicetype]
[c_dict_net_type]
[c_dict_protocol]
[c_dict_region]
**county_name
[c_dict_specility]
[c_dict_specility_level]
[c_dict_vendor]
[c_image]
[c_image_env]
[c_kafka_send_log]
[c_omc_device_type]
[c_omc_info]
[c_omc_ne]
[c_omc_net_type]
[c_redis_monitor]
[c_specility_topic]
[collect_schedule_file_log]
[collect_schedule_ftp_log]
[collect_schedule_ftp_log_d]
[collect_schedule_ftp_wait]
[collect_schedule_time_log]
[collect_stage]
[collect_stage_log]
[collect_stage_rel]
[collect_task]
++group_names
[collect_task_log]
[group_info]
[pars_data_class]
[s_protocoltype]
[sys_dict]
[sys_dict_item]
[ue_component]
[ue_component_class]
[ue_dir]
[ue_dir_type]
[ue_etl_task_publish]
[ue_stage]
[ue_stage_meta_rel]
[ue_task]
[ue_task_publish]
--group_names
[ue_template]
[ue_template_stage]
[ue_workspace]