(转)java中Oracle数据库访问不完全封装代码(仿.net的SQLHelper,不断升级中)

/*
* Created on 2005-12-17
*/
package com.taxware.zdsy_server.oracledal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.rowset.OracleCachedRowSet;

/**
* @author hucl
*
*
*/
public abstract class OracleHelper {

  /**
   * @param String cmdtext,SQL语句
   * @param OracleParameter[] parms,参数集合
   * @return int,SQL语句影响的行数
   */
  public static int executeSql(String cmdtext, OracleParameter[] parms)
      throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;

    try {
      conn = ConnectMgr.getConnect();

      pstmt = conn.prepareStatement(cmdtext);
      prepareCommand(pstmt, parms);

      return pstmt.executeUpdate();

    }
    catch (Exception e) {
      throw new Exception("executeNonQuery方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (pstmt != null)
          pstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("执行executeNonQuery方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param cmdtext
   * @param parms
   * @return int[] 多条SQL的影响行数
   * @throws Exception
   */
  public static int[] executeSqlBatch(String cmdtext, Object[] parms)
      throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;

    try {
      conn = ConnectMgr.getConnect();
      conn.setAutoCommit(false);
      pstmt = conn.prepareStatement(cmdtext);
      prepareCommand(pstmt, parms);

      int[] ret = pstmt.executeBatch();
      conn.commit();
      return ret;
    }
    catch (Exception e) {
      conn.rollback();
      throw new Exception("执行executeSqlBatch方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (pstmt != null)
          pstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("执行executeSqlBatch方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param cmdtext
   * @param parms
   * @return ResultSet
   * @throws Exception
   */
  public static ResultSet executeSqlResultSet(String cmdtext,
      OracleParameter[] parms) throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;

    try {
      conn = ConnectMgr.getConnect();

      pstmt = conn.prepareStatement(cmdtext);
      prepareCommand(pstmt, parms);
      return pstmt.executeQuery();

    }
    catch (Exception e) {
      throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (pstmt != null)
          pstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("executeSqlResultSet方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param sptext 存储过程语句
   * @param parms OracleParameter[]
   * @return boolean 成功为true,失败为false
   * @throws Exception
   */
  public static boolean executeProcedure(String sptext, OracleParameter[] parms)
      throws Exception {
    Connection conn = null;
    CallableStatement cstmt = null;

    try {
      conn = ConnectMgr.getConnect();
      conn.setAutoCommit(true);

      cstmt = conn.prepareCall(sptext);
      prepareCommand(cstmt, parms);

      return cstmt.execute();

    }
    catch (Exception e) {
      throw new Exception("executeProcedure方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (cstmt != null)
          cstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("执行executeProcedure方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param sptext 存储过程语句
   * @param parms OracleParameter[]
   * @return Object[] 出参的结果
   * @throws Exception
   */
  public static Object[] executeProcedureObject(String sptext,
      OracleParameter[] parms) throws Exception {
    Connection conn = null;
    CallableStatement cstmt = null;

    try {
      conn = ConnectMgr.getConnect();
      cstmt = conn.prepareCall(sptext);

      //应用参数
      prepareCommand(cstmt, parms);

      cstmt.execute();
      int count = parms.length;
      Object[] result = new Object[count];

      for (int i = 0; i < count; i++) {
        //判断是否有出参
        if (parms[i].getInorout().compareToIgnoreCase("OUT") >= 0) {
          /*判断是否是结果集,如果不是结果集，直接加到数组中;
           *如果是结果集执行else下的语句，把结果集写到OracleCachedRowSet中，ResultSet的关闭不会受到影响
           *原因:如果直接传ResultSet，
           *在执行finally语句时会连同ResultSet一同关闭，
           *在其他地方没法正确读出结果集
           */
          if (parms[i].getType() != OracleTypes.CURSOR) {
            result[i] = cstmt.getObject(parms[i].getIndex());
          }
          else {          
            OracleCachedRowSet ocrs = new OracleCachedRowSet();
            ResultSet rs = null;
            //取出ResutltSet对象
            rs = (ResultSet)(cstmt.getObject(parms[i].getIndex()));
            //写到OracleCachedRowSet对象
            ocrs.populate(rs);
            //关闭ResultSet
            rs.close();
            //把对象添加到数组
            result[i] = (Object)ocrs;
          }
          //判断结束
        }
        else {
          ;  //如果不是出参，执行下次循环
        }
      }

      return result;
    }
    catch (Exception e) {
      throw new Exception("executeProcedureObject方法出错:" + e.getMessage());
    }
    finally {
      try {      
        if (cstmt != null)
          cstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("executeProcedureObject方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param cmdtext SQL语句
   * @param name 列名
   * @param parms OracleParameter[]
   * @return Object
   * @throws Exception
   */
  public static Object executeSqlObject(String cmdtext, String name,
      OracleParameter[] parms) throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;
    ResultSet rs = null;

    try {
      conn = ConnectMgr.getConnect();

      pstmt = conn.prepareStatement(cmdtext);
      prepareCommand(pstmt, parms);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        return rs.getObject(name);
      }
      else {
        return null;
      }
    }
    catch (Exception e) {
      throw new Exception("executeSqlObject方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (rs != null)
          rs.close();
        if (pstmt != null)
          pstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("executeSqlObject方法出错:" + e.getMessage());
      }
    }
  }

  /**
   *
   * @param cmdtext SQL语句
   * @param index 列名索引
   * @param parms OracleParameter[]
   * @return Object
   * @throws Exception
   */
  public static Object executeSqlObject(String cmdtext, int index,
      OracleParameter[] parms) throws Exception {
    PreparedStatement pstmt = null;
    Connection conn = null;
    ResultSet rs = null;

    try {
      conn = ConnectMgr.getConnect();

      pstmt = conn.prepareStatement(cmdtext);
      prepareCommand(pstmt, parms);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        return rs.getObject(index);
      }
      else {
        return null;
      }
    }
    catch (Exception e) {
      throw new Exception("executeSqlObject方法出错:" + e.getMessage());
    }
    finally {
      try {
        if (rs != null)
          rs.close();
        if (pstmt != null)
          pstmt.close();
        if (conn != null)
          conn.close();
      }
      catch (Exception e) {
        throw new Exception("executeSqlObject方法出错:" + e.getMessage());
      }
    }
  }

  /**
   * @param pstmt
   * @param cmdtext
   * @param parms Object[]
   * @throws Exception
   */
  private static void prepareCommand(PreparedStatement pstmt, Object[] parms)
      throws Exception {
    int count = parms.length;
    for (int i = 0; i < count; i++) {
      OracleParameter[] op;
      op = (OracleParameter[]) parms[i];
      prepareCommand(pstmt, op);
      pstmt.addBatch();
    }
  }

  /**
   *
   * @param pstmt
   * @param parms
   * @throws Exception
   */
  private static void prepareCommand(PreparedStatement pstmt,
      OracleParameter[] parms) throws Exception {
    if (parms == null) {
      return;
    }

    int count;
    count = parms.length;

    for (int i = 0; i < count; i++) {
      OracleParameter op = new OracleParameter();
      op = parms[i];

      pstmt.setObject(op.getIndex(), op.getValue());
    }
  }

  /**
   *
   * @param cstmt
   * @param parms
   * @throws Exception
   */
  private static void prepareCommand(CallableStatement cstmt,
      OracleParameter[] parms) throws Exception {
    if (parms == null) {
      return;
    }

    int count;
    count = parms.length;

    try {
      for (int i = 0; i < count; i++) {
        OracleParameter op = new OracleParameter();
        op = parms[i];

        if (op.getInorout().compareToIgnoreCase("OUT") >= 0)
          cstmt.registerOutParameter(op.getIndex(), op.getType());
        else
          cstmt.setObject(op.getIndex(), op.getValue());
      }
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }

  }

  public static void main(String[] args) throws Exception {
    /*
     //测试1
     OracleParameter[] parms;
     parms = new OracleParameter[] {
     new OracleParameter(1,"4")
     };
     OracleHelper.executeNonQuery(1,"insert into SYS_CONFIG(name) values(?)",parms);
   
   
     //测试2
     Object[] parms;  
     OracleParameter[] op1;
     op1 = new OracleParameter[] {
     new OracleParameter(1,"4")
     };
   
     OracleParameter[] op2;  
     op2 = new OracleParameter[] {
     new OracleParameter(1,"5")
     };
   
     OracleParameter[] op3;  
     op3 = new OracleParameter[] {
     new OracleParameter(1,"6")
     };
   
     parms = new Object[] {
     op1,op2,op3
     };  
   
     String sql =  "insert into SYS_CONFIG(NAME) VALUES(?)";

   
     OracleHelper.executeNonQuery(sql,parms);
   
     //测试3
     Object o = OracleHelper.executeSqlObject("select name from SYS_CONFIG ","name",null);
     System.out.println(o);
   
    //测试4
    Object[] o;
    OracleParameter[] parms;
    parms = new OracleParameter[] {
        new OracleParameter(1, "1"),
        new OracleParameter(2, java.sql.Types.VARCHAR)
    };

    o = OracleHelper.executeProcedureObject(
        "{call USP_SYS_CONFIG_GETVALUE(?,?)}", parms);

    if (o != null) {
      System.out.println(o[1].toString());
    }
    else {
      System.out.println("null");
    }
    */
  }
}