Using database model decoders with connection pooling

The standard database model decoders uses 3 properties to establish a connection to the database:

  • The driver class

  • The connection url

  • The connection parameters

This way of working is not compatible with the standard Java connection pooling frameworks like Apache Commons DBCP, HikariCP and C3P0. They all allow you to create an implementation of DataSource which you can then use to create one or more Connections.

To be able to use one of these frameworks you need to implement your own custom driver that then delegates to the specific API of the framework. In this sample we will use the Hikari framework.

Hikari Driver
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Logger;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 * This is the Hikari implementation of the java.sql.Driver interface.
 */
public class HikariDriver implements Driver {

  public static final String HIKARI = "hikari";

  public static final String ALIAS_DELIMITER = ".";

  public static final String URL_DELIMITER = ":";

  private static final ConnectionPoolManager CONNECTION_POOL_MANAGER = new ConnectionPoolManager() {

    @Override
    protected ConnectionPool createNewConnectionPool(ConnectionPoolDefinition aConnectionPoolDefinition) {
      HikariConfig config = new HikariConfig();
      config.setJdbcUrl(aConnectionPoolDefinition.getUrl());
      config.setDriverClassName(aConnectionPoolDefinition.getDriver());
      config.setUsername(aConnectionPoolDefinition.getInfo().getProperty("user"));
      config.setPassword(aConnectionPoolDefinition.getInfo().getProperty("password"));
      HikariDataSource hikariDataSource = new HikariDataSource(config);
      return new ConnectionPool() {
        @Override
        public Connection getConnection() throws SQLException {
          return hikariDataSource.getConnection();
        }
      };
    }
  };

  static {
    try {
      DriverManager.registerDriver(new HikariDriver());
    } catch (SQLException e) {
      PrintWriter writer = DriverManager.getLogWriter();
      if (writer != null) {
        e.printStackTrace(writer);
      }
    }
  }

  /**
   * The url should be of the form:
   * <pre>
   *   hikari:delegate-class:delegate-url
   * </pre>
   * or,
   * <pre>
   *   hikari.name:delegate-class:delegate-url
   * </pre>
   * where <pre>delegate-class</pre> is the actual Driver that will be used and
   * <pre>delegate-url</pre> is the url that will be based to that Driver
   *
   * By defining <pre>name</pre> you are able to define multiple connection pools
   * even if the delegate url is the same. The entire url (including the hikari.name) is
   * used to uniquely identify this pool.
   *
   */
  @Override
  public Connection connect(String url, Properties info)
      throws SQLException {
    if (!url.startsWith(HIKARI)) {
      return null;
    }
    return CONNECTION_POOL_MANAGER.getConnection(getAlias(url), url, info);
  }

  /**
   * Extracts the pool alias from the url:
   *
   *    hikari.alias:driver:url -> alias
   *    hikari.alias -> alias
   *
   * @return the alias defined within the url
   * @throws SQLException if we couldn't find the alias
   */
  protected static String getAlias(String aUrl) throws SQLException {
    String alias;
    final String prefix = HikariDriver.HIKARI + HikariDriver.ALIAS_DELIMITER;

    // Check that the prefix is there
    if (aUrl.startsWith(prefix)) {

      // Check for the alias
      int endOfPrefix = aUrl.indexOf(HikariDriver.URL_DELIMITER);

      if (endOfPrefix > -1) {
        alias = aUrl.substring(prefix.length(), endOfPrefix);
      } else {
        alias = aUrl.substring(prefix.length());
      }
    } else {
      alias = aUrl;
    }
    // Check we found it.
    if (alias.length() == 0) {
      throw new SQLException("The URL '" + aUrl + "' is not in the correct form. It should be: 'hikari.alias:driver:url'");
    }

    return alias;
  }

  /**
   * @see Driver#acceptsURL
   */
  public boolean acceptsURL(String url) {
    return (url.startsWith(HIKARI));
  }

  /**
   * @see Driver#getPropertyInfo
   */
  @Override
  public DriverPropertyInfo[] getPropertyInfo(String url, Properties info) {
    return new DriverPropertyInfo[0];
  }

  /**
   * @see Driver#getMajorVersion
   */
  @Override
  public int getMajorVersion() {
    return 1;
  }

  /**
   * @see Driver#getMinorVersion
   */
  @Override
  public int getMinorVersion() {
    return 0;
  }

  /**
   * @see Driver#jdbcCompliant
   */
  @Override
  public boolean jdbcCompliant() {
    return true;
  }

  @Override
  public Logger getParentLogger() {
    return null;
  }

}

The HikariDriver will support urls of the format: hikari.name:delegate-class:delegate-url. Where:

  • delegate-class is the actual Driver that will be used

  • delegate-url is the url that will be based to that driver

  • name is optional and allows you to define multiple connection pools even if the delegate url is the same

HikariDriver implements the createNewConnectionPool method of ConnectionPoolManager and creates a HikariDataSource from the HikariConfig. The config needs following properties:

  • The delegate driver class

  • The delegate connection url

  • The username and password

You can extend the createNewConnectionPool method to use additional properties defined in the config file to further customize the connection pool. For example configuring the maximum pool size, the connection time out, the idle time out etc.

In the configuration file of the database model decoder you specify:

  • samples.format.database.hikari.HikariDriver as your driver class

  • the connection url using the hikari.name:delegate-class:delegate-url format

  • The connection parameters of both your delegate connection as those used to configure the connection pool.