HOWTO do global JNDI registered Connection Pooling (DataSource) in Tomcat

Using a connection pool of the servlet container seems to be a good idea IMHO. Here's a litlle HOWTO do so using tomcat and postgresql.

1. Provide the jdbc driver by copying the postgresql-8.4-701.jdbc4.jar (or any other version working for you) to the CATALINA_HOME/lib directory.
2. Register the jndi resource by adding the following lines to your CATALINA_HOME/conf/server.xml under <GlobalNamingResources>

   <Resource
    name="jdbc/postgres-ds" type="javax.sql.DataSource"
    driverClassName="org.postgresql.Driver" url="jdbc:postgresql://MYSERVER:5432/testdb"
    username="MYUSER" password="MYPASS"
    defaultAutoCommit="false" 
    maxActive="5" maxIdle="2" maxWait="30000" initialSize="0" 
    validationQuery="SELECT 1;" testOnBorrow="true" testWhileIdle="false"  
   />

3. Create or user the file /META-INF/context.xml(Using maven it is src/main/webapp/META-INF/context.xml in your webapps project with the following content:

   <?xml version="1.0" encoding="UTF-8"?>
   <Context>
    <ResourceLink name="jdbc/dbds" global="jdbc/postgres-ds" type="javax.sql.DataSource"/>  
   </Context>

4. edit your web.xml and add the following lines to the root node:

   <resource-ref>
    <description>postgreSQL Datasource example</description>
    <res-ref-name>jdbc/dbds</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
   </resource-ref>

5. Use the connection in a way like this:

   InitialContext cxt = new InitialContext();
   DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/dbds");
   Connection con = ds.getConnection();
   con.setAutoCommit(false);
   Statement statement = con.createStatement();
   ResultSet rs = statement.executeQuery("SELECT id, \"name\" FROM test;");
   while (rs.next()) {
    long long1 = rs.getLong("id");
    String name = rs.getString("name");
    System.out.println(long1 + " - " + name);
   }
   
   rs.close();
   statement.close();
   con.commit();
   con.close();

Additional Info:
Tomcat 6 is using the Apache dbcp project to do connection pooling. So the available connection pool configuration parameters are described here

The pool is created at the time of the first connection request.

Make sure to always close the connection. Connections that are not closed are not released. This means those connections can't be reused.

If you are using eclipse wtp and Tomcat 7 you may get a nasty java.lang.NullPointerException at org.apache.catalina.core.StandardContext.getObjectNameKeyProperties. It is related to the <ResourceLink name="jdbc/dbds" global="jdbc/postgres-ds" type="javax.sql.DataSource"/> setting in the context.xml file. The problem is described here in more detail. Just enable the server option "Publish module contexts to separate XML files" and the problem is solved.

If you are using eclipse wtp and Tomcat you may need to remove and add the tomcat server again so the wtp plugin recognizes the changed server.xml file.

I'll provide an example project if someone requests :-)

J4v4 rul3z