• MYSQL 数据库对比 工具类

    1、数据库链接 jdbcTemplate

    2、datasource mysqldatasource

    3、使用：

         配置好2个数据源

         配置好2个数据库名

         配置好要对比的表名:主键

          执行main函数

          会打印好对比的结果

    项目下载地址： 下载的项目修改数据库后可以直接运行

    https://download.csdn.net/download/linhaihai0202/87173288

    1. package com.vince.xq.dataCompare.utils;
    2.  
    3. import com.mysql.cj.jdbc.MysqlDataSource;
    4. import org.springframework.jdbc.core.JdbcTemplate;
    5. import java.util.ArrayList;
    6. import java.util.List;
    7. import java.util.Map;
    8. import java.util.stream.Collectors;
    9.  
    10. public class MsqlDefComparisonUtil {
    11.  
    12.     //=================配置部分=================
    13.     String database1 = "lhh";
    14.     String database2 = "lhh2";
    15.     private String url1 = "jdbc:mysql://localhost:3306/lhh";
    16.     private String url2 = "jdbc:mysql://localhost:3306/lhh2";
    17.     private String user1 = "root";
    18.     private String user2 = "root";
    19.     private String password1 = "root";
    20.     private String password2 = "root";
    21.     //那些表需要对比数据；白名单
    22.     private List<String> compareDataTables = new ArrayList<>();
    23.     {
    24.         //表名:主键列明
    25.         compareDataTables.add("name:id");
    26.     }
    27.     //=================配置部分=================
    28.  
    29.  
    30.     JdbcTemplate jdbcTemplate1;
    31.     JdbcTemplate jdbcTemplate2;
    32.     public MsqlDefComparisonUtil(){
    33.         jdbcTemplate1 = getJdbcTemplate1();
    34.         jdbcTemplate2 = getJdbcTemplate2();
    35.     }
    36.     StringBuilder createTableSB = new StringBuilder();
    37.     StringBuilder createColumnSB = new StringBuilder();
    38.     StringBuilder updateColumnSB = new StringBuilder();
    39.     StringBuilder insertSB = new StringBuilder();
    40.  
    41.  
    42.  
    43.  
    44.     public static void main(String[] args){
    45.         MsqlDefComparisonUtil util = new MsqlDefComparisonUtil();
    46.         util.compareTable();
    47.         util.compareData();
    48.         System.out.println("建表语句");
    49.         System.out.println(util.createTableSB);
    50.         System.out.println("新增列语句");
    51.         System.out.println(util.createColumnSB);
    52.         System.out.println("修改列语句");
    53.         System.out.println(util.updateColumnSB);
    54.         System.out.println("插入数据语句");
    55.         System.out.println(util.insertSB);
    56.     }
    57.  
    58.     private void compareTable(){
    59.  
    60.  
    61.         //拿到2个库的表清单
    62.         List<Map<String, Object>> tables1 =
    63.                 jdbcTemplate1.queryForList(
    64.                         String.format("select * from information_schema.tables where table_schema = '%s'",database1));
    65.  
    66.         //拿到2个库的表清单
    67.         List<Map<String, Object>> tables2 =
    68.                 jdbcTemplate2.queryForList(
    69.                         String.format("select * from information_schema.tables where table_schema = '%s'",database2));
    70.  
    71.         for (Map<String, Object> table1 : tables1) {
    72.             String tableName = table1.get("TABLE_NAME").toString();
    73.             //对比列
    74.             //拿到2个库的表清单
    75.             List<Map<String, Object>> columns1 =
    76.                     jdbcTemplate1.queryForList(
    77.                             String.format("select * from information_schema.columns where table_schema = '%s' and table_name = '%s' ORDER BY ORDINAL_POSITION",database1,tableName));
    78.  
    79.  
    80.             long count = tables2.stream().filter(i -> i.get("TABLE_NAME").equals(table1.get("TABLE_NAME"))).count();
    81.             if(count == 0){
    82.                 //todo建表
    83.                 //CREATE TABLE t1(
    84.                 //id int not null primary key,
    85.                 //name char(20)
    86.                 //);
    87.                 createTableSB.append("create table `"+tableName+"` (\r\n");
    88.                 for (Map<String, Object> column1 : columns1) {
    89.                     createTableSB.append("    ");
    90.                     createTableSB.append("`"+column1.get("COLUMN_NAME")+"` "+column1.get("COLUMN_TYPE"));
    91.                     createTableSB.append("NO".equals(column1.get("IS_NULLABLE"))?" NOT NULL":"");
    92.                     if("auto_increment".equals(column1.get("EXTRA"))) {
    93.                         createTableSB.append(" AUTO_INCREMENT ");
    94.                     }
    95.                     createTableSB.append("PRI".equals(column1.get("COLUMN_KEY"))?" primary key":"");
    96.                     if(column1.get("COLUMN_DEFAULT") != null) {
    97.                         createTableSB.append(" DEFAULT '" + column1.get("COLUMN_DEFAULT") + "'");
    98.                     }
    99.                     createTableSB.append(" COMMON '"+column1.get("COLUMN_COMMENT")+"',\r\n");
    100.  
    101.                 }
    102.                 createTableSB.append(") ENGINE = "+table1.get("ENGINE")+" CHARACTER SET = utf8mb4;\r\n");
    103.             }else{
    104.  
    105.  
    106.  
    107.                 //拿到2个库的表清单
    108.                 List<Map<String, Object>> columns2 =
    109.                         jdbcTemplate2.queryForList(
    110.                                 String.format("select * from information_schema.columns where table_schema = '%s' and table_name = '%s' ORDER BY ORDINAL_POSITION",database2,tableName));
    111.  
    112.                 for (Map<String, Object> column1 : columns1) {
    113.                     List<Map<String, Object>> columnCount = columns2.stream()
    114.                             .filter(i -> i.get("COLUMN_NAME").equals(column1.get("COLUMN_NAME")))
    115.                             .collect(Collectors.toList());
    116.                     if(columnCount == null || columnCount.isEmpty()){
    117.                         //增加字段
    118.                         //alter table tb_user  add column   `create_time` datetime DEFAULT NULL COMMENT '创建时间' ;
    119.                         createColumnSB.append("ALTER TABLE ");
    120.                         createColumnSB.append("`"+column1.get("TABLE_NAME")+"`");
    121.                         createColumnSB.append(" ADD `"+column1.get("COLUMN_NAME")+"` ");
    122.                         createColumnSB.append(column1.get("COLUMN_TYPE"));
    123.                         if("NO".equals(column1.get("IS_NULLABLE"))) {
    124.                             createColumnSB.append(" NOT NULL");
    125.                         }
    126.                         if(column1.get("COLUMN_DEFAULT") != null) {
    127.                             createColumnSB.append(" DEFAULT `" + column1.get("COLUMN_DEFAULT")+"`");
    128.                         }
    129.                         createColumnSB.append(" COMMON '"+column1.get("COLUMN_COMMENT")+"'");
    130.                         createColumnSB.append(";\n");
    131.  
    132.  
    133.                     }else {
    134.                         Map<String, Object> column2 = columnCount.get(0);
    135.                         if(!twoMapKeyEquals(column1,column2,
    136.                                 "COLUMN_TYPE","COLUMN_COMMENT","COLUMN_DEFAULT","IS_NULLABLE")){
    137.                             //修改字段
    138.                             //ALTER TABLE `user_info` MODIFY COLUMN `name` CHAR(45) COMMENT '姓名';
    139.                             updateColumnSB.append("ALTER TABLE ");
    140.                             updateColumnSB.append("`"+column1.get("TABLE_NAME")+"`");
    141.                             updateColumnSB.append(" MODIFY COLUMN ");
    142.                             updateColumnSB.append("`"+column1.get("COLUMN_NAME")+"`");
    143.                             updateColumnSB.append(" "+column1.get("COLUMN_TYPE"));
    144.                             if("NO".equals(column1.get("IS_NULLABLE"))) {
    145.                                 updateColumnSB.append(" NOT NULL");
    146.                             }
    147.                             if(column1.get("COLUMN_DEFAULT") != null) {
    148.                                 updateColumnSB.append(" DEFAULT `" + column1.get("COLUMN_DEFAULT") + "`");
    149.                             }
    150.                             if(column1.get("COLUMN_COMMENT") != null) {
    151.                                 updateColumnSB.append(" COMMENT '" + column1.get("COLUMN_COMMENT") + "'");
    152.                             }
    153.                             updateColumnSB.append(";\r\n");
    154.                         }
    155.  
    156.                     }
    157.                 }
    158.             }
    159.         }
    160.  
    161.  
    162.     }
    163.  
    164.     /**
    165.      * 2个map 的属性都相等
    166.      * @param map1
    167.      * @param map2
    168.      * @param args
    169.      * @return
    170.      */
    171.     private boolean twoMapKeyEquals(Map<String,Object> map1,Map<String,Object> map2,String ... args){
    172.         for (String arg : args) {
    173.             Object o1 = map1.get(arg);
    174.             Object o2 = map2.get(arg);
    175.             if(o1 == null && o2 != null){
    176.                 return false;
    177.             }else if(o1 != null && o2 == null){
    178.                 return false;
    179.             }else if(o1 != null && o2 != null){
    180.                 if(!o1.equals(o2)){
    181.                     return false;
    182.                 }
    183.             }
    184.         }
    185.         return true;
    186.     }
    187.  
    188.     private JdbcTemplate getJdbcTemplate1(){
    189.         MysqlDataSource dataSource = new MysqlDataSource();
    190.         dataSource.setUser(user1);
    191.         dataSource.setPassword(password1);
    192.         dataSource.setUrl(url1);
    193.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    194.         return jdbcTemplate;
    195.     }
    196.  
    197.     private JdbcTemplate getJdbcTemplate2(){
    198.         MysqlDataSource dataSource = new MysqlDataSource();
    199.         dataSource.setUser(user2);
    200.         dataSource.setPassword(password2);
    201.         dataSource.setUrl(url2);
    202.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    203.         return jdbcTemplate;
    204.     }
    205.  
    206.  
    207.  
    208.     private void compareData(){
    209.  
    210.         for (String item : compareDataTables) {
    211.             String table = item.split(":")[0];
    212.             String id = item.split(":")[1];
    213.             List<Map<String,Object>> maps1 = jdbcTemplate1.queryForList("select * from "+table);
    214.             List<Map<String,Object>> maps2 = jdbcTemplate2.queryForList("select * from "+table);
    215.             for (Map<String, Object> map1 : maps1) {
    216.                 long count = maps2.stream().filter(i->i.get(id).equals(map1.get(id))).count();
    217.                 if(count == 0){
    218.                     insertSB.append("insert into `"+table+"` (");
    219.                     int i=0;
    220.                     for (String s : map1.keySet()) {
    221.                         if(i == 0){
    222.                             insertSB.append("`"+s+"`");
    223.                         }else{
    224.                             insertSB.append(",`"+s+"`");
    225.                         }
    226.                         i++;
    227.  
    228.                     }
    229.  
    230.                     insertSB.append(")");
    231.                     insertSB.append("values");
    232.                     insertSB.append("(");
    233.                     i=0;
    234.                     for (String s : map1.keySet()) {
    235.                         if(i == 0){
    236.                             insertSB.append("'"+map1.get(s)+"'");
    237.                         }else{
    238.                             insertSB.append(",'"+map1.get(s)+"'");
    239.                         }
    240.                         i++;
    241.  
    242.                     }
    243.                     insertSB.append(")");
    244.                     insertSB.append(";\r\n");
    245.                 }
    246.  
    247.             }
    248.         }
    249.     }
    250. }

  • 相关阅读:
    基于Java实现的离散数学测试实验
    redis6.2（二）Redis的新数据类型、使用java语言操作Redis
    推荐系统工业界顶会论文总结——WSDM 2021
    操作系统——网络编程——socket——TCP/UDP
    TiCDC 重要监控指标详解
    JAVASE---认识异常
    springboot：时间格式化的5种方法（解决后端传给前端的时间格式转换问题）推荐使用第4和第5种！
    基于截止至 2023 年 5 月 30 日，在 App Store 上进行交易的设备数据统计，iOS/iPadOS 各版本更新情况
    python-pytorch 实现seq2seq+luong general concat attention笔记1.0.10
    第二部分—C语言提高篇_12. 动/精态库的封装和使用
  • 原文地址：https://blog.csdn.net/linhaihai0202/article/details/128057822