Skip to main content

Connection Pool Configuration Using DBCP

Connection pool configuration, for most of the web applications with data base interaction, is a common process. Either small or big, its always suggested to have connection pool configured for better performance. If your JDBC or DAO code is creating data base connection for each call, it results a lot of overhead and reduces your application performance. Using connection pool will improve application performance a lot. If you are deploying your web application in Application Servers like Weblogic, Webspere, etc. they will allow you to configure connection pool and maintain them for you. But if you are opting for Web Server like Tomcat, which is the most common case when you go for shared hosting services, then you have to depend on resources like DBCP [Data Base Connection Pool] for connection pooling. Its one of the best options available for connection pooling. By configuring connection pool in Tomcat will relieve lot of responsibilities from your application. We let Tomcat create Connection Pool and let it maintain it for you. In this article I will try to explain how to configure DBCP in Tomcat. Well lets start then!
If we configure connection pool in Tomcat, there needs to be a way for your application to access it. This is where JNDI comes in to action. You can use JNDI to access connection pool inside your web application. There are many ways to create connection pool, lets first look at a simple way to do it. First we need to configure pool in Tomcat’s server.xml, lets see the xml code snippet.
<Context path="/sample" debug="0" reloadable="true">
 <Resource name="jdbc/samplePool" auth="Container"
  type="javax.sql.DataSource" removeAbandoned="true"
  removeAbandonedTimeout="30" maxActive="100"
  maxIdle="30" maxWait="10000" username="dbUser"
  password="dbPassword" driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://localhost:3306/testDB"/>
</Context>
Lets go through the above configuration. In the above code we tried to configure a context “/sample”, and for this context we’ve configured a JNDI resource which is a connection pool with the name “jdbc/samplePool”. This connection pool is configured for a MySQL database “testDB”, which is accessible locally using the JDBC URL “jdbc:mysql://localhost:3306/testDB”. Lets go through other configuration parameters briefly.
removeAbandoned Flag to remove abandoned connections if they exceed the removeAbandonedTimout. If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection.
removeAbandonedTimeout Timeout in seconds before an abandoned connection can be removed. Default value for this is 300 sec.
maxActive Maximum number of active connections in pool.
maxIdle The maximum number of connections that can remain idle in the pool. Default is 8.
maxWait The maximum number of milliseconds that the pool will wait
username Database username to be used to create a connection.
password Database password to be used to create a connection.
driverClassName The fully qualified Java class name of the JDBC driver to be used.
url The JDBC connection URL to be used to create a connection.

Once we configure connection pool at context level, we need to configure our web application deployment descriptor [web.xml] to get a reference of this pool. Lets see the code snippet to be added in web.xml file.
<resource-ref>
 <description>Resource references to connection pool</description>
 <res-ref-name>jdbc/samplePool</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
The above configuration indicates container to get a reference to the resource "jdbc/samplePool" configured at context level and have it locally available for the web application. Thats it all you have to do is access this connection pool using JNDI lookup in your code. Sample code block might look as the one given below.
// get environment naming context
Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
// do JNDI lookup for data source
DataSource  ds = (DataSource) envCtx.lookup("jdbc/samplePool");
JNDI look up is a bit expensive in terms of performance, so to avoid JNDI look up every time you need to access connection pool, what you can do is have a Servlet Context Listener class [by implementing ServletContextListener interface] and write the above look up code in its contextInitialized() method, and set the reference in context object, so that you can use this context object to access connection pool. This makes the JNDI lookup be done only once when the context gets deployed.
This is the simple way of configuring connection pool. You will find many articles explaining the simple DBCP configuration [even in more elaborative way] on many other sites/blogs. So, why another one?
If you observe the context tag configuration, we used almost 9 configuration parameters for connection pool creation. It made the XML A BIT COMPLEX. If you are thinking that is OK, let me tell you, DBCP configuration allows you to configure so many other parameters to fine tune it exactly for your situation. If you add all those parameters to the xml, it makes the configuration a lot more complex and makes it difficult to modify.
Alternative! YES, we do have an alternative way for this. Explaining this alternative way is the sole purpose of writing this article. So, lets get on with it. First we need to create a properties file with all the connection pool configuration parameters, and lets name it as "my_db_config.properties". Following is the sample file with few configuration parameters.
# See: http://jakarta.apache.org/commons/dbcp/configuration.html

####################################################################
###################### CONNECTION PROPERTIES #######################
####################################################################

# database username and password
username=root
password=root

# JDBC driver class name
driverClassName=com.mysql.jdbc.Driver

# JDBC connection url
url=jdbc:mysql://localhost:3306/testDB


####################################################################
#################### CONNECTION POOL PROPERTIES ####################
####################################################################

removeAbandoned=true

# Time out in seconds
removeAbandonedTimeout=30

# Maximum number of connections in pool. Set to 0 for no limit.
maxActive=100

# Maximum number of idle connections in pool. Set to -1 for no limit. 
maxIdle=30

# Maximum time in milli seconds to wait for a connection to become available
# Set to -1 to wait indefinitely.
maxWait=10000
For the complete list of configuration parameters please go through this URL: DBCP Configuration Parameters.
Second thing we need to do is modify the above given connection pool configuration to use this properties file. Following is the modified configuration
<Context path="/sample" debug="0" reloadable="true">
 <Resource name="jdbc/samplePool" auth="Container"
  type="javax.sql.DataSource" factory="com.rakesh.DataSourceFactory"
  configFile="my_db_config.properties"/>
