当前位置： 首页 > article >正文

使用screw来对比数据库表和字段差异

article 2024/12/22 1:28:30

1.Screw 库简介

Screw 是一个用于 数据库结构分析和文档生成的 Java 库。它支持多种数据库，包括 MySQL、PostgreSQL 和 Oracle。Screw 可以帮助开发者快速获取数据库的表结构、字段信息，并进行比较。

2.原理

使用 Screw 库对比数据库表和字段的基本原理如下：

  1. 连接数据库：使用 JDBC 连接到需要对比的两个数据库。
  2. 获取表结构：使用 Screw 提供的 API 获取每个数据库中的表和字段信息。
  3. 比较表和字段：将两个数据库的表和字段信息进行比较，识别出存在的差异。
  4. 生成报告：将比较结果生成报告，方便后续查看和分析。

3.环境搭建

第一个mysql数据库

docker run --name docker-mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql

初始化数据

CREATE DATABASE database1;

USE database1;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE DATABASE database2;

USE database2;

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    amount INT NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

说明

msyql账号root
mysql密码123456

4.代码工程

目标

使用 Screw 库对比两个 MySQL  数据库表和字段

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>springboot-demo</artifactId>
        <groupId>com.et</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>Screw</artifactId>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>cn.smallbun.screw</groupId>
            <artifactId>screw-core</artifactId>
            <version>1.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.24.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.24.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
    </dependencies>
</project>

对比代码

package com.et;

import cn.smallbun.screw.core.query.DatabaseQuery;
import cn.smallbun.screw.core.query.mysql.MySqlDataBaseQuery;
import cn.smallbun.screw.core.query.mysql.model.MySqlColumnModel;
import cn.smallbun.screw.core.query.mysql.model.MySqlTableModel;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.poi.xwpf.usermodel.*;

import javax.sql.DataSource;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DatabaseComparison {
    public static void main(String[] args) {
        // Configure database connection information
        HikariConfig hikariConfig1 = new HikariConfig();
        hikariConfig1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        hikariConfig1.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/database1");
        hikariConfig1.setUsername("root");
        hikariConfig1.setPassword("123456");
        hikariConfig1.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig1.setMinimumIdle(2);
        hikariConfig1.setMaximumPoolSize(5);
        DataSource dataSource1 = new HikariDataSource(hikariConfig1);
        DatabaseQuery query1 = new MySqlDataBaseQuery(dataSource1);

        HikariConfig hikariConfig2 = new HikariConfig();
        hikariConfig2.setDriverClassName("com.mysql.cj.jdbc.Driver");
        hikariConfig2.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/database2");
        hikariConfig2.setUsername("root");
        hikariConfig2.setPassword("123456");
        hikariConfig2.addDataSourceProperty("useInformationSchema", "true");
        hikariConfig2.setMinimumIdle(2);
        hikariConfig2.setMaximumPoolSize(5);
        DataSource dataSource2 = new HikariDataSource(hikariConfig2);
        DatabaseQuery query2 = new MySqlDataBaseQuery(dataSource2);

        try {
            // Retrieve table structure
            List<MySqlTableModel> tableInfos1 = (List<MySqlTableModel>) query1.getTables();
            List<MySqlTableModel> tableInfos2 = (List<MySqlTableModel>) query2.getTables();

            // Create Word document
            XWPFDocument document = new XWPFDocument();
            XWPFParagraph titleParagraph = document.createParagraph();
            titleParagraph.createRun().setText("Database Table and Field Comparison");
            titleParagraph.setAlignment(ParagraphAlignment.CENTER);

            // Create table for database comparison
            XWPFTable table = document.createTable();
            XWPFTableRow headerRow = table.getRow(0);
            headerRow.getCell(0).setText("Table Name");
            headerRow.addNewTableCell().setText("Exists in Database 1");
            headerRow.addNewTableCell().setText("Exists in Database 2");

            // Compare tables
            Map<String, MySqlTableModel> tableMap1 = new HashMap<>();
            for (MySqlTableModel tableInfo : tableInfos1) {
                tableMap1.put(tableInfo.getTableName(), tableInfo);
            }

            Map<String, MySqlTableModel> tableMap2 = new HashMap<>();
            for (MySqlTableModel tableInfo : tableInfos2) {
                tableMap2.put(tableInfo.getTableName(), tableInfo);
            }

            // Record table differences
            for (String tableName : tableMap1.keySet()) {
                XWPFTableRow row = table.createRow();
                row.getCell(0).setText(tableName);
                row.getCell(1).setText("Yes");
                row.getCell(2).setText(tableMap2.containsKey(tableName) ? "Yes" : "No");
            }

            for (String tableName : tableMap2.keySet()) {
                if (!tableMap1.containsKey(tableName)) {
                    XWPFTableRow row = table.createRow();
                    row.getCell(0).setText(tableName);
                    row.getCell(1).setText("No");
                    row.getCell(2).setText("Yes");
                }
            }

            // Add table differences title
            document.createParagraph().createRun().setText("\nTable Differences:");
            for (String tableName : tableMap1.keySet()) {
                if (!tableMap2.containsKey(tableName)) {
                    document.createParagraph().createRun().setText("Table " + tableName + " does not exist in Database 2");
                } else {
                    compareColumns(document, tableMap1.get(tableName), tableMap2.get(tableName), query1, query2);
                }
            }

            for (String tableName : tableMap2.keySet()) {
                if (!tableMap1.containsKey(tableName)) {
                    document.createParagraph().createRun().setText("Table " + tableName + " does not exist in Database 1");
                }
            }

            // Save Word document
            try (FileOutputStream out = new FileOutputStream("D://tmp/Database_Comparison.docx")) {
                document.write(out);
            }

            System.out.println("Database table and field differences have been generated in the Word document.");

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("An error occurred: " + e.getMessage());
        }
    }

    private static void compareColumns(XWPFDocument document, MySqlTableModel mySqlTableModel1, MySqlTableModel mySqlTableModel2, DatabaseQuery query1, DatabaseQuery query2) {
        // Retrieve column information for both tables
        List<MySqlColumnModel> columnNames1 = (List<MySqlColumnModel>) query1.getTableColumns(mySqlTableModel1.getTableName());
        List<MySqlColumnModel> columnNames2 = (List<MySqlColumnModel>) query2.getTableColumns(mySqlTableModel2.getTableName());

        // Create mappings from column names to column models
        Map<String, MySqlColumnModel> columnsMap1 = new HashMap<>();
        for (MySqlColumnModel column : columnNames1) {
            columnsMap1.put(column.getColumnName(), column);
        }

        Map<String, MySqlColumnModel> columnsMap2 = new HashMap<>();
        for (MySqlColumnModel column : columnNames2) {
            columnsMap2.put(column.getColumnName(), column);
        }

        // Create column difference table
        XWPFTable columnTable = document.createTable();
        XWPFTableRow columnHeaderRow = columnTable.getRow(0);
        columnHeaderRow.getCell(0).setText("Column Name");
        columnHeaderRow.addNewTableCell().setText("Exists in Database 1");
        columnHeaderRow.addNewTableCell().setText("Exists in Database 2");

        // Compare columns
        for (String columnName : columnsMap1.keySet()) {
            XWPFTableRow row = columnTable.createRow();
            row.getCell(0).setText(columnName);
            row.getCell(1).setText("Yes");
            row.getCell(2).setText(columnsMap2.containsKey(columnName) ? "Yes" : "No");

            if (!columnsMap2.containsKey(columnName)) {
                document.createParagraph().createRun().setText("Column " + columnName + " does not exist in table " + mySqlTableModel2.getTableName());
            } else {
                // Compare column types and other properties
                MySqlColumnModel column1 = columnsMap1.get(columnName);
                MySqlColumnModel column2 = columnsMap2.get(columnName);
                // Compare column types
                if (!column1.getDataType().equals(column2.getDataType())) {
                    document.createParagraph().createRun().setText("Column " + columnName + " in table " + mySqlTableModel1.getTableName() + " has type " + column1.getDataType() +
                            ", while in table " + mySqlTableModel2.getTableName() + " it has type " + column2.getDataType());
                }
            }
        }

        // Check reverse differences
        for (String columnName : columnsMap2.keySet()) {
            if (!columnsMap1.containsKey(columnName)) {
                document.createParagraph().createRun().setText("Column " + columnName + " does not exist in table " + mySqlTableModel1.getTableName());
            }
        }
    }
}

