/**
*
*/
package mytest.jdbc;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
//
// Here's a simple example of how to use the BasicDataSource.
// In this example, we'll construct the BasicDataSource manually,
// but you could also configure it using an external conifguration file.
//
//
// Note that this example is very similiar to the PoolingDriver
// example.
//
// To compile this example, you'll want:
// * commons-pool-1.3.jar
// * commons-dbcp-1.2.2.jar
// * j2ee.jar (for the javax.sql classes)
// in your classpath.
//
// To run this example, you'll want:
// * commons-pool-1.3.jar
// * commons-dbcp-1.2.2.jar
// * j2ee.jar (for the javax.sql classes)
// * the classes for your (underlying) JDBC driver
// in your classpath.
//
// Invoke the class using two arguments:
// * the connect string for your underlying JDBC driver
// * the query you'd like to execute
// You'll also want to ensure your underlying JDBC driver
// is registered. You can use the "jdbc.drivers"
// property to do this.
//
// For example:
// java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver /
// -classpath
// commons-pool-1.3.jar:commons-dbcp-1.2.2.jar:j2ee.jar:oracle-jdbc.jar:. /
// ManualPoolingDataSourceExample
// "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
// "SELECT * FROM DUAL"
//
public class Generator{
static String tableAlias = "T";
// static String TABLE_ALL_PROCEDURES = "ALL_PROCEDURES";
static String TABLE_ALL_ARGUMENTS = "ALL_ARGUMENTS";
static String COLUMN_OWNER = "OWNER";
static String COLUMN_OBJECT_NAME = "OBJECT_NAME";
static String COLUMN_PACKAGE_NAME = "PACKAGE_NAME";
static String COLUMN_POSITION = "POSITION";
static String COLUMN_ROW_COUNT = "ROW_COUNT";
static String OWNER = "TRADELOG";
static List pkgNameList = new ArrayList();
static List pkgAndProcedureList = new ArrayList();
// static String SQL_PROCEDURE_INFO = "SELECT DISTINCT T.OWNER,
// T.PACKAGE_NAME, T.OBJECT_NAME"
// + " FROM ALL_ARGUMENTS T"
// + " WHERE T.OWNER = 'TRADELOG'"
// + " AND T.PACKAGE_NAME IS NOT NULL"
// + " AND T.PACKAGE_NAME NOT LIKE '%ERROR%'"
// + " AND T.PACKAGE_NAME NOT LIKE '%2008%'"
// + " ORDER BY T.OWNER,T.PACKAGE_NAME,T.OBJECT_NAME";
static String SQL_PKGNAME_INFO = "SELECT DISTINCT T.OWNER, T.PACKAGE_NAME"
+ " FROM ALL_ARGUMENTS T" + " WHERE T.OWNER = 'TRADELOG'"
+ " AND T.PACKAGE_NAME IS NOT NULL"
+ " AND T.PACKAGE_NAME NOT LIKE '%ERROR%'"
+ " AND T.PACKAGE_NAME NOT LIKE '%2008%'"
+ " ORDER BY T.OWNER,T.PACKAGE_NAME";
static String SQL_PROC_INFO = "SELECT DISTINCT T.OWNER, T.PACKAGE_NAME,T.OBJECT_NAME"
+ " FROM ALL_ARGUMENTS T"
+ " WHERE T.OWNER like ?"
+ " AND T.PACKAGE_NAME like ?"
+ " ORDER BY T.OWNER,T.PACKAGE_NAME,T.OBJECT_NAME";
static String SQL_ARGUMENTS_POSITIONS = "SELECT DISTINCT T.OWNER, T.PACKAGE_NAME, T.OBJECT_NAME, T.POSITION"
+ " FROM ALL_ARGUMENTS T"
+ " WHERE T.OWNER like ?"
+ " AND T.PACKAGE_NAME like ?"
+ " AND T.OBJECT_NAME like ?"
+ " ORDER BY T.POSITION";
static String SQL_COUNT_ARGUMENTS = "SELECT DISTINCT count(T.POSITION) ROW_COUNT"
+ " FROM ALL_ARGUMENTS T"
+ " WHERE T.OWNER like ?"
+ " AND T.PACKAGE_NAME like ?"
+ " AND T.OBJECT_NAME like ?"
+ " ORDER BY T.POSITION";
// static String SQL_ARGUMENTS_POSITIONS = "SELECT DISTINCT T.OWNER,
// T.PACKAGE_NAME, T.OBJECT_NAME, T.POSITION"
// + " FROM ALL_ARGUMENTS T"
// + " WHERE T.OWNER = ?"
// + " AND T.PACKAGE_NAME = ?"
// + " AND T.OBJECT_NAME = ?"
// + " ORDER BY T.POSITION";
//
// static String SQL_COUNT_ARGUMENTS = "SELECT DISTINCT count(T.POSITION)
// ROW_COUNT"
// + " FROM ALL_ARGUMENTS T"
// + " WHERE T.OWNER = ?"
// + " AND T.PACKAGE_NAME = ?"
// + " AND T.OBJECT_NAME = ?"
// + " ORDER BY T.POSITION";
public static void main(String[] args) throws IOException, Exception {
// First we set up the BasicDataSource.
// Normally this would be handled auto-magically by
// an external configuration, but in this example we'll
// do it manually.
//
System.out.println("Setting up data source.");
DataSource dataSource = setupDataSource("jdbc:oracle:thin:@172.16.30.46:1521:tradelogbdb");
System.out.println("Done.");
//
// Now, we can use JDBC DataSource as we normally would.
//
Connection connection = null;
String pkgName = "";
String pkgAndProc = "";
String constantKey = "";
String constantValue = "";
String result = "";
// ArrayList headList = new ArrayList();
try {
System.out.println("Creating connection.");
connection = dataSource.getConnection();
// 得到pkg和procedure的名字,将她们数据库的顺序放入List
pkgNameList = getPkgName(connection);
Iterator pkgIt = pkgNameList.iterator();
while (pkgIt.hasNext()) {
pkgName = (String) pkgIt.next();
// System.out.println(pkgName);
pkgAndProcedureList = getPkgAndProcedureName(connection, OWNER,
pkgName);
Iterator procIt = pkgAndProcedureList.iterator();
StringBuffer sb = new StringBuffer();
while (procIt.hasNext()) {
pkgAndProc = (String) procIt.next();
// System.out.println(pkgAndProc);
constantValue = genCallableStatement(connection, pkgAndProc);
constantKey = pkgAndProc.replaceAll("//.", "_")
.toUpperCase();
result = genConstantStatement(constantKey, constantValue)
+ ";";
result = "/tString " + result + "/n";
System.out.print(result);
sb.append(result);
}
pkgName = pkgName.replaceAll("_", "").toLowerCase();
FileHelper.genFile(pkgName, pkgName, sb.toString());
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();
} catch (Exception e) {
}
}
}
/**
* @param conn
* @return 得到包名的list
* @throws SQLException
*/
public static List getPkgName(Connection conn) throws SQLException {
List list = new ArrayList();
Statement stmt = conn.createStatement();
// 得到pkg和procedure的名字,将她们数据库的顺序放入List
ResultSet rset = stmt.executeQuery(SQL_PKGNAME_INFO);
while (rset.next()) {
list.add(rset.getString(COLUMN_PACKAGE_NAME));
}
// conn.close();
return list;
}
/**
* @param conn
* @return 得到全包名的list
* @throws SQLException
*/
public static List getPkgAndProcedureName(Connection conn, String owner,
String pkgName) throws SQLException {
List list = new ArrayList();
// 得到pkg和procedure的名字,将她们数据库的顺序放入List
PreparedStatement pstmt = conn.prepareStatement(SQL_PROC_INFO);
pstmt.setString(1, owner);
pstmt.setString(2, pkgName);
// pstmt.setString(3, proc);
ResultSet rset = pstmt.executeQuery();
while (rset.next()) {
list.add(rset.getString(COLUMN_PACKAGE_NAME) + "."
+ rset.getString(COLUMN_OBJECT_NAME));
}
// conn.close();
return list;
}
/**
* @param num
* @return 根据num返回"?,?,?..."
*/
public static String GenInterrogations(double num) {
String result = "";
if (num > 0)
for (int i = 0; i < num; i++) {
if (i == num - 1)
result += "?";
else
result += "?,";
}
return result;
}
/**
* @param spName
* @param type
* @return 输出为CallableStatement的形式,如: {?= call <procedure-name>[<arg1>,<arg2>,
* ...]} {call <procedure-name>[<arg1>,<arg2>, ...]}
*/
public static String genCallableStatement(String spName, String type,
double argNum) {
String result = "call " + spName + "(";
if ("0".equals(type))
result += GenInterrogations(argNum - 1);
else
result += GenInterrogations(argNum);
result += ")";
if ("0".equals(type))
result = "{?= " + result + "}";
if ("1".equals(type))
result = "{" + result + "}";
return result;
}
/**
* @param conn
* @param spName
* @return CallableStatement的形式,如: {?= call <procedure-name>[<arg1>,<arg2>,
* ...]} {call <procedure-name>[<arg1>,<arg2>, ...]}
* @throws SQLException
*/
public static String genCallableStatement(Connection conn, String spName)
throws SQLException {
String pkgName = "";
String functionName = "";
int position = spName.indexOf(".");
if (position > -1) {
pkgName = spName.substring(0, position);
functionName = spName.substring(position + 1, spName.length());
}
// 得到查询结果的总记录数
double totalRows = 0;
PreparedStatement pCountStmt = conn
.prepareStatement(genCountSql(SQL_ARGUMENTS_POSITIONS));
pCountStmt.setString(1, OWNER);
pCountStmt.setString(2, pkgName);
pCountStmt.setString(3, functionName);
ResultSet countResultSet = pCountStmt.executeQuery();
while (countResultSet.next()) {
totalRows = countResultSet.getDouble(1);
}
countResultSet.close();
pCountStmt.close();
// 得到sp是否有返回值,type=0,有返回值;type=1没有返回值;type=null,没有参数
String type = "";
PreparedStatement pPositionsStmt = conn
.prepareStatement(SQL_ARGUMENTS_POSITIONS);
pPositionsStmt.setString(1, OWNER);
pPositionsStmt.setString(2, pkgName);
pPositionsStmt.setString(3, functionName);
ResultSet positionsResultSet = pPositionsStmt.executeQuery();
while (positionsResultSet.next()) {
if (positionsResultSet.isFirst())
type = positionsResultSet.getString(COLUMN_POSITION);
}
positionsResultSet.close();
pPositionsStmt.close();
// System.out.println(genCallableStatement(spName, type, totalRows));
return genCallableStatement(spName, type, totalRows);
}
/**
* 根据pkg返回sp的CallableStatement数组
*
* @param conn
* @param pkg
* @return CallableStatement的形式,如: {?= call <procedure-name>[<arg1>,<arg2>,
* ...]} {call <procedure-name>[<arg1>,<arg2>, ...]}
* @throws SQLException
*/
// public static String[] genCallableStatementByPkg(Connection conn, String
// pkg)
// throws SQLException {
// String SQL_ARGUMENTS_POSITIONS = "SELECT DISTINCT T.OWNER,
// T.PACKAGE_NAME, T.OBJECT_NAME, T.POSITION"
// + " FROM ALL_ARGUMENTS T"
// + " WHERE T.OWNER = ?"
// + " AND T.PACKAGE_NAME = ?"
// + " AND T.OBJECT_NAME = ?"
// + " ORDER BY T.POSITION";
//
// String SQL_COUNT_ARGUMENTS = "SELECT DISTINCT count(T.POSITION)
// ROW_COUNT"
// + " FROM ALL_ARGUMENTS T"
// + " WHERE T.OWNER = ?"
// + " AND T.PACKAGE_NAME = ?"
// + " AND T.OBJECT_NAME = ?"
// + " ORDER BY T.POSITION";
//
// String pkgName = "";
// String functionName = "";
//
// int position = pkg.indexOf(".");
// if (position > -1) {
// pkgName = pkg.substring(0, position);
// functionName = pkg.substring(position + 1, pkg.length());
// }
//
// // 得到查询结果的总记录数
// double totalRows = 0;
//
// PreparedStatement pCountStmt = conn
// .prepareStatement(genCountSql(SQL_ARGUMENTS_POSITIONS));
// pCountStmt.setString(1, OWNER);
// pCountStmt.setString(2, pkgName);
// pCountStmt.setString(3, functionName);
// ResultSet countResultSet = pCountStmt.executeQuery();
// while (countResultSet.next()) {
// totalRows = countResultSet.getDouble(1);
// }
// countResultSet.close();
// pCountStmt.close();
//
// // 得到sp是否有返回值,type=0,有返回值;type=1没有返回值;type=null,没有参数
// String type = "";
// PreparedStatement pPositionsStmt = conn
// .prepareStatement(SQL_ARGUMENTS_POSITIONS);
// pPositionsStmt.setString(1, OWNER);
// pPositionsStmt.setString(2, pkgName);
// pPositionsStmt.setString(3, functionName);
// ResultSet positionsResultSet = pPositionsStmt.executeQuery();
// while (positionsResultSet.next()) {
// if (positionsResultSet.isFirst())
// type = positionsResultSet.getString(COLUMN_POSITION);
// }
// positionsResultSet.close();
// pPositionsStmt.close();
//
// // System.out.println(genCallableStatement(spName, type, totalRows));
// return genCallableStatement(pkg, type, totalRows);
// }
/**
* @param sql
* @return 得到查询结果的记录数
*/
public static String genCountSql(String sql) {
return "SELECT COUNT(*) FROM (" + sql + ")";
}
/**
* @param spName
* @param spStatement
* @return 生成sp的常量表
*/
public static String genConstantStatement(String spName, String spStatement) {
return spName + " = /"" + spStatement + "/"";
}
/**
* @param pkg
* @param procedure
* @return 生成sp的常量名,用pkg名和procedure名拼成sp的常量名
*/
public static String genConstantKey(String pkg, String procedure) {
return (pkg + "_" + procedure).toUpperCase();
}
/**
* @param connectURI
* @return 获得DataSource
*/
public static DataSource setupDataSource(String connectURI) {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUsername("tradelog");
ds.setPassword("tradelog");
ds.setUrl(connectURI);
return ds;
}
/**
* @param datasource
* @throws SQLException
*/
public static void printDataSourceStats(DataSource datasource)
throws SQLException {
BasicDataSource bds = (BasicDataSource) datasource;
System.out.println("NumActive: " + bds.getNumActive());
System.out.println("NumIdle: " + bds.getNumIdle());
}
/**
* @param datasource
* @throws SQLException
*/
public static void shutdownDataSource(DataSource datasource)
throws SQLException {
BasicDataSource bds = (BasicDataSource) datasource;
bds.close();
}
}