数据库连接池， 没有说的那么简单

今天整理硬盘，发现一个刚毕业那会儿收藏的问题了。记得刚毕业的时候出去面试， 有4次被问到怎么实现一个数据库连接池，可见这个题目当初还是比较吃香的。当时的答案几乎都是这个样子的：在程序/server启动的时候，先申请一定数量的连接放到一个list中缓存起来，当程序需要使用数据库的时候，list中如果还有连接，则分配给它，若没有，则再次申请连接，然后分配给程序， 当程序使用完连接之后， 把链接归还到list。现在想想觉得好笑，如果今天还有人问我这个问题，我说不定会反问他，为什么要连接池，他不一定能脱口而出告诉我原因。

连接池无非就是提高的效率，提高了反映速度。因为数据库连接是一种tcp/ip连接，建立连接需要花费一定的时间，尤其当数据库server和web server不再一台机器上的时候，8秒钟都说不准。如果用户每次提交请求，都需要花费8秒钟建立数据库连接，最后会疯掉。

至少有两个问题需要考虑：1 连接池的缓存/分配策略。需要考虑最小连接数目，最大连接数目， 管理正在被使用的连接和未使用的连接， 处理因为网络超使等特别原因而无效的连接，什么时候再次申请连接等等，这些问题实现起来不难，就是繁琐。 2 Connection关闭问题。当程序调用Connection.close()之后，则这个连接对应的tcp/ip 连接就消失了，所以必须替换掉这种行为。这个问题解决起来也不难，jdk 动态代理就可以。pool里面不要放原生的Connection，而放被代理之后的, close方法被替换之后的Connection包装类。

在网上找了一个，稍加修改，注释明了。

