devbox
木道寻08
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

java实现mysql两个数据库对比表结构是否一致_mybaties连接两个库比较表结构是否发生变化

作者：木道寻08 | 2024-08-10 07:50:53

赞

踩

mybaties连接两个库比较表结构是否发生变化

【使用场景】

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

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

【类存放路径】

CompareDbMain是主函数。

【具体代码】

  1. public class ColumnEntity {
  2.     private String tableName;
  3.     private String columnName;
  4.     private String columnTypeName;
  5.     private Integer columnDisplaySize;
  6.     private Integer scale;
  7.  
  8.     public String getTableName() {
  9.         return tableName;
  10.     }
  11.  
  12.     public void setTableName(String tableName) {
  13.         this.tableName = tableName;
  14.     }
  15.  
  16.     public String getColumnName() {
  17.         return columnName;
  18.     }
  19.  
  20.     public void setColumnName(String columnName) {
  21.         this.columnName = columnName;
  22.     }
  23.  
  24.     public String getColumnTypeName() {
  25.         return columnTypeName;
  26.     }
  27.  
  28.     public void setColumnTypeName(String columnTypeName) {
  29.         this.columnTypeName = columnTypeName;
  30.     }
  31.  
  32.     public Integer getColumnDisplaySize() {
  33.         return columnDisplaySize;
  34.     }
  35.  
  36.     public void setColumnDisplaySize(Integer columnDisplaySize) {
  37.         this.columnDisplaySize = columnDisplaySize;
  38.     }
  39.  
  40.     public Integer getScale() {
  41.         return scale;
  42.     }
  43.  
  44.     public void setScale(Integer scale) {
  45.         this.scale = scale;
  46.     }
  47. }
  1. public class TableEntity {
  2.     private String tableName; //表名
  3.     private String tableType; //表类型
  4.     private String tableCat; //表所属数据库
  5.     private String tableSchem; //表所属用户名
  6.     private String remarks; //表备注
  7.  
  8.     public String getTableName() {
  9.         return tableName;
  10.     }
  11.  
  12.     public void setTableName(String tableName) {
  13.         this.tableName = tableName;
  14.     }
  15.  
  16.     public String getTableType() {
  17.         return tableType;
  18.     }
  19.  
  20.     public void setTableType(String tableType) {
  21.         this.tableType = tableType;
  22.     }
  23.  
  24.     public String getTableCat() {
  25.         return tableCat;
  26.     }
  27.  
  28.     public void setTableCat(String tableCat) {
  29.         this.tableCat = tableCat;
  30.     }
  31.  
  32.     public String getTableSchem() {
  33.         return tableSchem;
  34.     }
  35.  
  36.     public void setTableSchem(String tableSchem) {
  37.         this.tableSchem = tableSchem;
  38.     }
  39.  
  40.     public String getRemarks() {
  41.         return remarks;
  42.     }
  43.  
  44.     public void setRemarks(String remarks) {
  45.         this.remarks = remarks;
  46.     }
  47. }
  1. import java.sql.*;
  2.  
  3. /**
  4.  * JDBC工具类
  5.  * 包含数据库连接及关闭数据库资源
  6.  */
  7. public class MysqlDBTools {
  8.     static Connection connection = null;
  9.     static PreparedStatement preparedStatement = null;
  10.     static ResultSet resultSet = null;
  11.     static String DB_URL = "";
  12.     static String DB_USER = "";
  13.     static String DB_PWD = "";
  14.  
  15.     public MysqlDBTools(String dbUrl, String dbUser, String dbPwd){
  16.         this.DB_URL = dbUrl;
  17.         this.DB_USER = dbUser;
  18.         this.DB_PWD = dbPwd;
  19.     }
  20.  
  21.     public static Connection getConnection(){
  22.         try{
  23.             Class.forName("com.mysql.cj.jdbc.Driver");
  24.             connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
  25.         }catch (Exception e){
  26.             System.out.println("连接失败！");
  27.             e.printStackTrace();
  28.         }
  29.         return connection;
  30.     }
  31.  
  32.  
  33.     public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
  34.         if(resultSet != null){
  35.             try {
  36.                 resultSet.close();
  37.             } catch (SQLException throwables) {
  38.                 throwables.printStackTrace();
  39.             }
  40.         }
  41.  
  42.         if(preparedStatement != null){
  43.             try {
  44.                 preparedStatement.close();
  45.             } catch (SQLException throwables) {
  46.                 throwables.printStackTrace();
  47.             }
  48.         }
  49.  
  50.         if(connection != null){
  51.             try {
  52.                 connection.close();
  53.             } catch (SQLException throwables) {
  54.                 throwables.printStackTrace();
  55.             }
  56.         }
  57.     }
  58. }
  1. import java.sql.*;
  2. import java.util.*;
  3.  
  4. /**
  5.  * 功能
  6.  * 更新可能因为数据表结构发生变化导致程序启动失败
  7.  * 此程序能查找对比两个数据库的表结构以及字段的差异
  8.  * 方便查找问题所在
  9.  */
  10. public class CompareDbMain {
  11.  
  12.     private static String db_url1 = "jdbc:mysql://xx.xx.xx.xx:3306/udesign?useUnicode=true&characterEncoding=utf8&useSSL=false";
  13.     private static String db_user1 = "root";
  14.     private static String db_pwd1 = "xxx";
  15.  
  16.     private static String db_url2 = "jdbc:mysql://xx.xx.xx.xx:3306/udesign-v2?useUnicode=true&characterEncoding=utf8&useSSL=false";
  17.     private static String db_user2 = "root";
  18.     private static String db_pwd2 = "xxx";
  19.  
  20.     public static void main(String[] args) throws Exception{
  21.         System.out.println("[结果说明]");
  22.         System.out.println("1. ++表示A比B多，--表示A比B少，**表示有变化");
  23.         System.out.println("");
  24.  
  25.         Connection conn1 = new MysqlDBTools(db_url1, db_user1, db_pwd1).getConnection();
  26.         Connection conn2 = new MysqlDBTools(db_url2, db_user2, db_pwd2).getConnection();
  27.  
  28.         //对比表信息
  29.         List<TableEntity> tables1 = getTables(conn1);
  30.         List<TableEntity> tables2 = getTables(conn2);
  31.         StringBuffer tableRes = compareTable(tables1, tables2);
  32.         System.out.println("[表对比结果]");
  33.         System.out.println(conn1.getCatalog() + " vs " + conn2.getCatalog());
  34.         System.out.println(tableRes);
  35.  
  36.         StringBuffer columnRes = compareColumn(conn1, conn2, tables1, tables2);
  37.         System.out.println("[表字段对比结果]");
  38.         System.out.println(columnRes);
  39.     }
  40.  
  41.  
  42.     public static StringBuffer compareColumn(Connection conn1, Connection conn2, List<TableEntity> tables1, List<TableEntity> table2){
  43.         StringBuffer sb = new StringBuffer();
  44.         for(TableEntity t1: tables1){
  45.             if(tableContains(table2, t1)){
  46.                 sb.append("["+ t1.getTableName() +"]"  + "\r\n");
  47.                 List<ColumnEntity> columnEntities1 = getColumns(conn1, t1);
  48.                 List<ColumnEntity> columnEntities2 = getColumns(conn2, t1);
  49.  
  50.                 for(ColumnEntity c1: columnEntities1){
  51.                     if(columnContains(columnEntities2, c1)){
  52.                         ColumnEntity c2 = getColumnFromList(columnEntities2, c1);
  53.                         if(!c2.getColumnDisplaySize().equals(c1.getColumnDisplaySize())
  54.                         || !c2.getColumnTypeName().equals(c1.getColumnTypeName())
  55.                         || !c2.getScale().equals(c1.getScale())){
  56.                             sb.append("  **" + c2.getColumnName() + "\r\n");
  57.                         }
  58.                         //System.out.println("对比字段属性");
  59.                         continue;
  60.                     }
  61.                 }
  62.  
  63.                 for(ColumnEntity c2: columnEntities2){
  64.                     if(!columnContains(columnEntities1, c2)){
  65.                         sb.append("  --" + c2.getColumnName() + "\r\n");
  66.                         continue;
  67.                     }
  68.                 }
  69.  
  70.                 for(ColumnEntity c1: columnEntities1){
  71.                     if(!columnContains(columnEntities2, c1)){
  72.                         sb.append("  ++" + c1.getColumnName() + "\r\n");
  73.                         continue;
  74.                     }
  75.                 }
  76.             }
  77.         }
  78.         return sb;
  79.     }
  80.     //对比表信息
  81.     public static StringBuffer compareTable(List<TableEntity> tab1, List<TableEntity> tab2){
  82.         StringBuffer sb = new StringBuffer();
  83.         for(TableEntity t1: tab1){
  84.             if(tableContains(tab2, t1)){
  85.                 sb.append("    " + t1.getTableName() + "\r\n");
  86.                 continue;
  87.             }
  88.         }
  89.  
  90.         for(TableEntity t2: tab2){
  91.             if(!tableContains(tab1, t2)){
  92.                 sb.append("  --" + t2.getTableName() + "\r\n");
  93.                 continue;
  94.             }
  95.         }
  96.  
  97.         for(TableEntity t1: tab1){
  98.             if(!tableContains(tab2, t1)){
  99.                 sb.append("  ++" + t1.getTableName() + "\r\n");
  100.                 continue;
  101.             }
  102.         }
  103.  
  104.         return sb;
  105.     }
  106.     //获取字段信息
  107.     public static List<ColumnEntity> getColumns(Connection conn, TableEntity table){
  108.         List<ColumnEntity> columnEntities = new LinkedList<>();
  109.         String sql = "select * from " + table.getTableName();
  110.         try {
  111.             PreparedStatement ps = conn.prepareStatement(sql);
  112.             ResultSet rs = ps.executeQuery();
  113.             ResultSetMetaData meta = rs.getMetaData();
  114.             int columnCount = meta.getColumnCount();
  115.             for (int i = 1; i < columnCount + 1; i++) {
  116.                 ColumnEntity columnEntity = new ColumnEntity();
  117.                 columnEntity.setColumnName(meta.getColumnName(i).toLowerCase());
  118.                 columnEntity.setColumnTypeName( meta.getColumnTypeName(i).toLowerCase());
  119.                 columnEntity.setColumnDisplaySize(meta.getColumnDisplaySize(i));
  120.                 columnEntity.setScale(meta.getScale(i));
  121.                 columnEntity.setTableName(meta.getTableName(i).toLowerCase());
  122.  
  123.                 columnEntities.add(columnEntity);
  124.             }
  125.         } catch (SQLException e) {
  126.             e.printStackTrace();
  127.         }
  128.  
  129.         return columnEntities;
  130.     }
  131.  
  132.     //获取表信息
  133.     public static List<TableEntity> getTables(Connection conn){
  134.         List<TableEntity> tableEntities = new LinkedList<>();
  135.         try {
  136.             DatabaseMetaData dbMetaData = conn.getMetaData();
  137.             ResultSet rs = dbMetaData.getTables(conn.getCatalog(), null, null,new String[] { "TABLE" });
  138.             while (rs.next()) {// ///TABLE_TYPE/REMARKS
  139.                 TableEntity table = new TableEntity();
  140.                 table.setTableName(rs.getString("TABLE_NAME").toLowerCase());
  141.                 table.setTableType(rs.getString("TABLE_TYPE").toLowerCase());
  142.                 table.setTableCat(rs.getString("TABLE_CAT").toLowerCase());
  143.                 table.setTableSchem(rs.getString("TABLE_SCHEM")==null? "": rs.getString("TABLE_SCHEM").toLowerCase());
  144.                 table.setRemarks(rs.getString("REMARKS").toLowerCase());
  145.                 tableEntities.add(table);
  146.             }
  147.         } catch (SQLException e) {
  148.             e.printStackTrace();
  149.         }
  150.  
  151.         return tableEntities;
  152.     }
  153.  
  154.     //从list里面获取
  155.     public static ColumnEntity getColumnFromList(List<ColumnEntity> columnEntities, ColumnEntity column){
  156.         for(ColumnEntity c: columnEntities){
  157.             if(c.getColumnName().equals(column.getColumnName())
  158.                     && c.getTableName().equals(column.getTableName())){
  159.                 return c;
  160.             }
  161.         }
  162.  
  163.         return null;
  164.     }
  165.  
  166.     public static boolean tableContains(List<TableEntity> tableEntities, TableEntity table){
  167.         for(TableEntity tab: tableEntities){
  168.             if(tab.getTableName().equals(table.getTableName())){
  169.                 return true;
  170.             }
  171.         }
  172.  
  173.         return  false;
  174.     }
  175.  
  176.     public static boolean columnContains(List<ColumnEntity> columnEntities, ColumnEntity column){
  177.         for(ColumnEntity tab: columnEntities){
  178.             if(tab.getColumnName().equals(column.getColumnName())
  179.                     && tab.getTableName().equals(column.getTableName())){
  180.                 return true;
  181.             }
  182.         }
  183.  
  184.         return false;
  185.     }
  186. }

结果展示：

[结果说明]
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]

1.多了kafka表

2.c_dict_region county_name字段属性不一致

3. collect_task多了group_names字段

4.ue_task_publish少了group_names字段

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/木道寻08/article/detail/957522
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号