DiffKit -- 世上最牛且开源的表数据对比工具

---

---

1. DiffKit Introduction

1.1 Introduction

DiffKit Website: http://www.diffkit.org/index.html.

1.2 Compatibility

1.3 Download

Download: https://code.google.com/archive/p/diffkit/downloads.

2. Quick Start

2.1 Demo01测试excel

• 比较两个csv文件
  java -jar …/diffkit-app.jar -planfiles test9.plan.xml
  前两个是对比的csv，第三个是产出的结果.fiff file
  
  test9.sink.diff
  

2.2 Demo02测试excel other function

2.3 Demo03连接DB

• 先测试一个mssql
  java -jar diffkit-app.jar -test flavors=SQLSERVER
  
• 结合源码，查看配置，如果报警，请继续往下看
  

2.4 Demo04一个DB两个Table

• 对比的table name
  
• 数据库连接信息
  
• 执行命令
  java -jar …/diffkit-app.jar -planfiles test10.plan.xml,dbConnectionInfo.xml

2.5 Demo05两个DB两个Table

• 配置对比的两张表名
  
• 配置对应的数据库连接信息
  
• 执行命令
  java -jar …/diffkit-app.jar -planfiles test18.plan.xml,
test18.lhs.dbConnectionInfo.xml,test18.rhs.dbConnectionInfo.xml

2.6 Generating DB patch files

• xml中添加属性
  
• 执行命令
  java -jar …/diffkit-app.jar -planfiles test26.plan.xml,dbConnectionInfo.xml
• 如何让两表数据同比
  

3. Running prompt

• 原来这个功能java也可以实现，基本都是每次安装包的时候会遇到，执行包，然后提示一些命令参数
  
• 需要这个包
  

public class DKApplication {
   private static final String APPLICATION_NAME = "diffkit-app";

   private static final String VERSION_OPTION_KEY = "version";
   private static final String HELP_OPTION_KEY = "help";
   private static final String TEST_OPTION_KEY = "test";
   private static final String PLAN_FILE_OPTION_KEY = "planfiles";
   private static final String ERROR_ON_DIFF_OPTION_KEY = "errorOnDiff";
   private static final String DEMO_DB_OPTION_KEY = "demoDB";
   private static final Options OPTIONS = new Options();

   private static final String LOGBACK_FILE_NAME = "logback.xml";
   private static final String LOGBACK_CONFIGURATION_FILE_PROPERTY_KEY = "logback.configurationFile";

   private static Logger _systemLog;

   static {
      OptionGroup optionGroup = new OptionGroup();
      optionGroup.addOption(new Option(VERSION_OPTION_KEY,
         "print the version information and exit"));
      optionGroup.addOption(new Option(HELP_OPTION_KEY, "print this message"));

      OptionBuilder.hasOptionalArgs(2);
      OptionBuilder.withArgName("[cases=?,] [flavors=?,]");
      OptionBuilder.withDescription("run TestCases");
      OPTIONS.addOption(OptionBuilder.create(TEST_OPTION_KEY));

      OptionBuilder.withArgName("file1[,file2...]");
      OptionBuilder.hasArg();
      OptionBuilder.withDescription("perform diff using given file(s) for plan");
      optionGroup.addOption(OptionBuilder.create(PLAN_FILE_OPTION_KEY));
      optionGroup.addOption(new Option(
         ERROR_ON_DIFF_OPTION_KEY,
         "exit with error status code (-1) if diffs are detected. otherwise will always exit with 0 unless an operating Exception was encountered"));
      optionGroup.addOption(new Option(DEMO_DB_OPTION_KEY,
         "run embedded demo H2 database"));
      OPTIONS.addOptionGroup(optionGroup);
   }

