DbConnectionBroker

Thanx for ur reply..
can u porvide some sample code how can i create method to get IP adress...

i have sufficient connections..but my doubt is how can i be sure that all non-busy (idle) database connections in pool are active...

Then you should look at the difference between the number of connections in the pool versus the number of connections in use:       getSize()  - getUseCount()  will give you the number of available connections. If you can getting refused connections, you should calll this just to check that you have maxed out your connections.

Getting the users IP address is just a matter of calling getRemoteAddr() on the HttpServletRequest in the servlet that is using the connection, so you wouldn't need to hack the source code.

I am just going to post this code out anyway in case someone else who uses this pool might find it useful.  

YOU WILL NEED...jdom.jar in the lib folder, and the dbconnection broker.
http://www.jdom.org/
http://www.javaexchange.com/


Below is the code to the servlet
----------------------------------------------------------------------------------------------------------
package com.iws.dbpool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;

import com.javaexchange.dbConnectionBroker.DbConnectionBroker;



public class ConnectionProperties extends HttpServlet {
       private static String path=null;
      private static ArrayList connections = null;
      private static ArrayList connectionNames = null;
      /**
       * Gets run when the servlet containter starts(java app server) calls readConfig to
       * read the configuation file for the mysql connnection
       *
       * @param  ServletConfig
       * @return void
       */
      public void init(ServletConfig config) throws ServletException {
            ArrayList conConfig=new ArrayList();
            connections = new ArrayList();
            connectionNames= new ArrayList();
            path = config.getServletContext().getRealPath("") + "/WEB-INF/dbConf.xml";
            DatabaseConfig dbconf = new DatabaseConfig();
            try{
                  conConfig=dbconf.load(path);
                  /**
                   * DbConnectionBroker Arguments
                   *
                   * @param String JDBC Driver class
                   * @param String JDBC Driver URL
                   * @param String Database username
                   * @param StringDatabase password
                   * @param int Minimum number of connection to keep open to the database
                   * @param int Maximum number of connections to keep open to the database
                   * @param String path to log file
                   * @param Double maxConnection time in days for the pool to perform a cleanup
                   * this will drop all connections to the database and then reconnect them .01 =1 day
                   * @param boolean Append to log file or overwrite the log
                   * @param int maxCheckoutSeconds Max time connection can be checked out by
                   * a servlet before it is auto returned to the pool.  Default is 60 sec if set
                   * to 0 then it will turn this option off
                   * @param int debugLevel 0=log nothing 1=log errors 2=errors,warnings 3=Log Everything
                   * */
                  
                  /*Array Definitions
                  * 1.Connection Name
                  * 2.Driver
                  * 3.Connection URL
                  * 4.User Name
                  * 5.Password
                  * 6.Min Connections
                  * 7.Maximum Connectoins
                  * 8.Log Path
                  * 9.Append To Log
                  * 10.Max Connect Time
                  * 11.Max check Time
                  * 12.Debug Level
                  */
                  String driver=null,conUrl=null,user=null,pass=null,logPath=null;
                  int minCon=0,maxCon=0,maxCheckout=0,debugLevel=0;
                  double MaxConnectTime=0;
                  boolean appendToLog=false;
                  //loop through the connections that were returned by the xml file
                  for (int i=0;i<conConfig.size();i++){
                        //get the connection name
                        connectionNames.add(((ArrayList) conConfig.get(i)).get(0).toString());
                        driver=((ArrayList) conConfig.get(i)).get(1).toString();
                        conUrl=((ArrayList) conConfig.get(i)).get(2).toString();
                        user=((ArrayList) conConfig.get(i)).get(3).toString();
                        pass=((ArrayList) conConfig.get(i)).get(4).toString();
                        minCon=Integer.parseInt(((ArrayList) conConfig.get(i)).get(5).toString());
                        maxCon=Integer.parseInt(((ArrayList) conConfig.get(i)).get(6).toString());
                        logPath=((ArrayList) conConfig.get(i)).get(7).toString();
                        appendToLog=convertBool(((ArrayList) conConfig.get(i)).get(8).toString());
                        MaxConnectTime=Double.parseDouble(((ArrayList) conConfig.get(i)).get(9).toString());
                        maxCheckout=Integer.parseInt(((ArrayList) conConfig.get(i)).get(10).toString());
                        debugLevel=Integer.parseInt(((ArrayList) conConfig.get(i)).get(11).toString());
                        System.out.println("Connection: "+((ArrayList) conConfig.get(i)).get(0).toString());
                        System.out.println("      driver="+driver);
                        System.out.println("      jdbc url="+conUrl);
                        System.out.println("      username="+user);
                        System.out.println("      password="+pass);
                        System.out.println("      Min Connection="+minCon);
                        System.out.println("      Max Connection="+maxCon);
                        System.out.println("      Log Path="+logPath);
                        System.out.println("      Append To Log="+appendToLog);
                        System.out.println("      Max Connection Time="+MaxConnectTime);
                        System.out.println("      Max Connection Checkout Time="+maxCheckout);
                        System.out.println("      debug level="+debugLevel);
                        connections.add(new DbConnectionBroker (driver,
                                                                                    conUrl,
                                                                                    user,
                                                                                    pass,
                                                                                    minCon,
                                                                                    maxCon,
                                                                                    logPath,
                                                                                    MaxConnectTime,
                                                                                    appendToLog,
                                                                                    maxCheckout,
                                                                                    debugLevel));
                  }
                  System.out.println("Sql Connection Configuration Loaded Successfully");
            } catch(Exception ex) {
                  System.out.println("Could not Load Connection Configuration! Error: " + ex);
                  ex.printStackTrace();
            }      
      }
      /**
       * Gets run when the servlet containter stops.  Calls myBroker.destroy which closes
       * all connections in the pool to the database
       *
       * @return void
       */
      public void destroy (){  
            System.out.println("Closing Connections in the pool");
            for (int i=0; i<connections.size(); i++){
                  System.out.println("Closing Connections For Pool: "+connectionNames.get(i).toString());
                  ((DbConnectionBroker)connections.get(i)).destroy();      
            }
        super.destroy();
    }
   
