Mysql根據一個基庫產生其他庫與其不同的庫升級指令碼

標籤：new   stat   connect   檢查   show   指令碼   url   cut   oid   

今天研究了一下不同資料庫之間如何做同步。弄了一個升級工具類，希望以後還能有所協助。

 

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;    }}

　　

　　

Mysql根據一個基庫產生其他庫與其不同的庫升級指令碼