www.pudn.com > dbPool.rar > DBUtils.java

package dev.trade.common.db; 
 
import java.sql.Connection; 
import java.sql.SQLException; 
import org.apache.log4j.Logger; 
import java.sql.*; 
import java.util.*; 
import java.io.*; 
 
/** 
 * 
 * 
Title: 福建客服支撑系统
 
 * 
 * 
Description: 数据库操作的工具类
 
 * 
 * 
Copyright: Copyright (c) 2006
 
 * 
 * 
Company: Newland
 
 * 
 * @author ZhengYanNan 
 * @version 1.0 
 */ 
public class DBUtils 
{ 
  private static Logger log = Logger.getLogger(DBUtils.class); 
  private static boolean isInit = false; 
  private static Map dbPools = new HashMap(); 
  public final static String DEFAULT_POOL_NAME = "default"; 
  public static final String DEF_POOL_NAME = "default"; 
  public static final String DEF_DATE_FORMAT = "yyyy-mm-dd"; 
  public static final String DEF_TIME_FORMAT = "hh24:mi:ss"; 
  public static final String DEF_DATETIME_FORMAT = "yyyy-mm-dd hh24:mi:ss"; 
 
  static{ 
    try{ 
      initDBConnPool(); 
    } catch(Exception ex){ 
    } 
  } 
 
  public DBUtils(){ 
  } 
 
  /** 
   * 初始化整个数据库连接池 
   * @throws Exception 
   */ 
  public synchronized static void initDBConnPool() throws Exception{ 
    if(isInit){ 
      return; 
    } 
    log.info("开始初始化数据库连接池..."); 
    DBConfig.init(); 
    String strPools = DBConfig.get(DBConfig.INIT_POOLS, ""); 
    String[] poolNames = strPools.split(","); 
    int cnt = 0; 
    for(int i = 0; i < poolNames.length; i++){ 
      String poolName = poolNames[i].trim(); 
      if(!"".equals(poolName)){ 
        try{ 
          ConnectionPool pool = initPool(poolName); 
          dbPools.put(poolName, pool); 
          cnt++; 
        } catch(Exception ex){ 
          log.error("初始化数据库连接池[" + poolName + "]时发生错误!", ex); 
        } 
      } 
    } 
    isInit = true; 
    log.info("数据库连接池初始化结束, 共有" + cnt + "个连接池初始化成功!"); 
  } 
 
  /** 
   * 初始化指定名称配置的数据库连接池 
   * @param poolName String 
   * @return ConnectionPool 
   * @throws Exception 
   */ 
  private static ConnectionPool initPool(String poolName) throws Exception{ 
    String poolKey = DBConfig.POOL_PREFIX + poolName; 
    String driver = DBConfig.get(poolKey + DBConfig.POOL_DRIVER); 
    String url = DBConfig.get(poolKey + DBConfig.POOL_URL); 
    String user = DBConfig.get(poolKey + DBConfig.POOL_USER); 
    String pwd = DBConfig.get(poolKey + DBConfig.POOL_PWD); 
    if(driver == null || url == null || user == null || pwd == null){ 
      throw new Exception("连接池配置信息[" + poolName + "]不完整,无法完成初始化!"); 
    } 
    int loginTimeout = DBConfig.getInt(poolKey + DBConfig.POOL_LOGIN_TIMEOUT, 120); 
    log.info("开始初始化数据库连接池[" + poolName + "]"); 
//    log.debug(driver + "," + url + "," + user + "," + pwd + "," + loginTimeout); 
    ConnectionPool connPool = new ConnectionPool(driver, url, user, pwd, loginTimeout); 
    connPool.setTestTable(DBConfig.get(poolKey + DBConfig.POOL_TEST_TABLE, "dual")); 
    connPool.setInitialConnections(DBConfig.getInt(poolKey + DBConfig.POOL_INIT_CONNS, 5)); 
    connPool.setIncrementalConnections(DBConfig.getInt(poolKey + DBConfig.POOL_INC_CONNS, 5)); 
    connPool.setMaxConnections(DBConfig.getInt(poolKey + DBConfig.POOL_MAX_CONNS, 50)); 
    try{ 
      connPool.createPool(); 
      log.info("数据库连接池[" + poolName + "]初始化成功!"); 
    } catch(Exception ex){ 
      log.fatal("无法建立数据库连接池[" + poolName + "]!", ex); 
      throw ex; 
    } 
    return connPool; 
  } 
 
