数据库表海量数据对比代码

package test; import java.io.File; import java.io.FileOutputStream; import java.io.OutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import com.amarsoft.are.sql.ASResultSet; import com.amarsoft.are.sql.Transaction; import com.amarsoft.are.util.DataConvert; public class CompareTable { public static StringBuffer[] sb = { new StringBuffer(), new StringBuffer(), new StringBuffer(), new StringBuffer(), new StringBuffer(), new StringBuffer() }; public static Transaction getTransaction_product() throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.1.1:1621:orcl", "demo1", "demo1"); if (conn != null)System.out.println("数据库加载成功！"); Transaction transaction = new Transaction(conn); return transaction; } public static Transaction getTransaction_develop() throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.1.2:1621:orcl", "demo2", "demo2"); if (conn != null)System.out.println("数据库加载成功！"); Transaction transaction = new Transaction(conn); return transaction; } public static void main(String[] args) throws Exception { compareTables(); // 比较数据库 writeFile(); // 写入文件 } /** * @author YUJIYU090 比较生产库和开发库的数据表，包括表名、字段名、字段类型、字段长度 * */ public static void compareTables() throws Exception { // 生产数据库连接 Transaction trans_product = getTransaction_product(); Map<String, Table> map_product = getTables(trans_product); // 开发数据库连接 Transaction trans_develop = getTransaction_develop(); Map<String, Table> map_develop = getTables(trans_develop); // 遍历开发库Map for (Iterator<String> iter_table = map_develop.keySet().iterator(); iter_table .hasNext();) { String key_table = (String) iter_table.next(); Table table_develop = map_develop.get(key_table);// 获得开发库中的表 Table table_product = map_product.get(key_table);// 尝试从生产库中获得同名表 if (table_product == null) { // 如果获得表为空，说明开发存在，生产不存在 append(table_develop, null, 2); } else { // 表相同，判断字段、字段类型、字段长度 for (Iterator<String> iter_column = table_develop.columns .keySet().iterator(); iter_column.hasNext();) { String key_column = (String) iter_column.next(); Column column_develop = table_develop.columns.get(key_column);// 获得开发库中的列 Column column_product = table_product.columns.get(key_column);// 尝试从生产库中获得同名列 if (column_product == null) {// 如果列名为空，说明开发存在，生产不存在 append(table_develop, column_develop, 4); } else {// 说明两者都存在 if (!column_develop.dataType.equals(column_product.dataType))// 字段类型不一致 append(table_develop, column_develop, 5); if (column_develop.length != column_product.length)// 字段长度不一致 append(table_develop, column_develop, 6); } } } } // 遍历生产库Map for (Iterator<String> iter_table = map_product.keySet().iterator(); iter_table .hasNext();) { String key_table = (String) iter_table.next(); Table table_product = map_product.get(key_table);// 尝试从生产库中获得同名表 Table table_develop = map_develop.get(key_table);// 获得开发库中的表 if (table_develop == null) { // 如果获得表为空，说明开发存在，生产不存在 append(table_product, null, 1); } else { // 表相同，判断字段、字段类型、字段长度 for (Iterator<String> iter_column = table_product.columns .keySet().iterator(); iter_column.hasNext();) { String key_column = (String) iter_column.next(); Column column_product = table_product.columns.get(key_column);// 获得生产库中的列 Column column_develop = table_develop.columns.get(key_column);// 尝试从开发库中获得同名列 if (column_develop == null) {// 如果列名为空，说明生产存在，开发不存在 append(table_product, column_product, 3); } } } } } /** * @author YUJIYU090 传入数据库连接，返回数据库中所有TABLE对象的MAP * */ public static Map<String, Table> getTables(Transaction transaction) throws Exception { String sSql = " select table_name,Column_Name,Data_Type," + " DECODE(DATA_TYPE,'NUMBER',DATA_PRECISION,'VARCHAR2'," + " DATA_LENGTH,'VARCHAR',DATA_LENGTH,'CHAR',DATA_LENGTH,0) Length," + " NVL(DATA_SCALE, 0) SCALE,DECODE(NULLABLE, 'N', '1', '0') NULLABLE " + " from user_tab_columns where 1=1 Order By table_name,column_name"; ASResultSet rs = transaction.getASResultSet(sSql); Map<String, Table> map = new HashMap<String, Table>(); String tableName = ""; Table table = null; while (rs.next()) { if (!tableName.equals(rs.getString("table_name"))) {// 一张新表 tableName = rs.getString("table_name"); table = new Table(tableName); Column column = new Column(rs.getString("Column_Name"), rs.getString("Data_Type"), rs.getInt("Length")); table.columns.put(column.columnName, column); map.put(rs.getString("table_name"), table); } else {// 已存在的表，增加字段 Column column = new Column(rs.getString("Column_Name"), rs.getString("Data_Type"), rs.getInt("Length")); table.columns.put(column.columnName, column); } } if (null != rs) rs.close(); transaction.finalize(); return map; } /** * @author YUJIYU090 根据标示位，追加到满足条件的StringBuffer * */ public static void append(Table table, Column column, int flag) throws Exception { switch (flag) { case 1: System.out.println("1、生产存在，开发不存在的表：" + table.getTableName());// 跳过 sb[0].append(table.getTableName() + "\n"); break; case 2: System.out.println("2、生产不存在，开发存在的表：" + table.getTableName());// 需要人工判断脚本 sb[1].append(table.getTableName() + "\n"); break; case 3: System.out.println("3、生产存在，开发不存在的字段：" + table.getTableName() + " | " + column.getColumnName());// 需人工判断如何处理 sb[2].append(table.getTableName() + " | " + column.getColumnName() + "\n"); break; case 4: System.out.println("4、生产不存在，开发存在的字段：" + table.getTableName() + " | " + column.getColumnName());// 需要人工判断脚本 sb[3].append(table.getTableName() + " | " + column.getColumnName() + "\n"); break; case 5: System.out.println("5