比较两个数据库之间的差异

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/**
* 对比两个数据库, 显示不同的表、不同的字段等信息
*/
public class ShowDBDifferences {
private String className;

// 数据库1的数据库url、用户名、密码
private String dbName1;
private String dbUrl1;
private String userName1;
private String password1;

// 数据库2的数据库url、用户名、密码
private String dbName2;
private String dbUrl2;
private String userName2;
private String password2;

public String getClassName() {
return className;
}

public void setClassName(String className) {
this.className = className;
}

public String getDbName1() {
return dbName1;
}

public void setDbName1(String dbName1) {
this.dbName1 = dbName1;
}

public String getDbUrl1() {
return dbUrl1;
}

public void setDbUrl1(String dbUrl1) {
this.dbUrl1 = dbUrl1;
}

public String getUserName1() {
return userName1;
}

public void setUserName1(String userName1) {
this.userName1 = userName1;
}

public String getPassword1() {
return password1;
}

public void setPassword1(String password1) {
this.password1 = password1;
}

public String getDbName2() {
return dbName2;
}

public void setDbName2(String dbName2) {
this.dbName2 = dbName2;
}

public String getDbUrl2() {
return dbUrl2;
}

public void setDbUrl2(String dbUrl2) {
this.dbUrl2 = dbUrl2;
}

public String getUserName2() {
return userName2;
}

public void setUserName2(String userName2) {
this.userName2 = userName2;
}

public String getPassword2() {
return password2;
}

public void setPassword2(String password2) {
this.password2 = password2;
}

private Connection getConnection(String url, String userName,
String password) {
Driver driver = null;
try {
driver = (Driver) Class.forName(className).newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

Connection connection = null;
try {
DriverManager.registerDriver(driver);
connection = DriverManager.getConnection(url, userName, password);
} catch (SQLException e) {
e.printStackTrace();
}

return connection;
}

private Map getTableInfo(Connection conn, String tbName) {
// 查询所有表名
// String sql =
// "SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE CREATOR NOT LIKE '%SYS%' order by name ";

// 查询所有表结构定义的信息
String sql = "select TBCREATOR, TBNAME, NAME, COLTYPE, TYPENAME, LENGTH, SCALE, COLNO, NULLS from sysibm.syscolumns where TBCREATOR NOT LIKE '%SYS%' and TBNAME like '%"
+ tbName + "%' order by TBNAME, COLNO ";

Map map = new HashMap();

PreparedStatement pstm = null;
ResultSet rs = null;
try {
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
while (rs.next()) {
String tableName = rs.getString(2);

String colName = rs.getString(3);
String colType = rs.getString(4);
int colLength = rs.getInt(6);
int colScale = rs.getInt(7);
int colNo = rs.getInt(8);
String isAllowNull = rs.getString(9);

ColumnInfo colInfo = new ColumnInfo();
colInfo.setTableName(tableName);
colInfo.setColName(colName);
colInfo.setColType(colType.trim());
colInfo.setColLength(colLength);
colInfo.setColScale(colScale);
colInfo.setColNo(colNo);
colInfo.setIsAllowNull(isAllowNull);

// System.out.println(colInfo);

Map colMap = (Map) map.get(tableName);
if (colMap == null) {
colMap = new HashMap();
}
colMap.put(colName, colInfo);
map.put(tableName, colMap);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (pstm != null)
pstm.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

return map;
}

public void diff(String tbName) {
Connection conn1 = this.getConnection(this.dbUrl1, this.userName1,
this.password1);

Connection conn2 = this.getConnection(this.dbUrl2, this.userName2,
this.password2);

Map map1 = this.getTableInfo(conn1, tbName);
Map map2 = this.getTableInfo(conn2, tbName);

String addTables = ""; // 多出的表
Set tableSet1 = map1.keySet();
Iterator iter1 = tableSet1.iterator();
while (iter1.hasNext()) {
String tableName = (String) iter1.next();
if (map2.get(tableName) == null) {
addTables += "," + tableName;
} else { // 比较两个数据库间同表列的信息
Map colMap1 = (Map) map1.get(tableName);
Map colMap2 = (Map) map2.get(tableName);

String addCols = ""; // 多出的列
Set colSet1 = colMap1.keySet();
Iterator colIter1 = colSet1.iterator();
while (colIter1.hasNext()) {
String colName = (String) colIter1.next();
if (colMap2.get(colName) == null) {
addCols += "," + addCols;
} else { // 比较两个数据库间同表同列的其它信息
ColumnInfo colInfo1 = (ColumnInfo) colMap1.get(colName);
ColumnInfo colInfo2 = (ColumnInfo) colMap2.get(colName);

// 对比类型
if (!colInfo1.getColType()
.equals(colInfo2.getColType())) {
System.out.println(tableName + "." + colName + "的类型不相同："
+ colInfo1.getColType() + " 和 "
+ colInfo2.getColType());
}
// 对比长度
if (colInfo1.getColLength() != colInfo2.getColLength()) {
System.out.println(tableName + "." + colName + "的长度不相同："
+ colInfo1.getColLength() + " 和 "
+ colInfo2.getColLength());
}
// 对比小数点长度
if (colInfo1.getColScale() != colInfo2.getColScale()) {
System.out.println(tableName + "." + colName + "的小数点长度不相同："
+ colInfo1.getColScale() + " 和 "
+ colInfo2.getColScale());
}
// 对比列的位置
if (colInfo1.getColNo() != colInfo2.getColNo()) {
System.out.println(tableName + "." + colName + "的位置不相同："
+ colInfo1.getColNo() + " 和 "
+ colInfo2.getColNo());
}
// 对比是否允许为空
if (!colInfo1.getIsAllowNull().equals(
colInfo2.getIsAllowNull())) {
System.out.println(tableName + "." + colName + "的是否允许为空不相同："
+ colInfo1.getIsAllowNull() + " 和 "
+ colInfo2.getIsAllowNull());
}
}
}
if (!"".equals(addCols)) {
System.out.println(this.dbName1 + "数据库中" + tableName
+ "表多出的列：" + addCols.substring(1));
}

String missCols = ""; // 缺少的列
Set colSet2 = colMap2.keySet();
Iterator colIter2 = colSet2.iterator();
while (colIter2.hasNext()) {
String colName = (String) colIter2.next();
if (colMap1.get(colName) == null) {
missCols += "," + missCols;
}
}
if (!"".equals(missCols)) {
System.out.println(this.dbName1 + "数据库中" + tableName
+ "表缺少的列：" + missCols.substring(1));
}

}
}
if (!"".equals(addTables)) {
System.out.println(this.dbName1 + "数据库多出的表："
+ addTables.substring(1));
}

String missTables = ""; // 缺少的表
Set tableSet2 = map2.keySet();
Iterator iter2 = tableSet2.iterator();
while (iter2.hasNext()) {
String tableName = (String) iter2.next();
if (map1.get(tableName) == null) {
missTables += "," + tableName;
}
}
if (!"".equals(missTables)) {
System.out.println(this.dbName1 + "数据库缺少的表："
+ missTables.substring(1));
}
}

/**
* @param args
*/
public static void main(String[] args) {
ShowDBDifferences util = new ShowDBDifferences();
util.setClassName("COM.ibm.db2.jdbc.app.DB2Driver");

util.setDbName1("PFMS");
util.setDbUrl1("jdbc:db2:PFMS"); // 空库
util.setUserName1("db2admin");
util.setPassword1("db2admin");

util.setDbName2("PFMS_ZS");
util.setDbUrl2("jdbc:db2:PFMS_ZS"); // 正式库
util.setUserName2("db2admin");
util.setPassword2("db2admin");

util.diff("GZTF");
}
}

class ColumnInfo {
private String tableName; // 表名
private String colName; // 列名
private String colType; // 类型
private int colLength; // 长度
private int colScale; // 小数点位数
private int colNo; // 列的位置顺序, 从 0 开始
private String isAllowNull; // 是否允许为空, Y or N

public String getTableName() {
return tableName;
}

public void setTableName(String tableName) {
this.tableName = tableName;
}

public String getColName() {
return colName;
}

public void setColName(String colName) {
this.colName = colName;
}

public String getColType() {
return colType;
}

public void setColType(String colType) {
this.colType = colType;
}

public int getColLength() {
return colLength;
}

public void setColLength(int colLength) {
this.colLength = colLength;
}

public int getColScale() {
return colScale;
}

public void setColScale(int colScale) {
this.colScale = colScale;
}

public int getColNo() {
return colNo;
}

public void setColNo(int colNo) {
this.colNo = colNo;
}

public String getIsAllowNull() {
return isAllowNull;
}

public void setIsAllowNull(String isAllowNull) {
this.isAllowNull = isAllowNull;
}

public String toString() {
return this.colName + "," + this.colType + "," + this.colLength + ","
+ this.colScale + "," + this.colNo + "," + this.isAllowNull;
}

}