Implementation of Connection Pooling for PostgreSQL in Tomcat6.0

  1. Copy the below Resource tag in between open tag <Context> and end tag          </Context> of context.xml available in META-INF directory of your Web Application

For Example

D:\SampleWeb\web\META-INF

<Context>

                                                                                                                         

<Resource name="jdbc/psql"

    auth="Container"

    type="javax.sql.DataSource"

    maxActive="100"

    maxIdle="30"

    maxWait="20000"

    removeAbandoned="true"

    removeAbandonedTimeout="120"

    username="postgres"

    password="postgres"

    driverClassName="org.postgresql.Driver"

    url="jdbc:postgresql://127.0.0.1:5432/testdb"/>

</Context>

2. Copy the same above 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/psql"

    auth="Container"

    type="javax.sql.DataSource"

    maxActive="100"

    maxIdle="30"

    maxWait="20000"

    removeAbandoned="true"

    removeAbandonedTimeout="120"

    username="postgres"

    password="postgres"

    driverClassName="org.postgresql.Driver"

    url="jdbc:postgresql://10.1.14.38:5432/testnpermit"/>

</Context>

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

    

4. Copy the postgresql-8.4-701.jdbc3.jar in Tomcat’s lib directory

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

5. Create a Java Class ConnectionPooling

import java.sql.Connection;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

import nic.java.util.Debug;

public class ConnectionPooling {

    private static DataSource ds;

    public static Connection getDBConnection() throws NamingException, Exception {

        Connection con = null;

        try {

            if (ds != null) {

                con = ds.getConnection();

            } else {

                String dsString = "java:/comp/env/ jdbc/psql";

              ds = (DataSource) new InitialContext().lookup(dsString);

                con = ds.getConnection();

            }

        } catch (Exception e) {

            e.printStackTrace();

                  }

        return con;

    }

}