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

zyh3033

　　        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包）