Database Connection Pooling In Tomcat

Software Object pooling is not a new concept. This technique is used to improve the performance of the application.

Creating a new Database connection is a very expensive process. Opening a connection for every request creates massive load on the server.
So, for avoiding the load and to improve performance of the server we use connection pooling concept for creating database connection.

Lets see the steps for creating connection pool in Tomcat:

step1:
Open the server.xml file in 'tomcathome-dir'/conf/server.xml

step2:
Add the resource in 'GlobalNamingResources' tag.

Sample resource:

<Resource name="jdbc/myDB" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:thin:@localhost:1521:orcl" removeAbandoned="true" logAbandoned="true"
user="username"
password="password"
maxActive="200"
maxIdle="10"
maxWait="-1"/>

step3:
Open the context.xml file in 'tomcathome-dir'/conf/context.xml

step4:
Add the resourceLink in 'context' tag.

Sample ResourceLink:
<ResourceLink global="jdbc/myDB" name="jdbc/myDB" type="oracle.jdbc.pool.OracleDataSource"/>

step5:
Save the above two files and restart the server

---

The context.xml looks like

<?xml version='1.0' encoding='utf-8'?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
<ResourceLink global="jdbc/myDB" name="jdbc/myDB" type="oracle.jdbc.pool.OracleDataSource"/>
</Context>

---

The server.xml looks like

<?xml version='1.0' encoding='utf-8'?>
<Server port="8005" shutdown="SHUTDOWN">
  <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" />
  <Listener className="org.apache.catalina.core.JasperListener" />
  <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" />
  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />

  <GlobalNamingResources>
    <Resource name="UserDatabase" auth="Container"
              type="org.apache.catalina.UserDatabase"
              description="User database that can be updated and saved"
              factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
              pathname="conf/tomcat-users.xml" />


    <Resource name="jdbc/myDB" auth="Container" type="oracle.jdbc.pool.OracleDataSource"
     driverClassName="oracle.jdbc.driver.OracleDriver"
     factory="oracle.jdbc.pool.OracleDataSourceFactory"
             url="jdbc:oracle:thin:@localhost:1521:orcl" removeAbandoned="true" logAbandoned="true"
       user="username"
     password="password"
     maxActive="200"
     maxIdle="10"
             maxWait="-1"/>
  </GlobalNamingResources>

  <Service name="Catalina">
    <Connector port="8080" protocol="HTTP/1.1" 
               connectionTimeout="20000" 
               redirectPort="8443" />
    <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />
    <Engine name="Catalina" defaultHost="localhost">
    <Realm className="org.apache.catalina.realm.UserDatabaseRealm"
             resourceName="UserDatabase"/>
      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true"
            xmlValidation="false" xmlNamespaceAware="false">
      </Host>
    </Engine>
  </Service>
</Server>

• The JNDI datasource provided at step2 is used as a factory for connections.
• One of the major advantages of using a configuration like this is that the characteristics of the pool can be changed without affecting the application code.
• Our application's use of connection pooling is almost transparent.

Now you are done with creation of Connection pooling setting at server side in TOMCAT server.

---

HOW TO ACCESS TOMCAT DATASOURCE IN JAVA:

Now, we ready with server side settings for database connection pooling.

For accessing the Datasource from the server, we use InitialContext lookup.
The snippet is given below:

Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
javax.sql.DataSource dataSource = (javax.sql.DataSource) envContext.lookup("jdbc/myDB");

After getting the datasource from the server, we can get the database connection from datasource.
The snippet is given below:
java.sql.Connection connection = dataSource.getConnection();



Sample Java Program for Creating Database Connection:


package testapp;
import java.sql.*;
import javax.naming.*;
/**
 * @author naveen.k
 */
public class CreateDBConnection{
    public static void main(String args[])
        throws SQLException{
        try {
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            javax.sql.DataSource dataSource = (javax.sql.DataSource) envContext.lookup("jdbc/myDB");
            
            Connection con = dataSource.getConnection();
            Statement stmt = con.createStatement();
            
        } catch (NamingException ne) {
            ne.printStackTrace();
        }

    }
}


Cheers.......!!!!