?
Solved

DbConnectionBroker

Posted on 2005-04-15
7
Medium Priority
?
663 Views
Last Modified: 2008-01-09
Hi.
       We are using dbconnection borker for our jsp application which will open required number of database connections and keep it in a pool..when ever application requests for database connection..it will give a free database connection from that pool.so it eliminates the overhead time in establishing database connection..

i want to know some details about DbConnectionBroker:
1)How can i know how many database connections are in use and preferably the IP Adress of the system which are using those database connections currently..

2)i want to loop thourgh all the non-busy database connections in poll and fire some dummy sql statement just to check that database connection is active..suppose if it's disconnected some how..i want to re-establish connection and put it in pool..

Sample code is preferred.

Hope this makes sense!
If u need any more information..plz let me know

Thanx in advance..
0
Comment
Question by:nagki
7 Comments
 
LVL 29

Accepted Solution

by:
bloodredsun earned 400 total points
ID: 13796614
1)Number of connections in use:   use the getUseCount() method. You can't get the IP directly but as you have access to the source code, it would be possible to create this method.

2)You could use getSize()  to get the number of connections in the dynamic pool to detect if any connections are available. If not then you could recreate another DbConnectionBroker.

Go here for the APIs: http://www.javaexchange.com/api/index.html
1
 
LVL 8

Author Comment

by:nagki
ID: 13796627
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...
0
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13796651
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:aozarov
aozarov earned 400 total points
ID: 13799739
I have no experience with DbConnectionBroker but based on the API I don't see where you can define your own "test connection" logic.
Did you try Jakarta DB connection pool? http://jakarta.apache.org/commons/dbcp/
It is a well know well tested connection pool that is used by many products and it is easy to use and highly configurable (including
applying "test connection" logic and getting very detailed usage information).
You can see usage examples at: http://cvs.apache.org/viewcvs/jakarta-commons/dbcp/doc/
and at the botton of this page: http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/package-summary.html
If you are using tomcat as your web-container then you already have it and just need to configure it:
http://jakarta.apache.org/tomcat/tomcat-5.5-doc/jndi-datasource-examples-howto.html
0
 
LVL 16

Assisted Solution

by:Joe
Joe earned 1200 total points
ID: 13800779
Check the API docs, but I believe this can be set on the constructor.  The param is maxCheckTime.  This is expressed in days.  If hte connection is NOT in use and is older then the amount here it will droip the connection and re-establish it.  I believe there is logic in the pool already to handle stale connections.

I am not sure what type of app you are trying to run this pool with, but I have developed a servlet that reads an XML config file for this pool.  In the config file you can speicify all the properties of the connection pool, and setup multiple DB connections.  The servlet contains methods to get a connection from the pool, and give a connection back to the pool.

Joe
0
 
LVL 16

Expert Comment

by:Joe
ID: 13800822
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>
0
 
LVL 16

Expert Comment

by:Joe
ID: 13800826
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month15 days, 8 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question