Connection Pooling

Connection pooling with MySQL Connector/J

http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them.

When the connection is "loaned out" from the pool, it is used exclusively by the thread that requested it. From a programming point of view, it is the same as if your thread called DriverManager.getConnection() every time it needed a JDBC connection, however with connection pooling, your thread may end up using either a new, or already-existing connection.

Luckily, Sun has standardized the concept of connection pooling in JDBC through the JDBC-2.0 "Optional" interfaces, and all major application servers have implementations of these APIs that work fine with MySQL Connector/J.

Generally, you configure a connection pool in your application server configuration files, and access it via the Java Naming and Directory Interface (JNDI).

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.InitialContext;
import javax.sql.DataSource;


public class MyServletJspOrEjb {

   public void doSomething() throws Exception {
       /*
        * Create a JNDI Initial context to be able to
        *  lookup  the DataSource
        *
        * In production-level code, this should be cached as
        * an instance or static variable, as it can
        * be quite expensive to create a JNDI context.
        *
        * Note: This code only works when you are using servlets
        * or EJBs in a J2EE application server. If you are
        * using connection pooling in standalone Java code, you
        * will have to create/configure datasources using whatever
        * mechanisms your particular connection pooling library
        * provides.
        */

       InitialContext ctx = new InitialContext();

        /*
         * Lookup the DataSource, which will be backed by a pool
         * that the application server provides. DataSource instances
         * are also a good candidate for caching as an instance
         * variable, as JNDI lookups can be expensive as well.
         */
        
       DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");

       /*
        * The following code is what would actually be in your
        * Servlet, JSP or EJB 'service' method...where you need
        * to work with a JDBC connection.
        */

       Connection conn = null;
       Statement stmt = null;

       try {
           conn = ds.getConnection();

           /*
            * Now, use normal JDBC programming to work with
            * MySQL, making sure to close each resource when you're
            * finished with it, which allows the connection pool
            * resources to be recovered as quickly as possible
            */

           stmt = conn.createStatement();
           stmt.execute("SOME SQL QUERY");

           stmt.close();
           stmt = null;

           conn.close();
           conn = null;
       } finally {
           /*
            * close any jdbc instances here that weren't
            * explicitly closed during normal code path, so
            * that we don't 'leak' resources...
            */

           if (stmt != null) {
               try {
                   stmt.close();
               } catch (sqlexception sqlex) {
                   // ignore -- as we can't do anything about it here
               }

               stmt = null;
           }

           if (conn != null) {
               try {
                   conn.close();
               } catch (sqlexception sqlex) {
                   // ignore -- as we can't do anything about it here
               }

               conn = null;
           }
       }
   }
}

The most important thing to remember when using connection pooling is to make sure that no matter what happens in your code (exceptions, flow-of-control, etc), connections, and anything created by them (statements, result sets, etc) are closed, so that they may be re-used, otherwise they will be "stranded," which in the best case means that the MySQL server resources they represent (buffers, locks, sockets, etc) may be tied up for some time, or worst case, may be tied up forever.

Connection Pooling Configuration Documentation

Place a copy of mysql-connector-java-[version]-bin.jar in $CATALINA_HOME/common/lib/. Then, follow the instructions in the section MySQL DBCP Example of the Tomcat documentation.

Notice that Connector/J 3.0 and newer work with the same settings: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html