How to set up a MySQL connection pool in Java

MySQL connection pool

A MySQL connection pool is a pool of connections to MySQL database. Opening and maintaining a database connection for each process (or thread), is time costly (connection creation time) and wastes resources (connections).
Connection pool increase the performance of (Java) applications that needs to connect to the database by reusing the connections. In connection pooling, after a connection is created, it is placed in the pool and it is used over and over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool.

How Do I Implement MySQL Connection Pooling in Java?

I am using the GenericObjectPool that provides robust pooling functionality for arbitrary objects.

pom.xml:

    <dependency>
        <groupId>commons-pool</groupId>
        <artifactId>commons-pool</artifactId>
        <version>1.5.5</version>
    </dependency>

MySqlPoolableException.java:

package com.foobar.storage;
@SuppressWarnings("serial")
 
public class MySqlPoolableException extends Exception {
     public MySqlPoolableException(final String msg, Exception e) {
         super(msg, e);
     }
}

MySqlPoolableObjectFactory.java:

package com.foobar.storage;
 
import java.sql.DriverManager;
import org.apache.commons.pool.BasePoolableObjectFactory;
 
public class MySqlPoolableObjectFactory extends BasePoolableObjectFactory {
     private String host;
     private int port;
     private String schema;
     private String user;
     private String password;
 
     public MySqlPoolableObjectFactory(String host, int port, String schema,
                                                String user, String password) {
          this.host = host;
          this.port = port;
          this.schema = schema;
          this.user = user;
          this.password = password;
     }
 
     @Override
     public Object makeObject() throws Exception {
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          String url = "jdbc:mysql://" + host + ":" + port + "/"
               + schema + "?autoReconnectForPools=true";
          return DriverManager.getConnection(url, user, password);
      }
}

ExampleClassUsesMySQLConnectionPool.java:

public class ExampleClassUsesMySQLConnectionPool {
     private static final Log LOG = LogFactory.getLog(ExampleClassUsesMySQLConnectionPool.class);
     private static final String SQL_SELECT = "SELECT ... FROM ... ORDER BY ... DESC LIMIT ...";
     private final ObjectPool connPool;
 
     public ExampleClassUsesMySQLConnectionPool(ObjectPool connPool) {
          this.connPool = connPool;
     }
 
     public List<SomeRecord> getRecords(String sql) throws SQLException, MySqlPoolableException {
          Builder<SomeRecord> builder = new ImmutableList.Builder<SomeRecord>();
          Connection conn = null;
          Statement st = null;
          ResultSet res = null;
          try {
               conn = (Connection)connPool.borrowObject();
               st = conn.createStatement();
               res = st.executeQuery(sql);
               while (res.next()) {
                    SomeRecord someRecord = new SomeRecord(String.valueOf(res.getInt(1)),
                    String.valueOf(res.getInt(2)), res.getString(3));
                    builder.add(someRecord);
               }
          } catch (SQLException e) {
               throw e;
          }  catch (Exception e) {
               throw new MySqlPoolableException("Failed to borrow connection from the pool", e);
          } finally {
               safeClose(res);
               safeClose(st);
               safeClose(conn);
          }
          return builder.build();
     }
 
     private void safeClose(Connection conn) {
          if (conn != null) {
               try {
                    connPool.returnObject(conn);
               }
               catch (Exception e) {
                    LOG.warn("Failed to return the connection to the pool", e);
               }
          }
     }
 
     private void safeClose(ResultSet res) {
          if (res != null) {
               try {
                    res.close();
               } catch (SQLException e) {
                    LOG.warn("Failed to close databse resultset", e);
               }
          }
     }
 
     private void safeClose(Statement st) {
          if (st != null) {
               try {
                    st.close();
               } catch (SQLException e) {
                    LOG.warn("Failed to close databse statment", e);
               }
          }
     }
}

MyApplication.java:

import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.PoolableObjectFactory;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.pool.impl.GenericObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool.Config;
import com.foobar.storage.MySqlPoolableObjectFactory;
 
public class MyApplication {
     private static ObjectPool initMySqlConnectionPool() {
          Properties properties = new Properties();
          properties.load(Boot.class.getClassLoader().getResourceAsStream("config.properties"));
          /*
               If you are using Google's Guava:
               properties.load(Resources.getResource("config.properties");
          */
          String host = properties.getProperty("host");
          String port = properties.getProperty("port");
          String schema = properties.getProperty("schema");
          String user = properties.getProperty("user");
          String password = properties.getProperty("password");
 
          PoolableObjectFactory mySqlPoolableObjectFactory = new MySqlPoolableObjectFactory(host,
               Integer.parseInt(port), schema, user, password);
          Config config = new GenericObjectPool.Config();
            config.maxActive = 10;
            config.testOnBorrow = true;
            config.testWhileIdle = true;
            config.timeBetweenEvictionRunsMillis = 10000;
            config.minEvictableIdleTimeMillis = 60000;
 
          GenericObjectPoolFactory genericObjectPoolFactory = new GenericObjectPoolFactory(mySqlPoolableObjectFactory, config);
          pool = genericObjectPoolFactory.createPool();
          return pool;
     }
 
     public static void main(String[] args) {
          private ObjectPool pool;
          pool = initMySqlConnectionPool();
          ExampleClassUsesMySQLConnectionPool exampleClassUsesMySQLConnectionPool
               = new ExampleClassUsesMySQLConnectionPool(pool);
          ...
 
     }
}

Important: Don’t forget to include the property file (“config.properties”) in the project CLASS_PATH.

Notes:
1. Good reference for Java Properties: http://www.javaworld.com/javaworld/javaqa/2003-08/01-qa-0808-property.html
2. In Guava, reading lines from file can be easily done by:
List terms = Resources.readLines(Resources.getResource(termsFileName), Charsets.UTF_8);
3. Please see comment bellow by Lucas. He added a fix for ‘network hiccup’ problems.

MySQL Quiz
4 Comments
  1. Hi,
    This post begs the question: why not use an existing implementation? c3p0? DBCP?
    Your implementation does not answer for many basic, common functionality expected in connection pools:
    - connection validation
    - self timeout
    - keep alive
    - resetting of session variables
    - more…

    Shlomi

  2. Jeff Schenk says:

    I would use this over dbcp or c3p0, both of those have several bugs, especially testOnBorrow. Even oracle’s connection pooling has issues.

    Nothing is perfect, but the most simplistic to implement usually are the most promising.

    Jeff

  3. Lucas says:

    I’ve been working with this connection pool but it will barf consistently if there is any network hiccup. I was able to fix it by declaring the ObjectFactory like this:

    public class MySqlPoolableObjectFactory
         extends BasePoolableObjectFactory<Connection>

    and then adding this validateObject override within it:

    @Override
    public boolean validateObject(Connection conn) {
       try {
         if (conn.isValid(0))
           return true;
       }
       catch (SQLException e) {
         // ignore
       }
       return false;
    }
    }

    Now, if there is a network hiccup, it will come back to life.

Leave a Reply

*