oracle.java

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

/**
 * <p>Title: 处理数据增、删、改的Java Bean</p>
 * <p>Description: 该类是真正意义的数据Bean，只面向数据，与业务无关，也可用于其他项目，可重用度极高！</p>
 * <p>Company: 大连超维计算机技术有限公司</p>
 * @author 崔冠宇
 * @version 1.0
 */
package client.chaowei.intraweb.bean.data;

import javax.servlet.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import oracle.sql.BLOB;
import javax.naming.*;
import java.util.*;
import client.chaowei.intraweb.bean.util.RecordLog;

public class Oracle {

  /**
   * 完成数据库的增删改操作，要求传入的sql语句必须为insert,update或delete
   * 有返回值，-1表示操作不成功，0表示没有更新行，正整数代表更新的行数
   * @param sql
   */
  public int update(String sql) {
    Connection conn = getConnection();
    Statement  stmt = null;
    int result = 0;
    try {
      stmt = conn.createStatement();
      conn.setAutoCommit(false);
      result = stmt.executeUpdate(sql);
      // 如果是更新和删除操作，记录到日志文件
      if ((sql.toLowerCase().trim().startsWith("update")||sql.toLowerCase().trim().startsWith("delete"))) {
        RecordLog.println(sql);
      }
      stmt.close();
      conn.commit();
      conn.setAutoCommit(true);
      conn.close();
      return result;
    } catch(SQLException e1) {
      System.out.println("Oracle.java-->update(String sql) e1:" + e1.getMessage());
      try{
        conn.rollback();
        conn.setAutoCommit(true);
        return -1;
      } catch(SQLException e2) {
        System.out.println("Oracle.java-->update(String sql) e2:" + e2.getMessage());
        return -1;
      }
    } finally {
      this.disconnect(stmt, conn);
    }
  }

  /**
   * 传入一组sql语句，完成数据库的增删改操作
   *有返回值，-1表示操作不成功，0表示没有更新行，正整数代表更新的行数
   * 要求传入的sql语句必须为insert,update或delete。
   * 将这组sql语句做成一个Transaction
   * @param sqlArr
   */
  public int update(String[] sqlArr) {
    Connection conn = getConnection();
    Statement  stmt = null;
    int result = 0;
    try {
      stmt = conn.createStatement();
      conn.setAutoCommit(false);
      for (int i = 0; i < sqlArr.length; i++) {
        // 如果是更新和删除操作，记录到日志文件
        if ( (sqlArr[i].toLowerCase().trim().startsWith("update") ||
              sqlArr[i].toLowerCase().trim().startsWith("delete"))) {
          RecordLog.println(sqlArr[i]);
        }

        result += stmt.executeUpdate(sqlArr[i]);
      }
      stmt.close();
      conn.commit();
      conn.setAutoCommit(true);
      conn.close();
      return result;
    } catch(SQLException e1) {
      System.out.println("Oracle.java-->update(String[] sqlArr) e1:" + e1.getMessage());
      try{
        conn.rollback();
        conn.setAutoCommit(true);
        return -1;
      } catch(SQLException e2) {
        System.out.println("Oracle.java-->update(String[] sqlArr) e2:" + e2.getMessage());
        return -1;
      }
    } finally {
      this.disconnect(stmt, conn);
    }

  }

  /**
   * 更新Blob类型查询结果
   * @param sql
   * @param columnName
   * @param sis
   */
  public void updateBlob(String sql, String sqlBlob, String columnName, ServletInputStream sis, int fileSize) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    oracle.sql.BLOB content = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      stmt = conn.createStatement();
      stmt.executeUpdate(sql);
      rs = stmt.executeQuery(sqlBlob);
      while (rs.next()) {
        content = (oracle.sql.BLOB)rs.getBlob(columnName);
      }
      bos = new BufferedOutputStream(content.getBinaryOutputStream());
      bis = new BufferedInputStream(sis);
      byte[] buffer = new byte[ content.getBufferSize() ];
      int bytesRead = 0;
      int hasRead = 0;
      while( ( bytesRead = bis.read( buffer ) ) != -1 ) {
        hasRead += bytesRead;
        if (hasRead > fileSize) {
          bytesRead = bytesRead - (hasRead - fileSize);
        }
        bos.write( buffer, 0, bytesRead );
      }
      bos.flush();
      bos.close();
      conn.commit();
      conn.setAutoCommit(true);
    } catch(Exception e1) {
      System.out.println("Oracle.java-->updateBlob e1:" + e1.getMessage());
      try {
        conn.rollback();
        conn.setAutoCommit(true);
        bos.flush();
        bos.close();
      } catch (Exception e2) {
        System.out.println("Oracle.java-->updateBlob e2:" + e2.getMessage());
      }
    } finally {
      this.disconnect(rs, stmt, conn);
    }
  }

  /**
   * 返回Blob类型查询结果
   * @param sql
   * @param columnName
   * @return
   */
  public Blob getBlob(String sql, String columnName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Blob blob = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      while (rs.next()) {
        blob = rs.getBlob(columnName);
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getBlob:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return blob;
  }

  /**
   * 返回一行一列查询结果
   * @param sql
   * @param columnName
   * @return
   */
  public String getSnglRowSnglCol(String sql, String columnName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    String str = "";
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      String colType = rsmd.getColumnTypeName(1);
      while (rs.next()) {
        if (colType.equals("CLOB")) {
          Clob clob = rs.getClob(columnName);
          str = clob.getSubString(1,(int)clob.length());
        } else {
          str = rs.getString(columnName);
        }
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getSnglRowSnglCol:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return str;
  }

  /**
   * 返回一行多列查询结果
   * @param sql
   * @param columnName
   * @return
   */
  public String[] getSnglRowMultiCol(String sql, String[] colArr) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();
    String[] colType = new String[colArr.length];
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        colType[i-1] = rsmd.getColumnTypeName(i);
      }
      while (rs.next()) {
        for (int i = 0; i < colArr.length; i++) {
          if (colType[i].equals("CLOB")) {
            Clob clob = rs.getClob(colArr[i]);
            v.addElement(clob.getSubString(1,(int)clob.length()));
          } else {
            v.addElement(rs.getString(colArr[i]));
          }
        }
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getSnglRowMultiCol:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return this.cvtVtrToArr(v);
  }

  /**
   * 返回多行一列查询结果
   * @param sql
   * @param columnName
   * @return
   */
  public String[] getMultiRowSnglCol(String sql, String columnName) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();
      String colType = rsmd.getColumnTypeName(1);
      while (rs.next()) {
        if (colType.equals("CLOB")) {
          Clob clob = rs.getClob(columnName);
          v.addElement(clob.getSubString(1,(int)clob.length()));
        } else {
          v.addElement(rs.getString(columnName));
        }
      }
    } catch(Exception e) {
      System.out.println("Oracle.java-->getMultiRowSnglCol:" + e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }
    return this.cvtVtrToArr(v);
  }

  /**
   * 返回多行多列查询结果
   * @param sql
   * @param colArr
   * @return
   */
  public String[][] getMultiRowMultiCol(String sql, String colArr[]) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();
    int colCount = colArr.length;
    String[] colType = new String[colCount];
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();