代码解析

  1.  数据库连接配置：使用 HikariCP 配置数据库连接信息，包括数据库 URL、用户名和密码。

  2. 获取表结构：通过 query.getTables() 方法获取数据库中的表信息。

  3. 比较表：将两个数据库的表信息存储在 Map 中，并进行比较，输出存在于一个数据库但不存在于另一个数据库的表。

  4. 比较字段：在比较表的同时，调用 compareColumns 方法获取字段信息，并进行比较，输出字段的存在性和类型差异。

以上只是一些关键代码，所有代码请参见下面代码仓库

代码仓库

  • GitHub - Harries/springboot-demo: a simple springboot demo with some components for example: redis,solr,rockmq and so on.(screw)

5.测试

运行测试类的main方法，将结果写入word文档

compare

6.引用

  • 使用screw来对比数据库表和字段差异 | Harries Blog™

http://www.kler.cn/a/445830.html

相关文章：

  • 20241217使用M6000显卡在WIN10下跑whisper来识别中英文字幕
  • 实现 WebSocket 接入文心一言
  • wireshark初认识
  • 音视频入门基础：MPEG2-TS专题（21）——FFmpeg源码中，获取TS流的视频信息的实现
  • 数智化转型是什么？
  • bestphp‘s revenge
  • R语言的数据结构-数据框
  • docker打包镜像并迁移：如何从A服务器打包docker镜像到B服务器上容器中运行
  • 如何利用Python爬虫获得Lazada商品评论列表
  • UE5 跟踪能力的简单小怪
  • 请求go web后端接口 java安卓端播放视频
  • U盘出现USBC乱码文件的全面解析与恢复指南
  • Go 1.24即将到来！
  • [spring]XML配置文件标签
  • 通过smem 定时检测系统内存占用情况
  • windows下搭建本地sofa-registry
  • C哈的刷题计划之新矩阵（4）
  • Syslog 管理工具
  • 基于单片机的医院盒装药品自动分拣系统（论文+源码）
  • 充分利用 AIStor 的网络配置
  • 54、库卡机器人轴的软限位设置
  • flutter 使用dio 请求go语言后台数据接口展示瀑布流图片
  • EE308FZ_Sixth Assignment_Beta Sprint_Sprint Essay 3
  • opencv图片的纠正透视转换函数的应用，水印的添加，轮廓的绘制
  • 【Mysql】数据库分区技术详解
  • @FeignClient用于Nacos微服务间的接口调用