How to create a Datasource and a Connection Pool on Tomcat.

This post describes how you can setup a Connection Pool for a web application under Tomcat. I have been using NetBeans 5.5.1, with bundled Tomcat (version 5.5.17). NetBeans does offer some visual tools to create datasources and connection pools, but as far as I understand, these only work with the Sun Application Server.

Before doing anything else, the following jars are required:

• mysql-connector-java-5.0.7-bin.jar
• commons-dbcp-1.2.2.jar
• commons-collections-3.2.jar
• commons-pool-1.3.jar

The first one is the official mysql driver which can be downloaded from the mysql site. The other three are part of the Apache Commons collection.
They need to be placed in the WEB-INF/lib subdirectory of the webapp. (Of course this can also be done through the NetBeans IDE.) Actually, although I read on various websites about the last 3 jars, I am not absolutely sure if they are definitely required. Even after I removed them, my application continued to work fine.
Important: The MySQL Connector/J jar needs to be placed under the Tomcat common/lib/ subdirectory. (For the bundled with the NetBeans IDE version of Tomcat, this directory is $NETBEANS_HOME/enterprise3/apache-tomcat-5.5.17/common/lib.

1. Add this to the context.xml file (which is located in the META-INF directory of the web-app.

<Context path="/ePmashup">
  <Resource 
      name="jdbc/dataSource"
      auth="Container"
      type="javax.sql.DataSource"
      factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
      username="username"
      password="password"
      driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/databaseName"
      maxWait="1000"
      removeAbandoned="true"
      maxActive="30"
      maxIdle="10"
      removeAbandonedTimeout="60"
      logAbandoned="true"/>    
</Context>

The options in italics have to be substituted with the appropriate values.

2. Create a DataSource object in the application code.

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

This snippet has to be wrapped in a try-catch block, because it might throw a NamingEsception. In my code, I placed this piece of code in the init() method of the Servlet that contacts the database and returns the results to the client.

 public void init() throws ServletException {
   super.init();
   try {
       Context initContext = new InitialContext();
       Context envContext  = (Context)initContext.lookup("java:/comp/env");
       datasource = (DataSource)envContext.lookup("jdbc/dataSource");
          
   } catch( NamingException ne ) {
     throw new RuntimeException( "Unable to aquire data source", ne );
  }
 }

Obviously, the string passed in the lookup method, needs to be the Resource name (in context.xml).

3. Now, a connection from the pool can be retrieved and used like this:

Connection conn = dataSource.getConnection();