副标题#e#
因为事情需要要利用到毗连池,所以拜读了互联网上浩瀚前辈的文章,学了不少履历,这里想做一个小结,加上本身的想法和在一起,但愿能给各人一些辅佐。
目标:消除数据库频繁毗连带来的开销和瓶颈。
办理方案:不外多的限制用户的利用,既不能太多的要求用户按划定的要领获得和利用数据库连只管保持用户的习惯
今朝的许多要领都是要求用户只能按划定要领利用毗连,不能利用直接封锁数据毗连的要领。办理步伐就是利用署理类,来中间办理。可以参考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;
}
#p#副标题#e#
提供精采的用户接口,简朴实用
利用静态要领建设工场,然厥后获得毗连,利用完全和普通的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();
}
}
}
}
}
毗连池的打点
#p#分页标题#e#
对付毗连池的打点,我是设想利用静态打点和动态打点两种计策,配置了最大限制,和恒定的毗连数。利用了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
#p#分页标题#e#
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 ++;
}
}
}
}
#p#分页标题#e#
ConnectionParam.javapackage 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
#p#分页标题#e#
/*
* 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
#p#分页标题#e#
/*
* 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();
}
}
}
}
