开发web，离不开对数据库的操作。每次JDBC操作，都会用到Connection。如果没一次数据库操作都新建一个Connection，会造成较大的系统开销。数据库连接池就是用来解决这个问题。
数据库连接池的基本思路就是，我先初始化一些Connection，当需要连接时，从连接池里取，如果没有空闲连接就新建一个连接，如果达到连接池设置的上限，则等待其他连接的释放。
像C3P0之类的连接池，都支持配置，如上线多少个，如果新建一次性增加多少个，初始化时多少个。每隔多久回收一次等等策略。
下面来所说我的实现：
连接池实现类
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.yz.db.pool;
import com.yz.db.datasource.DataSourceBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.log4j.Logger;
/**
*
* @author yingzi.zhu
*/
public class DBConnectionPool {
private static final Logger logger = Logger.getLogger(DBConnectionPool.class);
//连接池在无空闲连接可用时一次性创建的新数据库连接数
private int acquireIncrement = 3;
//连接池初始化时创建的连接数
private int initialPoolSize = 3;
//连接池中拥有的最大连接数，如果获得新连接时会使连接总数超过这个值则不会再获取新连接，而是等待其他连接释放，所以这个值有可能会设计地很大
private int maxPoolSize = 15;
//连接的最大空闲时间，如果超过这个时间，某个数据库连接还没有被使用，则会断开掉这个连接如果为0，则永远不会断开连接
private int maxIdleTime = 0;
//连接池保持的最小连接数，后面的maxIdleTimeExcessConnections跟这个配合使用来减轻连接池的负载
private int minPoolSize = 0;
private int currentSize = 0;
private DataSourceBean dataSource;
private ArrayList<Connection> freeConnectionList = new ArrayList<Connection>();
private ArrayList<Connection> usedConnectionList = new ArrayList<Connection>();
public DBConnectionPool() {
}
public DBConnectionPool(DataSourceBean dataSource, int acquireIncrement, int initialPoolSize, int maxPoolSize, int maxIdleTime, int minPoolSize) {
this.dataSource = dataSource;
this.acquireIncrement = acquireIncrement;
this.initialPoolSize = initialPoolSize;
this.maxPoolSize = maxPoolSize;
this.maxIdleTime = maxIdleTime;
this.minPoolSize = minPoolSize;
createConnection(initialPoolSize);
}
/**
* 得到Connection
*
* @return
*/
public synchronized Connection getConnection() {
Connection conn = null;
while (currentSize >= maxPoolSize && freeConnectionList.isEmpty()) {
try {
logger.info("达到连接上线，等待其他程序释放连接");
wait();
} catch (InterruptedException ex) {
logger.error("线程异常" + ex.getMessage());
}
}
if (freeConnectionList.size() > 0) {
conn = freeConnectionList.remove(0);
usedConnectionList.add(conn);
System.out.println(currentSize + " " + usedConnectionList.size() + " " + freeConnectionList.size());
return conn;
}
if (currentSize < maxPoolSize) {
createConnection(acquireIncrement);
if (freeConnectionList.isEmpty()) {
return getConnection();
}
conn = freeConnectionList.remove(0);
usedConnectionList.add(conn);
System.out.println(currentSize + " " + usedConnectionList.size() + " " + freeConnectionList.size());
return conn;
}
return conn;
}
public int getPooSize() {
return currentSize;
}
public int getFreeConnSize() {
return freeConnectionList.size();
}
public int getUsedConnSize() {
return usedConnectionList.size();
}
/**
* 从DriverManager中新建一个链接
*
* @return
*/
private Connection newConnection() {
Connection conn = null;
try {
Class.forName(dataSource.getDriver());
conn = DriverManager.getConnection(dataSource.getUrl(), dataSource.getUser(), dataSource.getPassword());
return conn;
} catch (ClassNotFoundException ex) {
logger.error("注册驱动类异常" + ex.getMessage());
} catch (SQLException ex) {
logger.error("获得连接异常" + ex.getMessage());
}
return conn;
}
/**
* 释放连接
*
* @param conn
*/
public synchronized void freeConnection(Connection conn) {
usedConnectionList.remove(conn);
freeConnectionList.add(conn);
notifyAll();
}
/**
* 验证连接是否可用，不可用则删除
*
* @param conn
*/
public synchronized void isValid(Connection conn) {
try {
if (!conn.isValid(3000)) {
if (freeConnectionList.contains(conn)) {
freeConnectionList.remove(conn);
}
if (usedConnectionList.contains(conn)) {
usedConnectionList.remove(conn);
}
conn.close();
currentSize--;
}
} catch (SQLException ex) {
logger.error("数据库异常" + ex.getMessage());
}
}
/**
* 初始化最小连接
*/
private synchronized void createConnection(int count) {
for (int i = 0; i < count; i++) {
if (currentSize < maxPoolSize) {
freeConnectionList.add(newConnection());
currentSize++;
}
}
}
public void releaseAllConnection() {
try {
for (int i = 0; i < freeConnectionList.size(); i++) {
freeConnectionList.get(i).close();
}
if (usedConnectionList.size() > 0) {
try {
Thread.currentThread().sleep(100);
} catch (InterruptedException ex) {
logger.error(ex.getMessage());
}
releaseAllConnection();
}
} catch (SQLException ex) {
logger.error("数据库关闭连接异常" + ex.getMessage());
}
}
}
这个是连接池管理的核心类。里面的操作也很简单，声明一些属性用来保存配置项，声明两个List用来存放正在使用的连接和空闲连接。根据配置来判断，怎么去获取一个连接，怎么释放一个连接，什么时候新建一个连接等等。最主要的是考虑线程安全问题。如果没有，且已经达到上限，则需要等待其他线程的释放。
连接池管理类
/**
*
* @author yingzi.zhu
*/
public class DBConnectionManager {
private static DBConnectionManager instance = null;
private ConcurrentHashMap<String, DBConnectionPool> pools = new ConcurrentHashMap<String, DBConnectionPool>();
private DBConnectionManager() {
}
public static DBConnectionManager getInstance() {
if (instance == null) {
synchronized (DBConnectionManager.class) {
if (instance == null) {
instance = new DBConnectionManager();
}
}
}
return instance;
}
/**
*
* @param dsConfig
*/
public void createPool(DsConfig dsConfig){
DataSourceBean bean = new DataSourceBean();
bean.setDriver(dsConfig.getDriver());
bean.setUrl(dsConfig.getUrl());
bean.setPassword(dsConfig.getPassword());
bean.setUser(dsConfig.getUser());
DBConnectionPool pool = new DBConnectionPool(bean, dsConfig.getAcquireIncrement(), dsConfig.getInitialPoolSize(), dsConfig.getMaxPoolSize(), dsConfig.getMaxIdleTime(), dsConfig.getMinPoolSize());
pools.put(dsConfig.getConnName(), pool);
}
public Connection getConnection(String connName){
DBConnectionPool pool = pools.get(connName);
if(pool == null){
throw new RuntimeException("没有该连接池!");
}
return pool.getConnection();
}
public void freeConnection(Connection conn, String connName){
DBConnectionPool pool = pools.get(connName);
if(pool == null){
throw new RuntimeException("没有该连接池!");
}
pool.freeConnection(conn);
}
public int getPoolSize(String connName){
DBConnectionPool pool = pools.get(connName);
if(pool == null){
throw new RuntimeException("没有该连接池!");
}
return pool.getPooSize();
}
public int getFreeConnSize(String connName){
DBConnectionPool pool = pools.get(connName);
if(pool == null){
throw new RuntimeException("没有该连接池!");
}
return pool.getFreeConnSize();
}
public int getUsedConnSize(String connName){
DBConnectionPool pool = pools.get(connName);
if(pool == null){
throw new RuntimeException("没有该连接池!");
}
return pool.getUsedConnSize();
}
}
该类实现了对线程池的管理，一般一个数据源简历一个pool,然后manager类管理多个pool
下面是一个简单的测试：
public static void main(String[] args) throws SQLException {
DsConfig config = new DsConfig();
config.setConnName("conn1");
config.setAcquireIncrement(3);
config.setDriver("com.mysql.jdbc.Driver");
config.setInitialPoolSize(3);
config.setMaxIdleTime(0);
config.setMaxPoolSize(15);
config.setMinPoolSize(3);
config.setUrl("jdbc:mysql://localhost:3306/world");
config.setUser("root");
config.setPassword("123456");
final DBConnectionManager mg = DBConnectionManager.getInstance();
mg.createPool(config);
for (int i = 0; i < 100; i++) {
new Thread(new Runnable() {
@Override
public void run() {
try {
Connection connection = mg.getConnection("conn1");
Statement createStatement = connection.createStatement();
ResultSet executeQuery = createStatement.executeQuery("select * from city");
Thread.sleep(100);
mg.freeConnection(connection, "conn1");
} catch (SQLException ex) {
Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
}
catch (InterruptedException ex){
}
}
}).start();
}
}
分别获取100个链接，查看日志输出，全部正常。但是该线程池管理类比较简单，如果达到上限，则不再新增。使连接一直保持在最大限额。也不释放。其实也可以加一个定时程序，每隔多久释放一些符合可以释放条件的连接。