</Context>
If you observe we removed all the connection pool configuration parameters and replaced them with two parameters one "factory" and second "configFile". First parameter "factory" is used to give factory class to create DataSource object, second is used to give the properties file with all connection pool configuration parameters, in our case it is "my_db_config.properties" created above.
It is the factory class "com.rakesh.DataSourceFactory", which is responsible for reading configuration parameters from the properties file "my_db_config.properties" and create connection pool. The last part is the factory class, lets see the code blocks in it which will do the job for us.
 . . .
    public Object getObjectInstance(Object obj, Name name, Context nameCtx,
                                    Hashtable environment)
        throws Exception {

        // We only know how to deal with javax.naming.References
        // that specify a class name of "javax.sql.DataSource"
        if ((obj == null) || !(obj instanceof Reference)) {
            return null;
        }
        Reference ref = (Reference) obj;
        if (!"javax.sql.DataSource".equals(ref.getClassName())) {
            return null;
        }
        /* ============================================================
         * RAKESH: Commented following block

        Properties properties = new Properties();
        for (int i = 0 ; i < ALL_PROPERTIES.length ; i++) {
            String propertyName = ALL_PROPERTIES[i];
            RefAddr ra = ref.get(propertyName);
            if (ra != null) {
                String propertyValue = ra.getContent().toString();
                properties.setProperty(propertyName, propertyValue);
            }
        }
         * =============================================================== */
        
        /* =============================================================
   * RAKESH: Customized code to use .properties file
         * ============================================================= */
  RefAddr configFileName = ref.get("configFile");
  if (configFileName == null)
   throw new IllegalArgumentException(
    "configFile attribute used to provide " +
    "DB Configuration properties file is missing");
  String configValue = configFileName.getContent().toString();
  Properties properties = loadProperties(configValue, getClass());

        return createDataSource(properties);
    }

 . . .
    static private Properties loadProperties(final String fileName, Class c)
   throws IOException {
  ClassLoader classLoader = c.getClassLoader();
  InputStream in = classLoader.getResourceAsStream(fileName);
  if (in == null)
   throw new FileNotFoundException("Unable to load properties file: '"
     + fileName + "'");
  try {
   Properties p = new Properties();
   p.load(in);
   return p;
  } finally {
   in.close();
  }
 }

 . . .
Well do not get scared by looking at the above code, only small blocks were written by me. What I did is downloaded DBCP API source code and modified the class "BasicDataSourceFactory" class, thats it. If you observe I added comment blocks with "RAKESH:" text in it those are the blocks I modified, its hardly 20-30 lines of code, thats it. Whats that code block does is, looks for a configuration attribute "configFile" and calls a private method "loadProperties()" which will read the .properties file and returns a "Properties" object with all the configuration parameters. Complete source code is provided at the end of this post, so that you can download it and have a look in to it.
Now the final part, as the connection pool creation is done by the server, the above class should be available for the server to call. The question: how do we make the above class available for the web server. All you have to do is follow the simple steps given below.
1. Compile the above give java class [DataSourceFactory.java] and make a jar out of it.
2. Place this jar file under TOMCAT_HOME/common/lib directory
3. Place the data base pool configuration file [my_db_config.properties] under TOMCAT_HOME/common/classes directory
And make sure you download and place DBCP and Commons Pool jar files in TOMCAT_HOME/common/lib directory. You can find links to download pages of these jar files in resources section below.
Thats it, you are done! you are ready with connection pool configured for your application.
Resources:
1. Complete source code of DataSourceFactory.java
2. Download DBCP jar.
3. Download Commons Pool jar.

Comments

Popular posts from this blog

Hosting Multiple Domains In Tomcat

Tomcat allows us to host multiple domains in one instance, using multiple 'Host' tags. In this article I will explain how to do it on Tomcat. This is very simple configuration using 'Host' tags in your server.xml. A novice can also understand this configuration very easily.
Before going into the details of the configuration first lets have a look at the 'Host' tag, 'Context' tag and 'Alias' tags first.
<Host name="domain1" appBase="[application base]" autoDeploy="[true/false]" unpackWARs="[true/false]"> <Alias>...</Alias> <Context path="" docBase="" reloadable="[true/false]"/> </Host> First lets have a look at 'Alias' tag. This tag is used to provide aliases for your actual domain. For example you have a domain called 'domain1.com', and you want to run the same application for 'www.domain1.com' also…

File Uploading Using Servlets, JSP and Commons File Upload API

I’ve seen many developers who are at the early stages of their career have problems with this topic and seen many posts in forums asking how to do it – File Uploading using Servlets, JSP!; this article will provide an example using Commons File Upload API. I tried to make the example as simple as possible, hope it helps those early birds. Example uses JSP to provide the pages with form where user can select the file to upload with other form fields, Commons File Upload API to process submitted form and read form fields separately, and Servlets as middle layer between JSP and Commons File Upload API, example also has ANT build script to generate the distributables. All the code can be downloaded, links to these resources are provided at the end of this post, lets get on with the example then.
The flow in this example is as depicted in the following picture.
As you can see, user selects the file to upload and provides normal form data as well, using "index.jsp" and submits th…

Simple AJAX [, JSP] Example

You will find many examples to learn how AJAX [Asynchronous Java Script And XML] works, but this one is different. Its one of the best way of using AJAX in web applications. The difference -  many of the examples you find uses Java Script to parse the AJAX response, this example avoids that parsing Java Script and uses JSP to generate the HTML and uses simple Java Script just to make the asynchronous call and show the response. It shows simple way of using JSP and AJAX with as much less Java Script as possible. This article wont go into details of what AJAX is and how to make AJAX calls, it explains one good way among many to use AJAX. Lets get on with it then!
The flow of this example is like this – A simple JSP page where user performs an action [button click in this example] to get list of employees, on this action we’ll make an Asynchronous call to a Servlet which will prepare the dummy employee data and sends that data to a JSP page. This JSP generates the HTML displaying the em…