在服务器端涉及数据库访问的应用程序里头,数据库连接池是一项确保性能的关键技术。一谈起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个资源,这不尽合理。