一个简易的能直接运行同结构库表数据比对的工具类。
package org.example.sql;
import java.sql.*;
import java.util.*;
/**
* @Author: linK
* @Date: 2022/6/28 15:07
* @Description 测试两表比对
*/
public class Test {
@org.junit.Test
public void test() throws ClassNotFoundException, SQLException {
// 获取字段sql
ResultSet resultColumn = getResultColumn("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "199911", "test", "test");
// 获取表中所有字段集合
List<String> column = getColumn(resultColumn);
// 获取数据
ResultSet oldResultData = getResultData("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "199911", "test", "test");
ResultSet newResultData = getResultData("jdbc:mysql://localhost:3306/test?useSSL=false", "root", "199911", "test", "test1");
Map<String, Map<String, String>> oldMap = getData(oldResultData, column);
Map<String, Map<String, String>> newMap = getData(newResultData, column);
// 比对两表数据差异
List<String> diff = getDiff(oldMap, newMap);
diff.forEach(System.out::println);
}
/**
* 比对差异
*
* @param oldMap 旧表数据map
* @param newMap 新表数据map
* @return
*/
public List<String> getDiff(Map<String, Map<String, String>> oldMap, Map<String, Map<String, String>> newMap) {
List<String> diffList = new ArrayList<>();
oldMap.keySet().forEach(m -> {
// 字符串差异
StringBuilder diffStr = new StringBuilder("");
if (newMap.containsKey(m)) {
Map<String, String> oldData = oldMap.get(m);
Map<String, String> newData = newMap.get(m);
for (String old : oldData.keySet()) {
// 同时为空 跳过比对
if (oldData.get(old) == null && newData.get(old) == null) {
continue;
}
if (oldData.get(old) == null && newData.get(old) != null) {
diffStr.append("所在行数据:").append(m).append("，列名称：").append(old).append(" 旧值为空：").append(" 新值：").append(newData.get(old));
continue;
}
if (oldData.get(old) != null && newData.get(old) == null) {
diffStr.append("所在行数据:").append(m).append("，列名称：").append(old).append(" 旧值：").append(oldData.get(old)).append(" 新值为空");
continue;
}
// 比对列的数据
if (oldData.get(old).equals(newData.get(old))) {
// 结果相等跳过
} else {
diffStr.append("所在行数据:").append(m).append("，列名称：").append(old).append(" 旧值：").append(oldData.get(old)).append(" 新值：").append(newData.get(old));
}
}
// 数据比对信息不为空 添加进list
if (!Objects.isNull(diffStr)) {
diffList.add(diffStr.toString());
}
} else {
diffList.add(m + "行数据newMap为空");
}
});
return diffList;
}
public List<String> getColumn(ResultSet rs) throws SQLException {
ArrayList<String> column = new ArrayList<>();
while (rs.next()) {
column.add(rs.getString("column_name"));
}
return column;
}
/**
* 获取行数据
*
* @param result
* @param column
* @return
* @throws SQLException
*/
public Map<String, Map<String, String>> getData(ResultSet result, List<String> column) throws SQLException {
Map<String, Map<String, String>> stringMapHashMap = new HashMap<>();
ResultSetMetaData metaData = result.getMetaData();
while (result.next()) {
// 字段数据
HashMap<String, String> columnData = new HashMap<>();
column.forEach(m -> {
try {
columnData.put(m, result.getString(m));
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
// result.getString("cur_date") 暂时字符串截取
String cur_date = result.getString("cur_date").substring(0, 10);
stringMapHashMap.put(cur_date.concat(result.getString("city")), columnData);
}
return stringMapHashMap;
}
/**
* 获取字段结果集
*
* @param url
* @param user
* @param password
* @param db
* @param table
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public ResultSet getResultColumn(String url, String user, String password, String db, String table) throws ClassNotFoundException, SQLException {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
Connection conn = DriverManager.getConnection(url, user, password);
//3.通过数据库的连接操作数据库，实现增删改查
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select Table_name,column_name from information_schema.columns where table_schema= '" + db + "' and table_name= " + "'" + table + "'");
return rs;
}
/**
* 查询数据
*
* @param url
* @param user
* @param password
* @param db
* @param table
* @return
* @throws ClassNotFoundException
* @throws SQLException
*/
public ResultSet getResultData(String url, String user, String password, String db, String table) throws ClassNotFoundException, SQLException {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
Connection conn = DriverManager.getConnection(url, user, password);
//3.通过数据库的连接操作数据库，实现增删改查
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + db + "." + table);
return rs;
}
}
用到的maven依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
</dependencies>
