xmltable.java

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

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import sun.security.krb5.internal.crypto.e;

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 XMLTable extends XMLNode {

	String tableSpace=null;

	String sqlTabColumn="select column_name,data_type,nullable,data_length,data_default,data_precision from dba_tab_columns where owner=? and table_name=?";
	ArrayList columns=new ArrayList();

	String sqlTabComment = "select comments from DBA_TAB_COMMENTS where owner=? and table_name=? and table_type='TABLE'";
	
	String sqlCompareTabColumns="SELECT COUNT(*) FROM COMPARE_TAB_COLUMNS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=?";
	String sqlCompareIndexes="SELECT COUNT(*) FROM COMPARE_INDEXES WHERE SRC_INDEX_OWNER=? AND SRC_TABLE_NAME=?";
	String sqlCompareTriggers="SELECT COUNT(*) FROM COMPARE_TRIGGERS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=?";
	String sqlCompareConstraints="SELECT COUNT(*) FROM COMPARE_CONSTRAINTS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=?";
	String sqlCompareTablecomments="SELECT COUNT(*) FROM COMPARE_TABLECOMMENTS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=?";
	String sqlCompareColumncomments="SELECT COUNT(*) FROM COMPARE_COLUMNCOMMENTS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=?";

	public XMLTable(String tableName,String tbs) throws NodeException {
		super();
		//this.tableName=tableName;
		this.name=tableName;
		this.tableSpace=tbs;
		
	}
	
	public XMLTable(String tableName) throws NodeException {
		super();
		this.name=tableName;
		
	}
	
	/**
	 * @see com.mysqlecc.pub.xml.XmlProcess.XMLNode#getType()
	 */
	public int getType() {
		return XMLNode.XML_TABLE;
	}

	/**
	 * @see com.mysqlecc.pub.xml.XmlProcess.XMLNode#refresh()
	 */
	public void refreshDDLInfo() throws Exception {
		JDBCPool cnn=null;
		this.columns.clear();
		
		XMLDatabase db=this.getDatabase();
		if(db!=null) 
			cnn = db.getConnPool();
		else return;

//			获取表的注释
		PreparedStatement pStmtComm=cnn.prepareStatement(this.sqlTabComment);
		pStmtComm.setString(1,this.getSchema().name.toUpperCase());
		pStmtComm.setString(2,this.getName().toUpperCase());
		ResultSet rsComment=pStmtComm.executeQuery();
		String comment = "";
		while(rsComment.next()){
			comment = rsComment.getString(1);
		}			
		pStmtComm.close();
		rsComment.close();
		if(comment == null || comment.length() == 0)
			comment="";

		this.addChild(new XMLTabColumns());
		this.addChild(new XMLTriggers());
		this.addChild(new XMLConstraints());
		this.addChild(new XMLIndexes());
		this.addChild(new XMLTabComment(comment));

		PreparedStatement pStmt=cnn.prepareStatement(this.sqlTabColumn);
		pStmt.setString(1,this.getParent().getParent().getName());
		pStmt.setString(2,this.getName());
		ResultSet rsColumns=pStmt.executeQuery();
		
		while(rsColumns.next()){
			XMLTabColumn column=new XMLTabColumn(rsColumns.getString(1),
									rsColumns.getString(2),
									rsColumns.getString(3),
									rsColumns.getString(4),
									rsColumns.getString(5),
									rsColumns.getString(6));
			this.columns.add(column);
		}
		
		pStmt.close();
		rsColumns.close();


		//super.refresh();
	}
	
	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties() throws Exception{

		if(hasTabColumns()){
			XMLTabColumns tabColumns=new XMLTabColumns();
			tabColumns.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(tabColumns);
		}
		
		if(hasIndexes()){
			XMLIndexes indexes=new XMLIndexes();
			indexes.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(indexes);
		}
		
		if(hasTriggers()){
			XMLTriggers triggers=new XMLTriggers();
			triggers.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(triggers);
		}
		
		if(hasConstraints()){
			XMLConstraints constraints=new XMLConstraints();
			constraints.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(constraints);
		}
		
		if(hasTablecomments()){
			XMLTabComment tablecomments=new XMLTabComment();
			tablecomments.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(tablecomments);
		}

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

	public ArrayList getColumns(){
		return this.columns;
	}

	/**
	 * Manager类型的节点下面只能添加Database类型的节点
	 * */
	public boolean chkChildType(XMLNode node){
		if(node.getType()==XMLNode.XML_INDEXES) return true;
		if(node.getType()==XMLNode.XML_CONSTRAINTS) return true;
		if(node.getType()==XMLNode.XML_TABCOLUMNS) return true;
		if(node.getType()==XMLNode.XML_TABCOMMENT) return true;
		if(node.getType()==XMLNode.XML_TRIGGERS) return true;
		//if(node.getType()==XMLNode.XML_TABCOLCOMMENT) return true;

		return false;
	}

	/**
	 * Returns the 返回定义导出语句.
	 * @return String
	 */
	public String getSqlDDL(){
		return this.getSchemaDDLStr();
	}
	
	public String getTablespace(){
		return this.tableSpace;
	}



	/**
	 * @return Returns the tableSpace.
	 */
	public String getTableSpace() {
		return tableSpace;
	}
	/**
	 * @param tableSpace The tableSpace to set.
	 */
	public void setTableSpace(String tableSpace) {
		this.tableSpace = tableSpace;
	}
	public static void main(String[] args) {
	}

	boolean hasTabColumns(){
		return hasObjects(sqlCompareTabColumns);
	}
	
	boolean hasIndexes(){
		return hasObjects(sqlCompareIndexes);
	}
	
	boolean hasTriggers(){
		return hasObjects(sqlCompareTriggers);
	}
	
	boolean hasConstraints(){
		return hasObjects(sqlCompareConstraints);
	}
	boolean hasTablecomments(){
		return hasObjects(sqlCompareTablecomments);
	}
	boolean hasColumncomments(){
		return hasObjects(sqlCompareColumncomments);
	}
	
	boolean hasObjects(String sql){
		boolean result=false;
		
		JDBCPool cnn=null;
		try {
			XMLDatabase db=this.getDatabase();
			if(JDBCPoolManager.getInstance().isDebug()) 
				System.out.println(this.getClass().toString());
				
			if(db!=null) cnn = db.getConnPool();
			else return false;

			PreparedStatement pStmt=cnn.prepareStatement(sql);
			pStmt.setString(1,this.getSchema().getName());
			pStmt.setString(2,this.getName());
			ResultSet rs=pStmt.executeQuery();
			if(rs.next()&&rs.getLong(1)>0) result=true;
			else result=false;
			
			rs.close();
			pStmt.close();

			return result;				
		} catch (Exception e) {
			Logger.log(Logger.ERROR,e.getMessage());
			return false;
		}
	}
	

	public String getAlterSQL() throws Exception{
		String sqlstr=null;
		Logger.log(Logger.INFO,"alterTableProperties:"+alterTableProperties());
		Logger.log(Logger.INFO,"columnClauses:"+columnClauses());
		Logger.log(Logger.INFO,"constraintClauses:"+constraintClauses());
		Logger.log(Logger.INFO,"alterTablePartitioning:"+alterTablePartitioning());
		Logger.log(Logger.INFO,"alterExternalTableClauses:"+alterExternalTableClauses());
		Logger.log(Logger.INFO,"enableDisableClause:"+enableDisableClause());
		sqlstr=	alterTableProperties()+
				columnClauses()+
				constraintClauses()+
				alterTablePartitioning()+
				alterExternalTableClauses()+
				enableDisableClause();
		
		sqlstr=sqlstr+lockClause();
		if(!sqlstr.trim().equals("")) 
			sqlstr="ALTER TABLE "+this.getSchema().getName()+"."+this.getName()+"\n"
					+sqlstr;
		return sqlstr;
	}
	
	String alterTableProperties(){
		String alterTableProperties=physicalAttributeClause()+
								loggingClause()+
								dataSegmentCompression()+
								supplementalLgGrpClauses()+
								allocateExtentClause()+
								deallocateUnusedClause()+
								cacheClause()+
								monitoringClause()+
								upgradeTableClause()+
								recordsPerBlockClause()+
								parallelClause()+
								rowMovementClause();
									
		return alterTableProperties;
	}
	
	String physicalAttributeClause(){
		String pctfree=this.getChangedPropertyNewValue("PCT_FREE");
		if(pctfree==null)
			pctfree="";
		else
			pctfree="PCTFREE "+pctfree+"\n";	
		
		String pctused=this.getChangedPropertyNewValue("PCT_USED");	
		if(pctused==null)
			pctused="";
		else