关于数据库:java-如何从零实现一个数据库差异对比工具

69次阅读

共计 11315 个字符，预计需要花费 29 分钟才能阅读完成。

比照数据的苦楚

不晓得你是否也像我一样，在高兴编写代码的时候，必须进行一些数据库的数据比照工作。

诚然，个别的数据差别，比方是每一行的内容不同，市场上有比拟成熟的 compare2 等比照工具。

然而如果是比照数据的每一列是否雷同，这个就会变得比拟麻烦。

v1.0 纯人工比照

咱们在做一些数据迁徙等性能时，常常须要比照数据是否正确，最常见的办法就是人工一列一列的比照。

一开始老马也是这么和共事对的，对了几天之后感觉效率切实是低，而且还容易看花眼。

于是我就是推敲，这个货色用程序比照应该会简略很多。

v2.0 半人工比照

说干就干，我花半天工夫实现了一个基于 jsqlparser 能够解析相似于 insert into xxx (xx, xx, xx) values (xx, xx, xx); 的工具类。

而后比照 2 边的数据，这下对于一张表上百个字段的比照，一些变得快了许多，准确率也高了很多。

不要问我为什么会有上百个字段，这都是历史积淀下来的珍宝。。。

ps: insert into 语句是否通过数据库连贯工具手工导出的。

起初又发现另一个问题：表太多，如果想换一个数据比照，我手工导出一遍又要花费数十分钟的工夫，要害是反复且干燥。

既然反复，那么能够应用程序实现吗？

v3.0 比照根本自动化

于是我上班后熬夜实现了这个版本：java 程序实现了数据的导出长久化，而后进行批改前后的差别比照。

上面我分享一下本人的思路，以及外围源码，文末有下载福利。

心愿对你工作和学习提供帮忙。

整体理念

我心愿这个工具是 MVP 的理念，由简略到简单，前期逐步丰盛个性。

要有可拓展性，目前反对 mysql/oracle/sql server 等支流数据库，用户能够定制化开发。

尽可能少的依赖，应用原生的 jdbc，不须要引入 mybatis 等框架。

外围依赖

上面列举一下我用到的外围依赖：

fastjson 用于数据长久化为 json

mysql-connector-java 数据库连贯驱动

jsqlparser 辅助工具，解析 sql 应用，非必须

实现思路

  1. 依据指定的 jdbc 连贯信息，主动抉择对应的 jdbc 实现。
  2. 执行对应的 sql，将后果解析为 map，进行 JSON 长久化
  3. 对长久化的 json 进行差别比照，展现出差别后果

有了这个思路，所有就会变得朴实无华。

当然在此之前，须要咱们把代码实现进去，上面进入写 BUG 环节：

jdbc 实现

外围接口

思考到前期不同数据库实现，咱们对立定义一个查问接口

/**
 * JDBC 拜访层
 * @author 老马啸东风
 * @date 2017/8/1
 */
public interface JdbcMapper {

    /**
     * 执行查问语句
     * @param querySql
     * @return
     */
    ResultSet query(String querySql);

}

形象实现

这里提供了根本的形象实现。

子类只须要实现对应的连贯获取信息即可。

public abstract class AbstractJdbcMapper implements JdbcMapper {

    protected JdbcVo jdbcVo;

    public AbstractJdbcMapper(JdbcVo jdbcVo) {this.jdbcVo = jdbcVo;}

    /**
     * 获取数据库连贯
     * @return
     */
    protected abstract Connection getConnection();

    @Override
    public ResultSet query(String querySql) {
        ResultSet rs = null;
        Connection connection = getConnection();
        try {
            Statement stmt = null;
            stmt = connection.createStatement();
            rs = stmt.executeQuery(querySql);
        } catch (Exception e) {System.out.println("SQL:" + querySql);
            throw new ExportdbException(e);
        }
        return rs;
    }

}

JdbcVo 连贯信息

这个对象次要是数据库连贯信息对象：

public class JdbcVo {

    /**
     * 驱动类名称
     */
    private String driverClassName;

    /**
     * 数据库链接
     */
    private String url;

    /**
     * 用户名称
     */
    private String username;

    /**
     * 明码
     */
    private String password;

    //getter & setter
}

mysql 实现

此处以 mysql 为例：

import com.github.houbb.exportdb.dto.JdbcVo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * mysql 实现
 * @author 老马啸东风
 * @date 2017/8/1
 */
public class MySqlJdbcMapper extends AbstractJdbcMapper {public MySqlJdbcMapper(JdbcVo jdbcVo) {super(jdbcVo);
    }