    /**
       * Gets a connection from the conection pool
       *
       * @param String Connection Name
       * @return Connection
       */
      public static Connection getConn(String connectionName)throws SQLException{
            if (connectionName==null){
                  throw new java.sql.SQLException("No Connection Name Specified");      
            }
            for (int i=0;i<connectionNames.size();i++){
                  if (connectionNames.get(i).toString().toLowerCase().equals(connectionName.toLowerCase())){
                        return(((DbConnectionBroker)connections.get(i)).getConnection());
                  }      
            }
            throw new java.sql.SQLException("Connection Name "+connectionName+" does not exist in dbConf");
    }
   
    /**
       * Returns a connection to the pool
       *
       * @param String Connection Name
       * @return void
       */
      public static void returnConn(Connection con,String connectionName)throws SQLException{  
            for (int i=0;i<connectionNames.size();i++){
                  if (connectionNames.get(i).toString().toLowerCase().equals(connectionName.toLowerCase())){
                        ((DbConnectionBroker)connections.get(i)).freeConnection(con);
                        return;
                  }            
            }
            throw new java.sql.SQLException("Connection Name "+connectionName+" does not exist in dbConf");
    }


      /**
       *Convert a string to a boolean
       *
       * @param String to convert to boolean
       * @return boolean
      */
      public boolean convertBool(String b) {
          return (b=="TRUE" ? true : false);
     }

}

-------------------------------------------------------------------------------------------------------
Below is the code to a helper class that the servlet uses to read the XML file
-------------------------------------------------------------------------------------------------------
package com.iws.dbpool;
import java.io.*;
import java.net.*;
import java.util.*;
import org.jdom.Document;
import org.jdom.JDOMException;
import org.jdom.*;
import org.jdom.input.DOMBuilder;
import org.jdom.input.SAXBuilder;


/**
 * dbConf is the class used to load the connection pool database connection
 * information from an XML file. The load method takes a string value which
 * is the path to the XML file.
 *
 */
public class DatabaseConfig {
      /**
      * Loads the xml file and returns an arraylist that holds arraylists inside of
      * it that stores the connection information for each connection.  See below for the definition
      * of the array.
      * 1.Connection Name
      * 2.Driver
      * 3.Connection URL
      * 4.User Name
      * 5.Password
      * 6.Min Connections
      * 7.Maximum Connectoins
      * 8.Log Path
      * 9.Append To Log
      * 10.Max Connect Time
      * 11.Max check Time
      * 12.Debug Level
      *
      * @param path the full path to the xml file to load from
      * @return ArrayList Holds connection definitions
      * @see
      */
      public ArrayList load(String path) throws IOException,JDOMException{
            Document doc = null;
            ArrayList connections=new ArrayList();
            ArrayList temp= new ArrayList();
            doc = new Document();
            // SAXBuilder is more does create the entire DOM at once
            //doc = new SAXBuilder().build(path);
            DOMBuilder builder=new DOMBuilder("org.jdom.adapters.XercesDOMAdapter");        
            doc = builder.build(new FileInputStream(path));
          //get the root of the xml document
            java.util.List conections = null;
            Element conectionElement = null;
            Element rootElem = doc.getRootElement();
            conections = rootElem.getChildren();
            //loops through each <connection> tag
            for (int i = 0; i < conections.size(); i++) {
                  conectionElement=(Element) conections.get(i);
                  temp.add(getXMLElement(conectionElement,"NAME"));
                  temp.add(getXMLElement(conectionElement,"DRIVER"));
                  temp.add(getXMLElement(conectionElement,"CONNECT"));
                  temp.add(getXMLElement(conectionElement,"USER"));
                  temp.add(getXMLElement(conectionElement,"PASS"));                        
                  temp.add(getXMLElement(conectionElement,"MINCON"));
                  temp.add(getXMLElement(conectionElement,"MAXCON"));
                  temp.add(getXMLElement(conectionElement,"LOGPATH"));
                  temp.add(getXMLElement(conectionElement,"LOGAPPEND"));
                  temp.add(getXMLElement(conectionElement,"MAXCONTIME"));
                  temp.add(getXMLElement(conectionElement,"MAXCHKTIME"));
                  temp.add(getXMLElement(conectionElement,"DEBUG"));
                  connections.add(temp);
                  //re init the temp array
                  temp =  new ArrayList();
            }
            
            return connections;
      }
      
