MySQL builds other libraries with different library upgrade scripts based on one base library

Today we have studied how to synchronize between different databases. Get an upgrade tool class, I hope it will help later.

public class Upgradedatabase {public static void main (string[] args) {//two different database names list<string> sq        ls = upgradedatabasetostandarddatabase ("Database1", "Database2");        for (String sql:sqls) {System.out.println (SQL); }}/** * Create a script in the base library that is synchronized to the target library by using the name as a reference in the table field (*/public static list<string> Upgradedatabasetostandarddat        Abase (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 ();            Get a link to 2 libraries Connstandard = drivermanager.getconnection (URL, username, password);            Connstandard.setcatalog (standard);            Conntarget = drivermanager.getconnection (URL, username, password);            Conntarget.setcatalog (target);            Stmtstandard = Connstandard.createstatement ();            Stmttarget = Conntarget.createstatement ();            First, all the tables of 2 libraries are isolated to 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));                    }//loop base library for each table for (String table:standardtables) {if ("Sm_user". Equals (table)) {                Continue } if (Targettables.contains (table)) {map<string, map<string, string>> Standa                    Rdcolumns = new hashmap<> ();                    Map<string, map<string, string>> targetcolumns = new hashmap<> ();  Check each field,//1. First, identify the target library and the base library all fields of the table 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"));//Column name Map.put ("Type", Rsstandard.getstring ("T Ype "));//Type + length Map.put (" Null ", RsSTandard.getstring ("Null"));//Can be null map.put ("Key", Rsstandard.getstring ("key");//whether the primary key Map.put ("Default", Rsstandard.getstring ("Default"));//Default Value Map.put ("Extra", Rsstandard.get                    String ("Extra"));//Other (self-increment, trigger, etc.) 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"));//Column name Map.put ("type", rstarget.getstring ("type")) ;//Type + length map.put ("null", rstarget.getstring ("null"));//Can be null map.put ("Key", Rstarget.getstring ("key"));//whether the primary key map.put ("Default", Rstarget.getstrinG ("Default"));//Default Value Map.put ("Extra", Rstarget.getstring ("Extra"));//Other (self-increment, trigger, etc.)                    Targetcolumns.put (rstarget.getstring ("Field"), map);  }//2. Based on the benchmark library, column by row comparison//todo does not handle key (no master key, self-increment processing) for (String column:                            Standardcolumns.keyset ()) {if (Targetcolumns.containskey (column)) {//existence of this column                            Boolean needgenesql = false;                            StringBuffer buffer = new StringBuffer (); The type has changed, but regardless of whether the type has changed, subsequent statements require//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.A               Ppend (Standardcolumns.get (column). Get ("Type"));             The default value is variable if (standardcolumns.get (column). Get ("default")! = null &&!standard Columns.get (column). Get ("Default"). Equals (Targetcolumns.get (column). Get ("Default")) {Buff                                Er.append ("Default" + Standardcolumns.get (column). Get ("default"));                            Needgenesql = true; }//Can be empty variable if (standardcolumns.get (column). Get ("Null")! = null &&                                amp;!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; }//Handles self-growth, etc. 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" + TA                                ble + "Change" + column + "+ column +" "+ buffer.tostring () +"; ";                            Upgradesqls.add (Changecolumnsql); }} else{String addcolumnsql = "ALTER TABLE" + Table + "Add column" + column + "" + Standardcolu                                    Mns.get (column). Get ("Type") + "default" + Standardcolumns.get (column). Get ("default") + ("NO". Equals (Standardcolumns.get (column). Get ("Null"))?                            "Not NULL": "null") + ";"; Upgradesqls.add (ADDCOLUMNSQL); }}} else{//in the target library, there is no table for the base library Stmtstandard.execute ("Show C                    reate table "+ table");                    Rsstandard = Stmtstandard.getresultset ();                    String createsql = null;                        while (Rsstandard.next ()) {//2nd column is a build table statement 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 builds other libraries with different library upgrade scripts based on one base library