foreignkeycompare.java

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

/*
 * Created on 2005-8-22
 *
 * 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.sql.ResultSet;

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 ForeignKeyCompare extends ConstraintCompare {

	/**
	 * @param dbSsource
	 * @param dbDest
	 * @param dbResult
	 * @throws Exception
	 */
	public ForeignKeyCompare(XMLNode dbSsource, XMLNode dbDest, XMLDatabase dbResult) throws Exception {
		super(dbSsource, dbDest, dbResult);
		
	}
	
	private ResultSet getConstraintData(String owner,XMLDatabase db) {
		ResultSet rs = null;
		String sqlstr = "select " +
				"dba_constraints.constraint_name," +
				"reference_table.table_name as reference_table," +
				"reference_table.column_name as reference_column_name," +
				"reference_table.position as reference_position," +
//				"dba_constraints.r_owner," +
				"primary_table.table_name as primary_table,"+
				"primary_table.column_name as primary_column_name," +
				"primary_table.position as primary_position ," +
				"dba_constraints.status,"+
				"dba_constraints.validated "
				+ "from dba_cons_columns primary_table,"
				+       "dba_cons_columns reference_table, "
                +       "dba_constraints "
				+ "where dba_constraints.constraint_type='R' "
				+ "and  dba_constraints.r_constraint_name=primary_table.constraint_name "
				+ "and  dba_constraints.constraint_name=reference_table.constraint_name "
				+ "and dba_constraints.generated='GENERATED NAME' "
				+ "and dba_constraints.owner='"
				+ owner
				+ "' "
				+ "order by dba_constraints.constraint_name,reference_table.table_name,primary_table.position";

		rs =db.connQuery(sqlstr);
		System.out.println("sqlstr:"+sqlstr);
		return rs;

	}

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

		findSameData();
		System.out.println("resultData.size():"+resultData.size());
		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);
		
	}
	
	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)
					+ rs.getString(6)+rs.getString(7)+rs.getString(8) + rs.getString(9) ;
			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)
			+ rs.getString(6)+rs.getString(7)+rs.getString(8) + rs.getString(9) ;
			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();
	}
	public static void main(String[] args) {
		
		try {
			   ForeignKeyCompare cc=new ForeignKeyCompare(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="A";
				cc.process();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}