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.*;

/**
 *
 * <p>Title: 福建客服支撑系统</p>
 *
 * <p>Description: 数据库操作的工具类</p>
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: Newland</p>
 *
 * @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;