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:

• DBCP (Apache Commons)
• c3p0
• Proxool
• BoneCP
• Tomcat JDBC Connection Pool

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.

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:

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();

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();

For SQLite:

org.sqlite.javax.SQLiteConnectionPoolDataSource dataSource = new org.sqlite.javax.SQLiteConnectionPoolDataSource();
dataSource.setUrl("jdbc:sqlite:c:/temp/test.db");
dataSource.setJournalMode("WAL");
dataSource.getConfig().setBusyTimeout("10000");
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