dbcompare.java

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

/*
 * Created on 2005-4-28
 *
 */
package com.icbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import com.icbc.core.TLException;

/**
 * @author liaomy
 */
public class DbCompare{
	//保存差异结果
	public static StringBuffer scanResult = new StringBuffer("");
	
	private TagElement tagElement;
	//需要比较的下属
	private Vector subComps = new Vector();
	//源数据库url
	private static String srcDBUrl;
	//目标数据库url
	private static String desDBUrl;
	//源数据库用户名
	private static String srcOwner;
	//目标数据库用户名
	private static String desOwner;
	
	private static PreparedStatement srcStat,desStat;
	private static ResultSet srcSet,desSet;
	
	
	public static Connection srcCon,desCon,storageCon;
	
	public DbCompare(){
	}
	
	/**
	 * @param tag
	 */
	public DbCompare(TagElement tag) {
		this.tagElement = tag;
	}
	/*
	 * srcDAO 为源数据库对象
	 * desDAO 为目标数据库对象
	 * storageDAO 为结果存放数据库对象
	 */
    public DbCompare(DAO srcDAO,DAO desDAO,DAO storageDAO) {
        super();
        DbCompare.scanResult = new StringBuffer("");
		DbCompare.desCon = desDAO.getConnection();
		DbCompare.srcCon = srcDAO.getConnection();
		DbCompare.storageCon = storageDAO.getConnection();
		
		DbCompare.desDBUrl = desDAO.getjdbcURL();
		DbCompare.srcDBUrl = srcDAO.getjdbcURL();
    }
	/*
	 * 差异分析操作的根入口
	 * params 为输入参数
	 * 如："ddl,system"
	 */
    public void doCompare(String params) throws Exception{
    	if(params==null)
				throw new TLException("请输入比较参数...");

        for(Enumeration e = subComps.elements();e.hasMoreElements();){
            DbCompare child = (DbCompare) e.nextElement();
            TagElement childTag = child.tagElement;
            Vector inputs = childTag.getInput();
            StringTokenizer stzer = new StringTokenizer(params,"|");
            int count = 0;
            while(stzer.hasMoreTokens()){
            	TagField field = (TagField) inputs.get(count);
            	field.setValue(stzer.nextToken());
            	count++;
            }
			try {
				child.run();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				System.out.println(childTag.getSql());
				System.out.println(childTag.getUpdateSql());
				e1.printStackTrace();
				throw e1;
			} catch (TLException e2) {
				// TODO Auto-generated catch block
				System.out.println(e2.getErrormsg());
				throw e2;
			}
        }
    }

	    /**
	     *
	     */
	