package db.connectionPool; import java.sql.*; import java.lang.reflect.*; import java.util.*; import java.io.*; /** * author:http://blog.csdn.net/sunxing007 **/ public class SimpleConnetionPool { //未用的连接 private static LinkedList notUsedConnections = new LinkedList(); //正在被使用的连接 private static HashSet usedConnections = new HashSet(); //driver name 怕ssword private static String url = "jdbc:mysql://localhost:3306/test"; private static String user = "root"; private static String password = "123456"; //initial pool size private static int POOL_SIZE = 10; //上次检查已关闭连接的时间 static private long lastcheckClosedConnectionTime = System.currentTimeMillis(); //检查已关闭连接的时间间隔 public static long CHECK_CLOSED_CONNECTION_TIME = 4 * 60 * 60 * 1000; // 4 hours // 测试用例 public static void main(String[] args) { SimpleConnetionPool.init(); Connection con = SimpleConnetionPool.getConnection(); Connection con1 = SimpleConnetionPool.getConnection(); Connection con2 = SimpleConnetionPool.getConnection(); SimpleConnetionPool.printDebugMsg(); try { con.close(); } catch (Exception e) { } try { con1.close(); } catch (Exception e) { } try { con2.close(); } catch (Exception e) { } con = SimpleConnetionPool.getConnection(); con1 = SimpleConnetionPool.getConnection(); try { con1.close(); } catch (Exception e) { } con2 = SimpleConnetionPool.getConnection(); SimpleConnetionPool.printDebugMsg(); } //静态初始化快，初始数据库驱动 static { initDriver(); } //pool初始化，申请 POOL_SIZE个连接 public static void init() { int i = 0; while (i < POOL_SIZE){ notUsedConnections.add(getNewConnection()); i++; } } private static void initDriver() { Driver driver = null; try { driver = (Driver) Class.forName("com.mysql.jdbc.Driver") .newInstance(); installDriver(driver); } catch (Exception e) { } } public static void installDriver(Driver driver) { try { DriverManager.registerDriver(driver); } catch (Exception e) { e.printStackTrace(); } } //分配连接 public static synchronized Connection getConnection() { checkClosedConnection(); while (notUsedConnections.size() > 0) { try { ConnectionWrapper wrapper = (ConnectionWrapper) notUsedConnections.removeFirst(); if (wrapper.connection.isClosed()) { continue; } usedConnections.add(wrapper); System.out.println("Allocate new connection succeed."); return wrapper.connection; } catch (Exception e) { e.printStackTrace(); } } int newCount = getIncreasingConnectionCount(); LinkedList list = new LinkedList(); ConnectionWrapper wrapper = null; for (int i = 0; i < newCount; i++) { wrapper = getNewConnection(); if (wrapper != null) { list.add(wrapper); } } if (list.size() == 0) { return null; } wrapper = (ConnectionWrapper) list.removeFirst(); usedConnections.add(wrapper); notUsedConnections.addAll(list); list.clear(); return wrapper.connection; } //申请新的数据库连接 private static ConnectionWrapper getNewConnection() { try { Connection con = DriverManager.getConnection(url, user, password); ConnectionWrapper wrapper = new ConnectionWrapper(con); return wrapper; } catch (Exception e) { e.printStackTrace(); return null; } } //将用完毕的连接还回pool static synchronized void pushConnectionBackToPool(ConnectionWrapper con) { boolean exist = usedConnections.remove(con); if (exist) { notUsedConnections.addLast(con); } } //pool清理， 关闭所有连接 public static int close() { int count = 0; Iterator iterator = notUsedConnections.iterator(); while (iterator.hasNext()) { try { ((ConnectionWrapper) iterator.next()).close(); count++; } catch (Exception e) { } } notUsedConnections.clear(); iterator = usedConnections.iterator(); while (iterator.hasNext()) { try { ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next(); wrapper.close(); count++; } catch (Exception e) { } } usedConnections.clear(); return count; } //检查因超时等其他原因导致的被关闭的连接 private static void checkClosedConnection() { long time = System.currentTimeMillis(); // sometimes user change system time,just return if (time < lastcheckClosedConnectionTime) { time = lastcheckClosedConnectionTime; return; } // no need check very often if (time - lastcheckClosedConnectionTime < CHECK_CLOSED_CONNECTION_TIME) { return; } lastcheckClosedConnectionTime = time; // begin check Iterator iterator = notUsedConnections.iterator(); while (iterator.hasNext()) { ConnectionWrapper wrapper = (ConnectionWrapper) iterator.next(); try { if (wrapper.connection.isClosed()) { iterator.remove(); } } catch (Exception e) { iterator.remove(); } } // make connection pool size smaller if too big int decrease = getDecreasingConnectionCount(); if (notUsedConnections.size() < decrease) { return; } while (decrease-- > 0) { ConnectionWrapper wrapper = (ConnectionWrapper) notUsedConnections .removeFirst(); try { wrapper.connection.close(); } catch (Exception e) { } } } //增开连接 public static int getIncreasingConnectionCount() { int count = 1; int current = getConnectionCount(); count = current / 4; if (count < 1) { count = 1; } return count; } //缩减连接 public static int getDecreasingConnectionCount() { int current = getConnectionCount(); if (current < 10) { return 0; } return current / 3; } //调试信息 public synchronized static void printDebugMsg() { printDebugMsg(System.out); } public synchronized static void printDebugMsg(PrintStream out) { StringBuffer msg = new StringBuffer(); msg.append("debug message in " + SimpleConnetionPool.class.getName()); msg.append("/r/n"); msg.append("total count is connection pool: " + getConnectionCount()); msg.append("/r/n"); msg.append("not used connection count: " + getNotUsedConnectionCount()); msg.append("/r/n"); msg.append("used connection, count: " + getUsedConnectionCount()); out.println("/nStatictics information/n--------------------------------------"); out.println(msg); } public static synchronized int getNotUsedConnectionCount() { return notUsedConnections.size(); } public static synchronized int getUsedConnectionCount() { return usedConnections.size(); } public static synchronized int getConnectionCount() { return notUsedConnections.size() + usedConnections.size(); } public static String getUrl() { return url; } public static void setUrl(String _url) { if (_url == null) { return; } url = _url.trim(); } public static String getUser() { return user; } public static void setUser(String _user) { if (_user == null) { return; } user = _user.trim(); } public static String getPassword() { return password; } public static void setPassword(String _password) { if (_password == null) { return; } password = _password.trim(); } } //连接包装类，通过jdk动态代理，替换close方法。 class ConnectionWrapper implements InvocationHandler { private final static String CLOSE_METHOD_NAME = "close"; public Connection connection = null; private Connection originConnection = null; public long lastAccessTime = System.currentTimeMillis(); ConnectionWrapper(Connection con) { Class[] interfaces = { java.sql.Connection.class }; this.connection = (Connection) Proxy.newProxyInstance( con.getClass().getClassLoader(), interfaces, this ); originConnection = con; } void close() throws SQLException { originConnection.close(); } public Object invoke(Object proxy, Method m, Object[] args) throws Throwable { Object obj = null; if (CLOSE_METHOD_NAME.equals(m.getName())) { SimpleConnetionPool.pushConnectionBackToPool(this); } else { obj = m.invoke(originConnection, args); } lastAccessTime = System.currentTimeMillis(); return obj; } }

当然，一个真正的连接池，需要考虑的问题远远不止这么多， 包括statement池，考虑result，事物，并发，分配策略等等。心血来潮， 把dbcp源码down下来看了看，很羞愧，没怎么明白。

 

转载请注明来自：http://blog.csdn.net/sunxing007