数据库连接池java实现小结

[转贴]TUXEDO精华贴总结

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

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

能维护连接的正常状态

因为针对数据库连接创建的资源，如果不能及时的释放，就会影响下一次数据连接的使用。例如在sql 2000中，一个连接不能创建多条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 {     
_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);     m_instance.FreeConnectionPool.add(_conn);    
m_instance.current_conn_count ++;    }   }  
else   {
//如果不能新建，检查是否有已经归还的连接    
iter = m_instance.ConnectionPool.iterator();    
while(iter.hasNext())
{     _Connection _conn = (_Connection)iter.next();     
if(!_conn.isFree())
{      
conn = _conn.getFreeConnection();      
_conn.setIsFree(false);     
m_instance.ConnectionPool.remove(_conn);       
m_instance.FreeConnectionPool.add(_conn);         
break;     }    }       }  }
if (FreeConnectionPool.isEmpty())
//再次检查是否能分配连接
if(conn == null)
{   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(conn == null)
//如果不能则说明无连接可用    
throw new SQLException("没有可用的数据库连接");  }  
System.out.println("get connection");  return conn; }  
/**  
* 关闭该连接池中的所有数据库连接  
* @throws SQLException  
*/
public synchronized void close() throws SQLException
{  this.isflag = false;  SQLException excp = null;  
//关闭空闲池  
Iterator iter = m_instance.FreeConnectionPool.iterator();  
while(iter.hasNext())
{   
try{    ((_Connection)iter.next()).close();    System.out.println("close connection:free");   
m_instance.current_conn_count --;   
}catch(Exception e)
{    if(e instanceof SQLException)     
excp = (SQLException)e;   }  }  
//关闭在使用的连接池  
iter = m_instance.ConnectionPool.iterator();  
while(iter.hasNext())
{   
try{    
((_Connection)iter.next()).close();    
System.out.println("close connection:inused");    
m_instance.current_conn_count --;   
}catch(Exception e)
{    if(e instanceof SQLException)     
excp = (SQLException)e;   }  }   
if(excp != null)   throw excp; }   
/**
* 返回是否支持事务
* @return boolean  
*/
public boolean isSupportTransaction()
{
return m_instance.supportTransaction; }   
/**  
* 连接池调度管理  
*  
*/ public void schedule()
{  Connection conn = null;  
//再检查是否能够分配  
Iterator iter = null;
//检查是否有已经归还的连接  
{  
iter = m_instance.ConnectionPool.iterator();  
while(iter.hasNext())
{    _Connection _conn = (_Connection)iter.next();    
if(!_conn.isFree())
{     conn = _conn.getFreeConnection();     
_conn.setIsFree(false);   
m_instance.ConnectionPool.remove(_conn);    
m_instance.FreeConnectionPool.add(_conn);        
break;    }   }      }
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 {    
_Connection _conn = _Connection.getConnection(m_instance, m_instance.connparam);    m_instance.FreeConnectionPool.add(_conn);    
m_instance.current_conn_count ++;   }  } }}
--------------------------------------------------------------------------------
ConnectionParam.java

package scut.ailab.connectionpool;
import java.io.Serializable;
/**
* @author youyongming
* 实现数据库连接的参数类
*/
public class ConnectionParam implements Serializable
{ private String driver;    //数据库驱动程序
private String url;   //数据连接的URL
private String user;    //数据库用户名
private String password;   //数据库密码  
/**  
* 唯一的构造函数，需要指定连接的四个必要参数  
* @param driver 数据驱动  
* @param url  数据库连接url  
* @param user  用户名  
* @param password 密码  
*/
public ConnectionParam(String driver,String url,String user,String password)
{  this.driver = driver;  this.url = url;  this.user = user;  this.password = password; }
public String getDriver() {  return driver; }
public String getPassword() {  return password; }
public String getUrl() {  return url; }
public String getUser() {  return user; }
public void setDriver(String driver) {  this.driver = driver; }
public void setPassword(String password) {  this.password = password; }
public void setUrl(String url) {  this.url = url; }
public void setUser(String user) {  this.user = user; }
/**  
* @see java.lang.Object#clone()  
*/
public Object clone()
{  
ConnectionParam param = new ConnectionParam(driver,url,user,password);  return param;
}
/**  
* @see java.lang.Object#equals(java.lang.Object)  
*/
public boolean equals(Object obj)
{
if(obj instanceof ConnectionParam)
{   
ConnectionParam param = (ConnectionParam)obj;   
return ((driver.compareToIgnoreCase(param.getDriver()) == 0)&&   (url.compareToIgnoreCase(param.getUrl()) == 0)&&   (user.compareToIgnoreCase(param.getUser()) == 0)&&   (password.compareToIgnoreCase(param.getPassword()) == 0));  }
return false;
}
}
--------------------------------------------------------------------------------
FactoryMangeThread.java