  /** 
   * 关闭整个数据库连接池 
   */ 
  public synchronized static void closeDBConnPool(){ 
    if(!isInit) 
      return; 
    Iterator it = dbPools.keySet().iterator(); 
    while(it.hasNext()){ 
      closePool((String)it.next()); 
    } 
    isInit = false; 
  } 
 
  private synchronized static void closePool(String poolName){ 
    if(dbPools.containsKey(poolName)){ 
      ConnectionPool pool = (ConnectionPool)dbPools.get(poolName); 
      try{ 
        pool.closeConnectionPool(); 
        dbPools.remove(poolName); 
        pool = null; 
        log.info("数据库连接池[" + poolName + "]已关闭!"); 
      } catch(SQLException ex){ 
        log.error("无法关闭数据库连接池[" + poolName + "]!", ex); 
      } 
    } 
  } 
 
  /** 
   * 刷新整个数据库连接池 
   */ 
  public synchronized static void refreshDBConnPool(){ 
    Iterator it = dbPools.entrySet().iterator(); 
    while(it.hasNext()){ 
      Map.Entry et = (Map.Entry)it.next(); 
      String poolName = (String)et.getKey(); 
      ConnectionPool pool = (ConnectionPool)et.getValue(); 
      try{ 
        pool.refreshConnections(); 
      } catch(Exception ex){ 
        log.error("无法刷新连接池[" + poolName + "]", ex); 
      } 
    } 
  } 
 
  /** 
   * 刷新指定的数据库连接池 
   * @param poolName String    连接池名称 
   */ 
  public synchronized static void refreshDBConnPool(String poolName){ 
    if(dbPools.containsKey(poolName)){ 
      ConnectionPool pool = (ConnectionPool)dbPools.get(poolName); 
      try{ 
        pool.refreshConnections(); 
      } catch(Exception ex){ 
        log.error("无法刷新连接池[" + poolName + "]", ex); 
      } 
    } 
  } 
 
  /** 
   * 获取数据库连接池对象 
   * @return ConnectionPool    连接池名称 
   */ 
  public static ConnectionPool getDBConnPool(String name){ 
    return(ConnectionPool)dbPools.get(name); 
  } 
 
  /** 
   * 获取默认的数据库连接池 
   * @return ConnectionPool 
   */ 
  public static ConnectionPool getDBConnPool(){ 
    return getDBConnPool(DEFAULT_POOL_NAME); 
  } 
 
  /** 
   * 从默认连接池获取连接 
   * @return Connection 
   * @throws SQLException 
   */ 
  public static Connection getDBConn() throws Exception{ 
    return getDBConn(DEFAULT_POOL_NAME); 
  } 
 
  /** 
   * 从指定的连接池中获取连接 
   * @param poolName String      连接池名称 
   * @return Connection 
   * @throws Exception 
   */ 
  public static Connection getDBConn(String poolName) throws Exception{ 
    ConnectionPool connPool = getDBConnPool(poolName); 
    if(connPool != null) 
      return connPool.getConnection(); 
    else 
      return null; 
  } 
 
  /** 
   * 归还数据库连接到连接池 
   * @param conn Connection 
   */ 
  public static void returnDBConn(Connection conn){ 
    ConnectionPool pool = (ConnectionPool)getDBConnPool(DEFAULT_POOL_NAME); 
    boolean isDone = false; 
    if(pool != null) 
      isDone = pool.returnConnection(conn); 
    if(!isDone){ 
      Iterator it = dbPools.values().iterator(); 
      while(it.hasNext()){ 
        pool = (ConnectionPool)it.next(); 
        if(pool.returnConnection(conn)) 
          return; 
      } 
    } 
  } 
 
