xmlpackagebody.java

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

package com.icbcsdc.ddlexp.pub.xml.nodeinfo;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.icbcsdc.ddlexp.pub.connectionpool.JDBCException;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPool;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPoolManager;
import com.icbcsdc.ddlexp.pub.staticLog.Logger;
import com.icbcsdc.ddlexp.ui.CompareFrame;
import com.icbcsdc.ddlexp.ui.UIException;

/**
 * @author zhangyc
 *
 * To change this generated comment edit the template variable "typecomment":
 * Window>Preferences>Java>Templates.
 * To enable and disable the creation of type comments go to
 * Window>Preferences>Java>Code Generation.
 */
public class XMLPackageBody extends XMLNode {
	private String text="";
	//private boolean wrapped=false;

	String sqlPackageBodyPart="select line,TEXT from DBA_SOURCE where TYPE='PACKAGE BODY' and  owner = ? and name =? and line<1002 order by LINE";
	//String sqlPackageBodyAll="select line,TEXT from DBA_SOURCE where TYPE='PACKAGE BODY' and name =? and owner = ? and rownum<? order by LINE";
	String rsComparedPackageBodyLines="SELECT SRC_LINE,ATTR_NAME,SRC_ATTR_VALUE,DST_ATTR_VALUE FROM COMPARE_PACKAGE_BODY_LINES WHERE SRC_OWNER=?  AND SRC_PACKAGE_NAME=? AND ATTR_NAME<>'MYSTYLE'";

	public XMLPackageBody(String packName,String text,boolean wrapped){
		this.name = packName;
		this.text = text;
		this.wrapped = wrapped;
	}
		
