数据库连接池java实现小结

因为工作需要要使用到连接池，所以拜读了互联网上众多前辈的文章，学了不少经验，这里想做一个小结，加上自己的想法和在一起，希望能给大家一些帮助。

目的：

消除数据库频繁连接带来的开销和瓶颈。
解决方案：
不过多的限制用户的使用，既不能太多的要求用户按规定的方法得到和使用数据库连
尽量保持用户的习惯
目前的很多方法都是要求用户只能按规定方法使用连接，不能使用直接关闭数据连接的方法。解决办法就是使用代理类，来中间解决。可以参考http://www-900.ibm.com/developerWorks/cn/java/l-connpoolproxy/index.shtml

能维护连接的正常状态

因为针对数据库连接创建的资源，如果不能及时的释放，就会影响下一次数据连接的使用。例如在sql 2k中，一个连接不同创建多条Statement否则操作时会有数据连接占线的异常，所以必须在归还连接以后释放这些资源。

//判断是使用了createStatement语句
if (CREATESTATE.equals(method.getName()))
{
obj = method.invoke(conn, args);
statRef = (Statement)obj;//记录语句
return obj;
}

//判断是否调用了close的方法，如果调用close方法则把连接置为无用状态
if(CLOSE.equals(method.getName()))
{
//设置不使用标志
setIsFree(false);
//检查是否有后续工作，清除该连接无用资源
if (statRef != null)
statRef.close();
if (prestatRef != null)
prestatRef.close();
return null;
}

正确保护类不被违例使用

一个考虑就是不能让用户随便使用代理类，而只能自己使用，一个就是用内部私有类，一个就是使用只有指定类才能调用的标志。我的实现就是采用后者。

/**
* 创建连接的工厂，只能让工厂调用
* @param factory 要调用工厂，并且一定被正确初始化
* @param param 连接参数
* @return 连接
*/ static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param)
{
if (factory.isCreate())//判断是否正确初始化的工厂
{_Connection _conn = new _Connection(param);
return _conn;}
else
return null;
}

提供良好的用户接口，简单实用

使用静态方法创建工厂，然后来得到连接，使用完全和普通的Connection方法一样，没有限制。同时为了方便，设置了连接参数类和工厂参数类。

ConnectionParam param = new ConnectionParam(driver,url,user,password);
ConnectionFactory cf = null;//new ConnectionFactory(param, new FactoryParam());
try
{
cf = new ConnectionFactory(param,new FactoryParam());
Connection conn1 = cf.getFreeConnection();
Connection conn2 = cf.getFreeConnection();
Connection conn3 = cf.getFreeConnection();
Statement stmt = conn1.createStatement();
ResultSet rs = stmt.executeQuery("select * from requests");
if (rs.next())
{
System.out.println("conn1 y");
}
else
{
System.out.println("conn1 n");
}
stmt.close();
conn1.close();

为了实现连接池的正常运作，使用了单态模

/*** 使用指定的参数创建一个连接池*/
public ConnectionFactory(ConnectionParam param, FactoryParam fparam)throws SQLException
{
//不允许参数为空
if ((param == null)||(fparam == null))throw new SQLException("ConnectionParam和FactoryParam不能为空");
if (m_instance == null)
{
synchronized(ConnectionFactory.class)
{
if (m_instance == null)
{
//new instance
//参数定制
m_instance = new ConnectionFactory();
m_instance.connparam = param;
m_instance.MaxConnectionCount = fparam.getMaxConn();
m_instance.MinConnectionCount = fparam.getMinConn();
m_instance.ManageType = fparam.getType();
m_instance.isflag = true;
//初始化，创建MinConnectionCount个连接
System.out.println("connection factory 创建！");
try
{
for (int i=0; i < m_instance.MinConnectionCount; i++)
{
_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);
if (_conn == null) continue;
System.out.println("connection创建");
m_instance.FreeConnectionPool.add(_conn);//加入空闲连接池
m_instance.current_conn_count ++;//标志是否支持事务
m_instance.supportTransaction = _conn.isSupportTransaction();
}
}catch(Exception e){
e.printStackTrace();
}//根据策略判断是否需要查询
if (m_instance.ManageType != 0)
{
Thread t = new Thread(new FactoryMangeThread(m_instance));
t.start();}}}} }

