import java.sql.*;
|
public class ItemSortMigration {
|
public static void main(String[] a) throws Exception {
|
try (Connection c = DriverManager.getConnection("jdbc:mysql://1.95.78.124:9000/mom-xgdl?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", System.getenv("MOM_DB_PASSWORD")); Statement s=c.createStatement()) {
|
if (a.length > 0) { for (String table : new String[]{"mdm_item", "mes_md_item"}) { try (ResultSet r=s.executeQuery("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=\u0027"+table+"\u0027 AND column_name=\u0027sort\u0027")) { r.next(); if (r.getInt(1)==0) { try (Statement ddl=c.createStatement()) { ddl.executeUpdate("ALTER TABLE `"+table+"` ADD COLUMN sort INT NOT NULL DEFAULT 0 COMMENT \u0027排序，数值越小越靠前\u0027"); } } } } System.out.println("Synchronized rows="+s.executeUpdate("UPDATE mes_md_item m JOIN mdm_item d ON m.mdm_item_id=d.id SET m.sort=d.sort WHERE m.sort <> d.sort")); }
|
try(ResultSet r=s.executeQuery("SELECT COUNT(*), SUM(m.mdm_item_id IS NOT NULL), SUM(d.id IS NOT NULL) FROM mes_md_item m LEFT JOIN mdm_item d ON m.mdm_item_id=d.id WHERE m.deleted=0")) {r.next(); System.out.println("MES active="+r.getLong(1)+" linked="+r.getLong(2)+" matched="+r.getLong(3));}
|
for(String table : new String[]{"mdm_item","mes_md_item"}) {
|
try(ResultSet r=s.executeQuery("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='"+table+"' AND column_name='sort'")) { r.next(); System.out.println(table+" sort columns="+r.getInt(1)); }
|
try(ResultSet r=s.executeQuery("SELECT COUNT(*) FROM `"+table+"`")) {r.next(); System.out.println(table+" rows="+r.getLong(1));}
|
}
|
}
|
}
|
}
|