  /** 
   * 归还数据库连接到指定的数据库连接池 
   * @param poolName String 
   * @param conn Connection 
   */ 
  public static void returnDBConn(String poolName, Connection conn){ 
    ConnectionPool pool = (ConnectionPool)getDBConnPool(poolName); 
    if(pool != null) 
      pool.returnConnection(conn); 
  } 
 
  /** 
   * 关闭 ResultSet 
   * @param rs ResultSet 
   */ 
  public static void closeRS(ResultSet rs){ 
    if(rs != null){ 
      try{ 
        rs.close(); 
      } catch(SQLException ex){ 
      } 
    } 
  } 
 
  /** 
   * 关闭Statemet 
   * @param stmt Statement 
   */ 
  public static void closeStmt(Statement stmt){ 
    if(stmt != null){ 
      try{ 
        stmt.close(); 
      } catch(Exception ex){ 
      } 
    } 
  } 
 
  /** 
   * 扫尾工作，close, return 
   * @param rs ResultSet 
   * @param stmt Statement 
   * @param dbConn Connection 
   */ 
  public static void mopUpDBConn(ResultSet rs, Statement stmt, Connection dbConn){ 
    closeRS(rs); 
    closeStmt(stmt); 
    returnDBConn(dbConn); 
  } 
 
  /** 
   * 扫尾工作，close, return 
   * @param stmt Statement 
   * @param dbConn Connection 
   */ 
  public static void mopUpDBConn(Statement stmt, Connection dbConn){ 
    closeStmt(stmt); 
    returnDBConn(dbConn); 
  } 
 
  /** 
   * 扫尾工作，close, return 
   * @param rs ResultSet 
   * @param stmt Statement 
   * @param poolName String 
   * @param dbConn Connection 
   */ 
  public static void mopUpDBConn(ResultSet rs, Statement stmt, String poolName, Connection dbConn){ 
    closeRS(rs); 
    closeStmt(stmt); 
    returnDBConn(poolName, dbConn); 
  } 
 
  /** 
   * 扫尾工作，close, return 
   * @param stmt Statement 
   * @param poolName String 
   * @param dbConn Connection 
   */ 
  public static void mopUpDBConn(Statement stmt, String poolName, Connection dbConn){ 
    closeStmt(stmt); 
    returnDBConn(poolName, dbConn); 
  } 
  /** 
   * 开始一个事务 
   * @param conn Connection 
   * @return boolean 
   */ 
  public static void beginTrans(Connection conn) throws Exception{ 
    conn.setAutoCommit(false); 
  } 
 
  /** 
   * 结束事务 
   * @param conn Connection 
   * @throws Exception 
   */ 
  public static void endTrans(Connection conn) throws Exception{ 
    conn.setAutoCommit(true); 
  } 
 
  /** 
   * 提交事务 
   * @param conn Connection 
   * @return boolean 
   */ 
  public static void commitTrans(Connection conn) throws Exception{ 
    conn.commit(); 
  } 
 
  /** 
   * 回滚事务 
   * @param conn Connection 
   * @return boolean 
   */ 
  public static void rollbackTrans(Connection conn) throws Exception{ 
    conn.rollback(); 
  } 
 
  /** 
   * 构建分页的SQL语句 
   * @param pageno int    第几页 
   * @param pagenum int   每页的条数 
   * @param strSql String 原始的SQL 
   * @return String       包装后的分页SQL 
   */ 
  public static String getMultiPageSql(int pageno, int pagenum, String strSql){ 
    int min = (pageno - 1) * pagenum + 1; 
    int max = pageno * pagenum; 
    String str = "select * from (select t$_table.*,rownum as t$_rownum from(" + 
                 strSql + ") t$_table) where t$_rownum >= " + min + 
                 " and t$_rownum<=" + max; 
    return str; 
  } 
 
