mysql8.0创建dbcp,使用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包)

?

原文：http://java--hhf.iteye.com/blog/2171051