Connection Pooling in Tomcat6.0

Connection Pooling: is a pool of pre-established connections. Connection Pooling is the art of an Application. A connection with the database is a heavy weight process and if it is not created properly it downs the performance of the Application.

Connection Pooling can be maintained either in Applications level or in Web or Application Server level. Struts and Hibernate frame works are providing Application level Connection Pooling

Connection Pooling is provided by Web Server and Applications Server vendors. Implementation of Connection Pooling is different from Server to Server.

Implementation of Connection Pooling for Oracle 8i, 9i & 10g in Tomcat6.0

1. Copy the below Resource tag in between open tag <Context> and end tag </Context> of context.xml available in Tomcat’s conf directory.

For example

   C:\Program Files\Apache Software Foundation\Tomcat 6.0\conf\

<Context>

           <Resource name="jdbc/myoracle" auth="Container"

              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"

              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"

              username="scott" password="tiger" maxActive="20" maxIdle="10"

              maxWait="-1"/>          

</Context>

2. You can have any number of Resource tags as you wish but names must be different.

3. Copy the below tag in web.xml in WEB-INF available in your Web Project

<web-app>

    <resource-ref>

    <description>Oracle Datasource example</description>

    <res-ref-name>jdbc/myoracle</res-ref-name>

    <res-type>javax.sql.DataSource</res-type>

    <res-auth>Container</res-auth>

    </resource-ref>

    </web-app>

  Copy the ojdbc14.jar in Tomcat’s lib directory say

  C:\Program Files\Apache Software Foundation\Tomcat 6.0\lib

4. Create a Java Class ConnectionPooling

 

import java.sql.Connection;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

public class ConnectionPooling {

    private static DataSource ds;

public static Connection getDBConnection() throws NamingException {

Connection con =null;

    try {

  

if( ds!=null)

{

con =ds.getConnection();

}else

{

 Context initContext = new InitialContext();

    Context envContext = (Context)       initContext.lookup("java:/comp/env");

    DataSource ds = (DataSource) envContext.lookup("jdbc/myoracle");

    con = ds.getConnection();

}   

} catch (Exception e) {

    e.printStackTrace();

    }

    return con;

    }

}

Now Connection pooling has been setup now  test your application using this connection pooling.