   public static void main(String[] args_) {
      initialize();
      Logger systemLog = getSystemLog();
      systemLog.debug("args_->{}", Arrays.toString(args_));

      try {
         CommandLineParser parser = new PosixParser();
         CommandLine line = parser.parse(OPTIONS, args_);
         if (line.hasOption(VERSION_OPTION_KEY))
            printVersion();
         else if (line.hasOption(HELP_OPTION_KEY))
            printHelp();
         else if (line.hasOption(TEST_OPTION_KEY))
            runTestCases(line.getOptionValues(TEST_OPTION_KEY));
         else if (line.hasOption(PLAN_FILE_OPTION_KEY))
            runPlan(line.getOptionValue(PLAN_FILE_OPTION_KEY),
               line.hasOption(ERROR_ON_DIFF_OPTION_KEY));
         else if (line.hasOption(DEMO_DB_OPTION_KEY))
            runDemoDB();
         else
            printInvalidArguments(args_);
      }
      catch (ParseException e_) {
         System.err.println(e_.getMessage());
      }
      catch (Throwable e_) {
         Throwable rootCause = ExceptionUtils.getRootCause(e_);
         if (rootCause == null)
            rootCause = e_;
         if ((rootCause instanceof DKUserException)
            || (rootCause instanceof FileNotFoundException)) {
            systemLog.info(null, e_);
            DKRuntime.getInstance().getUserLog().info("error->{}", rootCause.getMessage());
         }
         else
            systemLog.error(null, e_);
      }
   }

   private static void printVersion() {
      DKRuntime.getInstance().getUserLog().info(
         "version->" + DKDistProperties.getPublicVersionString());
      System.exit(0);
   }

   private static void printInvalidArguments(String[] args_) {
      DKRuntime.getInstance().getUserLog().info(
         String.format("Invalid command line arguments: %s", Arrays.toString(args_)));
      printHelp();
   }

   private static void printHelp() {
      // automatically generate the help statement
      HelpFormatter formatter = new HelpFormatter();
      formatter.printHelp("java -jar diffkit-app.jar", OPTIONS);
   }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

4. Code

4.1 Run Demo

• 本地测试DB能通过，那肯定是祖坟冒青烟
  java -jar diffkit-app.jar -test flavors=ORACLE
  
• 我都没传值，就直接获取，你猜会怎样
  
• 竟然是number type，呜呜呜
  
  java -jar diffkit-app.jar -test flavors=ORACLE cases=1
  

4.2 Connect DB

• 将需要解析的xml复制到DKApplication同级目录（connectionInfo和plan）
  
• 修改配置，连接自己的数据库，再配置真实的tablename
  
• 通过runDemoDB,找到如何解析xml，以及创建connect
  
  DKDBConnectionInfo >> DKDatabase >> DKDBTable

4.3 整理之后的Code

• 部分包在maven repo中翻了一下，有的就写进了pom中，整合后还有这些
  

• 下载到本地，再mvn install h2的包
  mvn install:install-file -Dfile=F:/download/h2-1.0.20061103.jar -DgroupId=org.h2 -DartifactId=h2 -Dversion=1.0.2 -Dpackaging=jar

// org.h2我是自己下载到本地，在mvn install
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.dtflys.forest</groupId>
            <artifactId>forest-spring-boot-starter</artifactId>
            <version>1.5.26</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>
        <dependency>
            <groupId>commons-cli</groupId>
            <artifactId>commons-cli</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils-core</artifactId>
            <version>1.8.3</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-collections/commons-collections -->
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>3.2.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.thoughtworks.paranamer/paranamer -->
        <dependency>
            <groupId>com.thoughtworks.paranamer</groupId>
            <artifactId>paranamer</artifactId>
            <version>2.8</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>

        <dependency>
            <groupId>org.apache.kafka</groupId>
            <artifactId>kafka-clients</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.h2</groupId>
            <artifactId>h2</artifactId>
            <version>1.0.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <version>1.5.22.RELEASE</version>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

    private static final Logger LOG = LoggerFactory.getLogger(DKApplication.class);
    
