xmltabcolumn.java

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

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;

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

/**
 * @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 XMLTabColumn extends XMLNode{
	String sqlCompareColumncomments="SELECT COUNT(*) FROM COMPARE_COLUMNCOMMENTS WHERE SRC_OWNER=? AND SRC_TABLE_NAME=? AND SRC_COLUMN_NAME=?";
	
	
	private String DataType=null;
	private String isNotNull=null;
	private String dataLength=null;
	private String dataDefault=null;
	private String dataPrecision=null;
	
	private String sqlComment = "select comments from DBA_COL_COMMENTS where owner=? and table_name=? and column_name=?";
	
	/**
	 * Constructor for OracleDatFile.
	 */
	public XMLTabColumn() {
		super();
	}

	/**
	 * Constructor for OracleDatFile.
	 */
	public XMLTabColumn(String cName,String dataType,String isnotnull) {
		super();
		this.name=cName;
		this.DataType=dataType;
		this.isNotNull=isnotnull;
	}

	/**
	 * Constructor for OracleDatFile.
	 */
	public XMLTabColumn(String cName,String dataType,String isnotnull,String length,String defaultValue,String precision) {
		super();
		
		this.name=cName;
		this.DataType=dataType;
		this.isNotNull=isnotnull;
		this.dataLength=length;
		this.dataDefault=defaultValue;
		this.dataPrecision=precision;
	}

	/**
	 * Constructor for OracleDatFile.
	 */
	public XMLTabColumn(String cName) {
		super();		
		this.name=cName;
	}

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

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


	/**从数据库中获取数据对象比较后的结果*/
	public void refreshChangedProperties() throws Exception{
		if(hasColumncomments()){
			XMLTabColComment columncomments=new XMLTabColComment();
			columncomments.setCompareStatus(ComparedNode.COMPARED);
			this.addChild(columncomments);
		}
			

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

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

			this.addChild(new XMLTabColComment(comment));
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * @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#getSqlDDL()
	 */
	public String getSqlDDL() {
		return null;
	}
	
	public String toSQLString(){
		String result="";
		if(this.getCompareStatus()==XMLNode.NO_COMPARED){
			
			String type=this.getDataType();
			if(this.dataLength!=null&&this.dataPrecision==null)
				type=type+"("+this.dataLength+")";
			else if(this.dataLength!=null&&this.dataPrecision!=null)	
				type=type+"("+this.dataLength+","+this.dataPrecision+")";

			result=result+this.getName()+" "+type+" ";
			
			if(this.dataDefault!=null)
			result=result+"DEFAULT "+this.dataDefault+" ";
			
			if(this.isNotNull!=null&&this.isNotNull.equals("N")) 
				result=result+"NOT NULL ";
		}

		return result;
	}

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


	/**
	 * Returns the fileSize.
	 * @return long
	 */
	public String getDataType() {
		return DataType;
	}

	/**
	 * @return Returns the isNotNull.
	 */
	public String getIsNotNull() {
		return isNotNull;
	}

	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.getParent().getParent().getName());
			pStmt.setString(3,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;
		}
	}	



}