连接池的管理

对于连接池的管理，我是设想使用静态管理和动态管理两种策略，设置了最大限制，和恒定的连接数。使用了2个池，一个空闲池，一个使用池。静态就是使用的时候发现空闲连接不够再去检查。动态就是使用了一个线程定时检查。

//根据策略判断是否需要查询
if (m_instance.ManageType != 0)
{
Thread t = new Thread(new FactoryMangeThread(m_instance));
t.start();
}
//连接池调度线程
public class FactoryMangeThread implements Runnable
{
ConnectionFactory cf = null;
long delay = 1000;
public FactoryMangeThread(ConnectionFactory obj)
{
cf = obj;
}
/* (non-Javadoc)
* @see java.lang.Runnable#run()
*/
public void run()
{
while(true)
{
try
{
Thread.sleep(delay);
}catch(InterruptedException e){}
System.out.println("eeeee");//判断是否已经关闭了工厂，那就退出监听
if (cf.isCreate())
cf.schedule();
else
System.exit(1);}}}

最后给出完整的源代码：
--------------------------------------------------------------------------------
_Connectio.java

package scut.ailab.connectionpool;
import java.lang.reflect.*;
import java.sql.*;
/**
* @author youyongming
* 定义数据库连接的代理类
*/
public class _Connection implements InvocationHandler
{
//定义连接 private Connection conn = null;
//定义监控连接创建的语句
private Statement statRef = null;
private PreparedStatement prestatRef = null;
//是否支持事务标志
private boolean supportTransaction = false;
//数据库的忙状态
private boolean isFree = false;
//最后一次访问时间
long lastAccessTime = 0;
//定义要接管的函数的名字
String CREATESTATE = "createStatement";
String CLOSE = "close"; String PREPARESTATEMENT = "prepareStatement";
String COMMIT = "commit";
String ROLLBACK = "rollback";
/**
* 构造函数，采用私有，防止被直接创建
* @param param 连接参数
*/
private _Connection(ConnectionParam param)
{
//记录日至
try{
//创建连接
Class.forName(param.getDriver()).newInstance();
conn = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword());
DatabaseMetaData dm = null;
dm = conn.getMetaData();
//判断是否支持事务
supportTransaction = dm.supportsTransactions();
}catch(Exception e){e.printStackTrace();} }
/**
* (non-Javadoc)
* @see java.lang.reflect.InvocationHandler#invoke(java.lang.Object, java.lang.reflect.Method, java.lang.Object[])
*/
public Object invoke(Object proxy, Method method, Object[] args)throws Throwable
{
Object obj = null;
//判断是否调用了close的方法，如果调用close方法则把连接置为无用状态
if(CLOSE.equals(method.getName()))
{
//设置不使用标志
setIsFree(false);
//检查是否有后续工作，清除该连接无用资源
if (statRef != null)
statRef.close();
if (prestatRef != null)
prestatRef.close();return null;}
//判断是使用了createStatement语句
if (CREATESTATE.equals(method.getName()))
{
obj = method.invoke(conn, args);
statRef = (Statement)obj;
//记录语句
return obj;}
//判断是使用了prepareStatement语句
if (PREPARESTATEMENT.equals(method.getName()))
{obj = method.invoke(conn, args);prestatRef = (PreparedStatement)obj;return obj;}//如果不支持事务，就不执行该事物的代码
if ((COMMIT.equals(method.getName())||ROLLBACK.equals(method.getName())) && (!isSupportTransaction()))
return null;
obj = method.invoke(conn, args);
//设置最后一次访问时间，以便及时清除超时的连接
lastAccessTime = System.currentTimeMillis();
return obj; }
/**
* 创建连接的工厂，只能让工厂调用
* @param factory 要调用工厂，并且一定被正确初始化
* @param param 连接参数
* @return 连接
*/
static public _Connection getConnection(ConnectionFactory factory, ConnectionParam param)
{
if (factory.isCreate())
//判断是否正确初始化的工厂
{_Connection _conn = new _Connection(param);
return _conn;
}
else
return null;
}
public Connection getFreeConnection()
{
//返回数据库连接conn的接管类，以便截住close方法
Connection conn2 = (Connection)Proxy.newProxyInstance(conn.getClass().getClassLoader(), conn.getClass().getInterfaces(),this);
return conn2;
}
/**
* 该方法真正的关闭了数据库的连接
* @throws SQLException
*/
void close() throws SQLException
{
//由于类属性conn是没有被接管的连接，因此一旦调用close方法后就直接关闭连接
conn.close();
}
public void setIsFree(boolean value)
{isFree = value; }
public boolean isFree() {return isFree; }
/**
* 判断是否支持事务
* @return boolean
*/
public boolean isSupportTransaction()
{return supportTransaction; }
}
--------------------------------------------------------------------------------
ConnectionFactory.java

