lobindexcompare.java

来自「导出ORACLE数据库对象DDL语句的程序」· Java 代码 · 共 221 行

/*
 * 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.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 LobIndexCompare {
	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 LobIndexCompare(XMLNode dbSsource, XMLNode dbDest,
			XMLDatabase dbResult) {
		
	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();
			}

			try {
				sourcePool = dbSsource.getDatabase().getConnPool();
				destPool = dbDest.getDatabase().getConnPool();
				ResultPool = dbResult.getConnPool();
				sourcedb=dbSsource.getDatabase();
				destdb=dbDest.getDatabase();
				resultdb=dbResult;
			} catch (Exception e) {
				e.printStackTrace();
			}
	}

	private ResultSet getLobIndexData(String owner,XMLDatabase db) {
		ResultSet rs = null;
		String sqlstr = "select " +
				"a.INDEX_NAME," +
				"a.INDEX_TYPE," +
				"a.TABLE_NAME," +
				"COLUMN_NAME," +
				"POSITION," +
				"PARTITIONED, "+
				"a.STATUS " 
				+ "from dba_indexes a,DBA_IND_COLUMNS b "
				+ "where a.INDEX_NAME=b.INDEX_NAME "
				+ "and  a.owner='"
				+ owner
				+ "' "
				+ "and  b.owner='"
				+ owner
				+ "' "
				+ "and a.GENERATED='Y' "
				+ "and a.INDEX_TYPE='R' "
				+ "order by a.INDEX_NAME,b.table_name,b.column_name ";
		try {
			rs =db.connQuery(sqlstr);
		} catch (Exception e) {
			System.out.println("Exception sqlstr:" + sqlstr);
			e.printStackTrace();
		}
		return rs;

	}

	public void process() {
		try {
			generateSrcHashtable();
			generateDstHashtable();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		findSameData();
		for (int i = 0; i < resultData.size(); i++) {
			deleteCompareRecord((String)resultData.get(i),resultdb);
		}
	}

	void deleteCompareRecord(String name,XMLDatabase dbResult){

		deleteCompareIndex(name,dbResult);
	}
	
	

	private void deleteCompareIndex(String name,XMLDatabase dbResult){
		JDBCPool cnn=null;
		String sqlstr="delete from compare_indexes where src_index_name=? ";
		if(dbResult!=null){
			try {
				cnn = dbResult.getConnPool();
				PreparedStatement pstmt=cnn.prepareStatement(sqlstr);
				pstmt.setString(1,name);
				pstmt.executeUpdate();	
				pstmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}		
		} 
		else return;
				
	
	}
	
	private 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 SQLException {
		ResultSet rs = getLobIndexData(OwnerA,sourcedb);
		if (rs == null)
			return;
		String last="";
		String indexName = "";
		String value = "";
		while (rs.next()) {
			if(!rs.getString(1).equals(last)) value="";
			indexName=rs.getString(1);
			value += rs.getString(2) + rs.getString(3) + rs.getString(4)
					+ rs.getString(5)+rs.getString(6)+rs.getString(7)  ;
			int hashcode = value.hashCode();
//			System.out.println("constraintName:"+constraintName);
//			System.out.println("value:"+value);
			srcData.put((Object)indexName,(Object)new Integer(hashcode));
			last=rs.getString(1) ;
		}
		rs.close();
	}
	
	private void generateDstHashtable() throws SQLException {
		ResultSet rs = getLobIndexData(OwnerB,destdb);
		if (rs == null)
			return;
		String last="";
		String indexName = "";
		String value = "";
		while (rs.next()) {
			if(!rs.getString(1).equals(last)) value="";
			indexName=rs.getString(1);
			value += rs.getString(2) + rs.getString(3) + rs.getString(4)
					+ rs.getString(5)+rs.getString(6)+rs.getString(7) ;
			int hashcode = value.hashCode();
//			System.out.println("constraintName:"+constraintName);
//			System.out.println("value:"+value);
			dstData.put((Object)indexName,(Object)new Integer(hashcode));
			last=rs.getString(1) ;
		}
		rs.close();
	}
	
	
	
	
	public static void main(String[] args) {
		
		
		   LobIndexCompare cc=new LobIndexCompare(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";
		   try {
			cc.process();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}