在服务器端涉及数据库访问的应用程序里头，数据库连接池是一项确保性能的关键技术。一谈起java数据库连接池，大家都可以罗列出一堆开源实现。
它们也各有优劣：
1. DBCP TOMCAT自带的
2. proxool
3. c3p0: see: http://sourceforge.net/projects/c3p0
4. DBPool
5. Taobao的druid
这些都是蛮不错的连接池实现。基于不用重复造轮子一说，完全可以挑一个自己喜欢的去用。已有的轮子如果没出问题，还好说，出了问题，往往很难解决。
本文的主旨是阐述连接池的基本思想：
1. 能将池化的资源（连接）控制在一定范围以内[min, max]
2. 能在jvm退出之前，释放这些连接资源
3. 能尽量简化用户的代码
4. 能确保连接池的连接始终是活跃的
下面还是上代码吧, 以下的代码是整理而成，缺点还是不少，但是也足够一般情况下的使用，至少是可控的。
1. 一个hook类，用于jvm退出前释放资源
package com.hisql;
import java.sql.SQLException;
import org.apache.log4j.Logger;
public class ReleasePoolHook implements Runnable
{
public static final Logger logger = Logger.getLogger(ReleasePoolHook.class);
ConnectionPool connpool;
public ReleasePoolHook(ConnectionPool pool)
{
// register it
connpool = pool;
Runtime.getRuntime().addShutdownHook(new Thread(this));
logger.info(">>> shutdown hook registered...");
}
@Override
public void run()
{
// TODO Auto-generated method stub
logger.info("\n>>> About to execute: " + ReleasePoolHook.class.getName() + ".run() to clean up before JVM exits.");
this.cleanUp();
logger.info(">>> Finished execution: " + ReleasePoolHook.class.getName() + ".run()");
}
private void cleanUp()
{
if (connpool != null)
{
try
{
connpool.closeConnectionPool();
logger.info("Pool realeased....");
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
logger.warn("Pool released with exception", e);
}
}
}
}
2. PooledConnection类, 用于对Connection接口类的一个封装
很粗糙，标准的做法，应该是将Connection所有的方法都delegate，这样，用户用起来就蛮方便。这里只是为了阐述思想，如下：
package com.hisql;
import java.sql.Connection;
public class PooledConnection
{
Connection connection = null;
boolean busy = false;
public PooledConnection(Connection connection)
{
this.connection = connection;
}
public Connection getConnection()
{
return connection;
}
public void setConnection(Connection connection)
{
this.connection = connection;
}
public boolean isBusy()
{
return busy;
}
public void setBusy(boolean busy)
{
this.busy = busy;
}
}
3. ConnectionPool类
这是主体实现类:
package com.hisql;
import java.sql.*;
import java.util.*;
import org.apache.log4j.Logger;
public class ConnectionPool
{
private String jdbcDriver;
private String dbUrl;
private String dbUsername;
private String dbPassword;
private String pingSql = "select 1"; // the test sql statement to ping the target database
private int minConnections = 5;
private int incrementalConnections = 2;
private int maxConnections = 20;
private Vector<PooledConnection> connections;
private ReleasePoolHook hook;
public static final Logger logger = Logger.getLogger(ConnectionPool.class);
public ConnectionPool(String driver, String url, String username, String password)
{
jdbcDriver = driver;
dbUrl = url;
dbUsername = username;
dbPassword = password;
hook = new ReleasePoolHook(this);
}
public int getInitialConnections()
{
return this.minConnections;
}
public void setInitialConnections(int initialConnections)
{
this.minConnections = initialConnections;
}
public int getIncrementalConnections()
{
return this.incrementalConnections;
}
public void setIncrementalConnections(int incrementalConnections)
{
this.incrementalConnections = incrementalConnections;
}
public int getMaxConnections()
{
return this.maxConnections;
}
public void setMaxConnections(int maxConnections)
{
this.maxConnections = maxConnections;
}
public String getPingSql()
{
return this.pingSql;
}
public void setPingSql(String sql)
{
this.pingSql = sql;
}
/**
* intialize the pool
* @throws Exception
*/
public synchronized void initialize() throws Exception
{
if (connections != null)
{
return;
}
Class.forName(this.jdbcDriver);
connections = new Vector();
createConnections(this.minConnections);
}
private void createConnections(int numConnections) throws SQLException
{
for (int i=0; i<numConnections; i++)
{
if (this.maxConnections > 0 && this.connections.size() >= this.maxConnections)
{
break;
}
// add a new PooledConnection object
try
{
connections.addElement(new PooledConnection(newConnection()));
}
catch (SQLException e)
{
logger.error("create connection failed: ", e);
throw new SQLException();
}
logger.info(" connection created ......");
}
}
private Connection newConnection() throws SQLException
{
Connection conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
if (connections.size() == 0)
{
DatabaseMetaData metaData = conn.getMetaData();
int driverMaxConnections = metaData.getMaxConnections();
if (driverMaxConnections > 0 && this.maxConnections > driverMaxConnections)
{
this.maxConnections = driverMaxConnections;
}
}
return conn;
}
public synchronized Connection getConnection() throws SQLException
{
if (connections == null)
{
return null;
}
Connection conn = getFreeConnection();
while (conn == null)
{
wait(250);
conn = getFreeConnection();
}
return conn;
}
private Connection getFreeConnection() throws SQLException
{
Connection conn = findFreeConnection();
if (conn == null)
{
createConnections(incrementalConnections);
conn = findFreeConnection();
if (conn == null)
{
return null;
}
}
return conn;
}
private Connection findFreeConnection() throws SQLException
{
Connection conn = null;
PooledConnection pConn = null;
Iterator<PooledConnection> iter = connections.iterator();
while (iter.hasNext())
{
pConn = (PooledConnection)iter.next();
if (!pConn.isBusy())
{
conn = pConn.getConnection();
pConn.setBusy(true);
if (!testConnection(conn))
{
try
{
conn = newConnection();
}
catch(SQLException e)
{
logger.error("create connection failed:", e);
return null;
}
pConn.setConnection(conn);
}
break;
}
}
return conn;
}
private boolean testConnection(Connection conn)
{
Statement stmt = null;
ResultSet rset = null;
try
{
stmt = conn.createStatement();
rset = stmt.executeQuery(this.pingSql);
}
catch (SQLException ex)
{
closeConnection(conn);
return false;
}
finally
{
try
{
if (rset!= null) rset.close();
}
catch (SQLException ex) {}
try
{
if (stmt!= null) stmt.close();
}
catch (SQLException ex) {}
}
return true;
}
public void returnConnection(Connection conn)
{
if (connections == null)
{
logger.warn("connection pool not exists.");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements())
{
pConn = (PooledConnection)enumerate.nextElement();
if (conn == pConn.getConnection())
{
pConn.setBusy(false);
break;
}
}
}
public synchronized void refreshConnections() throws SQLException
{
if (connections == null)
{
logger.warn("connection pool not exists, can't refresh...");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements())
{
pConn = (PooledConnection)enumerate.nextElement();
if (pConn.isBusy())
{
wait(5000);
}
closeConnection(pConn.getConnection());
pConn.setConnection(newConnection());
pConn.setBusy(false);
}
}
public synchronized void closeConnectionPool() throws SQLException
{
if (connections == null)
{
logger.warn("conneciton pool not exists, can't close..");
return;
}
PooledConnection pConn = null;
Enumeration enumerate = connections.elements();
while (enumerate.hasMoreElements())
{
pConn = (PooledConnection)enumerate.nextElement();
if (pConn.isBusy())
{
wait(5000);
}
closeConnection(pConn.getConnection());
connections.removeElement(pConn);
}
connections = null;
}
private void closeConnection(Connection conn)
{
try
{
conn.close();
}
catch (SQLException ex)
{
logger.warn("close connection error: ", ex);
}
}
private void wait(int mSeconds)
{
try
{
Thread.sleep(mSeconds);
}
catch (InterruptedException e)
{
}
}
}
使用方法：
ConnectionPool connpool = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/foo", "root", "******");
connpool.initialize();
Connection conn = connpool.getConnection();
try
{
......
}
catch ()
finally
{
connpool.returnConnection(conn);
}
三个类只依赖于log4j-1.2.*.jar 和 commons-logging-*.jar，所以很容易构建一个工程。
改进思路：
1. 对PooledConnection类，增加相应的delegate方法，让用户看不到connpool.returnConnection()这类api。只需要关注connection.close()，不是真正的close()
2. 对pingConnection操作，提供一个工作线程，使其能确保连接池中所有的connection都是活连接。现实中，有些数据库如果空闲时间超时，比如1个小时，它是自动断开客户端连接的，这样会对应用产生负责影响。
3. 最好能自动收缩，如果连接长期不使用，可以进行物理释放，让真正打开的连接处于[min, max]之间。而不是达到max以后，物理连接就始终占用max个资源，这不尽合理。