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檔案中