package scut.ailab.connectionpool;
/**
* @author youyongming
*/
import java.util.LinkedHashSet;import java.sql.*;
import java.util.Iterator;
public class ConnectionFactory
{ private static ConnectionFactory m_instance = null;
//在使用的连接池
private LinkedHashSet ConnectionPool = null;
//空闲连接池
private LinkedHashSet FreeConnectionPool = null;
//最大连接数
private int MaxConnectionCount = 4;
//最小连接数
private int MinConnectionCount = 2;
//当前连接数 private int current_conn_count = 0;
//连接参数
private ConnectionParam connparam = null;
//是否创建工厂的标志
private boolean isflag = false;
//是否支持事务
private boolean supportTransaction = false;
//定义管理策略
private int ManageType = 0;
private ConnectionFactory()
{
ConnectionPool = new LinkedHashSet();
FreeConnectionPool = new LinkedHashSet(); }
/**
* 使用指定的参数创建一个连接池
*/
public ConnectionFactory(ConnectionParam param, FactoryParam fparam)throws SQLException
{
//不允许参数为空
if ((param == null)||(fparam == null))throw new SQLException("ConnectionParam和FactoryParam不能为空");
if (m_instance == null)
{
synchronized(ConnectionFactory.class)
{
if (m_instance == null)
{
//new instance
//参数定制
m_instance = new ConnectionFactory();
m_instance.connparam = param;
m_instance.MaxConnectionCount = fparam.getMaxConn();
m_instance.MinConnectionCount = fparam.getMinConn();
m_instance.ManageType = fparam.getType();
m_instance.isflag = true;
//初始化，创建MinConnectionCount个连接
System.out.println("connection factory 创建！");
try
{
for (int i=0; i < m_instance.MinConnectionCount; i++)
{
_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);
if (_conn == null) continue;
System.out.println("connection创建");
m_instance.FreeConnectionPool.add(_conn);
//加入空闲连接池
m_instance.current_conn_count ++;
//标志是否支持事务
m_instance.supportTransaction = _conn.isSupportTransaction();
}
}catch(Exception e){e.printStackTrace();}
//根据策略判断是否需要查询
if (m_instance.ManageType != 0)
{
Thread t = new Thread(new FactoryMangeThread(m_instance));
t.start();}}}} }
/**
* 标志工厂是否已经创建
* @return boolean
*/
public boolean isCreate()
{return m_instance.isflag; }
/**
* 从连接池中取一个空闲的连接
* @return Connection
* @throws SQLException
*/ public synchronized Connection getFreeConnection()throws SQLException
{
Connection conn = null;
//获取空闲连接
Iterator iter = m_instance.FreeConnectionPool.iterator();
while(iter.hasNext())
{
_Connection _conn = (_Connection)iter.next();
//找到未用连接
if(!_conn.isFree())
{
conn = _conn.getFreeConnection();
_conn.setIsFree(true);
//移出空闲区
m_instance.FreeConnectionPool.remove(_conn);
//加入连接池
m_instance.ConnectionPool.add(_conn);
break;}}
//检查空闲池是否为空
if (m_instance.FreeConnectionPool.isEmpty())
{
//再检查是否能够分配
if (m_instance.current_conn_count < m_instance.MaxConnectionCount)
{
//新建连接到空闲连接池
int newcount = 0 ;
//取得要建立的数目
if (m_instance.MaxConnectionCount - m_instance.current_conn_count >=m_instance.MinConnectionCount)
{
newcount = m_instance.MinConnectionCount;
}
else
{
newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count;}
//创建连接
for (int i=0;i <newcount i>{<br>_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);m_instance.FreeConnectionPool.add(_conn);<br>m_instance.current_conn_count ++;}}<br>else{<br>//如果不能新建，检查是否有已经归还的连接<br>iter = m_instance.ConnectionPool.iterator();<br>while(iter.hasNext())<br>{_Connection _conn = (_Connection)iter.next();<br>if(!_conn.isFree())<br>{<br>conn = _conn.getFreeConnection();<br>_conn.setIsFree(false);<br>m_instance.ConnectionPool.remove(_conn);<br>m_instance.FreeConnectionPool.add(_conn);<br>break;}}}}<br>if (FreeConnectionPool.isEmpty()) <br>//再次检查是否能分配连接 <br>if(conn == null)<br>{iter = m_instance.FreeConnectionPool.iterator();<br>while(iter.hasNext())<br>{_Connection _conn = (_Connection)iter.next();<br>if(!_conn.isFree())<br>{<br>conn = _conn.getFreeConnection();<br>_conn.setIsFree(true);<br>m_instance.FreeConnectionPool.remove(_conn);<br>m_instance.ConnectionPool.add(_conn);break;}}<br>if(conn == null)<br>//如果不能则说明无连接可用<br>throw new SQLException("没有可用的数据库连接");}<br>System.out.println("get connection");return conn; }<br>/**<br>* 关闭该连接池中的所有数据库连接<br>* @throws SQLException<br>*/<br>public synchronized void close() throws SQLException <br>{this.isflag = false;SQLException excp = null;<br>//关闭空闲池<br>Iterator iter = m_instance.FreeConnectionPool.iterator();<br>while(iter.hasNext())<br>{<br>try{((_Connection)iter.next()).close();System.out.println("close connection:free");<br>m_instance.current_conn_count --;<br>}catch(Exception e)<br>{if(e instanceof SQLException)<br>excp = (SQLException)e;}}<br>//关闭在使用的连接池<br>iter = m_instance.ConnectionPool.iterator();<br>while(iter.hasNext())<br>{<br>try{<br>((_Connection)iter.next()).close();<br>System.out.println("close connection:inused");<br>m_instance.current_conn_count --;<br>}catch(Exception e)<br>{if(e instanceof SQLException)<br>excp = (SQLException)e;}}<br>if(excp != null)throw excp; }<br>/** <br>* 返回是否支持事务 <br>* @return boolean<br>*/ <br>public boolean isSupportTransaction() <br>{ <br>return m_instance.supportTransaction; }<br>/**<br>* 连接池调度管理<br>*<br>*/ public void schedule() <br>{Connection conn = null;<br>//再检查是否能够分配<br>Iterator iter = null; <br>//检查是否有已经归还的连接<br>{<br>iter = m_instance.ConnectionPool.iterator();<br>while(iter.hasNext())<br>{_Connection _conn = (_Connection)iter.next();<br>if(!_conn.isFree())<br>{conn = _conn.getFreeConnection();<br>_conn.setIsFree(false);<br>m_instance.ConnectionPool.remove(_conn);<br>m_instance.FreeConnectionPool.add(_conn);<br>break;}}} <br>if (m_instance.current_conn_count &lt; m_instance.MaxConnectionCount)<br>{<br>//新建连接到空闲连接池<br>int newcount = 0 ;<br>//取得要建立的数目<br>if (m_instance.MaxConnectionCount - m_instance.current_conn_count &gt;=m_instance.MinConnectionCount)<br>{<br>newcount = m_instance.MinConnectionCount;<br>}else{<br>newcount = m_instance.MaxConnectionCount - m_instance.current_conn_count;}<br>//创建连接<br>for (int i=0;i <newcount i>{<br>_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);m_instance.FreeConnectionPool.add(_conn);<br>m_instance.current_conn_count ++;}} }}<br>--------------------------------------------------------------------------------<br><b>ConnectionParam.java</b> <br><br>package scut.ailab.connectionpool;<br>import java.io.Serializable;<br>/** <br>* @author youyongming <br>* 实现数据库连接的参数类 <br>*/<br>public class ConnectionParam implements Serializable<br>{ private String driver;//数据库驱动程序 <br>private String url;//数据连接的URL <br>private String user;//数据库用户名 <br>private String password;//数据库密码<br>/**<br>* 唯一的构造函数，需要指定连接的四个必要参数<br>* @param driver 数据驱动<br>* @param url数据库连接url<br>* @param user用户名<br>* @param password 密码<br>*/ <br>public ConnectionParam(String driver,String url,String user,String password) <br>{this.driver = driver;this.url = url;this.user = user;this.password = password; }<br>public String getDriver() {return driver; }<br>public String getPassword() {return password; }<br>public String getUrl() {return url; }<br>public String getUser() {return user; }<br>public void setDriver(String driver) {this.driver = driver; }<br>public void setPassword(String password) {this.password = password; }<br>public void setUrl(String url) {this.url = url; }<br>public void setUser(String user) {this.user = user; }<br>/**<br>* @see java.lang.Object#clone()<br>*/ <br>public Object clone()<br>{<br>ConnectionParam param = new ConnectionParam(driver,url,user,password);return param; <br>}<br>/**<br>* @see java.lang.Object#equals(java.lang.Object)<br>*/ <br>public boolean equals(Object obj) <br>{ <br>if(obj instanceof ConnectionParam)<br>{<br>ConnectionParam param = (ConnectionParam)obj;<br>return ((driver.compareToIgnoreCase(param.getDriver()) == 0)&amp;&amp;(url.compareToIgnoreCase(param.getUrl()) == 0)&amp;&amp;(user.compareToIgnoreCase(param.getUser()) == 0)&amp;&amp;(password.compareToIgnoreCase(param.getPassword()) == 0));} <br>return false;<br>}<br>}<br>--------------------------------------------------------------------------------<br><b>FactoryMangeThread.java </b><br><br>/* * Created on 2003-5-13 * <br>* To change the template for this generated file go to<br>* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments <br>*/<br>package scut.ailab.connectionpool;<br>/** <br>* @author youyongming * <br>*/<br>//连接池调度线程<br>public class FactoryMangeThread implements Runnable <br>{<br>ConnectionFactory cf = null; <br>long delay = 1000;<br>public FactoryMangeThread(ConnectionFactory obj)<br>{cf = obj; } <br>/* (non-Javadoc)<br>* @see java.lang.Runnable#run()<br>*/ <br>public void run() <br>{while(true)<br>{<br>try<br>{<br>Thread.sleep(delay);<br>}catch(InterruptedException e){}<br>System.out.println("eeeee");<br>//判断是否已经关闭了工厂，那就退出监听<br>if (cf.isCreate())<br>cf.schedule();<br>else<br>System.exit(1);} }}<br>--------------------------------------------------------------------------------<br><b>FactoryParam.java</b> <br><br>/* * Created on 2003-5-13 *<br>* To change the template for this generated file go to <br>* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments <br>*/<br>package scut.ailab.connectionpool;<br>/** <br>* @author youyongming * <br>*///连接池工厂参数<br>public class FactoryParam <br>{ <br>//最大连接数 <br>private int MaxConnectionCount = 4; <br>//最小连接数 <br>private int MinConnectionCount = 2; <br>//回收策略 <br>private int ManageType = 0;<br>public FactoryParam() { }<br>/**<br>* 构造连接池工厂参数的对象<br>* @param max 最大连接数<br>* @param min 最小连接数<br>* @param type 管理策略<br>*/ <br>public FactoryParam(int max, int min, int type) <br>{<br>this.ManageType = type;<br>this.MaxConnectionCount = max;<br>this.MinConnectionCount = min; } <br>/*** 设置最大的连接数<br>* @param value<br>*/ <br>public void setMaxConn(int value) <br>{<br>this.MaxConnectionCount = value; }<br>/**<br>* 获取最大连接数<br>* @return<br>*/ <br>public int getMaxConn() <br>{return this.MaxConnectionCount; } <br>/**<br>* 设置最小连接数<br>* @param value<br>*/<br>public void setMinConn(int value) <br>{this.MinConnectionCount = value; } <br>/**<br>* 获取最小连接数<br>* @return<br>*/ <br>public int getMinConn() <br>{<br>return this.MinConnectionCount; <br>} <br>public int getType() <br>{return this.ManageType; <br>}}<br>------------------------------------------------------------------------------<br><b>testmypool.java</b> <br><br>/* <br>*Created on 2003-5-13 <br>* To change the template for this generated file go to * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments */<br>package scut.ailab.connectionpool;<br>/** <br>* @author youyongming * <br>*/<br>import java.sql.*;<br>public class testmypool <br>{<br>public void test1() <br>{<br>String user = "DevTeam";<br>String password = "DevTeam";<br>String driver = "sun.jdbc.odbc.JdbcOdbcDriver";<br>String url = "jdbc:odbc:gfqh2";<br>ConnectionParam param = new ConnectionParam(driver,url,user,password);<br>ConnectionFactory cf = null;<br>new ConnectionFactory(param, new FactoryParam());<br>try{cf = new ConnectionFactory(param,new FactoryParam());<br>Connection conn1 = cf.getFreeConnection();<br>Connection conn2 = cf.getFreeConnection();<br>Connection conn3 = cf.getFreeConnection();<br>Statement stmt = conn1.createStatement();<br>ResultSet rs = stmt.executeQuery("select * from requests");<br>if (rs.next())<br>{System.out.println("conn1 y");<br>}else<br>{<br>System.out.println("conn1 n");<br>}<br>stmt.close();<br>conn1.close();<br>Connection conn4 = cf.getFreeConnection(); <br>Connection conn5 = cf.getFreeConnection(); <br>stmt = conn5.createStatement(); <br>rs = stmt.executeQuery("select * from requests");<br>if (rs.next()){<br>System.out.println("conn5 y");<br>}else{ <br>System.out.println("conn5 n");<br>}<br>conn2.close();<br>conn3.close();<br>conn4.close();<br>conn5.close();<br>}<br>catch(Exception e)<br>{<br>e.printStackTrace();}<br>finally<br>{<br>try<br>{<br>cf.close();<br>}catch(Exception e){<br>e.printStackTrace();<br>}}} <br>public static void main(String[] args) <br>{String user = "DevTeam";<br>String password = "DevTeam";<br>String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; <br>String url = "jdbc:odbc:gfqh2";<br>ConnectionParam param = new ConnectionParam(driver,url,user,password);<br>ConnectionFactory cf = null;<br>new ConnectionFactory(param,new FactoryParam());<br>try{<br>cf = new ConnectionFactory(param,new FactoryParam());<br>ConnectionFactory cf1= new ConnectionFactory(param,new FactoryParam());<br>Connection conn1 = null;<br>long time = System.currentTimeMillis();<br>for (int i=0; i &lt;10;i++)<br>{conn1 = cf.getFreeConnection();<br>Statement stmt = conn1.createStatement();<br>ResultSet rs = stmt.executeQuery("select * from requests");<br>if (rs.next())<br>{System.out.println("conn1 y");}<br>else<br>{System.out.println("conn1 n");}<br>conn1.close();}<br>System.out.println("pool:" + (System.currentTimeMillis()-time));<br>time = System.currentTimeMillis();<br>Class.forName(param.getDriver()).newInstance();<br>for (int i=0; i &lt;10;i++)<br>{<br>conn1 = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword());<br>Statement stmt = conn1.createStatement();<br>ResultSet rs = stmt.executeQuery("select * from requests");<br>if (rs.next())<br>{<br>System.out.println("conn1 y");<br>}else{<br>System.out.println("conn1 n");<br>}conn1.close();<br>}<br>System.out.println("no pool:" + (System.currentTimeMillis()-time)); <br>}catch(Exception e)<br>{e.printStackTrace();}<br>finally{<br>try{<br>cf.close(); <br>}<br>catch(Exception e) <br>{<br>e.printStackTrace(); <br>} <br>}<br>}<br>}</newcount></newcount>