java 比较Java中的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2352865/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two tables in Java
提问by NETQuestion
I have to compare two tables and tell the user what's the difference between two tables.
我必须比较两个表并告诉用户两个表之间有什么区别。
Table1
表格1
------+---------
|Code | Label |
------+---------
|a1 | a1text |
------+---------
|b1 | b1text |
------+---------
|c1 | bartext1|
------+---------
|e1 | foo |
-----+---------
Table2
表2
------+---------
|Code | Label |
------+---------
|a1 | a1text |
------+---------
|b1 | b2text |
------+---------
|d1 | bartext2|
------+---------
|f1 | bar |
------+---------
Comparison Info
As you see in table1 for Code c1label is bartext1and for Code d1label is bartext2.
They are same expect last character. I have to put in my report they are same except last character. There are few rows in tables where there may be extra word or special character and its position is anywhere. Somehow I have to tell in the report both labels are same expect word is missing or there is a special character in one of them.Code is not important in report.
比较信息
正如您在表 1 中看到的代码c1标签是bartext1和代码d1标签是bartext2。他们是相同的期待最后一个字符。我必须在我的报告中输入它们除了最后一个字符外都是相同的。表格中很少有可能有多余单词或特殊字符的行，并且其位置在任何地方。不知何故，我必须在报告中告诉两个标签是相同的，期望缺少单词或其中一个中有特殊字符。代码在报告中并不重要。
More Info
This data is coming from Third party.Code is always unique, they are no duplicate Codes.
It may be possible that two codes have similar value
like
更多信息
此数据来自第三方。代码始终是唯一的，它们不是重复的代码。两个代码可能具有相似的值，例如
Code | Label
代码 | 标签
ER4 | i have a brother
ER4 | 我有个兄弟
WE3 | i have a brother
WE3 | 我有个兄弟
Expected Output should be
预期输出应该是
- Labels are different in two tables. Table1 label is : b1text and Tabl2 label is: b2text.
- Labels are different in two tables. Table1 label is : bartext1 and Tabl2 label is: bartext2.
- label foo is missing in Table2.
- label bar is missing in Table1.
- 两个表中的标签不同。表 1 标签是：b1text，表 2 标签是：b2text。
- 两个表中的标签不同。表 1 标签是：bartext1，表 2 标签是：bartext2。
- 表 2 中缺少标签 foo。
- 表 1 中缺少标签栏。
回答by duffymo
I'd create an abstraction for this Table object with an equals implementation that would hide all the details from clients. Java's an object-oriented language, so best to use objects for their raison d'etre.
我将为这个 Table 对象创建一个抽象，并使用一个 equals 实现来隐藏客户端的所有细节。Java 是一种面向对象的语言，因此最好使用对象作为其存在的理由。
回答by Michael Couck
This worked for me, please feel free to add salt to taste:
这对我有用，请随意加盐调味：
public final class ComparisonTest {
@Test
public void compare() throws Exception {
String url = "your.url";
String user = "your.user";
String password = "your.password";
// I am using Oracle here, but you can use any database
Connection connection = getConnection(url, user, password, OracleDriver.class);
ResultSet sourceResultSet = getResultSet(connection, "first_table");
ResultSet targetResultSet = getResultSet(connection, "second_table");
Map<Long, String> sourceIdHash = new HashMap<Long, String>();
Map<Long, String> targetIdHash = new HashMap<Long, String>();
try {
long rows = 0;
do {
if (sourceResultSet.next()) {
if (targetResultSet.next()) {
// Compare the lines
long sourceHash = hash(getRowValues(sourceResultSet, sourceResultSet.getMetaData()));
long targetHash = hash(getRowValues(targetResultSet, targetResultSet.getMetaData()));
sourceIdHash.put(sourceHash, sourceResultSet.getString(1));
targetIdHash.put(targetHash, targetResultSet.getString(1));
if (targetIdHash.containsKey(sourceHash)) {
targetIdHash.remove(sourceHash);
sourceIdHash.remove(sourceHash);
}
if (sourceIdHash.containsKey(targetHash)) {
sourceIdHash.remove(targetHash);
targetIdHash.remove(targetHash);
}
} else {
// Add the source row
long sourceHash = hash(getRowValues(sourceResultSet, sourceResultSet.getMetaData()));
sourceIdHash.put(sourceHash, sourceResultSet.getString(1));
}
} else {
if (targetResultSet.next()) {
// Add the target row
long targetHash = hash(getRowValues(targetResultSet, targetResultSet.getMetaData()));
targetIdHash.put(targetHash, targetResultSet.getString(1));
} else {
break;
}
}
if (rows++ % 10000 == 0) {
System.out.println("Rows : " + rows);
}
} while (true);
} finally {
closeAll(sourceResultSet);
closeAll(targetResultSet);
}
for (final Map.Entry<Long, String> mapEntry : sourceIdHash.entrySet()) {
if (targetIdHash.containsKey(mapEntry.getKey())) {
targetIdHash.remove(mapEntry.getKey());
continue;
}
System.out.println("Not in target : " + mapEntry.getValue());
}
for (final Map.Entry<Long, String> mapEntry : targetIdHash.entrySet()) {
if (sourceIdHash.containsKey(mapEntry.getKey())) {
sourceIdHash.remove(mapEntry.getKey());
continue;
}
System.out.println("Not in source : " + mapEntry.getValue());
}
System.out.println("In source and not target : " + sourceIdHash.size());
System.out.println("In target and not source : " + targetIdHash.size());
}
private ResultSet getResultSet(final Connection connection, final String tableName) {
String query = "select * from " + tableName + " order by pdb_key, organization_code, service_littera, day, resource_category";
return executeQuery(connection, query);
}
private Object[] getRowValues(final ResultSet resultSet, final ResultSetMetaData resultSetMetaData) throws SQLException {
List<Object> rowValues = new ArrayList<Object>();
for (int i = 2; i < resultSetMetaData.getColumnCount(); i++) {
rowValues.add(resultSet.getObject(i));
}
return rowValues.toArray(new Object[rowValues.size()]);
}
private final Connection getConnection(final String url, final String user, final String password, final Class<? extends Driver> driverClass) {
try {
DriverManager.registerDriver(driverClass.newInstance());
return DriverManager.getConnection(url, user, password);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private final ResultSet executeQuery(final Connection connection, final String query) {
try {
return connection.createStatement().executeQuery(query);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private final Long hash(final Object... objects) {
StringBuilder builder = new StringBuilder();
for (Object object : objects) {
builder.append(object);
}
return hash(builder.toString());
}
public Long hash(final String string) {
// Must be prime of course
long seed = 131; // 31 131 1313 13131 131313 etc..
long hash = 0;
char[] chars = string.toCharArray();
for (int i = 0; i < chars.length; i++) {
hash = (hash * seed) + chars[i];
}
return Long.valueOf(Math.abs(hash));
}
private void closeAll(final ResultSet resultSet) {
Statement statement = null;
Connection connection = null;
try {
if (resultSet != null) {
statement = resultSet.getStatement();
}
if (statement != null) {
connection = statement.getConnection();
}
} catch (Exception e) {
e.printStackTrace();
}
close(resultSet);
close(statement);
close(connection);
}
private void close(final Statement statement) {
if (statement == null) {
return;
}
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void close(final Connection connection) {
if (connection == null) {
return;
}
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void close(final ResultSet resultSet) {
if (resultSet == null) {
return;
}
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
回答by NETQuestion
There's an Open Source Java framework that does that:
有一个开源 Java 框架可以做到这一点：
www.diffkit.org
www.diffkit.org
