关键字:Java,JDBC,ConnectionPool,Database,数据库连接池,sourcecode
虽然J2EE程序员一般都有现成的应用服务器所带的JDBC数据库连接池，不过对于开发一般的JavaApplication、Applet或者JSP、velocity时，我们可用的JDBC数据库连接池并不多，并且一般性能都不好。Java程序员都很羡慕WindowsADO，只需要newConnection就可以直接从数据库连接池中返回Connection。并且ADOConnection是线程安全的，多个线程可以共用一个Connection，所以ASP程序一般都把getConnection放在Global.asa文件中，在IIS启动时建立数据库连接。ADO的Connection和Result都有很好的缓冲，并且很容易使用。
其实我们可以自己写一个JDBC数据库连接池。写JDBCconnectionpool的注意事项有：
1.有一个简单的函数从连接池中得到一个Connection。
2.close函数必须将connection放回数据库连接池。
3.当数据库连接池中没有空闲的connection，数据库连接池必须能够自动增加connection个数。
4.当数据库连接池中的connection个数在某一个特别的时间变得很大，但是以后很长时间只用其中一小部分，应该可以自动将多余的connection关闭掉。
5.如果可能，应该提供debug信息报告没有关闭的newConnection。
如果要newConnection就可以直接从数据库连接池中返回Connection，可以这样写(Mediatorpattern)(以下代码中使用了中文全角空格)：
publicclassEasyConnectionimplementsjava.sql.Connection{
privateConnectionm_delegate=null;
publicEasyConnection(){
m_delegate=getConnectionFromPool();
}
publicvoidclose(){
putConnectionBackToPool(m_delegate);
}
publicPreparedStatementprepareStatement(Stringsql)throwsSQLException{
m_delegate.prepareStatement(sql);
}
//......othermethod
}
看来并不难。不过不建议这种写法，因为应该尽量避免使用JavaInterface,关于JavaInterface的缺点我另外再写文章讨论。大家关注的是ConnectionPool的实现方法。下面给出一种实现方法。
importjava.sql.*;
importjava.lang.reflect.*;
importjava.util.*;
importjava.io.*;
publicclassSimpleConnetionPool{
privatestaticLinkedListm_notUsedConnection=newLinkedList();
privatestaticHashSetm_usedUsedConnection=newHashSet();
privatestaticStringm_url="";
privatestaticStringm_user="";
privatestaticStringm_password="";
staticfinalbooleanDEBUG=true;
staticprivatelongm_lastClearClosedConnection=System.currentTimeMillis();
publicstaticlongCHECK_CLOSED_CONNECTION_TIME=4*60*60*1000;//4hours
static{
initDriver();
}
privateSimpleConnetionPool(){
}
privatestaticvoidinitDriver(){
Driverdriver=null;
//loadmysqldriver
try{
driver=(Driver)Class.forName("com.mysql.jdbc.Driver").newInstance();
installDriver(driver);
}catch(Exceptione){
}
//loadpostgresqldriver
try{
driver=(Driver)Class.forName("org.postgresql.Driver").newInstance();
installDriver(driver);
}catch(Exceptione){
}
}
publicstaticvoidinstallDriver(Driverdriver){
try{
DriverManager.registerDriver(driver);
}catch(Exceptione){
e.printStackTrace();
}
}
publicstaticsynchronizedConnectiongetConnection(){
clearClosedConnection();
while(m_notUsedConnection.size()>0){
try{
ConnectionWrapperwrapper=(ConnectionWrapper)m_notUsedConnection.removeFirst();
if(wrapper.connection.isClosed()){
continue;
}
m_usedUsedConnection.add(wrapper);
if(DEBUG){
wrapper.debugInfo=newThrowable("Connectioninitialstatement");
}
returnwrapper.connection;
}catch(Exceptione){
}
}
intnewCount=getIncreasingConnectionCount();
LinkedListlist=newLinkedList();
ConnectionWrapperwrapper=null;
for(inti=0;i wrapper=getNewConnection();
if(wrapper!=null){
list.add(wrapper);
}
}
if(list.size()==0){
returnnull;
}
wrapper=(ConnectionWrapper)list.removeFirst();
m_usedUsedConnection.add(wrapper);
m_notUsedConnection.addAll(list);
list.clear();
returnwrapper.connection;
}
privatestaticConnectionWrappergetNewConnection(){
try{
Connectioncon=DriverManager.getConnection(m_url,m_user,m_password);
ConnectionWrapperwrapper=newConnectionWrapper(con);
returnwrapper;
}catch(Exceptione){
e.printStackTrace();
}
returnnull;
}
staticsynchronizedvoidpushConnectionBackToPool(ConnectionWrappercon){
booleanexist=m_usedUsedConnection.remove(con);
if(exist){
m_notUsedConnection.addLast(con);
}
}
publicstaticintclose(){
intcount=0;
Iteratoriterator=m_notUsedConnection.iterator();
while(iterator.hasNext()){
try{
((ConnectionWrapper)iterator.next()).close();
count++;
}catch(Exceptione){
}
}
m_notUsedConnection.clear();
iterator=m_usedUsedConnection.iterator();
while(iterator.hasNext()){
try{
ConnectionWrapperwrapper=(ConnectionWrapper)iterator.next();
wrapper.close();
if(DEBUG){
wrapper.debugInfo.printStackTrace();
}
count++;
}catch(Exceptione){
}
}
m_usedUsedConnection.clear();
returncount;
}
privatestaticvoidclearClosedConnection(){
longtime=System.currentTimeMillis();
//sometimesuserchangesystemtime,justreturn
if(time time=m_lastClearClosedConnection;
return;
}
//noneedcheckveryoften
if(time-m_lastClearClosedConnection return;
}
m_lastClearClosedConnection=time;
//begincheck
Iteratoriterator=m_notUsedConnection.iterator();
while(iterator.hasNext()){
ConnectionWrapperwrapper=(ConnectionWrapper)iterator.next();
try{
if(wrapper.connection.isClosed()){
iterator.remove();
}
}catch(Exceptione){
iterator.remove();
if(DEBUG){
System.out.println("connectionisclosed,thisconnectioninitialStackTrace");
wrapper.debugInfo.printStackTrace();
}
}
}
//makeconnectionpoolsizesmalleriftoobig
intdecrease=getDecreasingConnectionCount();
if(m_notUsedConnection.size() return;
}
while(decrease-->0){
ConnectionWrapperwrapper=(ConnectionWrapper)m_notUsedConnection.removeFirst();
try{
wrapper.connection.close();
}catch(Exceptione){
}
}
}
/**
*getincreasingconnectioncount,notjustadd1connection
*@returncount
*/
publicstaticintgetIncreasingConnectionCount(){
intcount=1;
intcurrent=getConnectionCount();
count=current/4;
if(count<1){
count=1;
}
returncount;
}
/**
*getdecreasingconnectioncount,notjustremove1connection
*@returncount
*/
publicstaticintgetDecreasingConnectionCount(){
intcount=0;
intcurrent=getConnectionCount();
if(current<10){
return0;
}
returncurrent/3;
}
publicsynchronizedstaticvoidprintDebugMsg(){
printDebugMsg(System.out);
}
publicsynchronizedstaticvoidprintDebugMsg(PrintStreamout){
if(DEBUG==false){
return;
}
StringBuffermsg=newStringBuffer();
msg.append("debugmessagein"+SimpleConnetionPool.class.getName());
msg.append("\r\n");
msg.append("totalcountisconnectionpool:"+getConnectionCount());
msg.append("\r\n");
msg.append("notusedconnectioncount:"+getNotUsedConnectionCount());
msg.append("\r\n");
msg.append("usedconnection,count:"+getUsedConnectionCount());
out.println(msg);
Iteratoriterator=m_usedUsedConnection.iterator();
while(iterator.hasNext()){
ConnectionWrapperwrapper=(ConnectionWrapper)iterator.next();
wrapper.debugInfo.printStackTrace(out);
}
out.println();
}
publicstaticsynchronizedintgetNotUsedConnectionCount(){
returnm_notUsedConnection.size();
}
publicstaticsynchronizedintgetUsedConnectionCount(){
returnm_usedUsedConnection.size();
}
publicstaticsynchronizedintgetConnectionCount(){
returnm_notUsedConnection.size()+m_usedUsedConnection.size();
}
publicstaticStringgetUrl(){
returnm_url;
}
publicstaticvoidsetUrl(Stringurl){
if(url==null){
return;
}
m_url=url.trim();
}
publicstaticStringgetUser(){
returnm_user;
}
publicstaticvoidsetUser(Stringuser){
if(user==null){
return;
}
m_user=user.trim();
}
publicstaticStringgetPassword(){
returnm_password;
}
publicstaticvoidsetPassword(Stringpassword){
if(password==null){
return;
}
m_password=password.trim();
}
}
classConnectionWrapperimplementsInvocationHandler{
privatefinalstaticStringCLOSE_METHOD_NAME="close";
publicConnectionconnection=null;
privateConnectionm_originConnection=null;
publiclonglastAccessTime=System.currentTimeMillis();
ThrowabledebugInfo=newThrowable("Connectioninitialstatement");
ConnectionWrapper(Connectioncon){
Class[]interfaces={java.sql.Connection.class};
this.connection=(Connection)Proxy.newProxyInstance(
con.getClass().getClassLoader(),
interfaces,this);
m_originConnection=con;
}
voidclose()throwsSQLException{
m_originConnection.close();
}
publicObjectinvoke(Objectproxy,Methodm,Object[]args)throwsThrowable{
Objectobj=null;
if(CLOSE_METHOD_NAME.equals(m.getName())){
SimpleConnetionPool.pushConnectionBackToPool(this);
}
else{
obj=m.invoke(m_originConnection,args);
}
lastAccessTime=System.currentTimeMillis();
returnobj;
}
}
使用方法
publicclassTestConnectionPool{
publicstaticvoidmain(String[]args){
SimpleConnetionPool.setUrl(DBTools.getDatabaseUrl());
SimpleConnetionPool.setUser(DBTools.getDatabaseUserName());
SimpleConnetionPool.setPassword(DBTools.getDatabasePassword());
Connectioncon=SimpleConnetionPool.getConnection();
Connectioncon1=SimpleConnetionPool.getConnection();
Connectioncon2=SimpleConnetionPool.getConnection();
//dosomethingwithcon...
try{
con.close();
}catch(Exceptione){}
try{
con1.close();
}catch(Exceptione){}
try{
con2.close();
}catch(Exceptione){}
con=SimpleConnetionPool.getConnection();
con1=SimpleConnetionPool.getConnection();
try{
con1.close();
}catch(Exceptione){}
con2=SimpleConnetionPool.getConnection();
SimpleConnetionPool.printDebugMsg();
}
}
运行测试程序后打印连接池中Connection状态，以及正在使用的没有关闭Connection信息。
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=605741
