java中自訂資料庫連接池

串連池是非常好的想法，應用很普遍。自己寫一個資料庫連接池，並不像想象中那樣困難。一般系統對串連池的功能不會有太多要求，使用自己的串連池未必是個壞主意。下面以Oracle為例，但是對Teradata和Greenplum也是可行的。另外我還實現了串連有效性檢查（checkConn）和恢複串連(resetConn)的方法。本例編程採用的是JRE1.4.2環境（別忘了準備訪問資料庫的jar包）。有任何問題請隨時留言，歡迎探討。

在Oracle內建立測試資料：

drop table my_table;     create table my_table(            field_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) values('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 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();             this.url=poolConfiguration.getUrl();             this.usr=poolConfiguration.getUsr();             this.pss=poolConfiguration.getPss();             this.connections=new Connection[this.connCount];             this.connStatus=new String[this.connCount];             for(int i=0;i<this.connCount;i++){                 this.connStatus[i]="X";//初始化全部未串連             }             this.lastQueryTime = new Date[this.connCount];               }                      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 Connection[this.connCount];             this.connStatus=new String[this.connCount];             for(int i=0;i<this.connCount;i++){                 this.connStatus[i]="X";//初始化全部未串連             }             this.lastQueryTime = new Date[this.connCount];         }                 public void initPool(){             if(connCount<1){                 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;i<this.connCount;i++){                     try{                         this.connections[i]=DriverManager.getConnection(this.url, this.usr, this.pss);                         this.connStatus[i]="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 freePool(){             for(int i=0;i<this.connCount;i++){                 try{                     this.connections[i].commit();                     this.connections[i].close();                     this.connStatus[i]="X";                     this.lastQueryTime[i]=null;                 }catch(Exception e){                     try{                         this.connections[i].close();                         this.connStatus[i]="X";                         this.lastQueryTime[i]=null;                                      }catch(Exception e1){                         System.out.println(e1.getMessage());//just for catch                     }                 }                        }             System.out.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(poolConnection.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;i<this.connStatus.length;i++){                 System.out.println("");                 System.out.print(this.connStatus[i].toString());                 if(this.lastQueryTime[i]==null){                     System.out.print("-[null] ");                 }else{                     System.out.print("-["+this.lastQueryTime[i].toString()+"] ");                 }             }             System.out.println("");         }                      public String getCls(){             return this.cls;         }                 public String getUrl(){             return this.url;         }                 public String getUsr(){             return this.usr;         }                 int getConnNbr(){             int iConn=-1;             for(int i=0;i<this.connCount;i++){                 if(this.connStatus[i].equals("Y")){                     this.lastQueryTime[i]=new Date();                     this.connStatus[i]="N";                     iConn=i;                     break;                 }             }             return iConn;         }                      Connection getConn(int i){             return this.connections[i];         }                          void closeConnForTest(DBPoolConnection poolConnection){             try{                 this.connections[poolConnection.connNbr].close();             }catch(SQLException e){                 System.out.println(e.getMessage());              }         }                          boolean checkConn(DBPoolConnection poolConnection){             Statement stmt=null;             String checkMessage="";             boolean checkResult=true;                              //檢查串連是否有效             try{                 String sql = "select * from dual";                 stmt = this.connections[poolConnection.connNbr].createStatement();                 stmt.executeQuery(sql);//execute sql                 stmt.close();                 checkMessage = "checkConn:checkMessage:execute sql success";                 System.out.println(checkMessage);             }catch(Exception e){                 checkMessage = e.getMessage();                 System.out.println(e.getMessage());//other exceptions                 if(checkMessage==null){                     checkMessage="e.getMessage() is null";                     System.out.println(checkMessage);                 }                 //採取激進重連的策略，盡量避免業務中斷                 if (checkMessage.indexOf("ORA-00942")>=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(DBPoolConnection 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{                     //重設串連                     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.connections[poolConnection.connNbr]=DriverManager.getConnection(this.url, this.usr, this.pss);                         this.connStatus[poolConnection.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.println("i=-1，不需要釋放");                 }else{                     this.connections[i].commit();                 }             }catch(SQLException e){                 System.out.println(e.getMessage());             }catch(Exception e){                 System.out.println(e.getMessage());//other exceptions             }             this.connStatus[i]="Y";         }     }