/* * Created on 2003-5-13 *
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package scut.ailab.connectionpool;
/**
* @author youyongming *
*/
//连接池调度线程
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);  } }}
--------------------------------------------------------------------------------
FactoryParam.java

/* * Created on 2003-5-13 *
* To change the template for this generated file go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
package scut.ailab.connectionpool;
/**
* @author youyongming *
*///连接池工厂参数
public class FactoryParam
{
//最大连接数
private int MaxConnectionCount = 4;
//最小连接数
private int MinConnectionCount = 2;
//回收策略
private int ManageType = 0;  
public FactoryParam() { }  
/**  
* 构造连接池工厂参数的对象  
* @param max 最大连接数  
* @param min 最小连接数  
* @param type 管理策略  
*/
public FactoryParam(int max, int min, int type)
{  
this.ManageType = type;  
this.MaxConnectionCount = max;  
this.MinConnectionCount = min; }
/**  * 设置最大的连接数  
* @param value  
*/
public void setMaxConn(int value)
{  
this.MaxConnectionCount = value; }
/**  
* 获取最大连接数  
* @return  
*/
public int getMaxConn()
{  return this.MaxConnectionCount; }
/**  
* 设置最小连接数  
* @param value  
*/
public void setMinConn(int value)
{  this.MinConnectionCount = value; }
/**  
* 获取最小连接数  
* @return  
*/
public int getMinConn()
{  
return this.MinConnectionCount;
}
public int getType()
{  return this.ManageType;
}}
------------------------------------------------------------------------------
testmypool.java

/*
*  Created on 2003-5-13
* To change the template for this generated file go to * Window>Preferences>Java>Code Generation>Code and Comments */
package scut.ailab.connectionpool;
/**
* @author youyongming *
*/
import java.sql.*;
public class testmypool
{
public void test1()
{  
String user = "DevTeam";  
String password = "DevTeam";  
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";  
String url = "jdbc:odbc:gfqh2";  
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();     
Connection conn4 = cf.getFreeConnection();
Connection conn5 = cf.getFreeConnection();
stmt = conn5.createStatement();
rs = stmt.executeQuery("select * from requests");  
if (rs.next())   {    
System.out.println("conn5 y");    
}   else   {
System.out.println("conn5 n");     
}    
conn2.close();   
conn3.close();   
conn4.close();   
conn5.close();  
}  
catch(Exception e)  
{   
e.printStackTrace();  }  
finally
{   
try
{    
cf.close();  
}   catch(Exception e)   {   
e.printStackTrace();   
}  }  }
public static void main(String[] args)
{  String user = "DevTeam";  
String password = "DevTeam";  
String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url = "jdbc:odbc:gfqh2";  
ConnectionParam param = new ConnectionParam(driver,url,user,password);  
ConnectionFactory cf = null;
new ConnectionFactory(param,new FactoryParam());  
try{   
cf = new ConnectionFactory(param,new FactoryParam());   
ConnectionFactory cf1= new ConnectionFactory(param,new FactoryParam());  
Connection conn1 = null;   
long time = System.currentTimeMillis();   
for (int i=0; i <10;i++)  
{    conn1 = 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");      }     
conn1.close();     }   
System.out.println("pool:" + (System.currentTimeMillis()-time));   
time = System.currentTimeMillis();   
Class.forName(param.getDriver()).newInstance();   
for (int i=0; i <10;i++)   
{    
conn1 = DriverManager.getConnection(param.getUrl(),param.getUser(), param.getPassword());       
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");      
}     conn1.close();   
}      
System.out.println("no pool:" + (System.currentTimeMillis()-time));
}  catch(Exception e)  
{   e.printStackTrace();  }  
finally{   
try{   
cf.close();
}   
catch(Exception e)
{   
e.printStackTrace();
}
}
}
}