xmlprocedures.java

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

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

import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.icbcsdc.ddlexp.pub.staticLog.Logger;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCCnn;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPool;
import com.icbcsdc.ddlexp.pub.connectionpool.JDBCPoolManager;

/**
 * @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 XMLProcedures extends XMLNode {
	public static String NAME="Procedures";
//	String sqlProcedures = "select name,text from dba_SOURCE where type='PROCEDURE' and owner=? order by name";	
//	String sqlProcedures = "select name,line,text from DBA_SOURCE where type='PROCEDURE' and owner=? order by name,line";
	String sqlProcedures = "select name, MAX(LINE)  from DBA_SOURCE where type='PROCEDURE' and owner=? group by name";
	String sqlProceduresWrapped = "select name from DBA_SOURCE where type='PROCEDURE' and line=1 and owner=? and text like '% wrapped%'";
	
	String crComparedProcedures="SELECT SRC_PROCEDURE_NAME,ATTR_NAME,SRC_ATTR_VALUE,DST_PROCEDURE_NAME FROM COMPARE_PROCEDURES WHERE SRC_OWNER=? AND  ATTR_NAME='MYSTYLE'";
	String upComparedProcedures="SELECT SRC_PROCEDURE_NAME,ATTR_NAME,SRC_ATTR_VALUE,DST_ATTR_VALUE FROM COMPARE_PROCEDURES WHERE SRC_OWNER=?  AND ATTR_NAME<>'MYSTYLE'";

	private String text = "";
	private String funName = "";
	//private boolean wrapped = false;

	/**
	 * Constructor for XMLManager.
	 */
	public XMLProcedures() {
		super();
		this.name=NAME;
		this.setParent(null);
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getType()
	 */
	public int getType() {
		return XMLNode.XML_PROCEDURES;
	}

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#refresh()
	 */
	public void refreshDDLInfo() throws Exception{
		//this.clear();
		JDBCPool cnn=null;
		//try {
		XMLDatabase db=this.getDatabase();
		if(db!=null) 
			cnn = db.getConnPool();
		else return;
		
		/**
		if(db.getVersion() == XMLDatabase.VERSION_ORACLE8I){
			String sqlGenDDL = "BEGIN GEN_PROC_DDL(?); END;";
			CallableStatement cStmt = cnn.prepareCall(sqlGenDDL);
			cStmt.setString(1,this.getSchema().name);
			cStmt.execute();
		}**/

		//获取Procedure列表
		PreparedStatement pStmt=cnn.prepareStatement(this.sqlProcedures);
		pStmt.setString(1,this.getParent().getParent().getName().toUpperCase());
		ResultSet rsProcedures=pStmt.executeQuery();
		while(rsProcedures.next()){
			XMLProcedure procedure=new XMLProcedure(rsProcedures.getString(1).trim());
			if(rsProcedures.getInt(2)>XMLNode.MAX_SOURCE_LINES)
				procedure.setOverflow(true);
			this.addChild(procedure);
		}
		pStmt.close();
		rsProcedures.close();

		PreparedStatement pStmt1=cnn.prepareStatement(this.sqlProceduresWrapped);
		pStmt1.setString(1,this.getParent().getParent().getName().toUpperCase());
		ResultSet rsProceduresWrapped=pStmt1.executeQuery();
		while(rsProceduresWrapped.next()){
			XMLProcedure procedure=(XMLProcedure)this.getDirectChildren(XMLNode.XML_PROCEDURE,rsProceduresWrapped.getString(1).trim());
			procedure.setWrapped(true);
		}
		pStmt1.close();
		rsProceduresWrapped.close();
	}

	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties()throws Exception{
		JDBCPool cnn=null;
		try {
			//XMLManager.getInstance().
			XMLDatabase db=this.getDatabase();
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return;

			//先创建比较节点
			PreparedStatement pStmt=cnn.prepareStatement(crComparedProcedures);
			pStmt.setString(1,this.getSchema().getName().toUpperCase());
			//pStmt.setString(2,this.getParent().getName().toUpperCase());
			ResultSet rsCrProcedures=pStmt.executeQuery();
			
			while(rsCrProcedures.next()){
				XMLProcedure procedure=null;
				byte status=XMLNode.toCompareStatus(rsCrProcedures.getString("SRC_ATTR_VALUE"));
				if(status==XMLNode.SRC_NULL_DST_EXIST)
					procedure=new XMLProcedure(rsCrProcedures.getString("DST_PROCEDURE_NAME"));
				else
					procedure=new XMLProcedure(rsCrProcedures.getString("SRC_PROCEDURE_NAME"));

				procedure.setCompareStatus(status);
				this.addChild(procedure);
			}
			rsCrProcedures.close();

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

			while(rsUpProcedures.next()){
				XMLProcedure procedure1=(XMLProcedure)this.getDirectChildren(XMLNode.XML_PROCEDURE,rsUpProcedures.getString("SRC_PROCEDURE_NAME"));
				procedure1.putChangedProperty(rsUpProcedures.getString("ATTR_NAME"),rsUpProcedures.getString("SRC_ATTR_VALUE"),rsUpProcedures.getString("DST_ATTR_VALUE"));				
			}

			rsUpProcedures.close();
			pStmt.close();						
		} catch (Exception e) {
			e.printStackTrace();
			Logger.log(Logger.ERROR,e.getMessage());
		}		
	}
	

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

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

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getXmlStream()
	public InputStream getXmlStream() {
		return null;
	}
	 */

	/**
	 * @see com.icbcsdc.ddlexp.pub.xml.nodeinfo.XMLNode#getSqlDDL()
	 */
	public String getSqlDDL() {
		return null;
	}

	public static void main(String[] args) {
	}
}