今天研究了一下不同数据库之间如何做同步。弄了一个升级工具类，希望以后还能有所帮助。
public class UpgradeDataBase {
public static void main(String[] args) {
//两个不同数据库名称
List<String> sqls = UpgradeDataBaseToStandardDataBase("database1", "database2");
for(String sql : sqls) {
System.out.println(sql);
}
}
/**
* 将基准库里有的表字段,以名称为基准,生成同步到目标库中的脚本
*/
public static List<String> UpgradeDataBaseToStandardDataBase(String standard, String target) {
List<String> upgradeSqls = new ArrayList<>();
List<String> standardTables = new ArrayList<>();
List<String> targetTables = new ArrayList<>();
Connection connStandard = null;
Connection connTarget = null;
Statement stmtStandard = null;
Statement stmtTarget = null;
ResultSet rsStandard = null;
ResultSet rsTarget = null;
try {
Properties props = Resources.getResourceAsProperties("jdbc.properties");
String url = props.getProperty("url");
String driver = props.getProperty("driver");
String username = props.getProperty("username");
String password = props.getProperty("password");
Class.forName(driver).newInstance();
//拿到2个库的链接
connStandard = DriverManager.getConnection(url, username, password);
connStandard.setCatalog(standard);
connTarget = DriverManager.getConnection(url, username, password);
connTarget.setCatalog(target);
stmtStandard = connStandard.createStatement();
stmtTarget = connTarget.createStatement();
//先把2个库所有的表查出来
stmtStandard.execute("show tables");
stmtTarget.execute("show tables");
rsStandard = stmtStandard.getResultSet();
rsTarget = stmtTarget.getResultSet();
while (rsStandard.next()){
standardTables.add(rsStandard.getString(1));
}
while (rsTarget.next()){
targetTables.add(rsTarget.getString(1));
}
//循环基准库中每一张表
for(String table : standardTables) {
if("SM_USER".equals(table)) {
continue;
}
if(targetTables.contains(table)) {
Map<String, Map<String, String>> standardColumns = new HashMap<>();
Map<String, Map<String, String>> targetColumns = new HashMap<>();
//检查每一个字段,
//1.首先先查出目标库和基准库该表的所有字段
stmtStandard.execute("show columns from " + table + " from " + standard);
rsStandard = stmtStandard.getResultSet();
while (rsStandard.next()){
Map<String, String> map = new HashMap<>();
map.put("Field", rsStandard.getString("Field"));//列名
map.put("Type", rsStandard.getString("Type"));//类型+长度
map.put("Null", rsStandard.getString("Null"));//是否可为空
map.put("Key", rsStandard.getString("Key"));//是否主键
map.put("Default", rsStandard.getString("Default"));//默认值
map.put("Extra", rsStandard.getString("Extra"));//其他(自增列,触发器等)
standardColumns.put(rsStandard.getString("Field"), map);
}
stmtTarget.execute("show columns from " + table + " from " + target);
rsTarget = stmtTarget.getResultSet();
while (rsTarget.next()){
Map<String, String> map = new HashMap<>();
map.put("Field", rsTarget.getString("Field"));//列名
map.put("Type", rsTarget.getString("Type"));//类型+长度
map.put("Null", rsTarget.getString("Null"));//是否可为空
map.put("Key", rsTarget.getString("Key"));//是否主键
map.put("Default", rsTarget.getString("Default"));//默认值
map.put("Extra", rsTarget.getString("Extra"));//其他(自增列,触发器等)
targetColumns.put(rsTarget.getString("Field"), map);
}
//2.以基准库为准,逐个列比较
//TODO 没有处理Key(没有做主键、自增处理)
for(String column : standardColumns.keySet()) {
if(targetColumns.containsKey(column)) {//存在这一列
boolean needGeneSql = false;
StringBuffer buffer = new StringBuffer();
//类型有变化, 但是不管类型有没有变化,后续的语句都需要
// if(standardColumns.get(column).get("Type") != null && !standardColumns.get(column).get("Type").equals(targetColumns.get(column).get("Type"))) {
// buffer.append(standardColumns.get(column).get("Type"));
// }
buffer.append(standardColumns.get(column).get("Type"));
//默认值有变
if(standardColumns.get(column).get("Default") != null && !standardColumns.get(column).get("Default").equals(targetColumns.get(column).get("Default"))) {
buffer.append(" default " + standardColumns.get(column).get("Default"));
needGeneSql = true;
}
//是否可空有变
if(standardColumns.get(column).get("Null") != null && !standardColumns.get(column).get("Null").equals(targetColumns.get(column).get("Null"))) {
buffer.append(("NO".equals(standardColumns.get(column).get("Null")) ? " not null " : " null "));
needGeneSql = true;
}
//处理自增长等
if(standardColumns.get(column).get("Extra") != null && !standardColumns.get(column).get("Extra").equals(targetColumns.get(column).get("Extra"))) {
buffer.append(" ").append(standardColumns.get(column).get("Extra"));
needGeneSql = true;
}
if(needGeneSql) {
String changeColumnSql = "alter table " + table + " change " + column + " " + column + " " + buffer.toString() + ";";
upgradeSqls.add(changeColumnSql);
}
}
else{
String addColumnSql = "alter table " + table +
" add column " + column + " " +
standardColumns.get(column).get("Type") + " default " + standardColumns.get(column).get("Default") +
("NO".equals(standardColumns.get(column).get("Null")) ? " not null " : " null ") + ";";
upgradeSqls.add(addColumnSql);
}
}
}
else{//目标库中,没有基准库的表
stmtStandard.execute("show create table " + table);
rsStandard = stmtStandard.getResultSet();
String createSql = null;
while (rsStandard.next()){
//第2列是建表语句
createSql = rsStandard.getString(2);
upgradeSqls.add(createSql + ";");
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rsStandard.close();
rsTarget.close();
stmtStandard.close();
stmtTarget.close();
connStandard.close();
connTarget.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return upgradeSqls;
}
}