oracle存储过程接口自动生成器(一)

来源：互联网发布：nginx 文件上传下载编辑：程序博客网时间：2024/05/01 02:38

/**
*
*/
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();
}
}