oracle.java

通过JAVA访问ORACLE数据库的一系列通用工具

      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        colType[i-1] = rsmd.getColumnTypeName(i);
      }
      while (rs.next()) {
        String[] arr = new String[colCount];
        for (int i = 0; i < colCount; i++) {
          if (colType[i].equals("CLOB")) {
            Clob clob = rs.getClob(colArr[i]);
            arr[i] = clob.getSubString(1,(int)clob.length());
          } else {
            arr[i] = rs.getString(colArr[i]);
          }
        }
        v.addElement(arr);
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getMultiRowMultiCol:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return this.cvtVtrToArr(v, colCount);
  }

  /**
   * 返回记录的数目
   * @param sql
   * @return
   */
  public int getRecordCount(String sql) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    int count = 0;
    try {
      conn = getConnection();
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
      rs   = stmt.executeQuery(sql);
      if (rs != null) {
        if (rs.next()) {
          rs.last();
          count = rs.getRow();
        }
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getRecordCount:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return count;
  }

  /**
   * 返回sql语句中用到的字段的名称数组
   * @param sql
   * @return
   */
  public String[] getColNames(String sql) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();
    StringBuffer s = new StringBuffer();
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs   = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        v.addElement(rsmd.getColumnName(i));
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getColNames:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return this.cvtVtrToArr(v);
  }

  /**
   * 返回字段对应的字段类型
   * @param tblName 表名
   * @param Id 表的Id字段
   * @param names 字段组成的向量
   * @return
   */
  public String[] getColTypes(String tblName, String[] colNames) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    StringBuffer strBuff = new StringBuffer();
    int k = 0;
    String[] colTypes = new String[colNames.length];
    String[] selColName = {"column_name","data_type"};
    strBuff.append("select column_name,data_type from user_tab_columns where table_name='");
    strBuff.append(tblName.toUpperCase());
    strBuff.append("' and (column_name in (");
    for (int i = 0; i < colNames.length; i++) {
      strBuff.append("'");
      strBuff.append(colNames[i].toUpperCase());
      strBuff.append("',");
    }
    strBuff.deleteCharAt(strBuff.length()-1);
    strBuff.append("))");
    String sql = strBuff.toString();
    // 字段类型数组，包含Id字段
    String[][] colNameType = this.getMultiRowMultiCol(sql,selColName);
    // 把从数据字典中查询出的字段名与字段类型数组中查到的与传入的字段名数组相对应的字段类型放入新的字段类型数组中
    for (int i = 0; i < colNames.length; i++) {
      for (int j = 0; j < colNameType.length; j++) {
        if (colNames[i].toUpperCase().equals(colNameType[j][0])) {
          colTypes[k] = colNameType[j][1];
          k++;
        }
      }
    }
    return colTypes;
  }

  /**
   * 返回表的所有的字段名和类型
   * @param tblName 表名
   * @return
   */
  public String[][] getColumnsTypes(String tblName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    StringBuffer strBuff = new StringBuffer();
    strBuff.append("select column_name,data_type from user_tab_columns where table_name='");
    strBuff.append(tblName.toUpperCase());
    strBuff.append("'");
    String sql = strBuff.toString();
    String[] colArr = this.getColNames(sql);
    String[][] ColumnsTypes = this.getMultiRowMultiCol(sql, colArr);
    return ColumnsTypes;
  }

  /**
   * 返回表的所有的字段
   * @param tblName 表名
   * @return
   */
  public String[] getColumns(String tblName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    StringBuffer strBuff = new StringBuffer();
    strBuff.append("select column_name from user_tab_columns where table_name='");
    strBuff.append(tblName.toUpperCase());
    strBuff.append("'");
    String sql = strBuff.toString();
    String[] columns = this.getMultiRowSnglCol(sql, "COLUMN_NAME");
    return columns;
  }


  /**
   * 返回表的所有的主键字段
   * @param tblName 表名
   * @return主键字段数组
   */
  public String[] getPKColumns(String tblName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    StringBuffer strBuff = new StringBuffer();
    strBuff.append("select column_name from user_cons_columns a,user_constraints b where a.constraint_name=b.constraint_name and constraint_type='P' and table_name='");
    strBuff.append(tblName.toUpperCase());
    strBuff.append("'");
    String sql = strBuff.toString();
    String[] columns = this.getMultiRowSnglCol(sql, "COLUMN_NAME");
    return columns;
  }

  /**
   * 返回表的所有的非主键字段
   * @param tblName 表名
   * @return非主键字段数组
   */
  public String[] getNotPKColumns(String tblName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    StringBuffer strBuff = new StringBuffer();
    strBuff.append("select column_name from user_cons_columns a,user_constraints b where a.constraint_name=b.constraint_name and constraint_type<>'P' and table_name='");
    strBuff.append(tblName.toUpperCase());
    strBuff.append("'");
    String sql = strBuff.toString();
    String[] columns = this.getMultiRowSnglCol(sql, "COLUMN_NAME");
    return columns;
  }

  /**
   * 将Vector转换为一个一维数组
   * @param v
   * @param colCount
   * @return
   */
  public String[] cvtVtrToArr(Vector v) {
    Object[] obj = v.toArray();
    int rowCount = obj.length;
    String[] arr = new String[rowCount];
    for (int i = 0; i < rowCount; i++) {
      arr[i] = (String)obj[i];
    }
    return arr;
  }

  /**
   * 将Vector转换为一个二维数组
   * @param v
   * @param colCount
   * @return
   */
  public String[][] cvtVtrToArr(Vector v, int colCount) {
    Object[] obj = v.toArray();
    int rowCount = obj.length;
    String[][] arr = new String[rowCount][colCount];
    for (int i = 0; i < rowCount; i++) {
      arr[i] = (String[])obj[i];
    }
    return arr;
  }

  /**
   * 将数组中的null转换为""，并返回转换后的数组
   * @param arr
   */
  public String[] cvtNullToBlank(String[] arr) {
    for (int i = 0; i < arr.length; i++) {
      if (arr[i] == null) {
        arr[i] = "";
      }
    }
    return arr;
  }

  /**
   * 将数组中的null转换为""，并返回转换后的数组
   * @param arr
   */
  public String[][] cvtNullToBlank(String[][] arr) {
    for (int i = 0; i < arr.length; i++) {
      for (int j = 0; j < arr[i].length; j++) {
        if (arr[i][j] == null) {
          arr[i][j] = "";
        }
      }
    }
    return arr;
  }

  /**
   * 断开与数据库的连接
   * @param rs
   * @param stmt
   * @param conn
   */
  public void disconnect(ResultSet rs, Statement stmt, Connection conn) {
    try {
      rs.close();
      stmt.close();
      conn.close();
    } catch (Exception e) {
      System.out.println("Oracle.java-->disconnect 1:" + e.getMessage());
    }
  }

  /**
   * 断开与数据库的连接
   * @param stmt
   * @param conn
   */
  public void disconnect(Statement stmt, Connection conn) {
    try {
      stmt.close();
      conn.close();
    } catch (Exception e) {
      System.out.println("Oracle.java-->disconnect 2:" + e.getMessage());
    }
  }

  /**
   * 从连接池中返回一个数据库连接
   * @return
   */
  public static Connection getConnection() {
    Connection connection = null;
    try {
      InitialContext initContext = new InitialContext();
      String strDataSourceName = "jdbc/intraweb";
      DataSource dsOracle = (DataSource)initContext.lookup(strDataSourceName);
      connection = dsOracle.getConnection();
    } catch(Exception e) {
    }
    return connection;
  }

}