    //在DKApplication直接进行测试
    public static void main(String[] args)throws Exception{

        final String CONNECTION_INFO_CONFIG_FILE_PATH = "F:\\download\\diffkit\\diffkit-src-0.9.0\\src\\org\\diffkit\\diff\\conf\\dbConnectionInfo.xml";

       // DKDBConnectionInfo connectionInfo = ['MSSQL', DKDBFlavor.SQLSERVER,"xxx", "xxx", 1433, "xxx", 'xxx']
        DKDBConnectionInfo connectionInfo = (DKDBConnectionInfo) DKSpringUtil.getBean(
                "connectionInfo", new String[] { CONNECTION_INFO_CONFIG_FILE_PATH },
                DKDemoDB.class.getClassLoader());
        if (connectionInfo == null)
            throw new RuntimeException(String.format(
                    "cannot find connectionInfo in Spring config file->%s",
                    CONNECTION_INFO_CONFIG_FILE_PATH));
        LOG.info("connectionInfo->{}", connectionInfo);
        DKDatabase connectionSource = new DKDatabase(connectionInfo);
        Boolean aBoolean = connectionSource.canConnect();
        System.out.println("can connect this database :" + aBoolean);
        DKDBTableDataAccess tableDataAccess = new DKDBTableDataAccess(connectionSource);


        final String Compare_table = "F:\\download\\diffkit\\diffkit-src-0.9.0\\src\\org\\diffkit\\diff\\conf\\test10.plan.xml";
        DKMagicPlan plan = (DKMagicPlan) DKSpringUtil.getBean("plan", new String[] { Compare_table },
                DKApplication.class.getClassLoader());
        LOG.info("plan->{}", plan);

        //DKMagicPlan plan = []
        //String lhsDBTableName = leftTableName;
        //String rhsDBTableName = rightTaleName;

        String lhsDBTableName = plan.getLhsDBTableName();
        String rhsDBTableName = plan.getRhsDBTableName();
        System.out.println(lhsDBTableName+"---"+rhsDBTableName);

        DKDBTable lhsDBTable = tableDataAccess.getTable(lhsDBTableName);
        DKDBTable rhsDBTable = tableDataAccess.getTable(rhsDBTableName);
        System.out.println(lhsDBTable.toString());

        DKTableModel lhsTableModel = DKTableModelUtil.createDefaultTableModel(connectionSource.getFlavor(), lhsDBTable, null);
        DKTableModel rhsTableModel = DKTableModelUtil.createDefaultTableModel(connectionSource.getFlavor(), rhsDBTable, null);
        DKDBSource lhsSource = new DKDBSource(lhsDBTableName, null, connectionSource, lhsTableModel, null, null);
        DKDBSource rhsSource = new DKDBSource(rhsDBTableName, null, connectionSource, rhsTableModel, null, null);

        DKAutomaticTableComparison tableComparison = new DKAutomaticTableComparison( lhsSource, rhsSource, DKDiff.Kind.BOTH, null, null,
                null, Long.MAX_VALUE, null, null);
        DKStandardTableComparison dkStandardTableComparison = tableComparison.buildStandardComparison();
        DKColumnComparison[] dkColumnComparisons = dkStandardTableComparison.getMap();
        for (DKColumnComparison dkColumnComparison : dkColumnComparisons) {
            System.out.println(dkColumnComparison);
        }

        //DKSink sink = plan.getSink();
        DKListSink sink = new DKListSink();

        Map<UserKey, Object> userDictionary = new HashMap<UserKey, Object>();
        //userDictionary.put(UserKey.PLAN_FILES, planFilesString_);
        DKContext diffContext = doDiff(lhsSource, rhsSource, sink, tableComparison,userDictionary);
        LOG.info(sink.generateSummary(diffContext));

        List<DKDiff> diffs = sink.getDiffs();
        for (DKDiff diff : diffs) {
            if(diff instanceof DKColumnDiff){
                DKColumnDiff result = (DKColumnDiff)diff;
                System.out.println("Column name is : " + result.getColumnName() + " || row number is "+result.getRow().getRowStep()+" || left is "+ result.getLhs() + " <==> right is "+ result.getRhs());
            }else{
                DKRowDiff result = (DKRowDiff)diff;
                if (result.getSide().equals("left")){
                    System.out.println("the row diff on : [" + lhsDBTableName+"] , and row number => "+ result.getRowStep());
                }else{
                    System.out.println("the row diff on : [" + rhsDBTableName+"] , and row number => "+ result.getRowStep());
                }
                Object[] row = result.getRow();
                StringBuffer stringBuff = new StringBuffer();
                stringBuff.append("[");
                for (Object o : row) {
                    if (o instanceof Integer){
                        stringBuff.append(((Integer) o).intValue()).append(",");
                    }
                    if (o instanceof  String){
                        stringBuff.append(o).append(",");
                    }else{

                    }
                }
                stringBuff.delete(stringBuff.length()-1,stringBuff.length());
                stringBuff.append("]");
                System.out.println("row diff context :" + stringBuff.toString());
            }
        }

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

4.4 数据类型的不足，加以填充

• 毕竟太久没人更新了，市面上也没找到更好的包，数据库这10年都添加了很多新的数据类型，代码这边也要跟着调整。

4.4.1 数据类型映射关系

Official Document: mssql-data-type-mapping-for-oracle-publishers.

4.4.2 DKDBType.class

• 数据类型都在这个类里面，如果是大家共用的，就写在前面，如果是某个数据库特有的，格式则是(_databasetype_datatype)

public enum DKDBType {

   ARRAY, BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR(false), CLOB(true), DATALINK(true), DATE, DECIMAL(
      false), DISTINCT, DOUBLE, FLOAT(false), INTEGER, JAVA_OBJECT, LONGNVARCHAR(true), LONGVARBINARY(
      true), LONGVARCHAR, NCHAR(false), NCLOB, NULL, NUMERIC(false), NVARCHAR(false), OTHER, REAL, REF(
      true), ROWID, SMALLINT, SQLXML, STRUCT, TIME, TIMESTAMP(true), TINYINT, VARBINARY(
      true), VARCHAR(false), _H2_IDENTITY, _H2_UUID, _H2_VARCHAR_IGNORECASE(false), _DB2_LONG_VARCHAR_FOR_BIT_DATA(
      true), _DB2_VARCHAR_00_FOR_BIT_DATA(true), _DB2_CHAR_00_FOR_BIT_DATA, _DB2_LONG_VARCHAR(
      true), _DB2_LONG_VARGRAPHIC(true), _DB2_GRAPHIC, _DB2_VARGRAPHIC, _DB2_DECFLOAT(
      true), _DB2_XML(true), _DB2_DBCLOB, _ORACLE_INTERVALDS(true), _ORACLE_INTERVALYM(
      true), _ORACLE_TIMESTAMP_WITH_LOCAL_TIME_ZONE, _ORACLE_TIMESTAMP_WITH_TIME_ZONE(
      true), _ORACLE_NUMBER, _ORACLE_LONG_RAW, _ORACLE_RAW, _ORACLE_LONG,_ORACLE_NVARCHAR2(false), _ORACLE_VARCHAR(false), _ORACLE_VARCHAR2(
      false), _MYSQL_BOOL, _MYSQL_TINYINT_UNSIGNED, _MYSQL_BIGINT_UNSIGNED, _MYSQL_LONG_VARBINARY(
      true), _MYSQL_MEDIUMBLOB, _MYSQL_LONGBLOB, _MYSQL_TINYBLOB, _MYSQL_LONG_VARCHAR(
      true), _MYSQL_MEDIUMTEXT, _MYSQL_LONGTEXT, _MYSQL_TEXT, _MYSQL_TINYTEXT(true), _MYSQL_INTEGER_UNSIGNED(
      true), _MYSQL_INT, _MYSQL_INT_UNSIGNED, _MYSQL_MEDIUMINT, _MYSQL_MEDIUMINT_UNSIGNED(
      true), _MYSQL_SMALLINT_UNSIGNED, _MYSQL_DOUBLE_PRECISION, _MYSQL_ENUM, _MYSQL_SET(
      true), _MYSQL_DATETIME, _MYSQL_DECIMAL_UNSIGNED, _SQLSERVER_SQL_VARIANT, _SQLSERVER_UNIQUEIDENTIFIER(
      true), _SQLSERVER_NTEXT(true), _SQLSERVER_XML, _SQLSERVER_SYSNAME, _SQLSERVER_DATETIME2, _SQLSERVER_DATETIMEOFFSET(
      true), _SQLSERVER_TINYINT_IDENTITY(true), _SQLSERVER_BIGINT_IDENTITY, _SQLSERVER_IMAGE(
      true), _SQLSERVER_TEXT, _SQLSERVER_NUMERIC00_IDENTITY, _SQLSERVER_MONEY, _SQLSERVER_SMALLMONEY(
      true), _SQLSERVER_DECIMAL00_IDENTITY, _SQLSERVER_INT, _SQLSERVER_INT_IDENTITY(true), _SQLSERVER_SMALLINT_IDENTITY(
      true), _SQLSERVER_DATETIME, _SQLSERVER_SMALLDATETIME, _POSTGRES_BOOL, _POSTGRES_BYTEA(
      true), _POSTGRES_NAME, _POSTGRES_INT8, _POSTGRES_BIGSERIAL, _POSTGRES_INT2(true), _POSTGRES_INT2VECTOR(
      true), _POSTGRES_INT4, _POSTGRES_SERIAL, _POSTGRES_REGPROC, _POSTGRES_TEXT(true), _POSTGRES_OID(
      true), _POSTGRES_TID, _POSTGRES_XID, _POSTGRES_CID, _POSTGRES_OIDVECTOR, _POSTGRES_XML(
      true), _POSTGRES_SMGR, _POSTGRES_POINT, _POSTGRES_LSEG, _POSTGRES_PATH(true), _POSTGRES_BOX(
      true), _POSTGRES_POLYGON, _POSTGRES_LINE, _POSTGRES_FLOAT4(true), _POSTGRES_FLOAT8, _POSTGRES_ABSTIME(
      true), _POSTGRES_RELTIME, _POSTGRES_TINTERVAL(true), _POSTGRES_UNKNOWN, _POSTGRES_CIRCLE(
      true), _POSTGRES_MONEY, _POSTGRES_MACADDR, _POSTGRES_INET(true), _POSTGRES_CIDR, _POSTGRES_ACLITEM(
      true), _POSTGRES_BPCHAR, _POSTGRES_TIMESTAMPTZ(true), _POSTGRES_TIMETZ, _POSTGRES_VARBIT(
      true), _POSTGRES_UUID, _POSTGRES_TSVECTOR(true), _POSTGRES_GTSVECTOR(true), _POSTGRES_TSQUERY(
      true), _POSTGRES_TXID_SNAPSHOT, _POSTGRES_CSTRING(true), _POSTGRES_ANY, _POSTGRES_ANYARRAY(
      true), _POSTGRES_VOID, _POSTGRES_INTERNAL(true), _POSTGRES_ANYELEMENT(true), _POSTGRES_ANYNONARRAY(
      true), _POSTGRES_ANYENUM(true), _POSTGRES_INTERVAL, _POSTGRES_RECORD(true), _POSTGRES_CARDINAL_NUMBER(
      true), _POSTGRES_CHARACTER_DATA(true), _POSTGRES_SQL_IDENTIFIER(true), _HYPERSQL_CHARACTER(
      true), _HYPERSQL_VARCHAR_IGNORECASE; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

4.4.3 DKTableModelUtil.class

• 给数据类型一个映射的状态。来保证源端和目标端的数据类型一致。

public static DKColumnModel.Type getModelType(DKDBType dbType_) {
   switch (dbType_) {
   case INTEGER:
      return DKColumnModel.Type.INTEGER;
   case BIGINT:
      return DKColumnModel.Type.INTEGER;
   case REAL:
      return DKColumnModel.Type.REAL;
   case FLOAT:
      return DKColumnModel.Type.REAL;
   case DOUBLE:
      return DKColumnModel.Type.REAL;
   case _POSTGRES_FLOAT4:
      return DKColumnModel.Type.REAL;
   case _POSTGRES_FLOAT8:
      return DKColumnModel.Type.REAL;
   case NUMERIC:
      return DKColumnModel.Type.DECIMAL;
   case DECIMAL:
      return DKColumnModel.Type.DECIMAL;
   case BIT:
      return DKColumnModel.Type.INTEGER;
   case TINYINT:
      return DKColumnModel.Type.INTEGER;
   case SMALLINT:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_INT:
      return DKColumnModel.Type.INTEGER;
   case _POSTGRES_INT2:
      return DKColumnModel.Type.INTEGER;
   case _POSTGRES_INT4:
      return DKColumnModel.Type.INTEGER;
   case _POSTGRES_INT8:
      return DKColumnModel.Type.INTEGER;
   case _SQLSERVER_INT:
      return DKColumnModel.Type.INTEGER;
   case _SQLSERVER_INT_IDENTITY:
      return DKColumnModel.Type.INTEGER;
   case _SQLSERVER_BIGINT_IDENTITY:
      return DKColumnModel.Type.INTEGER;
   case _SQLSERVER_SMALLINT_IDENTITY:
      return DKColumnModel.Type.INTEGER;
   case _SQLSERVER_TINYINT_IDENTITY:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_BIGINT_UNSIGNED:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_INT_UNSIGNED:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_MEDIUMINT_UNSIGNED:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_SMALLINT_UNSIGNED:
      return DKColumnModel.Type.INTEGER;
   case _MYSQL_TINYINT_UNSIGNED:
      return DKColumnModel.Type.INTEGER;
   case _ORACLE_NUMBER:
      return DKColumnModel.Type.DECIMAL;
   case _MYSQL_DECIMAL_UNSIGNED:
      return DKColumnModel.Type.DECIMAL;
   case CHAR:
      return DKColumnModel.Type.STRING;
   case NCHAR:
      return DKColumnModel.Type.STRING;
   case VARCHAR:
      return DKColumnModel.Type.STRING;
   case NVARCHAR:
      return DKColumnModel.Type.STRING;
   case LONGVARCHAR:
      return DKColumnModel.Type.STRING;
   case _ORACLE_VARCHAR:
      return DKColumnModel.Type.STRING;
   case _ORACLE_VARCHAR2:
      return DKColumnModel.Type.STRING; 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

4.4.4 DKAutomaticTableComparison.class

• 映射后数据类型的比对

private static DKDiffor getConvertingDiffor(DKColumnModel lhsColumn_,
                                            DKColumnModel rhsColumn_,
                                            DKDiffor baseDiffor_) {
   DKColumnModel.Type lhsType = lhsColumn_.getType();
   DKColumnModel.Type rhsType = rhsColumn_.getType();
   if (lhsType == rhsType)
      return baseDiffor_;
   if ((lhsType == DKColumnModel.Type.INTEGER) && (rhsType == DKColumnModel.Type.STRING))
      return new DKConvertingDiffor(null, Long.class, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.STRING) && (rhsType == DKColumnModel.Type.INTEGER))
      return new DKConvertingDiffor(Long.class, null, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.REAL) && (rhsType == DKColumnModel.Type.STRING))
      return new DKConvertingDiffor(null, Double.class, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.STRING) && (rhsType == DKColumnModel.Type.REAL))
      return new DKConvertingDiffor(Double.class, null, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.DECIMAL) && (rhsType == DKColumnModel.Type.STRING))
      return new DKConvertingDiffor(null, BigDecimal.class, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.STRING) && (rhsType == DKColumnModel.Type.DECIMAL))
      return new DKConvertingDiffor(BigDecimal.class, null, baseDiffor_);
   else if ((lhsType == DKColumnModel.Type.MIXED) && (rhsType == DKColumnModel.Type.STRING))
      return new DKConvertingDiffor(String.class, null, baseDiffor_); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

5. Other Method

5.1 TableDif

5.1.1 Introduction

• gitee上面的开源代码，
  Gitee TableDif: https://gitee.com/wowtools/tabledif/tree/master.
  
  

5.1.2 Code

