DiffKit -- 世上最牛且开源的表数据对比工具
1. DiffKit Introduction
1.1 Introduction
DiffKit Website: http://www.diffkit.org/index.html.
1.2 Compatibility
| Source | Sun JRE 1.5 | Sun JRE 1.6 | Java for Mac OS X 10.6 | Microsoft JVM | Apache Harmony 5.0/6.0 | OpenJDK 6 |
|---|---|---|---|---|---|---|
Oracle 10g | Y | Y | Y | ? | ? | ? |
IBM DB2 9.5 | Y | Y | Y | ? | ? | ? |
MySQL 5.1 | Y | Y | Y | ? | ? | ? |
H2 1.2.135 | Y | Y | Y | ? | ? | ? |
SQL Server 2008 | Y | Y | Y | ? | ? | ? |
PostgreSQL 9.x | Y | Y | Y | ? | ? | ? |
HyperSQL 2.0 | Y | Y | Y | ? | ? | ? |
MS Excel 97/2000/XP (.xls) | Y | Y | Y | ? | ? | ? |
MS Excel 2007 (.xlsx) | Y | Y | Y | ? | ? | ? |
HyperSQL 2.0 | Y | Y | Y | ? | ? | ? |
Sybase ASE 15.X | X | X | X | X | X | X |
SQLite | X | X | X | X | X | X |
Apache Derby | X | X | X | X | X | X |
Open Document SS (.ods) | X | X | X | X | X | X |
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);
}
}
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>
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());
}
}
}
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;
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;
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_);
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;
}
5.1.3 Bug
- 没太细研究，感觉不太行，反正是集合就一个，多个栏位报空指针
5.2 TableDiff
5.2.1 Introduction
- github上面的开源代码，
Github TableDif: https://github.com/wwmbes/TableDiff.
- 没细研究，代码好少，应该扩展性不强
6. Waken
在一秒钟内看到本质的人和花半辈子也看不清一件事本质的人，自然是不一样的命运。
扫一扫
643
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
