ORACLE 批量生成表，类似mysql的show create table

最近有个需求，需要将1-200张表上传给第三方，每天定时抽取前一天的数据，生成TXT文件上传到FTP上，还需要将建表语句给他们。 。

oracle没有show create table能够直接显示建表语句，有个

dbms_metadata.get_ddl('TABLE','TABLE_NAME') 这个存过能生成，但是里面还有表空间等信息，还有些多余的信息，似乎不太满足，类似这样

 于是重新用java编写了下。

废话不多说，直接上代码

package com.asiainfo.crm.ftp.util;

import java.sql.Connection;
import java.util.Hashtable;
import java.util.List;

public class ShowCreateTable extends DBUtils{
	
	public static final String password = "xxx";// test#1234   ct04_a1234
	public static final String username = "xxxx";
	public static final String url = "jdbc:oracle:thin:@ip:1621:xxx";
 	public static final String driver = "oracle.jdbc.driver.OracleDriver";


	public static void main(String[] args) throws Exception {
		ShowCreateTable t = new ShowCreateTable();
		
		Connection conn = t.getConnection(driver,url,username,password);
 
		String sql = t.createTableSql(conn, "owner", "tablename");
		System.out.println(sql);
		t.closeConn(conn);
	}
	
	private  String createTableSql(Connection conn,String owner,String tablename) throws Exception{
		StringBuffer sb = new StringBuffer("create table ");
		sb.append(owner).append(".").append(tablename);
		sb.append("(");
		sb.append("\r");
//查询表所有列，按照顺序生成
		String sql = "SELECT * FROM DBA_TAB_COLS R  WHERE r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"' ORDER BY R.COLUMN_ID";
        List<Hashtable<String, String>> list = this.getResultBySQL(conn, sql, null);		
        int index = 0;
        for(Hashtable<String, String> h : list){
        	sb.append(h.get("COLUMN_NAME")).append(" ").append(h.get("DATA_TYPE"));
        	if(h.get("DATA_TYPE").equals("DATE") || h.get("DATA_TYPE").equals("TIMESTAMP")){
        		
        	}else if(h.get("DATA_TYPE").equals("NUMBER")){
        		sb.append("(").append(h.get("DATA_PRECISION")).append(")");
        	}else if(h.get("DATA_TYPE").equals("VARCHAR")||h.get("DATA_TYPE").equals("CHAR") || h.get("DATA_TYPE").equals("VARCHAR2")){
        		sb.append("(").append(h.get("DATA_LENGTH")).append(")");
        	}
        	
        	if(h.get("NULLABLE").equals("N")){
        		sb.append(" not null ");
        	}
        	index++;
        	if(index<list.size()){
        		sb.append(",");
        	}
        	sb.append("\r");
        }
		sb.append(");");
		sb.append("\r");
		

//查询表所有的comment
		sql = "SELECT COMMENTS FROM DBA_TAB_COMMENTS R  WHERE r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"'";
		List<String> comments = this.getListStrBySQL(conn, sql, null);
		if(comments!=null && comments.size()>0){
			sb.append(" COMMENT ON TABLE ").append(owner).append(".").append(tablename).append(" is '").append(comments.get(0)).append("'");
			sb.append(";");
			sb.append("\r");
		}
		
//查询列的comment
		sql = "SELECT COLUMN_NAME,comments FROM dba_col_comments R  WHERE  r.TABLE_NAME = '"+tablename+"' AND R.OWNER='"+owner+"'";
		List<Hashtable<String, String>> colComments = this.getResultBySQL(conn, sql, null);
		if(colComments!=null && colComments.size()>0){
			for(int i=0;i<colComments.size();i++){
				Hashtable<String, String> h = colComments.get(i);
				sb.append(" COMMENT ON COLUMN ").append(owner).append(".").append(tablename).append(".").append(h.get("COLUMN_NAME")).append(" is '").append(h.get("COMMENTS")).append("'");
				sb.append(";");
				sb.append("\r");
			}
			
		}
		
//生成索引
		sql = "select index_name,COLUMN_NAME FROM dba_ind_columns  r  WHERE r.TABLE_NAME = '"+tablename+"' AND R.TABLE_OWNER='"+owner+"'";
		List<Hashtable<String, String>> indexList = this.getResultBySQL(conn, sql, null);
		if(indexList!=null && indexList.size()>0){
			for(int i=0;i<indexList.size();i++){
				Hashtable<String, String> h = indexList.get(i);
				if(!h.get("INDEX_NAME").startsWith("PK_")){
					sb.append("create index ").append(owner).append(".").append(h.get("INDEX_NAME")).append(" ON ").append(owner).append(".").append(tablename).append("(").append(h.get("COLUMN_NAME")).append(");");
					sb.append("\r");
				} 
			}
		}
		
		//生成主键
		sql = "select   a.constraint_name,a.column_name from DBA_cons_columns a, DBA_constraints b where a.constraint_name = b.constraint_name and A.table_name = B.table_name AND A.owner = B.owner AND   b.constraint_type = 'P' and a.table_name = '"+tablename+"' AND A.owner = '"+owner+"' ORDER BY a.position";
		List<Hashtable<String, String>> pkList = this.getResultBySQL(conn, sql, null);
		if(pkList!=null && pkList.size()>0){
			String str = "";
			String constraintName = "";
			for(int i=0;i<pkList.size();i++){
				Hashtable<String, String> h = pkList.get(i);
				if(i==pkList.size()-1){
					 str += h.get("COLUMN_NAME");
				}else{
					 str += h.get("COLUMN_NAME")+",";
				}
				constraintName = h.get("CONSTRAINT_NAME");
			}
			sb.append("alter table ").append(owner).append(".").append(tablename).append(" add constraint ").append(constraintName).append(" primary key  (").append(str).append(") ;");
			sb.append("\r");
		}
		return sb.toString();
	}
	
	 //外键没有生成，懒得生成了
}

最终生成的效果就比较清爽，包含了主键，索引，comment，非空等信息