/**
* */ package com.sobey.fieldcompare; import java.io.File;
import java.io.IOException; import java.io.RandomAccessFile; import java.net.URL; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.util.Collections; import java.util.HashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; import org.dom4j.Attribute;
import org.dom4j.Document; import org.dom4j.io.SAXReader; /**
* @author Administrator * */ public class DbFieldCompare { private List srcTableNames = new LinkedList(); private HashMap srcTableColumns = new HashMap(); private RandomAccessFile raf; /** * @param args * @throws Exception */ public static void main(String[] args) throws Exception { // TODO Auto-generated method stub DbFieldCompare compare = new DbFieldCompare(); compare.openFile("d://comparefield.txt"); compare.compareField(); compare.closeFile("d://comparefield.txt"); } public void compareField() throws Exception {
// 读取hibernate配置文件,得到所有的映射 URL fileName = this.getClass().getClassLoader().getResource( "hibernate.cfg.xml"); SAXReader reader = new SAXReader(); Document document = reader.read(fileName); //查找节点mapping下的resource属性,注意DOM4J的应用 List listAttr = document.selectNodes("/hibernate-configuration/session-factory/mapping/@resource"); Iterator itAttr = listAttr.iterator(); while(itAttr.hasNext()){ Attribute attr = (Attribute) itAttr.next(); URL fileName1 = this.getClass().getClassLoader().getResource(attr.getValue()); SAXReader reader1 = new SAXReader(); Document document1 = reader1.read(fileName1); List listAttr1 = document1.selectNodes("/hibernate-mapping/class/@table"); Iterator itAttr1 = listAttr1.iterator(); while(itAttr1.hasNext()){ Attribute attr1 = (Attribute) itAttr1.next(); List columns = new LinkedList(); //得到唯一关键字 List listAttr2 = document1.selectNodes("/hibernate-mapping/class/id/@column"); Iterator itAttr2 = listAttr2.iterator(); while(itAttr2.hasNext()){ Attribute attr2= (Attribute) itAttr2.next(); columns.add(attr2.getValue().toUpperCase()); } //得到其它字段 List listAttr3 = document1.selectNodes("/hibernate-mapping/class/property/@column"); Iterator itAttr3 = listAttr3.iterator(); while(itAttr3.hasNext()){ Attribute attr3= (Attribute) itAttr3.next(); columns.add(attr3.getValue().toUpperCase()); } //得到其它字段,关联字段 List listAttr4 = document1.selectNodes("/hibernate-mapping/class/composite-id/key-property/@column"); Iterator itAttr4 = listAttr4.iterator(); while(itAttr4.hasNext()){ Attribute attr4 = (Attribute) itAttr4.next(); columns.add(attr4.getValue().toUpperCase()); } srcTableNames.add(attr1.getValue().toUpperCase()); srcTableColumns.put(attr1.getValue().toUpperCase(), columns); // System.out.println(attr1.getValue() + "=======" + columns); } } //从数据库结构中得到表的结构
Connection connection = HibernateSession.currentSession().connection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); String[] types = new String[1]; types[0] = "TABLE"; // 设置查询类型 //存放数据库中不存在的文稿表 List notExistTableList = new LinkedList(); for (Iterator iterator = srcTableNames.iterator(); iterator.hasNext();) { String name = (String) iterator.next(); //注意参数databaseMetaData.getTables(null, null, 表名,可为null表示取全部表, types);resultSet.getObject(3)得到表名
ResultSet resultSet = databaseMetaData.getTables(null, null, name, types); boolean isTableExist = resultSet.next(); if (isTableExist) { List list = (List)srcTableColumns.get(name); List reslist = new LinkedList(); List notExistColumns = new LinkedList(); StringBuffer writeBufferStr = new StringBuffer(); //写有文稿中的字段,排序的应用 Collections.sort(list); writeBufferStr.append("\n\n"+ name + "\n文稿表中的字段:" + list.toString()); //注意参数databaseMetaData.getColumns(null,null,表名,字段名);null取全部 ResultSet resColumns = databaseMetaData.getColumns(null,null,name,null); while (resColumns.next()) { //注意resColumns.getObject(4)取的是字段的名字,其它可打印出来查看具体信息
reslist.add(resColumns.getObject(4).toString().toUpperCase()); if(list.contains(resColumns.getObject(4).toString().toUpperCase())){ //都有的字段 list.remove(resColumns.getObject(4).toString().toUpperCase()); }else{ //文稿中没有,数据库表中有的字段 notExistColumns.add(resColumns.getObject(4).toString().toUpperCase()); } } resColumns.close(); //写入数据库表的字段 Collections.sort(reslist); writeBufferStr.append("\n数据库表的字段:"+ reslist.toString()); //写入文稿有,数据库中没有的字段 Collections.sort(list); writeBufferStr.append("\n文稿有,数据库中没有的字段:"+ list.toString()); //写入文稿没有,数据库中有的字段 Collections.sort(notExistColumns); writeBufferStr.append("\n文稿没有,数据库中有的字段:"+ notExistColumns.toString()); this.writeFile(writeBufferStr.toString()); //notExistColumns.clear(); }else { notExistTableList.add(name); } resultSet.close(); } //写入文稿在数据库表中不存在的表 Collections.sort(notExistTableList); this.writeFile("\n\n以下文稿表在数据库表中不存在:\n"+ notExistTableList.toString().replaceAll(",", "\n")); connection.rollback(); connection.close(); } //随机文件的读写应用
public void openFile(String pathFile) throws IOException { File file = new File(pathFile); if (file.exists()) { file.delete(); } file.createNewFile(); raf = new RandomAccessFile(file,"rw"); } public void writeFile(String str) throws Exception { raf.seek(raf.length()); raf.write(str.getBytes()); } public void closeFile(String pathFile) throws IOException{ raf.close(); } } |
|