oracleconnection.java

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

/**
 * <p> </p>
 * <p> </p>
 * <p>Copyright: Copyright: Copyright (c) 2003 Dalian ChaoWei Computer Technology Co.,Ltd</p>
 * <p> All right reserved;</p>
 * @author 孙婷婷
 * 创建时间 2003-11-11
 */
package client.chaowei.intraweb.bean.data;
import client.chaowei.intraweb.bean.util.RecordLog;
import oracle.sql.BLOB;
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.naming.*;
import javax.servlet.*;
import javax.sql.*;

public class OracleConnection extends SqlGenerator {
  /**
   * 操作一个字段类型为clob的方法(插入,更新)
   * @param sqlins 正常的插入语句
   * @param sqlsel 查询插入的字段id号
   * @param colname clob字段的名称
   * @param colvalue clob字段的内容
   * @param tablename 表名
   * @param PKColumn 表的主键
   * @param flag 用于区别是插入还是更新
   * @return
   */
  public int insClob(String sqlins, String id, String tablename,
                     String PKColumn, String colname, String colvalue,int flag) {
    Connection conn = getConnection();
    Statement stmt = null;
    ResultSet rs = null;
    String currid = "";
    try {
      stmt = conn.createStatement();
      conn.setAutoCommit(false);
      // 插入一个空的CLOB对象
      stmt.executeUpdate(sqlins);
      if (flag ==0) {
        rs = stmt.executeQuery("select " + id + " id from dual");
        while (rs.next()) {
          currid = rs.getString("id");
        }
      } else {
        currid = id;
      }
      // 查询此CLOB对象并锁定
      rs = stmt.executeQuery("select " + colname + " from " + tablename +
                             " where " + PKColumn + " =" + currid + " for update");
      while (rs.next()) {
        // 取出此CLOB对象
        oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(colname);
        // 向CLOB对象中写入数据
        BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
        BufferedReader in = new BufferedReader(new StringReader(colvalue));
        int c;
        while ( (c = in.read()) != -1) {
          out.write(c);
        }
        in.close();
        out.close();
      }
      // 正式提交
      stmt.close();
      conn.commit();
      conn.setAutoCommit(true);
      conn.close();
      return 1;
    } catch (Exception ex1) {
      System.out.println("insClob(String colname, String colvalue) ex1:" + ex1.getMessage());
      /* 出错回滚 */
      try {
        conn.rollback();
        conn.setAutoCommit(true);
        return -1;
      }
      catch (SQLException ex2) {
        System.out.println("insClob(String colname, String colvalue) ex2:" + ex2.getMessage());
        return -1;
      }
    } finally {
      this.disconnect(rs,stmt, conn);
    }
  }

  /**
     * 返回一行多列查询结果
     * @param sql
     * @param columnName
     * @return
     */
  public String[] getSnglRowMultiCol(String sql, String[] colArr, int num) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();
    String[] colType = new String[colArr.length];

    try {
      conn = getConnection(0);
      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("OracleConnection.java-->getSnglRowMultiCol:" +
        e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }

    return this.cvtVtrToArr(v);
  }

  /**
     * 返回一行一列查询结果
     * @param sql
     * @param columnName
     * @return
     */
  public String getSnglRowSnglCol(String sql, String columnName, int num) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    String str = "";

    try {
      conn = getConnection(0);
      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("OracleConnection.java-->getSnglRowSnglCol:" +
        e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }

    return str;
  }

  /**
   * 返回多行多列查询结果
   * @param sql
   * @param colArr
   * @return
   */
  public String[][] getMultiRowMultiCol(String sql, String[] colArr, int num) {
    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(0);
      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()) {
        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("OracleConnection.java-->getMultiRowMultiCol:" +
        e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }

    return this.cvtVtrToArr(v, colCount);
  }

  /**
    * 返回多行一列查询结果
    * @param sql
    * @param columnName
    * @return
    */
  public String[] getMultiRowSnglCol(String sql, String columnName, int num) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    Vector v = new Vector();

    try {
      conn = getConnection(0);
      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("OracleConnection.java-->getMultiRowSnglCol:" +
        e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }

    return this.cvtVtrToArr(v);
  }

  /**
   * 从连接池中返回一个数据库连接
   * @return
   */
  public static Connection getConnection(int num) {
    Connection connection = null;

    try {
      InitialContext initContext = new InitialContext();
      String strDataSourceName = "jdbc/jnpc";
      DataSource dsOracle = (DataSource) initContext.lookup(strDataSourceName);
      connection = dsOracle.getConnection();
    } catch (Exception e) {
    }

    return connection;
  }

  /**
   * 返回记录的数目
   * @param sql
   * @return
   */
  public int getRecordCount(String sql) {
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    int count = 0;

    try {
      conn = getConnection(0);
      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("OracleConnection.java-->getRecordCount:" +
        e.getMessage());
    } finally {
      this.disconnect(rs, stmt, conn);
    }

    return count;
  }
}