MiniConnectionPoolManager - A lightweight standalone JDBC connection pool manager

http://www.source-code.biz/snippets/java/8.htm

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 or c3p0, 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.

API documentation:	MiniConnectionPoolManager.html
Source code:	MiniConnectionPoolManager.java
Test program:	TestMiniConnectionPoolManager.java
Download full package:	MiniConnectionPoolManager.zip
Related work 1:	org.opensolaris.auth.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 ported to Java 1.4 and adapted to H2 by Thomas Müller.

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

Design pattern for working with JDBC connections

It is important to use error handling to ensure that Connection and Statement class 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