数据库连接池Java实现小结

503room





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



　　目的：



消除数据库频繁连接带来的开销和瓶颈。

　　解决方案：



不过多的限制用户的使用，既不能太多的要求用户按规定的方法得到和使用数据库连

尽量保持用户的习惯

　　目前的很多方法都是要求用户只能按规定方法使用连接，不能使用直接关闭数据连接的方法。解决办法就是使用代理类，来中间解决。可以参考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();

}

}

}

}