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