使用DBCP创建MySQL数据库连接池

        DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目，也是 tomcat 使用的连接池组件。单独使用dbcp需要3个包：commons-dbcp.jar,commons-pool.jar,commons-collections.jar。

       由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。

【1】配置文件

#mysql
db.className=com.mysql.jdbc.Driver
#mysql to write
wdb.url=jdbc:mysql://
wdb.username=root
wdb.pwd=123456
wdb.maxActive=30
wdb.maxIdle=10
wdb.maxWait=3600000
#mysql common config info
common.db.removeAbandoned=false
common.db.removeAbandonedTimeout=120
common.db.testOnBorrow=true
common.db.logAbandoned=true

 【2】创建数据库连接池并获得数据库连接

package hhf.dbcp.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

/**
 * Get the connection from writing DB
 */
public class WDBConnUtils {
	
	private static BasicDataSource dataSource = null;  
	
    public static void init() {  
        if (dataSource != null) {  
            try {  
                dataSource.close();  
            } catch (Exception e) {  
                e.printStackTrace(); 
            }  
            dataSource = null;  
        }  
        try {  
            Properties p = new Properties();  
            p.setProperty("driverClassName", SystemConstants.DB_CLASS_NAME);  
            p.setProperty("url", SystemConstants.W_DB_URL);  
            p.setProperty("username", SystemConstants.W_DB_USER_NAME);  
            p.setProperty("password", SystemConstants.W_DB_USER_PWD);  
            p.setProperty("maxActive", SystemConstants.W_DB_MAX_ACTIVE);  
            p.setProperty("maxIdle", SystemConstants.W_DB_MAX_IDLE);  
            p.setProperty("maxWait", SystemConstants.W_DB_MAX_WAIT);  
            p.setProperty("removeAbandoned", SystemConstants.DB_REMOVE_ABANDONED);  
            p.setProperty("removeAbandonedTimeout", SystemConstants.DB_REMOVE_ABANDONED_TIMEOUT);  
            p.setProperty("testOnBorrow", SystemConstants.DB_TEST_ONBORROW);  
            p.setProperty("logAbandoned", SystemConstants.DB_LOG_ABANDONED);  
            dataSource = (BasicDataSource) BasicDataSourceFactory.createDataSource(p);  
        } catch (Exception e) {  
            e.printStackTrace();
        }  
    }  
  
  
    public static synchronized Connection getConnection() throws  SQLException {  
        if (dataSource == null) {  
            init();  
        }  
        Connection conn = null;  
        if (dataSource != null) {  
            conn = dataSource.getConnection();  
            conn.setAutoCommit(false);
        }  
        return conn;  
    }  

	/**
	 * 关闭资源
	 * @param conn
	 * @param pstmt
	 * @param rs
	 */
	public static void closeResources(Connection conn, PreparedStatement pstmt,	ResultSet rs) {
		if (null != rs) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (null != pstmt) {
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					} finally {
						if (null != conn) {
							try {
								conn.close();
							} catch (SQLException e) {
								e.printStackTrace();
								throw new RuntimeException(e);
							}
						}
					}
				}
			}
		}
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 * @param pstmt
	 */
	public static void closeResources(Connection conn, PreparedStatement pstmt) {
		if (null != pstmt) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			} finally {
				if (null != conn) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
						throw new RuntimeException(e);
					}
				}
			}
		}
	}	
}

 【3】执行插入和查询操作

package hhf.dbcp.throughput;

import hhf.dbcp.util.WDBConnUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 数据库操作
 * @author HHF
 * 2014年12月29日
 */
public class InsertQueryDao {
    /**
     * 插入数据
     * @param num
     */
	public void save(String num) {
		String NEWS_INFO_INSERT = "INSERT INTO table(num) values(?)";
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = WDBConnUtils.getConnection();
			pst = conn.prepareStatement(NEWS_INFO_INSERT);
			pst.setString(1, num);
			pst.execute();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			WDBConnUtils.closeResources(conn, pst);
		}
	}
	/**
	 * 查询数据
	 * @param num
	 * @return
	 */
	public String query(String num) {
		String NEWS_INFO_QUERY_LIST = "SELECT news_id FROM table num=? ";
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String result = null;
		try {
			conn = WDBConnUtils.getConnection();
			pst = conn.prepareStatement(NEWS_INFO_QUERY_LIST);
			pst.setString(1, num);
			rs = pst.executeQuery();
			while(rs.next()){
				result = rs.getString("news_id");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			WDBConnUtils.closeResources(conn, pst, rs);
		}
		return result;
	}
}

 【4】测试

public class Main {
	  private static final Log log = LogFactory.getLog(Main.class); 
	
	  public void doProcess(String processTime1, String processTime2, int runMode) {
		    InsertQueryDao dao = new InsertQueryDao();
		    String num = dao.query("0");//获得查询结果
			log.info("num " + num);
	  }
}

 （PS：附上项目测试源码和相关的jar包）