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