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";    
    }    
}