    @Override
    protected Connection getConnection() {
        try {Class.forName(jdbcVo.getDriverClassName());
            return DriverManager.getConnection(jdbcVo.getUrl(),
                    jdbcVo.getUsername(),
                    jdbcVo.getPassword());
        } catch (ClassNotFoundException | SQLException e) {e.printStackTrace();
        }
        return null;
    }
}

这里次要是对连贯的初始化，连贯不同的数据库，都须要引入对应的数据源。

行数据导出实现

上面是导出的外围实现：

接口定义

public interface IExportdb {

    /**
     * 查问
     * @param context 上下文
     * @param sql sql
     * @return 后果
     * @since 0.0.1
     */
    QueryResultVo query(final ExportdbContext context, final String sql);

}

这里指定了须要执行的 sql。

context 中为了便于前期拓展，目前只有 JdbcMapper。

返回的就是 QueryResultVo，就是查问后果，定义如下：

public class QueryResultVo {
    /**
     * 表名称
     */
    private String tableName;

    /**
     * 数据库名称
     *
     * @since 0.0.2
     */
    private String databaseName;

    /**
     * 后果汇合
     */
    private List<Map<String, Object>> resultMaps;

    /**
     * 执行的 sql
     */
    private String sql;

    //getter & setter
}

默认实现

默认的导出实现如下：

import com.github.houbb.exportdb.core.ExportdbContext;
import com.github.houbb.exportdb.core.IExportdb;
import com.github.houbb.exportdb.dal.JdbcMapper;
import com.github.houbb.exportdb.dto.QueryResultVo;
import com.github.houbb.exportdb.exception.ExportdbException;
import com.github.houbb.heaven.util.lang.StringUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @author binbin.hou
 * @since 0.0.1
 */
public class Exportdb implements IExportdb {

    @Override
    public QueryResultVo query(ExportdbContext context, String sql) {
        try {final JdbcMapper jdbcMapper = context.jdbcMapper();

            ResultSet resultSet = jdbcMapper.query(sql);
            List<Map<String, Object>> maps = new ArrayList<>();

            String tableName = null;
            while (resultSet.next()) {final ResultSetMetaData metaData = resultSet.getMetaData();
                // 设置表名称
                if(tableName == null) {tableName = metaData.getTableName(1);
                }

                Map<String, Object> map = new LinkedHashMap<>();
                // 为空间接返回，大于 1 则报错
                // 列数的总数
                int columnCount = metaData.getColumnCount();

                for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnName(i);
                    Object value = resultSet.getObject(columnName);

                    map.put(columnName, value);
                }

                maps.add(map);
            }

            if(StringUtil.isEmptyTrim(tableName)) {Statement statement = CCJSqlParserUtil.parse(sql);
                Select select = (Select)statement;
                PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
                tableName = plainSelect.getFromItem().toString();
            }

            return QueryResultVo.newInstance().tableName(tableName)
                    .databaseName("")
                    .sql(sql)
                    .resultMaps(maps);
        } catch (SQLException | JSQLParserException throwables) {throw new ExportdbException(throwables);
        }
    }
}

其实实现非常简单，咱们次要讲一下两点：

（1）表名称

mysql 经测试能够通过如下形式获取:

resultSet.getMetaData();
tableName = metaData.getTableName(1);

oracle 我在测试的时候，发现无奈获取。所以是借助 sqlparser 解析咱们的查问语句失去的。

临时次要是反对查问，所以这里写的有些固定了，后续能够优化一下。

if(StringUtil.isEmptyTrim(tableName)) {Statement statement = CCJSqlParserUtil.parse(sql);
    Select select = (Select)statement;
    PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
    tableName = plainSelect.getFromItem().toString();
}

（2）列信息

每一个查问，可能都对应多条记录。

咱们看一下每一条记录的构建：

while (resultSet.next()) {final ResultSetMetaData metaData = resultSet.getMetaData();
    Map<String, Object> map = new LinkedHashMap<>();
    // 为空间接返回，大于 1 则报错
    // 列数的总数
    int columnCount = metaData.getColumnCount();
    for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnName(i);
        Object value = resultSet.getObject(columnName);
        map.put(columnName, value);
    }
    maps.add(map);
}

这个常常写 jdbc 的小伙伴也肯定不生疏。

你说当初都用 mybatis 了，谁还写 jdbc 啊，这么 low。

那么，你本人手写一个 mybatis，这些也是必会的。

从零开始手写 mybatis（一）MVP 版本

差别比照

导出的应用

咱们能够把一行数据导出，能够在批改前后别离导出。

如果是导出到不同的库，不同的表，那么就进行不同库表之间的导出。

