Table of contents:

Prerequisites for connection pooling

Before configuring connection pooling in tomcat server, some prerequisite steps have to be completed. These include:

  • Downloading database java driver library jar.
  • Adding the driver jar to tomcat class-path.

For the purpose of this tutorial, I am using MySQL. The java driver jar for MySQL can be downloaded from MySQL website. The link for the same is:
MySQL Java Driver

This jar is required for creating connections to MySQL. Tomcat connection pool shall use this jar to create the connections to database.

Global and Local connection pools

Before we see how to create a connection pool using tomcat, it is important to note that you may define scope for the pool. Sometimes, we only want the pool to be available to a single web application. At other times, we may want a global connection pool which is available to all web applications in the server.

The configuration to create the connection shall be same in both the scopes. The only difference is in the location of config file named context.xml. For global connection pool, place context.xml in $CATALINA_HOME/conf folder. But for local connection pool, place this context.xml file in the META-INF folder of particular application as shown in the following figure.

Creating Connection pool

Now we shall see various ways required to create the connection pool in tomcat. We shall also see how to create multiple connection pools with each one connecting to different database.

By using code

We can create a connection pool using java code as done in the following source code:

import java.sql.Connection;
        import java.sql.ResultSet;
        import java.sql.Statement;
        
        import org.apache.tomcat.jdbc.pool.DataSource;
        import org.apache.tomcat.jdbc.pool.PoolProperties;
        
        public class SimplePOJOExample {
        
            public static void main(String[] args) throws Exception {
                PoolProperties p = new PoolProperties();
                p.setUrl("jdbc:mysql://localhost:3306/mysql");
                p.setDriverClassName("com.mysql.jdbc.Driver");
                p.setUsername("root");
                p.setPassword("password");
                p.setJmxEnabled(true);
                p.setTestWhileIdle(false);
                p.setTestOnBorrow(true);
                p.setValidationQuery("SELECT 1");
                p.setTestOnReturn(false);
                p.setValidationInterval(30000);
                p.setTimeBetweenEvictionRunsMillis(30000);
                p.setMaxActive(100);
                p.setInitialSize(10);
                p.setMaxWait(10000);
                p.setRemoveAbandonedTimeout(60);
                p.setMinEvictableIdleTimeMillis(30000);
                p.setMinIdle(10);
                p.setLogAbandoned(true);
                p.setRemoveAbandoned(true);
                p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
                DataSource datasource = new DataSource();
                datasource.setPoolProperties(p); 
                
                Connection con = null;
                try {
                  con = datasource.getConnection();
                  Statement st = con.createStatement();
                  ResultSet rs = st.executeQuery("select * from user");
                  int cnt = 1;
                  while (rs.next()) {
                      System.out.println((cnt++)+". Host:" +rs.getString("Host")+
                        " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
                  }
                  rs.close();
                  st.close();
                } finally {
                  if (con!=null) try {con.close();}catch (Exception ignore) {}
                }
            }
        
        }
    

By using JNDI

For creating a connection pool in Java, the first step is to modify the context.xml file as shown below:


<Resource name="jdbc/TestDB" 
              auth="Container" 
              type="javax.sql.DataSource" 
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              testWhileIdle="true"
              testOnBorrow="true"
              testOnReturn="false"
              validationQuery="SELECT 1"
              validationInterval="30000"
              timeBetweenEvictionRunsMillis="30000"
              maxActive="100" 
              minIdle="10" 
              maxWait="10000" 
              initialSize="10"
              removeAbandonedTimeout="60"
              removeAbandoned="true"
              logAbandoned="true"
              minEvictableIdleTimeMillis="30000" 
              jmxEnabled="true"
              jdbcInterceptors=
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
              username="root" 
              password="root" 
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysql"/>

The above code has all the parameters configured for a connection pool. But not all of them are mandatory.

The next step will be to lookup the data source and then get the connection from it. The code for getting connection in servlet/jsp shall be:

  try {
    // Obtain our environment naming context
    Context envCtx = (Context) new InitialContext().
    lookup("java:comp/env");

    // Look up our data source
    DataSource  ds = (DataSource) envCtx.lookup
       ("jdbc/TestDB");

    Connection con  = ds.getConnection();
   } catch(NamingException e){ e.printStackTrace();
  }

Creating multiple connection pools

We can also create multiple connection pools with each one connecting to different databases. The following source code example will showcase the same

server.xml

<GlobalNamingResources>
    <Resource name="mail/Mail" auth="Container" type="javax.mail.Session"
              mail.smtp.host="localhost"/>
    <Resource auth="Container" type="javax.sql.DataSource" name="jdbc/lagalerie"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost/lagalerie?charSet=LATIN1"
              maxActive="100" maxIdle="30" maxWait="10000"
              username="casashop" password="casashop"/>
</GlobalNamingResources>

context.xml

<ResourceLink global="jdbc/lagalerie" name="jdbc/lagalerie" type="javax.sql.DataSource"/>
  <ResourceLink global="mail/Mail" name="mail/Mail" type="javax.mail.Session"/>

web.xml

<resource-ref>
    <description>The datasource</description>
    <res-ref-name>jdbc/DataSource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
    <description>The mail session</description>
    <res-ref-name>mail/Mail</res-ref-name>
    <res-type>javax.mail.Session</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

The above code example is taken from stackoverflow.

Adding interceptors

Interceptors are a way to enhance the functionality of connection pool. We can add interceptor at pre-defined points. A good example is the closing of statement objects.

Suppose there is an application which is using the connection pool but you want to make sure that all statement objects are properly closed. In this case, you can make use of StatementFinalizer interceptor. By using this interceptors, you can execute the code to close statement objects when they are no more required by the application.

For configuring an interceptor, we need to add the jdbcInterceptors property to your connection pool configuration and specify a semi-colon separated list containing the names of the interceptors to be used. For example, jdbcInterceptors=”ConnectionState;StatementFinalizer”. If the interceptor that you are adding resides in the package org.apache.tomcat.jdbc.pool.interceptor specify the short name, as in the previous example. However, if you are adding an interceptor that resides in a difference package, like a custom interceptor, you would just reference the full class name of your interceptor. For example, com.mycompany.interceptors.MyCustomInterceptor.

Alternatives

Apart from using tomcat connection pooling, we have other options:

  • Use Object Relational Mapping(ORM) tools.
  • Use JDBC tools which generate DAO and DTO code like FireStorm.
  • Use third party libraries like Apache Commons DBCP connection pool.

Out of these options, ORM is a good alternative as the developer doesn’t have the responsibility for writing code to create the pool. By using tools, the developers can reduce the work to be done for writing infrastructure code and focus more on the subtle issues like security, performance and functional requirements.

Connection leakages

Facing database connection leaks is a frequent problem in many applications. The problem lies in wrong configuration of the pool than anything else. Here are some of the common DB connection leak issues found in applications:

1) Abandoned connections leakages: If you have enabled “removeAbandoned” then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle() < 2) and (getNumActive() > getMaxActive() – 3)