  /** 
   * 构建DB2数据库格式的分页SQL语句 
   * @param pageno int      第几页 
   * @param pagecnt int     每页的条数 
   * @param strSql String   原始SQL 
   * @return String         包装后的分页SQL 
   */ 
  public static String getDB2MultiPageSql(int pageno, int pagecnt, 
                                          String strSql){ 
    int min = (pageno - 1) * pagecnt + 1; 
    int max = pageno * pagecnt; 
    String str = "select * from (select t$_table.*,rownumber() over() as t$_rownum from (" + 
                 strSql + ") t$_table) as t$_table2 where t$_table2.t$_rownum between " + 
                 min + " and " + max; 
    return str; 
  } 
 
  /** 
   * 构建获取最前面N条的SQL 
   * @param topn int         n值 
   * @param strSql String    原始SQL 
   * @return String          包装后的SQL 
   */ 
  public static String getTopNSql(int topn, String strSql){ 
    String str = "select * from (select t$_table.*,rownum from(" + strSql + 
                 ") t$_table where rownum <= " + topn + ")"; 
    return str; 
  } 
 
  /** 
   * 构建获取最前面N条的DB2格式SQL 
   * @param topn int          n值 
   * @param strSql String     原始SQL 
   * @return String           包装后的SQL 
   */ 
  public static String getDB2TopNSql(int topn, String strSql){ 
    String str = strSql + " fetch first " + topn + " rows only"; 
    return str; 
  } 
 
  /** 
   * 构建获取指定索引范围的SQL 
   * @param min int            最小索引(从1开始) 
   * @param max int            最大索引 
   * @param strSql String      原始SQL 
   * @return String            包装后的SQL 
   */ 
  public static String getIndexScopeSql(int min, int max, String strSql){ 
    String str = "select * from (select t$_table.*,rownum as t$_rownum from(" + 
        strSql + ") t$_table where rownum <= " + max + ") where t$_rownum >= " + min; 
    return str; 
  } 
 
  /** 
   * 构建获取指定索引范围的DB2格式SQL 
   * @param min int            最小索引(从1开始) 
   * @param max int            最大索引 
   * @param strSql String      原始SQL 
   * @return String            包装后的SQL 
   */ 
  public static String getDB2IndexScopeSql(int min, int max, String strSql){ 
    String str = "select * from (select t$_table.*,rownumber() over() as t$_rownum from (" + 
                 strSql + ") t$_table) as t$_table2 where t$_table2.t$_rownum between " + 
                 min + " and " + max; 
    return str; 
  } 
 
  /** 
   * 获取Clob字段的字串值 
   * @param rs ResultSet 
   * @param clobName String      字段名 
   * @return String 
   * @throws SQLException 
   */ 
  public static String getClobText(ResultSet rs, String clobName) throws SQLException{ 
    Clob clob = rs.getClob(clobName); 
    return getClobText(clob); 
  } 
 
  /** 
   * 获取Clob字段的字串值 
   * @param rs ResultSet 
   * @param idx int            字段序号(从1开始) 
   * @return String 
   * @throws SQLException 
   */ 
  public static String getClobText(ResultSet rs, int idx) throws SQLException{ 
    Clob clob = rs.getClob(idx); 
    return getClobText(clob); 
  } 
 
  /** 
   * 获取Clob字段的字串值 
   * @param clob Clob        Clob字段对象 
   * @return String 
   * @throws SQLException 
   */ 
  public static String getClobText(Clob clob) throws SQLException{ 
    if(clob != null) 
      return clob.getSubString(1L, (int)clob.length()); 
    else 
      return null; 
  } 
 
