MiniConnectionPoolManager - A lightweight standalone JDBC connection pool manager

The standard Java library (JDK 1.5) does not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like Apache Commons DBCP, c3p0 or Proxool, but these are huge complex packages. Modern JDBC drivers provide implementations of ConnectionPoolDataSource and PooledConnection. This makes it possible to build a much smaller connection pool manager.

MiniConnectionPoolManager is a lightweight JDBC connection pool manager. It may be used in Java servlets as well as in Java standalone applications. It only requires Java 1.5 (or newer) and has no dependencies on other packages.

Examples of how to use the MiniConnectionPoolManager class

For H2 (embedded mode):

org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
dataSource.setURL ("jdbc:h2:file:c:/temp/testDB");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Apache Derby (embedded mode):

org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName ("c:/temp/testDB");
dataSource.setCreateDatabase ("create");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For JTDS:

net.sourceforge.jtds.jdbcx.JtdsDataSource dataSource = new net.sourceforge.jtds.jdbcx.JtdsDataSource();
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For the Microsoft SQL Server driver:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
// The sqljdbc 1.1 documentation, chapter "Using Connection Pooling", recommends to use SQLServerXADataSource instead of SQLServerConnectionPoolDataSource.
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Oracle (example for Thin driver):

oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setDriverType ("thin");
dataSource.setServerName ("server1.yourdomain.com");
dataSource.setPortNumber (1521);
dataSource.setServiceName ("db1.yourdomain.com");
dataSource.setUser ("system");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For MaxDB:

com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB dataSource = new com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB();
dataSource.setDatabaseName ("dbname");
dataSource.setServerName ("dbhost");
dataSource.setUser ("user");
dataSource.setPassword ("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

(Works with the MaxDB 7.7 driver, which is able to connect to older DB versions. Version 7.6 has bug. Thanks to Joe (fischauto333) for the contribution of the MaxDB snippet.)

Design pattern for working with JDBC connections

It is important to use error handling to ensure that Connection and Statement objects are always closed, even when an exception occurs.

Example:

public static String getFirstName (int personKey) throws Exception {
   Connection connection = null;
   PreparedStatement statement = null;
   try {
      connection = poolMgr.getConnection();
      final String sql = "select firstName from person where personKey = ?";
      statement = connection.prepareStatement(sql);
      statement.setInt (1, personKey);
      ResultSet rs = statement.executeQuery();
      if (!rs.next()) throw new Exception("Person not found");
      return rs.getString(1); }
    finally {
      if (statement != null) statement.close();
      if (connection != null) connection.close(); }}

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
Index