java自定义数据库连接池.docx

1、java自定义数据库连接池java:自定义数据库连接池 连接池是非常好的想法，应用很普遍。自己写一个数据库连接池，并不像想象中那样困难。一般系统对连接池的功能不会有太多要求，使用自己的连接池未必是个坏主意。下面以Oracle为例，但是对Teradata和Greenplum也是可行的。另外我还实现了连接有效性检查（checkConn）和恢复连接(resetConn)的方法。本例编程采用的是JRE1.4.2环境（别忘了准备访问数据库的jar包）。有任何问题请随时留言，欢迎探讨。在Oracle内创建测试数据：drop table my_table; create table my_table( fi

2、eld_id varchar2(3), field_content varchar2(60), record_create_date date default sysdate ); insert into my_table(field_id,field_content) values(001,this is first record); insert into my_table(field_id,field_content) values(002,this is second record); insert into my_table(field_id,field_content) value

3、s(003,this is third record); commit; DBPool.java： package dataWebService; import java.sql.DriverManager; import java.util.Date; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class DBPool private String cls; private String url; private String usr; private

4、 String pss; private int connCount = 3;/连接数 private Connection connections;/保存数据库连接 private String connStatus;/ 已连可用Y 已连不可用N 未连接X private Date lastQueryTime;/时间戳 public DBPool(DBPoolConfiguration poolConfiguration) this.connCount=poolConfiguration.getConnCount(); this.cls=poolConfiguration.getCls();

5、 this.url=poolConfiguration.getUrl(); this.usr=poolConfiguration.getUsr(); this.pss=poolConfiguration.getPss(); this.connections=new Connectionthis.connCount; this.connStatus=new Stringthis.connCount; for(int i=0;ithis.connCount;i+) this.connStatusi=X;/初始化全部未连接 this.lastQueryTime = new Datethis.conn

6、Count; public DBPool(String cls,String url,String usr,String pss) this.cls=cls; this.url=url; this.usr=usr; this.pss=pss; this.connections=new Connectionthis.connCount; this.connStatus=new Stringthis.connCount; for(int i=0;ithis.connCount;i+) this.connStatusi=X;/初始化全部未连接 this.lastQueryTime = new Dat

