Setting Up JNDI JDBC MySQL Connection Pool in Tomcat

So it was time to set up a connection pool for our high concurrency application.  It seemed like an easy thing to do.  I went to avajava.com followed one of their instruction tutorials, and burned a whole afternoon debugging.  Tomcat 7 has connection pooling built in, so I figured it would be a walk in the park.  Man, I got the following list of errors:

• org.apache.tomcat.dbcp.dbcp.BasicDataSource cannot be cast to org.apache.tomcat.jdbc.pool.DataSource
• name is not bound in this context. unable to find 
• java.lang.ClassNotFoundException: org.apache.tomcat.jdbc.pool DataSourceFactor
• java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.BasicDataSource cannot be cast to org.apache.tomcat.jdbc.pool.DataSource
• java.lang.ClassNotFoundException: org.apache.tomcat.jdbc.pool DataSourceFactory

After wasting a whole bunch of time, I finally got it to bind to the database resource, but I had an error with the login stored procedure.  It was this one:

• mysql - java.sql.SQLException: Parameter number 3 is not an OUT parameter

I knew that I was getting some sort of binding but not a good connection.  Not knowing what I didn't know, I decided to do a debug on my connection called conn:

                       System.out.println(conn.toString());

System.out.println(conn.getCatalog());

System.out.println(conn.getAutoCommit());

System.out.println(conn.getMetaData().allTablesAreSelectable());

System.out.println(conn.getMetaData().getDriverName());

System.out.println(conn.getMetaData().getMaxConnections());

System.out.println(conn.getMetaData().supportsStoredFunctionsUsingCallSyntax());

System.out.println(conn.getMetaData().supportsStoredProcedures());

System.out.println(conn.getMetaData().allProceduresAreCallable());

It was quite an interesting transcript.  It told me that getMaxConnections() was zero, that allTablesAreSelectable was false, and allProceduresAreCallable() was false.  I spent a lot of time chasing down this dead end rabbit hole.

Finally I went to the expert:

http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

I followed the instructions implicitly and voila -- every thing works:

Simple Connection Pool for MySQL

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>

The first thing we notice is the factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" attribute.
When Tomcat reads the type="javax.sql.DataSource" it will automatically configure its repackaged DBCP, unless you specify a different factory. The factory object is what creates and configures the connection pool itself.

There are two ways to configure Resource elements in Apache Tomcat.

Configure a global connection pool

File: conf/server.xml

<GlobalNamingResources>
  <Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>
 </GlobalNamingResources>

You then create a ResourceLink element to make the pool available to the web applications. If you want the pool available to all applications under the same name, the easiest way is to edit the File: conf/context.xml

<Context>
  <ResourceLink type="javax.sql.DataSource"
                name="jdbc/LocalTestDB"
                global="jdbc/TestDB"
/>
 <Context>

Note, that if you don't want a global pool, move the Resource element from server.xml into your context.xml file for the web application.

And to retrieve a connection from this configuration, the simple Java code looks like

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

Mister TomcatExpert is really an expert.