Java之生成Oracle中用户定义的：函数,过程,包,触发器,并生成SQL文件

Oracle中查看用户定义的源码表名：[color=red]user_source[/color]
[b]在项目部署过程中,可能要执行sql文件,通常的方式为导出DMP,导入DMP文件解决,在有时也需要用到部署SQL文件,以下这个就为生成SQL文件的工具类,以后就方便多了呀![/b]
具体代码如下：

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * 获取Oracle中用户定义的：函数,过程,包,触发器,并生成文件.sql
 * @author liuzd
 * @version 1.5 
 * */
public class CreateUserSQLUtil {	

	/**
	 * 默认生成文件路径为C盘
	 * */
	private String sqlFilePath = "c:/";

	/**
	 * 默认生成.sql文件名为：用户名.sql
	 * */
	private String sqlFileName = DBConnection.newInstance().getUsername();

	/**
	 * 默认间距为三Tab
	 * */
	public static final String DEFAULTTAB = "			";

	public CreateUserSQLSource(){

	}
	public CreateUserSQLSource(String sqlFileName){
		this.sqlFileName = sqlFileName;
	}

	public CreateUserSQLSource(String sqlFilePath,String sqlFileName){
		this(sqlFileName);
		this.sqlFilePath = sqlFilePath;		
	}	

	/**
	 * 系统表:user_source各列名
	 * */
	public static final String USERSOURCE_NAME = "NAME";
	public static final String USERSOURCE_TYPE = "TYPE";
	public static final String USERSOURCE_LINE = "LINE";
	public static final String USERSOURCE_TEXT = "TEXT";

	/**
	 * 查询用户资源SQL
	 * */
	public static final String SELECTUSERSOURCE = "select *From user_source";


	class UserSource{

		private String name;
		private String tyep;
		private Integer line;
		private String text;
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getTyep() {
			return tyep;
		}
		public void setTyep(String tyep) {
			this.tyep = tyep;
		}
		public Integer getLine() {
			return line;
		}
		public void setLine(Integer line) {
			this.line = line;
		}
		public String getText() {
			return text;
		}
		public void setText(String text) {
			this.text = text;
		}
		public UserSource() {

		}
		public UserSource(String name, String tyep, Integer line, String text) {
			super();
			this.name = name;
			this.tyep = tyep;
			this.line = line;
			this.text = text;
		}		
	}

	/**
	 * 其实也可以直接在获取数据集时生成sql文件,但这样显示结构混乱
	 * */

	/**
	 * 获取用户资源表的数据
	 * */
	private Map<String,List<UserSource>> getMap(){
		Map<String,List<UserSource>> nameMap = new HashMap<String,List<UserSource>>(); 

		java.sql.Connection conn = null;
		java.sql.PreparedStatement ps = null;
		java.sql.ResultSet rs = null;		
		try {
			conn = DBConnection.newInstance().getConnection();
			ps = conn.prepareStatement(SELECTUSERSOURCE);
			ps.executeUpdate();
			rs = ps.getResultSet();
			String name = null;

			while(rs.next()){
				name = rs.getString(USERSOURCE_NAME);
				List<UserSource> list = nameMap.get(name);
				if(null == list){
					list = new ArrayList<UserSource>();
				}
				list.add(new UserSource(
						name,
						rs.getString(USERSOURCE_TYPE),
						rs.getInt(USERSOURCE_LINE),
						rs.getString(USERSOURCE_TEXT)
				));
				nameMap.put(name, list);				
			}


		} catch (Exception e) {
			throw new RuntimeException("获取用户资源表数据出错..." + e.getMessage(),e);
		}finally{
			try {
				if(null != rs){
					rs.close();					
				}
				if(null != ps){
					ps.close();					
				}
				if(null != conn){
					conn.close();					
				}				
			} catch (Exception e) {				
				rs = null;
				ps = null;
				conn = null;
				throw new RuntimeException("关闭数据库相关资源出错..." + e.getMessage(),e);
			}
			rs = null;
			ps = null;
			conn = null;
		}
		return nameMap;
	}

	/**
	 * 取出各个类型名称对应的源代码sql
	 * */
	private Map<String,Map<String,String>>  getTypeBySourceMap(){

		Map<String,List<UserSource>> nameMap = getMap();		

		String typeName = null;	
		String type = null;

		//统计类型个数Map
		Map<String,Map<String,String>> typeList = new HashMap<String,Map<String,String>>();

		StringBuilder info = new StringBuilder();

		for(Map.Entry<String,List<UserSource>> entity : nameMap.entrySet()){	

			//函数,过程...的名称
			typeName = entity.getKey();

			//存储函数或者过程的源代码
			StringBuilder sourceSbr = new StringBuilder();
			for(UserSource us : entity.getValue()){
				//可能值为：FUNCTION,PROCEDURE...
				type = us.getTyep();
				//sql文件中: --为注释性语句
				sourceSbr.append(us.getText()).append("\n");	
			}			

			//统计类型个数
			Map<String,String> typeBySourceMap = typeList.get(type);
			if(null == typeBySourceMap){
				typeBySourceMap = new HashMap<String,String>();
			}
			typeBySourceMap.put(typeName, sourceSbr.toString());			
			typeList.put(type, typeBySourceMap);	
		}

		return typeList;
	}

	/**
	 * 生成sql文件
	 * */
	public void createSqlFile(){

		Map<String,Map<String,String>> typeList = getTypeBySourceMap();

		String type = null;
		Integer counts = 0;

		StringBuilder showInfo = new StringBuilder();

		for(Map.Entry<String,Map<String,String>> entity : typeList.entrySet()){	

			type = entity.getKey();
			Map<String,String> sourceMap = entity.getValue();
			counts = sourceMap.size();

			showInfo.append("--"+type).append("--总共有:").append(counts).append("个\n");			
			int index = 0;			
			String typeName = null;

			for(Map.Entry<String,String> sourceEntity : sourceMap.entrySet()){	

				typeName = sourceEntity.getKey();				
				showInfo.append("--第").append((++index)).append("个").append(type).append("的名称是:").append(typeName).append(",内容如下: ").append("\n");
				showInfo.append(sourceEntity.getValue());	
			}			
		}

		//写入文本文件
		String fileName = getSqlFilePath()+getSqlFileName()+".sql";
		File file = new File(fileName);
		if(file.exists()){
			file.delete();
		}
		java.io.FileWriter fw = null;
		try {
			fw = new FileWriter(file);
			String fileContext = showInfo.toString(); 
			fw.write(fileContext);

		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				fw.flush();
				fw.close();	
			} catch (IOException e) {				
				e.printStackTrace();
			}					
		}		
		System.out.println("生成sql文件成功,路径:" + fileName);		
	}

	public String getSqlFilePath() {
		return sqlFilePath;
	}
	public CreateUserSQLSource setSqlFilePath(String sqlFilePath) {
		this.sqlFilePath = sqlFilePath;
		return this;
	}
	public String getSqlFileName() {
		return sqlFileName;
	}
	public CreateUserSQLSource setSqlFileName(String sqlFileName) {
		this.sqlFileName = sqlFileName;
		return this;
	}

	public static void main(String[] args) {

		//默认路径及用户名.sql
		/*CreateUserSQLUtil cus = new CreateUserSQLUtil ();
		cus.createSqlFile();*/

		new CreateUserSQLUtil ().setSqlFilePath("d:/").setSqlFileName("liuzd").createSqlFile();
		//打印输出: 生成sql文件成功,路径:d:/liuzd.sql		
	}
}