java对比两个库之间差异
package com.ruoyi.shht;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
/**
* @Title: CompareUtil
* @Author cxf
* @Package java.com.ruoyi.shht
* @Date 2024/7/19 11:31
* @description: 对比数据库
*/
public class CompareUtils {
static String driver = "com.mysql.cj.jdbc.Driver";
static String url = "jdbc:mysql://HOST:3306/DATABASE?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true";
static class Config {
String host;
String userName;
String password;
String dbSchema;
public Config(String host, String userName, String password, String dbSchema) {
this.host = host;
this.userName = userName;
this.password = password;
this.dbSchema = dbSchema;
}
}
static class Table {
//表名称
public String tableName;
//字段名称+字段类型
public HashMap<String,String> column;
//字段名称+字段注解
public HashMap<String,String> columnComment;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public HashMap<String, String> getColumn() {
return column;
}
public void setColumn(HashMap<String, String> column) {
this.column = column;
}
public HashMap<String, String> getColumnComment() {
return columnComment;
}
public void setColumnComment(HashMap<String, String> columnComment) {
this.columnComment = columnComment;
}
}
private static Config getConfig(String name){
Map<String, Config> map = new HashMap<>();
Config local = new Config("localhost", "root", "mysql","cxcmp");
Config local_test = new Config("localhost", "root", "mysql","cxcmp_test");
map.put("local",local);
map.put("local_test",local_test);
return map.get(name);
}
public static void main(String[] args) throws Exception {
compareTables(getConfig("local"),getConfig("local_test"));
writeFile();
}
public static Connection getTransaction(Config config) throws Exception {
Class.forName(driver);
Connection conn = DriverManager.getConnection(
url.replace("HOST", config.host).replace("DATABASE", config.dbSchema), config.userName, config.password);
if (conn != null)System.out.println("主机:" + config.host + " 数据库加载成功!");
return conn;
}
public static StringBuffer[] sb = {new StringBuffer(), new StringBuffer(), new StringBuffer(),
new StringBuffer(), new StringBuffer(), new StringBuffer(),new StringBuffer()};
/**
* 初始化 比对文件提示信息
*/
public static void initSbString(Config c1, Config c2){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sb[0].append("对比时间:").append(sdf.format(new Date())).append(" 对比库:")
.append(c1.host).append(":").append(c1.dbSchema).append(" -> ").append(c2.host).append(":").append(c2.dbSchema)
.append("\r\n");
sb[1].append("1、").append(c1.host).append("存在,").append(c2.host).append("不存在的表:\r\n");
sb[2].append("2、").append(c1.host).append("不存在,").append(c2.host).append("存在的表:\r\n");
sb[3].append("3、").append(c1.host).append("存在,").append(c2.host).append("不存在的字段:\r\n");
sb[4].append("4、").append(c1.host).append("不存在,").append(c2.host).append("存在的字段:\r\n");
sb[5].append("5、表和字段都相同,但字段类型不同的内容:\r\n");
sb[6].append("6、表和字段、字段类型都相同,但字段注解不同的内容:\r\n");
}
/**
* 比对两个库的数据表,包括表名、字段名、字段类型、字段注解
* @param c1
* @param c2
* @throws Exception
*/
public static void compareTables(Config c1, Config c2) throws Exception {
// 初始化SubString[]
initSbString(c1,c2);
// 生产数据库连接
Connection connection_product = getTransaction(c1);
Map<String, Table> map_product = getTables(connection_product,c1.dbSchema);
// 开发数据库连接
Connection connection_develop = getTransaction(c2);
Map<String, Table> map_develop = getTables(connection_develop,c2.dbSchema);
System.out.println("START----开始比对"+ c1.host +" 库");
// 遍历开发库Map
for (Iterator<String> iter_table = map_develop.keySet().iterator(); iter_table
.hasNext();) {
String key_table = (String) iter_table.next();
Table table_develop = map_develop.get(key_table);// 获得开发库中的表
Table table_product = map_product.get(key_table);// 尝试从生产库中获得同名表
if (table_product == null) { // 如果获得表为空,说明开发存在,生产不存在
append(table_develop, null, 2);
} else { // 表相同,判断字段、字段类型、字段注解
for (Iterator<String> column_develop = table_develop.column.keySet().iterator(); column_develop
.hasNext();) {
String key_column = (String) column_develop.next();
String value_develop = table_develop.column.get(key_column);// 获得开发库中的列
String value_column = table_product.column.get(key_column);// 尝试从生产库中获得同名列
if (value_column == null) {// 如果列名为空,说明开发存在,生产不存在
append(table_develop, key_column, 4);
} else {// 说明两者都存在
if (!value_column.equals(value_develop))// 字段类型不一致
append(table_develop, key_column, 5);
String comment_product = table_product.columnComment.get(key_column);// 获得生产库中的字段注解
String develop_product = table_develop.columnComment.get(key_column);// 尝试从开发库中获得同名字段注解
if (!comment_product.equals(develop_product))// 字段注解不一致
append(table_develop, key_column, 6);
}
}
}
}
System.out.println("END----结束比对"+ c1.host +"库");
System.out.println("START----开始比对"+ c2.host +"库");
// 遍历生产库Map
for (Iterator<String> iter_table = map_product.keySet().iterator(); iter_table
.hasNext();) {
String key_table = (String) iter_table.next();
Table table_product = map_product.get(key_table);// 从生产库中获得同名表
Table table_develop = map_develop.get(key_table);// 尝试获得开发库中的表
if (table_develop == null) { // 如果获得表为空,说明生产存在,开发不存在
append(table_product, null, 1);
} else { // 表相同,判断字段、字段类型、字段注解
for (Iterator<String> column_product = table_product.getColumn().keySet().iterator(); column_product
.hasNext();) {
String key_column = (String) column_product.next();
String value_column = table_product.column.get(key_column);// 获得生产库中的列
String value_develop = table_develop.column.get(key_column);// 尝试从开发库中获得同名列
if (value_develop == null) {// 如果列名为空,说明生产存在,开发不存在
append(table_develop, key_column, 3);
}
// 字段相等 类型或者注解不相等上一步已经比对过,这里无需重复比对。
}
}
}
System.out.println("END----结束比对"+ c2.host +"库");
}
/**
* 封装TABLE数据
* @param connection
* @param tableSchema
* @return
* @throws Exception
*/
public static Map<String, Table> getTables(Connection connection, String tableSchema)
throws Exception {
String sSql = "SELECT \n" +
" table_name AS tableName,\n" +
" GROUP_CONCAT(column_name) AS columnName,\n" +
" GROUP_CONCAT(column_type) AS columnType,\n" +
" GROUP_CONCAT(IF(column_comment=\"\",\"无\",column_comment)) AS columnComment\n" +
"FROM\n" +
" information_schema.`COLUMNS` \n" +
"WHERE table_schema = '"+tableSchema+"' \n" +
"GROUP BY tableName\n" +
"ORDER BY tableName;";
Statement statement = connection.createStatement();//创建Statement对象
ResultSet rs = statement.executeQuery(sSql);
System.out.println("sql: "+sSql);
Map<String, Table> map = new HashMap<String, Table>();
while (rs.next()) {
Table table = new Table();
HashMap<String,String> columnMap = new HashMap<>();
HashMap<String,String> columnCommentMap = new HashMap<>();
table.setTableName(rs.getString("tableName"));
if("cardinfo".equals(table.getTableName())){
// 表太大,跳过
continue;
}
//System.out.println("tableName: "+table.getTableName());
String columnName = rs.getString("columnName");
String columnType = rs.getString("columnType");
String columnComment = rs.getString("columnComment");
String columnName_ [] = columnName.split(",");
String columnType_ [] = columnType.split(",");
String columnComment_ [] = columnComment.split(",");
for(int i=0;i<columnName_.length;i++){
columnMap.put(columnName_[i],columnType_[i]);
columnCommentMap.put(columnName_[i],columnComment_[i]);
}
table.setColumn(columnMap);
table.setColumnComment(columnCommentMap);
map.put(rs.getString("tableName"), table);
}
if (rs!=null)
rs.close();
connection.close();
return map;
}
/**
* 封装提示信息 (追加到满足条件的StringBuffer)
* @param table
* @param column
* @param flag
* @throws Exception
*/
public static void append(Table table, String column, int flag)
throws Exception {
switch (flag) {
case 1:
sb[1].append(table.getTableName() + "\r\n");
break;
case 2:
sb[2].append(table.getTableName() + "\r\n");
break;
case 3:
sb[3].append(table.getTableName() + "["+ column+"]\r\n");
break;
case 4:
sb[4].append(table.getTableName() + "["+ column+"]\r\n");
break;
case 5:
sb[5].append(table.getTableName() + "["+ column+"]["+table.column.get(column)+"]\r\n");
break;
case 6:
sb[6].append(table.getTableName() + "["+ column+"]["+table.columnComment.get(column)+"]\r\n");
break;
}
}
/**
* 写出比对结果到文件
* 将StringBuffer中的值写入文件中
* @throws Exception
*/
public static void writeFile() throws Exception {
// 合并输出到txt
StringBuffer rs = new StringBuffer();
rs.append(sb[0]+ "\r\n\r\n").append(sb[1]+ "\r\n\r\n").append(sb[2]+ "\r\n\r\n").append(sb[3]+ "\r\n\r\n")
.append(sb[4]+ "\r\n\r\n").append(sb[5]+ "\r\n\r\n").append(sb[6]+ "\r\n\r\n");
File file = new File("C:\\Users\\27183\\Desktop\\数据库对比结果.txt");
OutputStream os = new FileOutputStream(file);
os.write(rs.toString().getBytes());
os.flush();
os.close();
}
}
对比结果将输出到.txt文件中