IDC资讯中心
因为工作需要要使用到连接池，所以拜读了互联网上众多前辈的文章，学了不少经验，这里想做一个小结，加上自己的想法和在一起，希望能给大家一些帮助。
目的：
消除数据库频繁连接带来的开销和瓶颈。
解决方案：
不过多的限制用户的使用，既不能太多的要求用户按规定的方法得到和使用数据库连
尽量保持用户的习惯
目前的很多方法都是要求用户只能按规定方法使用连接，不能使用直接关闭数据连接的方法。解决办法就是使用代理类，来中间解决。可以参考http://www-900.ibm.com/developerworks/cn/java/l-connpoolproxy/index.shtml
能维护连接的正常状态
要求用户按规定的方法得到和使用数据库连
尽量保持用户的习惯
目前的很多方法都是要求用户只能按规定方法使用连接，不能使用直接关闭数据连接的方法。解决办法就是使用代理类，来中间解决。可以参考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++)
{
_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 <newcount; 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();
}
}
}
}
