MiniConnectionPoolManager - A lightweight standalone Java 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:

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. If you just need connection pooling, MiniConnectionPoolManager might be the right choice.

MiniConnectionPoolManager may be used in Java servlets as well as in Java standalone applications. It only requires Java 1.5 or newer (or 1.6 if you use getValidConnection()) and has no dependencies on other packages.

API documentation: MiniConnectionPoolManager.html
Source code: MiniConnectionPoolManager.java
Download full package:  miniConnectionPoolManager.zip
Source code repository:   browse / Subversion URL
   
Related work 1: org.opensolaris.db.DbDataSource (by Alan Burlison), a DataSource wrapper class for MiniConnectionPoolManager, which can be used in JSP SQL tags.
Related work 2: org.h2.jdbcx.JdbcConnectionPool (source code), a version of MiniConnectionPoolManager back-ported to Java 1.4 and adapted to H2 by Thomas Müller.
Related work 3: Extended version by Daniel Jurado: This version of MiniConnectionPoolManager closes unused connections after a timeout.
Related work 4: Finnish translation of this page, by Oskari Laine.

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 PostgreSQL:

Version for pgjdbc:
org.postgresql.ds.PGConnectionPoolDataSource dataSource = new org.postgresql.ds.PGConnectionPoolDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("testDB");
dataSource.setUser("user");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();
Version for pgjdbc-ng:
com.impossibl.postgres.jdbc.PGConnectionPoolDataSource dataSource = new com.impossibl.postgres.jdbc.PGConnectionPoolDataSource();
dataSource.setHost("localhost");
dataSource.setDatabase("testDB");
dataSource.setUser("user");
dataSource.setPassword("password");
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.setServerName("localhost");
dataSource.setDatabaseName("Northwind");
dataSource.setUser("sa");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Microsoft SQL Server:

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("password");
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.setServerName("server1.yourdomain.com");
dataSource.setDriverType("thin");
dataSource.setPortNumber(1521);
dataSource.setServiceName("db1.yourdomain.com");
dataSource.setUser("system");
dataSource.setPassword("password");
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.setServerName("dbhost");
dataSource.setDatabaseName("dbname");
dataSource.setUser("user");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

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