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.