For example maxActive=20 and 18 active connections and 1 idle connection would trigger the “removeAbandoned”. But only the active connections that aren’t used for more then “removeAbandonedTimeout” seconds are removed, default (300 sec). Traversing a resultset doesn’t count as being used.

2) maxactive interval: I would set maxActive to smaller value like 50 and then check if the configuration is working correctly.

3) Not returning connections back to pool: This is a common silly mistake where developers get a connection from the Tomcat connection pool, use it and forget to return the connection back to pool. When we invoke the close() method on a connection reference got from Tomcat datasource, the connection is not actually closed but the application can’t use the same connection without invoking the getConnection method. The connection on which close method is invoked is available for other applications/modules to use.

References

1. The Tomcat JDBC Connection Pool
2. Tomcat JDBC Configuration

Related Posts

Configuring Connection Pooling in Tomcat admin Tomcat
Table of contents: 1. Prerequisites for connection pooling 2. Global and Local connection pools 3. Creating Connection pool 4. Creating multiple connection pools 5. Adding interceptors 6. Alternatives to connection pooling 7. Connection Leakages 8. References Prerequisites for connection pooling Before configuring connection pooling in tomcat server, some prerequisite steps have to be completed. These include: Downloading database java driver...
Table of contents: <div id="toc"> 1. <a href="#prerequisites">Prerequisites for connection pooling</a> 2. <a href="#global">Global and Local connection pools</a> 3. <a href="#createpool">Creating Connection pool</a> 4. <a href="#createmultiplepools">Creating multiple connection pools</a> 5. <a href="#addinterceptor">Adding interceptors</a> 6. <a href="#alternative">Alternatives to connection pooling</a> 7. <a href="#connectionleakage">Connection Leakages</a> 8. <a href="#references">References</a> </div> <a name="prerequisites"></a> <h2>Prerequisites for connection pooling</h2> Before configuring connection pooling in tomcat server, some prerequisite steps have to be completed. These include: <ul> <li>Downloading database java driver library jar.</li> <li>Adding the driver jar to tomcat class-path.</li> </ul> For the purpose of this tutorial, I am using MySQL. The java driver jar for MySQL can be downloaded from MySQL website. The link for the same is: <a href="http://dev.mysql.com/downloads/connector/j/">MySQL Java Driver</a> This jar is required for creating connections to MySQL. Tomcat connection pool shall use this jar to create the connections to database. <a name="global"></a> <h2>Global and Local connection pools</h2> Before we see how to create a connection pool using tomcat, it is important to note that you may define scope for the pool. Sometimes, we only want the pool to be available to a single web application. At other times, we may want a global connection pool which is available to all web applications in the server. The configuration to create the connection shall be same in both the scopes. The only difference is in the location of config file named context.xml. For global connection pool, place context.xml in $CATALINA_HOME/conf folder. But for local connection pool, place this context.xml file in the META-INF folder of particular application as shown in the following figure. <img src="http://www.javaexperience.com/wp-content/uploads/2012/08/META-INF.jpg"/ alt="META-INF file"> <a name="createpool"></a> <h2>Creating Connection pool</h2> Now we shall see various ways required to create the connection pool in tomcat. We shall also see how to create multiple connection pools with each one connecting to <a href="http://www.javaexperience.com/java-database-connectivity/" title="Java Database Connectivity">different database</a>. <h3>By using code</h3> We can create a connection pool using java code as done in the following source code: 1 <h3>By using JNDI</h3> For creating a connection pool in Java, the first step is to modify the context.xml file as shown below: 1 The above code has all the parameters configured for a connection pool. But not all of them are mandatory. The next step will be to lookup the data source and then get the connection from it. The code for getting connection in servlet/jsp shall be: 1 <a name="createmultiplepools"></a> <h2>Creating multiple connection pools</h2> We can also create multiple connection pools with each one connecting to different databases. The following source code example will showcase the same <strong>server.xml</strong> 1 <strong>context.xml</strong> 1 <a href="http://www.javaexperience.com/java-ee-load-on-startup-tag-in-web-xml/"><strong>web.xml</strong></a> 1 The above code example is taken from <a href="http://stackoverflow.com/questions/734902/tomcat-connection-pooling">stackoverflow</a>. <a name="addinterceptor"></a> <h2>Adding interceptors</h2> Interceptors are a way to enhance the functionality of connection pool. We can add interceptor at pre-defined points. A good example is the closing of statement objects. Suppose there is an application which is using the connection pool but you want to make sure that all statement objects are properly closed. In this case, you can make use of StatementFinalizer interceptor. By using this interceptors, you can execute the code to close statement objects when they are no more required by the application. For configuring an interceptor, we need to add the jdbcInterceptors property to your connection pool configuration and specify a semi-colon separated list containing the names of the interceptors to be used. For example, jdbcInterceptors="ConnectionState;StatementFinalizer". If the interceptor that you are adding resides in the package org.apache.tomcat.jdbc.pool.interceptor specify the short name, as in the previous example. However, if you are adding an interceptor that resides in a difference package, like a custom interceptor, you would just reference the full class name of your interceptor. For example, com.mycompany.interceptors.MyCustomInterceptor. <a name="alternative"></a> <h2>Alternatives</h2> Apart from using tomcat connection pooling, we have other options: <ul> <li>Use Object Relational Mapping(ORM) tools.</li> <li>Use <a href="http://www.javaexperience.com/java-database-connectivity/">JDBC tools</a> which generate DAO and DTO code like FireStorm.</li> <li>Use third party libraries like Apache Commons DBCP connection pool.</li> </ul> Out of these options, ORM is a good alternative as the developer doesn't have the responsibility for writing code to create the pool. By using tools, the developers can reduce the work to be done for writing infrastructure code and focus more on the subtle issues like security, performance and functional requirements. <a name="connectionleakage"></a> <h2>Connection leakages</h2> Facing database connection leaks is a frequent problem in many applications. The problem lies in wrong configuration of the pool than anything else. Here are some of the common DB connection leak issues found in applications: <strong>1) Abandoned connections leakages:</strong> If you have enabled "removeAbandoned" then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle() < 2) and (getNumActive() > getMaxActive() - 3) For example maxActive=20 and 18 active connections and 1 idle connection would trigger the "removeAbandoned". But only the active connections that aren't used for more then "removeAbandonedTimeout" seconds are removed, default (300 sec). Traversing a resultset doesn't count as being used. <strong>2) maxactive interval:</strong> I would set maxActive to smaller value like 50 and then check if the configuration is working correctly. <strong>3) Not returning connections back to pool:</strong> This is a common silly mistake where developers get a connection from the Tomcat connection pool, use it and forget to return the connection back to pool. When we invoke the close() method on a connection reference got from Tomcat datasource, the connection is not actually closed but the application can't use the same connection without invoking the getConnection method. The connection on which close method is invoked is available for other applications/modules to use. <a name="references"></a> <h2>References</h2> 1. <a href="http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html" target="_blank">The Tomcat JDBC Connection Pool</a> 2. <a href="http://www.onjava.com/2006/04/19/database-connection-pooling-with-tomcat.html" target="_blank">Tomcat JDBC Configuration</a>
Tomcat Interview Questions and Answers
How to deploy servlet on Tomcat
The following two tabs change content below.
I run this blog with lots of passion. In this website, you will find tutorials on Core Java, Spring, Struts, Web Applications, Portals and Database. Please support me and the website by sharing the posts on your facebook / twitter. You can tap the share button at the top of each post. Thanks for the support.

Latest posts by admin (see all)

Comments

comments