  /** 
   * 获取Blob字段的字节数组 
   * @param rs ResultSet 
   * @param colName String      字段名 
   * @return byte[] 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static byte[] getBlobBytes(ResultSet rs, String colName) throws SQLException, IOException{ 
    Blob blob = rs.getBlob(colName); 
    return getBlobBytes(blob); 
  } 
 
  /** 
   * 获取Blob字段的字节数组 
   * @param rs ResultSet 
   * @param idx int           字段序号(从1开始) 
   * @return byte[]           字节数组 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static byte[] getBlobBytes(ResultSet rs, int idx) throws SQLException, IOException{ 
    Blob blob = rs.getBlob(idx); 
    return getBlobBytes(blob); 
  } 
 
  /** 
   * 获取Blob字段的字节数组 
   * @param blob Blob         Blob对象 
   * @return byte[] 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static byte[] getBlobBytes(Blob blob) throws SQLException, IOException{ 
    if(blob != null){ 
      InputStream is = blob.getBinaryStream(); 
      byte data[] = new byte[(int)blob.length()]; 
      is.read(data); 
      is.close(); 
      return data; 
    } else{ 
      return null; 
    } 
  } 
 
  /** 
   * 输出Blob内容到指定的输出流 
   * @param rs ResultSet 
   * @param colName String      字段名 
   * @param os OutputStream     输出流 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static void outputBlob(ResultSet rs, String colName, OutputStream os) throws SQLException, 
      IOException{ 
    Blob blob = rs.getBlob(colName); 
    outputBlob(blob, os); 
  } 
 
  /** 
   * 输出Blob内容到指定的输出流 
   * @param rs ResultSet 
   * @param idx int            字段序号(从1开始) 
   * @param os OutputStream    输出流 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static void outputBlob(ResultSet rs, int idx, OutputStream os) throws SQLException, 
      IOException{ 
    Blob blob = rs.getBlob(idx); 
    outputBlob(blob, os); 
  } 
 
  /** 
   * 输出Blob内容到指定的输出流 
   * @param blob Blob          blob对象 
   * @param os OutputStream    输出流 
   * @throws SQLException 
   * @throws IOException 
   */ 
  public static void outputBlob(Blob blob, OutputStream os) throws SQLException, IOException{ 
    if(blob != null){ 
      InputStream is = blob.getBinaryStream(); 
      BufferedInputStream input = new BufferedInputStream(is); 
      byte buff[] = new byte[2048]; 
      for(int readLen = 0; -1 != (readLen = input.read(buff, 0, buff.length)); ) 
        os.write(buff, 0, readLen); 
      os.flush(); 
    } 
  } 
 
  /** 
   * 获取数据库的当前时间 
   * @return Timestamp 
   */ 
  public static Timestamp getSysDate() throws Exception{ 
    return getSysDate(DEF_POOL_NAME); 
  } 
 
  /** 
   * 获取指定连接池中数据库的系统时间 
   * @param poolName String 
   * @return Timestamp 
   */ 
  public static Timestamp getSysDate(String poolName) throws Exception{ 
    Connection conn = null; 
    Statement st = null; 
    ResultSet rs = null; 
    Timestamp sysdate = null; 
    try{ 
      conn = getDBConn(poolName); 
      st = conn.createStatement(); 
      rs = st.executeQuery("select sysdate from dual"); 
      if(rs.next()) 
        sysdate = rs.getTimestamp(1); 
    } catch(Exception ex){ 
      log.error("无法获取数据库当前时间,连接池=" + poolName, ex); 
      throw ex; 
    } finally{ 
      closeRS(rs); 
      closeStmt(st); 
      returnDBConn(poolName, conn); 
    } 
    return sysdate; 
  } 
 
  /** 
   * 获取数据库系统时间字符串 （日期+时间） 
   * @return String 
   */ 
  public static String getSysDateStr() throws Exception{ 
    return getSysDateStr(DEF_POOL_NAME, DEF_DATETIME_FORMAT); 
  } 
 
  /** 
   * 获取指定格式的数据库系统时间字符串 
   * @param dateFmt String 
   * @return String 
   */ 
  public static String getSysDateStr(String dateFmt) throws Exception{ 
    return getSysDateStr(DEF_POOL_NAME, dateFmt); 
  } 
 