导出后果之后，就须要进行比照了。

比照实现

接口定义

对于导出后果的解决，你能够依据本人的理论状况自行抉择。

比方导出为 csv/json/insert 等，比照差别也能够依照本人的需要定制。

public interface IQueryResultHandler {

    /**
     * 后果解决类
     * @param queryResultVo 查问后果
     */
    void handler(final QueryResultVo queryResultVo);

}

长久化

此处介绍一种比较简单实用的形式：json 长久化。

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.github.houbb.exportdb.dto.QueryResultVo;
import com.github.houbb.exportdb.support.result.IQueryResultHandler;
import com.github.houbb.heaven.util.io.FileUtil;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 老马啸东风
 * @since 0.0.1
 */
public class FileJsonQueryResultHandler implements IQueryResultHandler {

    /**
     * 默认的文件输入门路
     *
     * 依据操作系统，主动设置
     * @since 0.0.1
     */
    private final String dir;

    public FileJsonQueryResultHandler(String dir) {this.dir = dir;}

    public FileJsonQueryResultHandler() {this("D:\\exportdb\\");
    }

    /**
     * 后果解决类
     *
     * @param queryResultVo 查问后果
     */
    @Override
    public void handler(final QueryResultVo queryResultVo) {String path = dir+queryResultVo.tableName()+".edb";
        System.out.println("文件门路:" + path);

        List<Map<String, Object>> list = queryResultVo.resultMaps();
        List<String> lines = new ArrayList<>(list.size()+1);

        lines.add("--"+queryResultVo.sql());
        for(Map<String, Object> map : list) {lines.add(JSON.toJSONString(map, SerializerFeature.WriteMapNullValue));
        }

        FileUtil.write(path, lines);
    }

}

咱们将行数据长久化到文件中，留神这里指定了 JSON.toJSONString(map, SerializerFeature.WriteMapNullValue)；

这样能够让 null 字段也输入，更加不便比照。

文件差别比照实现

下面咱们假如将文件输入到 2 个文件，上面指定文件门路就能够进行比照了：

/**
 * 差别比照
 * @param oldPath 原始门路
 * @param newPath 新的门路
 */
public static void differ(final String oldPath, final String newPath) {List<String> oldLines = FileUtil.readAllLines(oldPath);
    List<String> newLines = FileUtil.readAllLines(newPath);
    System.out.println(FileUtil.getFileName(oldPath)+"比照开始 ---------------");
    for(int i = 0; i < oldLines.size(); i++) {String oldL = oldLines.get(i);
        String newL = newLines.get(i);
        if(oldL.startsWith("--")) {continue;}
        System.out.println("第" + (i+1) +"行比照:");
        differMaps(oldL, newL);
    }
    System.out.println(FileUtil.getFileName(oldPath)+"比照完结 ---------------");
    System.out.println();}

private static void differMaps(final String oldMap, final String newMap) {Map<String, Object> om = JSON.parseObject(oldMap);
    Map<String, Object> nm = JSON.parseObject(newMap);
    for(Map.Entry<String, Object> entry : om.entrySet()) {String key = entry.getKey();
        Object oldV = om.get(key);
        Object newV = nm.get(key);
        // 跳过 null 的比照
        if(oldV == null && newV == null) {continue;}
        if(!ObjectUtil.isEquals(oldV, newV)) {System.out.println("差别列：" + key +", 旧值：" + oldV + ", 新值：" + newV);
        }
    }
}

这里将差别内容，间接 console 控台输入。

文件夹

当然，咱们也能够比照两个文件夹下的内容。

实现如下：

public static void differDir(final String oldDir, final String newDir) {File[] oldFiles = new File(oldDir).listFiles();

    for(File file : oldFiles) {String fileName = file.getName();
        String aop = file.getAbsolutePath();
        String anp = newDir+fileName;
        differ(aop, anp);
    }
}

疏导类

便利性

下面咱们把外围实现都搞定了，然而用户应用起来还是不够不便。因为配置等不够优雅。

所以咱们引入疏导类，帮忙用户疾速应用：

/**
 * @author 老马啸东风
 * @since 0.0.1
 */
public class ExportdbBs {private ExportdbBs(){}

    /**
     * 导出实现
     * @since 0.0.1
     */
    private final IExportdb exportdb = new Exportdb();

    /**
     * 驱动类名称
     */
    private String driverName = DriverNameConstant.MYSQL;

    /**
     * 数据库链接
     */
    private String url = "jdbc:mysql://localhost:3306/test";

    /**
     * 用户名称
     */
    private String username = "root";