    @Test
    void  findDiff(){
        //定义表A
        Table tableA = new Table(getConn("10.xxxx","xxx","xxx","xxxx"),
                "xxxx",
                new Field("xxx", (rs, idx) -> {
                    return rs.getObject(idx);
                }),
                false,
                new Field[]{
                        new Field("xxxx", (rs, idx) -> {
                            return rs.getObject(idx);
                        }),
                        new Field("xxxx", (rs, idx) -> {
                            return rs.getObject(idx);
                        }),
                }
        );
        //定义表B
        Table tableB = new Table(getConn("xxxx","xxx","xxx","xxxx"),
                "xxxx",
                new Field("xxx", (rs, idx) -> {
                    return rs.getObject(idx);
                }),
                false,
                new Field[]{
                        new Field("xxxx", (rs, idx) -> {
                            return rs.getObject(idx);
                        }),
                        new Field("xxxx", (rs, idx) -> {
                            return rs.getObject(idx);
                        }),
                }
        );
        //定义比较器
        TableDif dif = new TableDif() {
            @Override
            public Comparator getKeyComparator() {
                return Comparator.comparingInt(o -> (int) o);
            }

            @Override
            public FileValueEquals[] getFieldsComparator() {
                //比较每个字段的比较器数组
                return new FileValueEquals[]{
                        (a, b) -> {
                            if (a == null) {
                                return b == null;
                            } else {
                                return a.equals(b);
                            }
                        }
                };
            }

            //各类状态的操作实现,这里把状态和id打印出来，你也可以把它写入数据库之类
            @Override
            public void notInTableA(Object key, Object[] rowB) {
                System.out.println("notInTableA " + key);
            }

            @Override
            public void notInTableB(Object key, Object[] rowA) {
                System.out.println("notInTableB " + key);
            }

            @Override
            public void difAb(Object key, Object[] rowA, Object[] rowB, int difIdx) {
                System.out.println("difAb " + key);
            }

            @Override
            public void equal(Object key, Object[] rowA) {
                System.out.println("equal " + key);
            }
        };
        TableDifFinder.find(tableA, tableB, dif);
    }


    private Connection getConn(String ip,String database,String username,String password) {
        String url = "jdbc:sqlserver://"+ip+":1433;DatabaseName="+database;
        Connection connection;
        try {
            String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);//setFetchSize的用法在各种数据库中略有不同，注意修改。postgresql需要setAutoCommit(false)
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return connection;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

5.1.3 Bug

• 没太细研究，感觉不太行，反正是集合就一个，多个栏位报空指针
  

5.2 TableDiff

5.2.1 Introduction

• github上面的开源代码，
  Github TableDif: https://github.com/wwmbes/TableDiff.
  

• 没细研究，代码好少，应该扩展性不强

6. Waken

         在一秒钟内看到本质的人和花半辈子也看不清一件事本质的人，自然是不一样的命运。