  /** 
   * 获取指定连接池与指定格式的数据库系统时间字符串 
   * @param poolName String 
   * @param dateFmt String 
   * @return String 
   */ 
  public static String getSysDateStr(String poolName, String dateFmt) throws Exception{ 
    Connection conn = null; 
    Statement st = null; 
    ResultSet rs = null; 
    String sysdate = null; 
    try{ 
      conn = getDBConn(poolName); 
      st = conn.createStatement(); 
      rs = st.executeQuery("select to_char(sysdate,'" + dateFmt + "') curdate from dual"); 
      if(rs.next()) 
        sysdate = rs.getString(1); 
    }catch(Exception ex){ 
      log.error("无法获取数据库时间,连接池=" + poolName + ",格式=" + dateFmt, ex); 
      throw ex; 
    }finally{ 
      closeRS(rs); 
      closeStmt(st); 
      returnDBConn(poolName, conn); 
    } 
    return sysdate; 
  } 
 
  /** 
   * 获取指定Sequence的下一取值 
   * @param seqName String     Sequence名称 
   * @return Long 
   */ 
  public static long getNextSeq(String seqName) throws Exception{ 
    return getNextSeq(DEF_POOL_NAME, seqName); 
  } 
 
  /** 
   * 获取指定连接池中指定Sequence的下一取值 
   * @param poolName String 
   * @param seqName String 
   * @return Long 
   */ 
  public static long getNextSeq(String poolName, String seqName) throws Exception{ 
    Connection conn = null; 
    Statement st = null; 
    ResultSet rs = null; 
    long seq = 0; 
    try{ 
      conn = getDBConn(poolName); 
      st = conn.createStatement(); 
      rs = st.executeQuery("select " + seqName + ".nextval from dual"); 
      if(rs.next()) 
        seq = rs.getLong(1); 
    }catch(Exception ex){ 
      log.error("无法获取连接池=" + poolName + ",序列名=" + seqName + "的下一取值",ex); 
      throw ex; 
    }finally{ 
      closeRS(rs); 
      closeStmt(st); 
      returnDBConn(poolName, conn); 
    } 
    return seq; 
  } 
 
  /** 
   * 获取指定Sequence的当前取值 
   * @param seqName String     Sequence名称 
   * @return Long 
   */ 
  public static long getCurrSeq(String seqName) throws Exception{ 
    return getCurrSeq(DEF_POOL_NAME, seqName); 
  } 
 
  /** 
   * 获取指定连接池中指定Sequence的当前取值 
   * @param poolName String 
   * @param seqName String 
   * @return long 
   * @throws Exception 
   */ 
  public static long getCurrSeq(String poolName, String seqName) throws Exception{ 
    Connection conn = null; 
    Statement st = null; 
    ResultSet rs = null; 
    long seq = 0; 
    try{ 
      conn = getDBConn(poolName); 
      st = conn.createStatement(); 
      rs = st.executeQuery("select " + seqName + ".currval from dual"); 
      if(rs.next()) 
        seq = rs.getLong(1); 
    }catch(Exception ex){ 
      log.error("无法获取连接池=" + poolName + ",序列名=" + seqName + "的当前取值",ex); 
      throw ex; 
    }finally{ 
      closeRS(rs); 
      closeStmt(st); 
      returnDBConn(poolName, conn); 
    } 
    return seq; 
  } 
 
  public static boolean isTableExist(String tabName){ 
    Connection conn = null; 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    try{ 
      conn = DBUtils.getDBConn(); 
      ps = conn.prepareStatement("select 1 from " + tabName + " where 1=2"); 
      rs = ps.executeQuery(); 
      return true; 
    } catch(Exception ex){ 
      return false; 
    } finally{ 
      DBUtils.mopUpDBConn(rs, ps, conn); 
    } 
  } 
 
  public static boolean isSequenceExist(String seqName){ 
    Connection conn = null; 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    try{ 
      conn = DBUtils.getDBConn(); 
      ps = conn.prepareStatement("select " + seqName + ".currval from dual"); 
      rs = ps.executeQuery(); 
      return true; 
    } catch(Exception ex){ 
      return false; 
    } finally{ 
      DBUtils.mopUpDBConn(rs, ps, conn); 
    } 
  } 
 
}