mysql 更新两个字段的数据库表,Mysql 比较两个数据库的表，表字段是否匹配，升级更新找sql有用...

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

import java.util.Set;

import org.apache.commons.collections.CollectionUtils;

public class DataBaseUtil {

public static void main(String[] args) {

try {

String url1 = "jdbc:mysql://ddd?characterEncoding=utf-8&noAccessToProcedureBodies=true&useUnicode=true";

String url2 = "jdbc:mysql://xxx?characterEncoding=utf-8&noAccessToProcedureBodies=true&useUnicode=true";

Set set1 = getTableNames(getConnection(url1));

Set set2 = getTableNames(getConnection(url2));

//Collection c = CollectionUtils.disjunction(set1,set2);

Collection c = CollectionUtils.subtract(set1,set2);

for (Object object : c) {

System.out.println("不同的表有："+object);

}

Map> map1 = new HashMap>();

for (String s : set1) {

map1.put(s, getFieldName(getConnection(url1), s));

}

Map> map2 = new HashMap>();

for (String s : set2) {

map2.put(s,getFieldName(getConnection(url2), s));

}

//比较 两个map

Set keyMap2 = map2.keySet();

for (String keyMap21 : keyMap2) {

Set keyMap1 = map1.keySet();

for (String keyMap11 : keyMap1) {

if(keyMap21.equalsIgnoreCase(keyMap11)){

if(map1.get(keyMap11).size()!=map2.get(keyMap21).size()){

System.out.println("表字段不同的表："+keyMap11);

}

difrentList(keyMap11,keyMap21,map1.get(keyMap11),map2.get(keyMap21));

}

}

}

} catch (Exception e1) {

e1.printStackTrace();

}

}

static Connection getConnection(String url){

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection(url, "db_admin", "123456");

} catch (Exception e) {

}

return conn;

}

public static Set getTableNames(Connection conn){

Set set1 = new HashSet();

try {

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery("show tables");

while (rs.next()) {

String tableName = rs.getString(1);

//System.out.println(tableName);

set1.add(tableName);

}

return set1;

} catch (Exception e) {

e.printStackTrace();

}

return set1;

}

public static List getFieldName(Connection conn,String tableName){

List list = new ArrayList();

try {

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery("describe "+tableName);

while (rs.next()) {

String tableNameR = rs.getString(1);

list.add(tableNameR);

//System.out.println(tableNameR);

}

return list;

} catch (Exception e) {

e.printStackTrace();

}

return list;

}

private static void difrentList(String table1,String table2,List list1,List list2){

//Collection c = CollectionUtils.disjunction(list1,list2);//集合A B 不同的字段

Collection c = CollectionUtils.subtract(list1,list2);//集合AB中A有B没有的字段

if(c!=null && c.size()>0){

System.out.println();

System.out.println(table1+" 不同的字段");

for (String object : c) {

System.out.println(object);

}

//System.out.println(table2+" 不同的字段");

}

}

}