7、ethis.connCount; public void initPool() if(connCount1) System.out.println(请正确设置连接池窗口个数); else try Class.forName(this.cls);/register class catch(ClassNotFoundException e) System.out.println(e.getMessage(); catch(Exception e) System.out.println(e.getMessage();/other exceptions for(int i=0;ithis.connCo

8、unt;i+) try this.connectionsi=DriverManager.getConnection(this.url, this.usr, this.pss); this.connStatusi=Y; catch(SQLException e) System.out.println(e.getMessage(); catch(Exception e) System.out.println(e.getMessage();/other exceptions System.out.println(initPool is ready.); /end if public void fre

9、ePool() for(int i=0;ithis.connCount;i+) try mit(); this.connectionsi.close(); this.connStatusi=X; this.lastQueryTimei=null; catch(Exception e) try this.connectionsi.close(); this.connStatusi=X; this.lastQueryTimei=null; catch(Exception e1) System.out.println(e1.getMessage();/just for catch System.ou

10、t.println(freePool is over .); public DBPoolConnection getPoolConn() throws DBPoolIsFullException DBPoolConnection poolConnection = new DBPoolConnection(); poolConnection.connNbr=getConnNbr(); if(poolConnection.connNbr=-1) throw new DBPoolIsFullException(连接池已满); else poolConnection.conn=getConn(pool

11、Connection.connNbr); return poolConnection; public void freePoolConn(DBPoolConnection poolConnection) if(poolConnection=null) System.out.println(poolConnection=null，不需要释放); else freeConn(poolConnection.connNbr); public void printPoolStatus() for(int i=0;ithis.connStatus.length;i+) System.out.println

12、(); System.out.print(this.connStatusi.toString(); if(this.lastQueryTimei=null) System.out.print(-null ); else System.out.print(-+this.lastQueryTimei.toString()+ ); System.out.println(); public String getCls() return this.cls; public String getUrl() return this.url; public String getUsr() return this

13、.usr; int getConnNbr() int iConn=-1; for(int i=0;i=0) checkResult=true;/不需要重连 else if(checkMessage.indexOf(does not exist)=0) checkResult=true;/不需要重连 else if(checkMessage.indexOf(Syntax error)=0) checkResult=true;/不需要重连 else checkResult=false;/需要重连 return checkResult; boolean resetConn(DBPoolConnect

14、ion poolConnection) boolean result=false;/默认不需要重建连接 if(poolConnection=null) System.out.println(poolConnection=null，不知道您想重设哪个连接); else if(poolConnection.connNbr=-1) System.out.println(poolConnection.connNbr=-1，不知道您想重设哪个连接); else if(checkConn(poolConnection)=true) System.out.println(连接有效，不需要重设); else

15、/重设连接 try Class.forName(this.cls);/register class catch(ClassNotFoundException e) System.out.println(e.getMessage(); catch(Exception e) System.out.println(e.getMessage();/other exceptions try this.connectionspoolConnection.connNbr=DriverManager.getConnection(this.url, this.usr, this.pss); this.connS

16、tatuspoolConnection.connNbr=Y; System.out.println(poolConnection.connNbr+连接已重建); result = true;/告知调用者连接已重建 catch(SQLException e) System.out.println(e.getMessage(); catch(Exception e) System.out.println(e.getMessage();/other exceptions return result; void freeConn(int i) try if(i=-1) System.out.print

17、ln(i=-1，不需要释放); else mit(); catch(SQLException e) System.out.println(e.getMessage(); catch(Exception e) System.out.println(e.getMessage();/other exceptions this.connStatusi=Y; DBPoolConfiguration.javapackage dataWebService; public class DBPoolConfiguration private String cls; private String url; pri

18、vate String usr; private String pss; private int connCount;/连接数 public String getCls() return cls; public void setCls(String cls) this.cls = cls; public String getUrl() return url; public void setUrl(String url) this.url = url; public String getUsr() return usr; public void setUsr(String usr) this.u

19、sr = usr; public String getPss() return pss; public void setPss(String pss) this.pss = pss; public int getConnCount() return connCount; public void setConnCount(int connCount) this.connCount = connCount; DBPoolConnection.java:package dataWebService; import java.sql.Connection; public class DBPoolCon

20、nection public int connNbr=-1; public Connection conn=null; DBPoolConnection() this.connNbr=-1; this.conn = null; DBPoolIsFullException.javapackage dataWebService; public class DBPoolIsFullException extends Exception static final long serialVersionUID=1L; DBPoolIsFullException(String message) super(

21、message); Test.java package myAction; import dataWebService.DBPool; import dataWebService.DBPoolConnection; import dataWebService.DBPoolConfiguration; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Test static String rpad(String str,int len) Str

22、ing s = str; if(s=null) s=; while(s.getBytes().lengthlen) s += ; return s; public static void main(String args) /初始化 String cls =,url=,usr=,pss=,sql=; Statement stmt=null; ResultSet rs=null; String gapStr=|;/分隔符 int connCount=2;/最大连接数 /连接Oracle 配置 cls = oracle.jdbc.driver.OracleDriver; url = jdbc:or

23、acle:thin:localhost:1521:myoradb; usr = abc; pss = 123; sql = select t.field_id,t.field_content,to_char(t.record_create_date,YYYYMMDD) day from my_table t; DBPoolConfiguration poolConfiguration=new DBPoolConfiguration(); poolConfiguration.setCls(cls); poolConfiguration.setUrl(url); poolConfiguration

24、.setUsr(usr); poolConfiguration.setPss(pss); poolConfiguration.setConnCount(connCount); DBPool myPool = new DBPool(poolConfiguration); myPool.initPool(); System.out.println(); System.out.print(after init Pool); myPool.printPoolStatus(); System.out.println(); DBPoolConnection c1=null; try c1 = myPool.getPoolConn(); System.out.println(); System.out.print(after getPoolConn); myPool.printPoolStatus(); System.out.println(); stmt = c1.conn.createSta