	public XMLPackageBody(String packName){
		this.name = packName;
	}
	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getType()
	 */
	public int getType() {
		return XMLNode.XML_PACKAGEBODY;
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#refresh()
	 */
	public void refresh() throws Exception {
		refreshNodeNeedAccessDB();
	}

	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties() throws Exception{
		if(this.getCompareStatus()==XMLNode.SRC_DST_DIFF){
			/**compare_package_lines相关的信息放到容器changedProperties中*/
			JDBCPool cnn=null;

			XMLDatabase db=this.getDatabase();
			if(db!=null) cnn = db.getConnPool();
			else return;

			//再修改比较节点
			PreparedStatement pStmt=cnn.prepareStatement(rsComparedPackageBodyLines);
			pStmt.setString(1,this.getSchema().getName().toUpperCase());
			pStmt.setString(2,this.getName().toUpperCase());
			ResultSet rsUpPackages=pStmt.executeQuery();

			while(rsUpPackages.next()){
				//LPackage package1=(XMLPackage)this.getDirectChildren(XMLNode.XML_PACKAGE,this.getName().toUpperCase());
				this.putChangedProperty(rsUpPackages.getString("SRC_LINE"),rsUpPackages.getString("SRC_ATTR_VALUE"),rsUpPackages.getString("DST_ATTR_VALUE"));				
			}

			rsUpPackages.close();
			pStmt.close();						
		}
	}
	
	/**从数据库中获取数据对象定义*/
	public void refreshDDLInfo() throws Exception{
		JDBCPool cnn=null;
		XMLDatabase db=this.getDatabase();
		if(db!=null) cnn = db.getConnPool();
		else return;

		//获取Function列表
		PreparedStatement pStmt=cnn.prepareStatement(this.sqlPackageBodyPart);
		pStmt.setString(1,this.getSchema().getName());
		pStmt.setString(2,this.getName());
		//pStmt.setInt(3,XMLNode.SOURCE_LINES);
		
		if(this.isWrapped()) return;
		
		this.text="";
		ResultSet rsPackBody=pStmt.executeQuery();
		while(rsPackBody.next()){
			this.text= this.text +rsPackBody.getString(2);
			
			/**
			if(rsPackBody.getInt(1)==1){
				this.text = rsPackBody.getString(2);
				if(rsPackBody.getString(2).indexOf("wrapped") > 0){
					//是否被加密
					this.wrapped = true;
					break;
				}else{
					this.wrapped = false;
				}
			}else if(rsPackBody.getInt(1)<=XMLNode.MAX_SOURCE_LINES){
				this.text= this.text +rsPackBody.getString(2);
			
			}else{
				this.overflow=true;
				break;
			}	
			**/
			//Logger.log(Logger.DEBUG,"XMLPackageBody refreshDDLInfo:"+this.getText());
		}

		//Logger.log(Logger.DEBUG,"XMLPackageBody refreshDDLInfo:"+this.getText());
		rsPackBody.close();
		pStmt.close();			
	}



	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#chkChildType(XMLNode)
	 */
	public boolean chkChildType(XMLNode node) {
		return false;
	}


	/**将DDL语句写入文件
	public void writeComparedSQLToFil(String fileName) throws Exception{
		File scriptFile=new File(fileName);
		writeComparedSQLToFil(scriptFile);	
	}
	*/
	
	/**将DDL语句写入文件,为了防止PACKAGEBODY过大导致内存溢出，必须重新改写该方法
	public void writeComparedSQLToFil(File scriptFile) throws Exception{
		BufferedWriter datIn=new BufferedWriter(new FileWriter(scriptFile,true));
		
		XMLSchema srcSchema = null;
		XMLSchema dstSchema = null;
		try {
			srcSchema = CompareFrame.getInstance().getSelectedSrcXMLSchema();
			dstSchema = CompareFrame.getInstance().getSelectedDstXMLSchema();
		} catch (UIException e) {
			e.printStackTrace();
			return;
		}
		
		XMLNode srcNode = srcSchema.getIndirectChildren(this);
		XMLNode dstNode = dstSchema.getIndirectChildren(this);
		String comparedSQL = "";
		if(srcNode == null || dstNode == null)
			return;

		String name = srcNode.getName().toUpperCase();
		String owner = srcNode.getSchema().name.toUpperCase();
		
		JDBCPool cnn = null;
		XMLDatabase db=(XMLDatabase)dstNode.getDatabase();
		PreparedStatement pStmt=cnn.prepareStatement(sqlPackageBodyAll);
			pStmt.setString(1,dstNode.getName().toUpperCase());
			pStmt.setString(2,dstNode.getSchema().name.toUpperCase());
		ResultSet rs = null;
		if(db!=null) 
			cnn = db.getConnPool();
		else 
			return;
		
		switch(srcNode.getCompareStatus()){
		case(ComparedNode.SRC_EXIST_DST_NULL):
//			drop
			comparedSQL = "DROP "+getDDLType(srcNode.getType())+" "+owner+"."+name+";";
			datIn.write(comparedSQL);
			datIn.newLine();
			break;
		
		case(ComparedNode.SRC_NULL_DST_EXIST):
//			create
			rs=pStmt.executeQuery();
			writeToFile(rs,datIn);			
			rs.close();
			pStmt.close();
			break;
		
		case(ComparedNode.SRC_DST_SAME):
//			Do Nothing
			break;
		
		case(ComparedNode.SRC_DST_DIFF):
//			Drop and Create
			comparedSQL = "DROP "+getDDLType(srcNode.getType())+" "+owner+"."+name+";";
			datIn.write(comparedSQL);
			datIn.newLine();
		
			rs=pStmt.executeQuery();		
			writeToFile(rs,datIn);
			rs.close();
			pStmt.close();
			break;
		
		default:
//			Nothing
			break;		
		}

		datIn.newLine();
		datIn.close();
	}
	 * */


	
	/**将DDL语句写入文件
	public void writeDDLToFil(String fileName) throws Exception{
		File scriptFile=new File(fileName);
		writeDDLToFil(scriptFile);	
	}*/
	
	/**
	 将DDL语句写入文件,为了防止PACKAGEBODY过大导致内存溢出，必须重新改写该方法
	public void writeDDLToFil(File scriptFile) throws Exception{
		BufferedWriter datIn = new BufferedWriter(new FileWriter(scriptFile,true));
		JDBCPool cnn=null;
		XMLDatabase db=(XMLDatabase)this.getDatabase();
		
			
		if(db!=null) 
			cnn = db.getConnPool();
		else 
			return;
		
		Logger.log(Logger.DEBUG,"获取packagebody的内容："+sqlPackageBodyAll);
		PreparedStatement pStmt=cnn.prepareStatement(sqlPackageBodyAll);
			pStmt.setString(1,this.getName().toUpperCase());
			pStmt.setString(2,this.getSchema().getName().toUpperCase());
		ResultSet rs=pStmt.executeQuery();
		
		writeToFile(rs,datIn);
		
		rs.close();
		pStmt.close();
		
		
		datIn.close();
	}
	 */

	
	/**
	 * 将从数据库中读取的结果集写入到脚本文件中
	 * @param rs			传入的结果集
	 * @param scriptFile	脚本文件
	private void writeToFile(ResultSet rs,BufferedWriter datIn) throws Exception{
		try {
			while(rs.next()){
				if(rs.isFirst()){
					datIn.write("CREATE OR REPLACE " + rs.getString(1));
					datIn.newLine();
				}else{
					datIn.write(rs.getString(1));
					datIn.newLine();
				}
			}
			datIn.write("/");
			datIn.newLine();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	 */

	/**
	 * @return Returns the text.
	 */
	public String getText() {
		if(this.overflow){
			return XMLNode.overMaxLines;
		}else
			return text;
	}
	/* (non-Javadoc)
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getSqlDDL()
	 */
	public String getSqlDDL() {
		// TODO Auto-generated method stub
		//return this.getSchemaDDLStr();
		return text;

	}
	/**
	 * @return Returns the wrapped.
	public boolean isWrapped() {
		return wrapped;
	}
	 */

	/**
	 * Sets the wrapped.
	 * @param wrapped The wrapped to set
	public void setWrapped(boolean wrapped) {
		this.wrapped = wrapped;
	}
	 */

}