Custom database connection pool in Java

Connection pooling is very good idea and application is very common. Writing a database connection pool is not as difficult as you might think. The general system does not have much to do with connection pooling, and using your own connection pool may not be a bad idea. Here's an example of Oracle, but it's also possible for Teradata and Greenplum. In addition, I have implemented a method for connection validity checking (checkconn) and Recovery Connections (resetconn). This example is programmed with a JRE1.4.2 environment (don't forget to prepare a jar package to access the database). Any questions please feel free to leave a message, welcome to discuss.

To create test data within Oracle:

drop table my_table;     
CREATE TABLE my_table (     
       field_id varchar2 (3),     
       field_content varchar2 (a),     
       record_create_date Date Default Sysdate     
);     
        
Insert into my_table (field_id,field_content) VALUES (' 001 ';     
Insert into my_table (field_id,field_content) VALUES (' 002 ', ' the ' is second The record ');     
Insert into my_table (field_id,field_content) VALUES (' 003 ', ' The ' is third the 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;//connection number private connection[] connections;//Save database Connection private string[] connstatus;/ /connected available y is not connected to n date[x private] lastquerytime;//time stamp public Dbpool (Dbpoolconfigurati     
        On 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";//Initialize all not connected} 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";//Initialize all not connected} this.     
    Lastquerytime = new Date[this.conncount]; public void Initpool () {if (conncount<1) {System.out.println ("Please set the connection pool correctly")     
        Window number "); }else{try{Class.forName (this.cls);//register Class}catch (classno     
             Tfoundexception 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.connectio     
                    Ns[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 pool     
        Connection = new Dbpoolconnection ();     
        POOLCONNECTION.CONNNBR=GETCONNNBR ();     
        if (poolconnection.connnbr==-1) {throw new dbpoolisfullexception ("Connection pool is full");     
        }else{Poolconnection.conn=getconn (POOLCONNECTION.CONNNBR); }     
        return poolconnection;     
            } public void Freepoolconn (Dbpoolconnection poolconnection) {if (poolconnection==null) {     
        System.out.println ("Poolconnection==null, no need to release");     
        }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.LASTQ     
                Uerytime[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{th     
        Is.connections[poolconnection.connnbr].close ();      
        }catch (SQLException e) {System.out.println (E.getmessage ()); } Boolean Checkconn (Dbpoolconnection poolconnection) {Statement Stmt=nu     
      ll  String checkmessage= "";     
                 
        Boolean checkresult=true;     
            Check that the connection is valid 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) {checkm     
                Essage= "E.getmessage () is null";     
            System.out.println (Checkmessage);     
                //Take a radical, heavily connected strategy to avoid business disruption if (Checkmessage.indexof ("ORA-00942") >=0) { checkresult=true;//does not need to be}else if (Checkmessage.indexof ("DoEs not exist ") >=0 {checkresult=true;//does not need to be connected}else if (Checkmessage.indexof (" Synta X error ") >=0 {checkresult=true;//does not need to be connected}else{che     
    ckresult=false;//need to return checkresult;       
                 
        Boolean resetconn (Dbpoolconnection poolconnection) {Boolean result=false;//default does not need to rebuild the connection if (poolconnection==null) {System.out.println ("poolconnection==null, not sure you want to     
        Which connection to reset ");     
        }else if (poolconnection.connnbr==-1) {System.out.println ("poolconnection.connnbr==-1, you do not know which connection you want to reset"); }else{if (Checkconn (poolconnection) ==true) {System.out.println ("Connection is valid, no need to reset")     
            ); }else{//Reset Connection try{class.forname (this.cls)//register C  Lass   
                }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+ "Connection has been rebuilt"); result = true;//tells the caller that the connection has been rebuilt}catch (SQLException e) {System.out.println (E.getm     
                Essage ());         
            }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, no release required");     
            }else{This.connections[i].commit ();     
        }}catch (SQLException e) {System.out.println (E.getmessage ()); }catch (Exception e) {System.out.println (E.getmessage ());//other Exceptions} th     
    Is.connstatus[i]= "Y"; }     
}