	    private void run() throws SQLException, TLException {
	        if(tagElement==null)
				return;
//			HashMap statMap = 
			executeQuery();
//			ResultSet srcSet = (ResultSet) statMap.get("srcSet");
//			ResultSet desSet = (ResultSet) statMap.get("desSet");
//			while(srcSet.next()){
//				String a = srcSet.getString(1);
//				System.out.println(a);
//				String b = srcSet.getString(2);
//				System.out.println(b);
//				String c = srcSet.getString(3);
//				System.out.println(c);
//				String d = srcSet.getString(4);
//				System.out.println(d);
//				String e = srcSet.getString(5);
//				System.out.println(e);
//				String f = srcSet.getString(6);
//				System.out.println(f);
//			}
//			System.out.println("----------------------------------");
//			while(desSet.next()){
//				String a = desSet.getString(1);
//				System.out.println(a);
//				String b = desSet.getString(2);
//				System.out.println(b);
//				String c = desSet.getString(3);
//				System.out.println(c);
//				String d = desSet.getString(4);
//				System.out.println(d);
//				String e = desSet.getString(5);
//				System.out.println(e);
//				String f = desSet.getString(6);
//				System.out.println(f);
//			}
			
			String nextResultStr = compResultSet(srcSet,desSet);
			srcStat.close();
			desStat.close();
			srcSet.close();
			desSet.close();
			if(!nextResultStr.equals("")&&nextResultStr!=null){
				
			for(Enumeration e = subComps.elements();e.hasMoreElements();){
				if(subComps!=null){
					int i = 0,j = 0;
					DbCompare child = (DbCompare) e.nextElement();
					TagElement childTagEle = child.tagElement;
					Vector childInputs = childTagEle.getInput();
					Vector thisInputs = this.tagElement.getInput();
					Vector thisOutputs = this.tagElement.getOutput();
						
						String name,value;
						for(i=0;i<thisInputs.size();i++){
							TagField thisField = (TagField) thisInputs.get(i);
							if(thisField!=null){
								name = thisField.getName();
								for(j=0;j<childInputs.size();j++){
									TagField childField = (TagField) childInputs.get(j);
									if(name.equals(childField.getName())){
										childInputs.set(i,thisField);
										break;
									}
								}
							}
						}
						
					StringTokenizer stzer = new StringTokenizer(nextResultStr,"|");
					while(stzer.hasMoreTokens()){
						String str = stzer.nextToken();
						if(str!=null&&str.length()>0){
//							TagField childField = (TagField) childInputs.get(i);
//							childField.setValue(str);
//							child.run();
							for(i=0;i<childInputs.size();i++){
								TagField childField = (TagField) childInputs.get(i);
								name = childField.getName();
								if(childField!=null){
									for(j=0;j<thisOutputs.size();j++){
										TagField outField = (TagField) thisOutputs.get(j);
										if(name.equalsIgnoreCase(outField.getName())){
											childField.setValue(str);
											child.run();
											break;
										}
									}
								}
							}
						}
					}
				}
			}
			
			}
			
	    }

/*
 * scope确定差异比较范围
 * 如，*，TABLE(*),1TABLE(2TABCOLUMN2INDEX)1VIEW
 * level 默认为0
 * context 为配置文件对象
 * 
 */
	public void prepareComp(String scope,int level,Context context){
		if(scope==null||scope.length()==0)
			return;
		level++;
		HashMap map= context.getChildren();
		Iterator cursor = map.keySet().iterator();
		
		if(scope.equals("*")){
			for(;cursor.hasNext();){
				String key = (String) cursor.next();
				Context childCtx = (Context) map.get(key);
				TagElement tag = (TagElement) childCtx.getTagElement();
				DbCompare childComp = new DbCompare(tag);
				subComps.add(childComp);
				childComp.prepareComp("*",level,childCtx);
			}
		}
		
		else{
			StringTokenizer strzer = new StringTokenizer(scope,Integer.toString(level));
			String subStr,compStr,subCompStr;
			while(strzer.hasMoreTokens()){
				subStr = strzer.nextToken();
				int pos1 = subStr.indexOf("(");
				if(pos1>0){
					int pos2 = subStr.lastIndexOf(")");
					compStr = subStr.substring(0,pos1);
					subCompStr = subStr.substring(pos1+1,pos2);
				}else{
					compStr = subStr;
					subCompStr = "";
				}
            
            	try {
					Context childCtx = (Context) map.get(compStr);
					if(childCtx!=null&&childCtx.getName().equals(compStr)){
						TagElement tag = (TagElement) childCtx.getTagElement();
						DbCompare childComp = new DbCompare(tag);
						subComps.add(childComp);
						childComp.prepareComp(subCompStr,level,childCtx);
					}
				} catch (Exception e) {
					e.printStackTrace();
            	}
			}
		}
	}

//	public TagElement findTagElement(Context context){
//		TagElement tag = null;
//		if(context.getName().equals(tagName)){
//			tag = (TagElement) context.getTagElement();
//			return tag;
//		}else{
//			HashMap ctxMap = context.getChildren();
//			Iterator cursor = ctxMap.keySet().iterator();
//			for(;cursor.hasNext();){
//				String key = (String) cursor.next();
//				Context childCtx = (Context) ctxMap.get(key);
//				tag = findTagElement(childCtx);
//				if(tag!=null)
//					break;
//			}
//			return tag;
//		}
//		
//	}
	/*
	 * 执行配置文件中的查询语句，并返回包含resultSet的HashMap
	 */
	private void executeQuery() throws SQLException, TLException{
		String sqlStr = tagElement.getSql();
		Vector inputs = tagElement.getInput();
		
		if(getSrcConnection()==null||getDesConnection()==null){
			throw new TLException("空数据库连接！");
		}
		srcStat = getSrcConnection().prepareStatement(sqlStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
		desStat = getDesConnection().prepareStatement(sqlStr,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
		
		for(int i=0;i<inputs.size();i++){
			TagField tagField = (TagField) inputs.get(i);
			String type,name,value;
			if(tagField!=null){
				type = tagField.getType();
				name = tagField.getName();
				value = tagField.getValue();
				if(type.equalsIgnoreCase("string")||type.equalsIgnoreCase("")){
					srcStat.setString(i+1,value);
					desStat.setString(i+1,value);
					if(name.equals("owner")){
						int pos = value.indexOf(",");
						srcOwner = value.substring(0,pos);
						desOwner = value.substring(pos+1,value.length());
						srcStat.setString(i+1,srcOwner);
						desStat.setString(i+1,desOwner);
					}
				}
					
				else if(type.equalsIgnoreCase("int")){
					srcStat.setInt(i+1,Integer.parseInt(value));
					desStat.setInt(i+1,Integer.parseInt(value));
				}
				
			}
				
		}
		srcSet = srcStat.executeQuery();
		desSet = desStat.executeQuery();

//		HashMap statMap = new HashMap(2);
//		statMap.put("srcSet",srcSet);
//		statMap.put("desSet",desSet); 
//		return statMap;
		
	}
	
	private Connection getSrcConnection(){
		return srcCon;
	}
	
	private Connection getDesConnection(){
		return desCon;
	}
	
	/**
	 *
	 *
	 */

	private String compResultSet(ResultSet srcSet, ResultSet desSet) throws SQLException, TLException{
		Vector outputs = tagElement.getOutput();
		Vector inputs = tagElement.getInput();
		String inputPath = "";
		String resultStr = "";
		String prefix = "";
		String MYSTYLE = "SAME";
		for(int i=0;i<inputs.size();i++){
			TagField inputField = (TagField) inputs.get(i);
			if(inputField!=null){
				String name = inputField.getName();
				String value = inputField.getValue();
				if(name.equalsIgnoreCase("owner")){
					prefix = prefix + "srcOwner:" + srcOwner
						   + "    desOwner:" + desOwner + "    ";
				}else{
					prefix = prefix + name
					+ ":" + value + "    ";
				}
			}
		}
		prefix+="\n";
		boolean isSrcNull = false,
		isDesNull = false,
		moveSrc = true,
		moveDes = true;

		while(true){
			if(srcSet.isLast()&&moveDes==false){
				moveDes = true;
				desSet.previous();
			}
			if(desSet.isLast()&&moveSrc==false){
				moveSrc = true;
				srcSet.previous();
			}