校验两个数据库表差异,并生成修改语句(BATE版,可能有BUG)

package com.exgrain.eoms.loginmanage; import java.io.File; import java.io.IOException; import java.net.MalformedURLException; import java.net.URL; import java.net.URLClassLoader; import java.sql.Connection; import java.sql.Driver; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import org.apache.commons.io.FileUtils; import org.apache.commons.lang.StringUtils; import com.alibaba.fastjson.JSON; import com.hzjs.common.utils.ArrayUtil; public class CheckDbColumn { private static final String JDBC_JAR = "file:D:\\repo\\mysql\\mysql-connector-java\\5.1.13\\mysql-connector-java-5.1.13.jar";//jar包地址,如项目无jar时可以指定外部jar包 private static final String TABLE_NAME = "information_schema.columns";//mysql存储表字段的表 // private static final String TABLE_COLUMN = "TABLE_NAME,COLUMN_NAME,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_COMMENT"; private static final String TABLE_COLUMN = "TABLE_NAME,COLUMN_NAME,IS_NULLABLE,COLUMN_DEFAULT,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE,COLUMN_COMMENT";//查询的字段,勿改 private static final String DB_COLUMN = "table_schema";//DB对应的列,勿改 private static final String PREFIX = " "; private static final String DRIVER = "com.mysql.jdbc.Driver";//jdbc驱动 private static final String SRC_DB_NAME = "testDB";//源数据库名 private static final String SRC_URL = "jdbc:mysql://192.168.0.33:3306/" + SRC_DB_NAME + "?useUnicode=true&characterEncoding=UTF-8";//源jdbc地址 private static final String SRC_USER_NAME = "root";//源用户名 private static final String SRC_PASSWORD = "root";//源密码 private static final String TAR_DB_NAME = "test";//目标数据库名 private static final String TAR_URL = "jdbc:mysql://192.168.0.80:3306/" + TAR_DB_NAME + "?useUnicode=true&characterEncoding=UTF-8";//目标jdbc地址 private static final String TAR_USER_NAME = "root";//目标用户名 private static final String TAR_PASSWORD = "rootxx";//目标密码 private static final boolean IS_TO_FILE = true;//是否写入文件 private static final boolean IS_TO_CONSOLE = false;//是否写入控制台,建议写入文件 private static final String SRC_PATH = "D:/src.txt";//源数据库变更为目标数据库sql private static final String TAR_PATH = "D:/tar.txt";//目标数据库变更为源数据库sql private static final String BANK1 = " "; private static final String RN1 = "\r\n"; private static final String[] IGORE_TABLE_REGEX = {".+_rt"}; private static final String[] ACCESS_TABLE_REGEX = {}; public static void main(String[] args) { Map<String,Map<String,Map<String, String>>> srcMap = loadTableInfo(SRC_URL, SRC_USER_NAME, SRC_PASSWORD, SRC_DB_NAME); Map<String,Map<String,Map<String, String>>> tarMap = loadTableInfo(TAR_URL, TAR_USER_NAME, TAR_PASSWORD, TAR_DB_NAME); checkMapAndRemoveRepeat(srcMap, tarMap); outNotRepeatMap(srcMap,tarMap); } /** * 查询表结构语句 * @param dbName * @return */ private static String getFindTablesSql(String dbName) { return "select " + TABLE_COLUMN + " from " + TABLE_NAME + " where " + DB_COLUMN + "='" + dbName +"'"; } /** * 比较源数据库和目标数据库差异,移除相同部分 * @param srcMap * @param tarMap */ private static void checkMapAndRemoveRepeat(Map<String,Map<String,Map<String, String>>> srcMap,Map<String,Map<String,Map<String, String>>> tarMap){ Iterator<String> tableKeys = srcMap.keySet().iterator(); while(tableKeys.hasNext()){ String tk = tableKeys.next(); Map<String,Map<String, String>> srcColumnsMap = srcMap.get(tk); Map<String,Map<String, String>> tarColumnsMap = tarMap.get(tk); if (tarColumnsMap==null) {//如果目标表中不存在则不再匹配 continue; } Iterator<String> columnsKeys = srcColumnsMap.keySet().iterator(); while (columnsKeys.hasNext()) { String csk = columnsKeys.next(); Map<String, String> srcColumnMap = srcColumnsMap.get(csk); Map<String, String> tarColumnMap = tarColumnsMap.get(csk); if (tarColumnMap==null) {//如果目标表中不存在则不再匹配 continue; } Iterator<String> columnKeys = srcColumnMap.keySet().iterator(); boolean isModify = false; while (columnKeys.hasNext()) { String ck = columnKeys.next(); String srcValue = srcColumnMap.get(ck); String tarValue = tarColumnMap.get(ck); if (tarValue==null) { continue; } if (tarValue.equals(srcValue)) {//如果重复则移除 if (!ck.equals("COLUMN_TYPE")) { columnKeys.remove(); // srcColumnMap.keySet().remove(ck); tarColumnMap.keySet().remove(ck); } }else{ isModify = true; } } if (!isModify&&srcColumnMap.size()==tarColumnMap.size()) {//全部匹配 srcColumnMap.clear(); tarColumnMap.clear(); } } if (checkSetIsNull(srcColumnsMap.values())) {//全部匹配 srcColumnsMap.clear(); } if (checkSetIsNull(tarColumnsMap.values())) { tarColumnsMap.clear(); } } if (checkSetIsNull(srcMap.values())) {//全部匹配 srcMap.clear(); } if (checkSetIsNull(tarMap.values())) { tarMap.clear(); } } /** * 获取数据库表信息 * @param url * @param username * @param password * @param dbName * @return */ private static Map<String,Map<String,Map<String, String>>> loadTableInfo(String url,String username,String password,String dbName) { Map<String, Map<String,Map<String, String>>> tableMap = new HashMap<>(); try { Connection srcConn = null; try { Class.forName(DRIVER); srcConn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { System.out.println("类不存在,尝试加载"+JDBC_JAR); try { //动态加载jar包 @SuppressWarnings("resource") URLClassLoader classLoader = new URLClassLoader(new URL[]{new URL(JDBC_JAR)}); Driver driver = (Driver) classLoader.loadClass(DRIVER).newInstance(); java.util.Properties info = new java.util.Properties(); info.put("user", username); info.put("password", password); srcConn = driver.connect(url, info); // classLoader.close(); } catch (MalformedURLException | InstantiationException | IllegalAccessException e1) { e1.printStackTrace(); return null; } } Statement stmt = srcConn.createStatement(); ResultSet rs = stmt.executeQuery(getFindTablesSql(dbName)); String[] columns = TABLE_COLUMN.split(","); while (rs.next()) { int columnCount = rs.getMetaData().getColumnCount(); if (columnCount == columns.length) { String tableName = rs.getString("TABLE_NAME"); // 匹配忽略的表 if (IGORE_TABLE_REGEX.length > 0) { boolean flag = false; for (int i = 0; i < IGORE_TABLE_REGEX.length; i++) { String regex = IGORE_TABLE_REGEX[i]; if (tableName.matches(regex)) { flag = true; break; } } if (flag) { continue; } } // 匹配仅通过的表 if (ACCESS_TABLE_REGEX.length > 0) { boolean flag = false; for (int i = 0; i < ACCESS_TABLE_REGEX.length; i++) { String regex = IGORE_TABLE_REGEX[i]; if (tableName.matches(regex)) { flag = true; break; } } if (!flag) { continue; } } String columnName = rs.getString("COLUMN_NAME"); Map<String,Map<String, String>> columnMaps = tableMap.get(tableName); if (columnMaps==null) { colum