    /**
     * 明码
     */
    private String password = "123456";


    public static ExportdbBs newInstance() {return new ExportdbBs();
    }

    public ExportdbBs driverName(String driverName) {
        this.driverName = driverName;
        return this;
    }

    public ExportdbBs url(String url) {
        this.url = url;
        return this;
    }

    public ExportdbBs username(String username) {
        this.username = username;
        return this;
    }

    public ExportdbBs password(String password) {
        this.password = password;
        return this;
    }

    /**
     * 查问
     * @param sql sql
     * @return 后果
     * @since 0.0.1
     */
    public QueryResultVo query(final String sql) {
        //1. 构建 vo
        JdbcVo jdbcVo = new JdbcVo(driverName, url, username, password);

        //2. 获取 mapper
        final JdbcMapper jdbcMapper = getJdbcMapper(jdbcVo);

        //3. 构建上下文
        final ExportdbContext context = ExportdbContext.newInstance().jdbcMapper(jdbcMapper);
        return this.exportdb.query(context, sql);
    }

    /**
     * 查问并且解决
     * @param queryResultHandler 查问后果处理器
     * @param sql sql
     * @since 0.0.1
     */
    public void queryAndHandle(final IQueryResultHandler queryResultHandler,
                               final String sql, final String... otherSqls) {QueryResultVo queryResultVo = this.query(sql);
        queryResultHandler.handler(queryResultVo);

        // 同理解决其余的 sql
        for(String os : otherSqls) {QueryResultVo vo = this.query(os);
            queryResultHandler.handler(vo);
        }
    }

    /**
     * 查问并且解决
     * @param queryResultHandler 查问后果处理器
     * @param sqlList sql 列表
     * @since 0.0.2
     */
    public void queryAndHandle(final IQueryResultHandler queryResultHandler,
                               List<String> sqlList) {
        // 同理解决其余的 sql
        for(String sql : sqlList) {System.out.println("开始执行：" + sql);
            QueryResultVo vo = this.query(sql);
            queryResultHandler.handler(vo);
        }
    }

    private JdbcMapper getJdbcMapper(JdbcVo jdbcVo) {if(DriverNameConstant.MYSQL.equalsIgnoreCase(driverName)) {return new MySqlJdbcMapper(jdbcVo);
        }
        if(DriverNameConstant.ORACLE.equalsIgnoreCase(driverName)) {return new OracleJdbcMapper(jdbcVo);
        }
        if(DriverNameConstant.SQL_SERVER.equalsIgnoreCase(driverName)) {return new SqlServerJdbcMapper(jdbcVo);
        }

        throw new UnsupportedOperationException();}

}

这里为用户提供了 mysql 最根本的配置，以及罕用的查询处理办法。

测试

上面咱们来看一下测试的成果：

间接查问

QueryResultVo resultVo = ExportdbBs.newInstance().query("select * from user;");
System.out.println(resultVo);

查问并解决

final String sql = "select * from user;";
final IQueryResultHandler handler = new FileJsonQueryResultHandler();
ExportdbBs.newInstance().queryAndHandle(handler, sql);

两次导出能够指定文件门路，比方别离是：

D:\exportdb\old\ 和 D:\exportdb\new\

针对两次后果比照

final String oldP = "D:\\exportdb\\old\\";
final String newP = "D:\\exportdb\\new\\";

CompareUtil.differDir(oldP, newP);

差别后果就会被输入到控台。

一切顺利，不过反动尚未胜利，同学仍需加班呀~~~

不足之处

这是一个 v0.0.1 版本，还有很多有余。

比方:

  • 导出为 csv
  • 导出为 insert/update 语句
  • 导出的文件名称自定义策略
  • 能够指定多个 sql 是否生成在同一个文件中
  • 导出门路依据操作系统，主动变更
  • 更加便于应用，比方页面指定数据源 +sql，页面显示对应差别后果。

不过也根本可用，合乎咱们最后的构想。

小结

不晓得你平时又是如何比照数据的呢？

如果你须要这个工具，能够关注【老马啸东风】，后盾回复【比照】即可。

心愿本文对你有帮忙，如果有其余想法的话，也能够评论区和大家分享哦。

各位 极客 的点赞珍藏转发，是老马继续写作的最大能源！

正文完
数据库
发表至： 数据库
2020-12-12
 0
关于数据库:HSD校园行丨华为Serverless校园公开课活动华中科技大学站圆满收官
关于数据库:容器化-在-NFS-备份恢复-RadonDB-MySQL-集群数据
关于数据库:一段基本的win32项目示例
关于数据库:全面提升腾讯云数据库H1成绩公布