constraintcompare.java

导出ORACLE数据库对象DDL语句的程序

/*
 * Created on 2005-6-20
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.icbcsdc.ddlexp.ui;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import com.icbcsdc.ddlexp.pub.connectionpool.JDBCException;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPool;
import com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLDatabase;
import com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode;

/**
 * @author lijf
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class ConstraintCompare {
	JDBCPool sourcePool;
	JDBCPool destPool;
	JDBCPool ResultPool;
	public XMLDatabase sourcedb;
	public XMLDatabase destdb;
	public XMLDatabase resultdb;
	public String OwnerA = null;
	String OwnerB = null;
	Hashtable srcData = new Hashtable();
	Hashtable dstData = new Hashtable();
	ArrayList resultData = new ArrayList();
	

	public ConstraintCompare(XMLNode dbSsource, XMLNode dbDest,
			XMLDatabase dbResult) throws Exception {
		
			if (dbSsource.getType() == XMLNode.XML_SCHEMA
					&& dbDest.getType() == XMLNode.XML_SCHEMA) {
				OwnerA = dbSsource.getName();
			}

			if (dbSsource.getType() == XMLNode.XML_SCHEMA
					&& dbDest.getType() == XMLNode.XML_SCHEMA) {
				OwnerB = dbDest.getName();
			}

			sourcePool = dbSsource.getDatabase().getConnPool();
			destPool = dbDest.getDatabase().getConnPool();
			ResultPool = dbResult.getConnPool();
			sourcedb=dbSsource.getDatabase();
			destdb=dbDest.getDatabase();
			resultdb=dbResult;
	}

	private ResultSet getConstraintData(String owner,XMLDatabase db) {
		ResultSet rs = null;
		String sqlstr = "select " +
				"a.CONSTRAINT_NAME," +
				"a.CONSTRAINT_TYPE," +
				"a.TABLE_NAME," +
				"COLUMN_NAME," +
				"POSITION," +
				"SEARCH_CONDITION, "+
				"a.STATUS," +
				"a.VALIDATED " 
				+ "from dba_constraints a,dba_CONS_COLUMNS b "
				+ "where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME "
				+ "and  a.owner='"
				+ owner
				+ "' "
				+ "and  b.owner='"
				+ owner
				+ "' "
				+ "and a.GENERATED='GENERATED NAME' "
				+ "and constraint_type!='R' "
				+ "order by a.CONSTRAINT_NAME,b.table_name,b.column_name ";

		rs =db.connQuery(sqlstr);
		return rs;

	}

	public void process() throws Exception {
		generateSrcHashtable();
		generateDstHashtable();

		findSameData();
		for (int i = 0; i < resultData.size(); i++) {
			deleteCompareRecord((String)resultData.get(i),resultdb);
		}
	}

	void deleteCompareRecord(String name,XMLDatabase dbResult) throws Exception{
		deleteCompareConstraint(name,dbResult);
		deleteCompareIndex(name,dbResult);
	}
	
	 protected void deleteCompareConstraint(String name,XMLDatabase dbResult) throws Exception{
		JDBCPool cnn=null;
		String sqlstr="delete from compare_constraints where src_constraint_name=? ";
		if(dbResult!=null){
			cnn = dbResult.getConnPool();
			PreparedStatement pstmt=cnn.prepareStatement(sqlstr);
			pstmt.setString(1,name);
			pstmt.executeUpdate();	
			pstmt.close();
		} 
		else return;
				
	
	}

	private void deleteCompareIndex(String name,XMLDatabase dbResult) throws Exception{
		JDBCPool cnn=null;
		String sqlstr="delete from compare_indexes where src_index_name=? ";
		if(dbResult!=null){
			cnn = dbResult.getConnPool();
			PreparedStatement pstmt=cnn.prepareStatement(sqlstr);
			pstmt.setString(1,name);
			pstmt.executeUpdate();	
			pstmt.close();
		} 
		else return;
				
	
	}
	
	protected void findSameData(){
		Enumeration srckeys=srcData.keys();
		while (srckeys.hasMoreElements()) {
			Object key =  srckeys.nextElement();
			Object value=srcData.get(key);
			if(dstData.containsValue(value))
			{
				resultData.add(key);
			}
		}
		
		Enumeration dstkeys=dstData.keys();
		while (dstkeys.hasMoreElements()) {
			Object key =  dstkeys.nextElement();
			Object value=dstData.get(key);
			if(srcData.containsValue(value))
			{
				resultData.add(key);
			}
		}
		
		
	}

	private void generateSrcHashtable() throws Exception {
		ResultSet rs = getConstraintData(OwnerA,sourcedb);
		if (rs == null)
			return;
		String last="";
		String constraintName = "";
		String value = "";
		while (rs.next()) {
			if(!rs.getString(1).equals(last)) value="";
			constraintName=rs.getString(1);
			value += rs.getString(2) + rs.getString(3) + rs.getString(4)
					+ rs.getString(5)+getLong(rs,6)+rs.getString(7) + rs.getString(8) ;
			int hashcode = value.hashCode();
//			System.out.println("constraintName:"+constraintName);
//			System.out.println("value:"+value);
			srcData.put((Object)constraintName,(Object)new Integer(hashcode));
			last=rs.getString(1) ;
		}
		rs.close();
	}
	
	private void generateDstHashtable() throws Exception {
		ResultSet rs = getConstraintData(OwnerB,destdb);
		if (rs == null)
			return;
		String last="";
		String constraintName = "";
		String value = "";
		while (rs.next()) {
			if(!rs.getString(1).equals(last)) value="";
			constraintName=rs.getString(1);
			value += rs.getString(2) + rs.getString(3) + rs.getString(4)
					+ rs.getString(5)+getLong(rs,6)+rs.getString(7) + rs.getString(8);
			int hashcode = value.hashCode();
//			System.out.println("constraintName:"+constraintName);
//			System.out.println("value:"+value);
			dstData.put((Object)constraintName,(Object)new Integer(hashcode));
			last=rs.getString(1) ;
		}
		rs.close();
	}
	
	
	private String getLong(ResultSet rs,int arg) throws Exception{
		String result=null;
		StringBuffer buff=new StringBuffer();

		InputStream s=rs.getBinaryStream(arg);
		if (s==null) {
			result="";
		}else
		{
			BufferedReader br=new BufferedReader(new InputStreamReader(s));
			String line=br.readLine();
			if (line!=null) 
			{buff.append(line);
			line=br.readLine();
			};
			while(line!=null){
				buff.append("/n"+line);
				line=br.readLine();
			}
			result=buff.toString();
			br.close();
			s.close();
			}	
			
		return result;
	}
	
	public static void main(String[] args) {
		
		
	   try {
		   ConstraintCompare cc=new ConstraintCompare(null,null,null);
		   cc.sourcedb.setUrl("jdbc:oracle:thin:@122.136.14.166:1521:ora92");
		   cc.sourcedb.setDbUser("B");
		   cc.sourcedb.setDbPassword("B");
		   cc.OwnerA="B";
			cc.process();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}