對比資料的痛苦

不知道你是否也像我一樣，在快樂編寫程式碼的時候，必須進行一些資料庫的資料對比工作。

誠然，一般的資料差異，比如是每一行的內容不同，市場上有比較成熟的 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，頁面顯示對應差異結果。

不過也基本可用，符合我們最初的設想。

小結

不知道你平時又是如何對比資料的呢？

如果你需要這個工具，可以關注【老馬嘯西風】，後臺回覆【對比】即可。

希望本文對你有幫助，如果有其他想法的話，也可以評論區和大家分享哦。

各位極客的點贊收藏轉發，是老馬持續寫作的最大動力！