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

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索java
， string
， private
， this
， import
， 自连接
java数据库连接数据库
java 自定义连接池、java自定义线程池、自定义连接池、自定义数据库连接池、mybatis自定义连接池，以便于您获取更多的相关知识。