      /**
      * Retrieves an XML element in a document based on a string variable
      * passed to the function
      *
      * @param Element element reference to search through
      * @param elem the name of the element to search for
      * @return String the xml element value
      * @see
      */
      public String getXMLElement(Element e, String elem) {
            java.util.List configElements = null;
            configElements = e.getChildren();
            Element tmpElem1 = null;
            for (int i = 0; i < configElements.size(); i++) {
                  tmpElem1 = (Element) configElements.get(i);
                  if(tmpElem1.getName().equals(elem)) {
                        return tmpElem1.getText();
                  }
            }
            return null;
      }


}

-----------------------------------------------------------------------------------------------------------
Below is the XML config file for the connection pool - this needs to be placed in the WEB-INF
directory, and named dbConf.xml
-----------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<!DOCTYPE DBCONF [
      <!ELEMENT DBCONF (CONNECTION*)>
      <!ELEMENT CONNECTION (NAME,DRIVER,CONNECT,USER,PASS,MINCON,MAXCON,LOGPATH,LOGAPPEND,MAXCONTIME,MAXCHKTIME,DEBUG)>
        <!ELEMENT NAME (#PCDATA)>
        <!ELEMENT DRIVER (#PCDATA)>
        <!ELEMENT CONNECT (#PCDATA)>
        <!ELEMENT USER (#PCDATA)>
        <!ELEMENT PASS (#PCDATA)>
        <!ELEMENT MINCON (#PCDATA)>
        <!ELEMENT MAXCON (#PCDATA)>
        <!ELEMENT LOGPATH (#PCDATA)>
        <!ELEMENT LOGAPPEND (#PCDATA)>
        <!ELEMENT MAXCONTIME (#PCDATA)>
        <!ELEMENT MAXCHKTIME (#PCDATA)>
        <!ELEMENT DEBUG (#PCDATA)>
        
]>
<DBCONF>
      <CONNECTION>
            <NAME>iwsdb</NAME>
            <DRIVER>com.inet.tds.TdsDriver</DRIVER>
            <CONNECT>jdbc:inetdae7:111.111.115.69:1433?database=iws</CONNECT>
            <USER>user</USER>
            <PASS>pass</PASS>
            <MINCON>2</MINCON>
            <MAXCON>20</MAXCON>
            <LOGPATH>C:\WebPrograms\IWS\logs\iwsdb.log</LOGPATH>
            <LOGAPPEND>true</LOGAPPEND>
            <MAXCONTIME>0.01</MAXCONTIME>
            <MAXCHKTIME>180</MAXCHKTIME>
            <DEBUG>3</DEBUG>
      </CONNECTION>      
       <CONNECTION>
            <NAME>ecidb</NAME>
            <DRIVER>com.dbcswc.fs.jdbc.Driver</DRIVER>
            <CONNECT>jdbc:fs3:111.111.111.210:9584/eci40test;encryption=off;localport=0</CONNECT>
            <USER>user</USER>
            <PASS>pass</PASS>
            <MINCON>1</MINCON>
            <MAXCON>3</MAXCON>
            <LOGPATH>C:\WebPrograms\IWS\logs\ecidb.log</LOGPATH>
            <LOGAPPEND>true</LOGAPPEND>
            <MAXCONTIME>0.01</MAXCONTIME>
            <MAXCHKTIME>0</MAXCHKTIME>
            <DEBUG>3</DEBUG>
      </CONNECTION>
</DBCONF>
      
-----------------------------------------------------------------------------------------------------------
The last thing you need to do is put an entry in the web.xml to start the servlet with the container.
-----------------------------------------------------------------------------------------------------------

    <servlet>
       <servlet-name>ConnectionProperties</servlet-name>
        <servlet-class>com.iws.dbpool.ConnectionProperties</servlet-class>
        <load-on-startup>1</load-on-startup>
    </servlet>

One more thing I should add.  You can specify as many <CONNECTION></CONNECTION> elements as you wish in the XML config file.  There has to be a minimum of 1 connection element for it to work though.

Joe