com.protomatter.jdbc.pool

1. Dynamically grows based on use.
2. Does not automatically shrink, but you can shrink the pool manually.
3. Can automatically refresh connections that have become stale as a result of database errors (including database restarts, etc).
4. Can be configured to automatically close connections that have been idle for too long, and will log a stack trace of where the connection was first opened (this is useful for debugging).
5. Automatically blocks threads until a connection is available.
6. Drop-in replacement for "normal" drivers -- just change the driver class and URL that's being used.
7. Can be configured to log all method calls and SQL being executed.

Class.forName("com.protomatter.jdbc.pool.JdbcConnectionPoolDriver");

The driver automatically registers itself with the JDBC DriverManager when the class is loaded by the VM. Because the driver keeps a list of pools as a static (class) variable, and because of a bug in JDK 1.1.x's classloader, you may need to either keep a reference to an instance of the JdbcConnectionPoolDriver class somewhere where it won't be re-claimed by the garbage collector, or you may need to give java the -noclassgc flag.

One example is:

// initialization params are kept in a Map
Map args = new HashMap();

// the underlying driver -- in this case, the Oracle thin driver.
args.put("jdbc.driver", "oracle.jdbc.driver.OracleDriver");

// the URL to connect the underlyng driver with the server
args.put("jdbc.URL", "jdbc:oracle:thin:@server:1521:ORCL");

// these are properties that get passed
// to DriverManager.getConnection(...)
Properties jdbcProperties = new Properties();
jdbcProperties.put("user", "admin");
jdbcProperties.put("password", "secret");
args.put("jdbc.properties", jdbcProperties);

// a statement that is guaranteed to work
// if the connection is working.
args.put("jdbc.validityCheckStatement", "SELECT 1 FROM DUAL");

// If this is specified, a low-priority thread will
// sit in the background and refresh this pool every
// N seconds.  In this case, it's refreshed every two minutes.
args.put("pool.refreshThreadCheckInterval", new Integer(120));

// the initial size of the pool.
args.put("pool.initialSize", new Integer(5));

// the maximum size the pool can grow to.
args.put("pool.maxSize", new Integer(10));

// each time the pool grows, it grows by this many connections
args.put("pool.growBlock", new Integer(2));

// between successive connections, wait this many milliseconds.
// Some database freak out if you try to open connections
// in succession too quickly.
args.put("pool.createWaitTime", new Integer(500));

// finally create the pool and we're ready to go!
JdbcConnectionPool oraclePool
  = new JdbcConnectionPool("oraclePool", args);

You can also simply use a properties file to configure pools, using the JdbcConnectionPool object.

Once a pool is created, it is automatically registered with the pool driver -- you can immediately start opening connections with it. And you don't have to keep a reference to the pool itself because the driver keeps track of it.

String url = "jdbc:protomatter:pool:oraclePool";
Connection c = DriverManager.getConnection(url);

If there is not a connection available, and the maximum size for the pool has been reached, the caller is placed in a FIFO queue of threads waiting for connections to become available, and is awakened after a connection is checked back in. If the maximum size for the queue has not been reached and there are no available connections, a new connection is created and handed out. Of course, if there is a connection available, it is handed out immediately.

You can also use the JDBC 2.x javax.sql.DataSource interface to get connections from a pool. Simply do the following:

DataSource ds = new JdbcConnectionPoolDataSource("oraclePool");
Connection c = ds.getConnection();

1. If autocommit was set to true, commit() is called.
2. clearWarnings() is called.
3. The catalog is set back to it's original value (only if its value was changed).
4. The type map is set back to it's original value (only if its value was changed).
5. The transaction isolation level is set back to it's original value (only if its value was changed).
6. The value for the autocommit flag is set back to it's original value (only if its value was changed).
7. The value for the read-only flag is set back to it's original value (only if its value was changed).

As you might guess, it's very important to close a connection when you are done using it, or the pool will "leak" connections. We suggest using the pool within a try/catch/finally block, like this:

Connection c = null;
PreparedStatement s = null;
ResultSet r = null;
try
{
  String url = "jdbc:protomatter:pool:myConnectionPool";
  c = DriverManager.getConnection(url);
  s = c.prepareStatement("SELECT * FROM MYTABLE");
  r = s.executeQuery();
  while (r.next())
  {
    // do something with each row
  }
}
catch (SQLException x)
{
  // handle the exception
}
finally
{
  // it's important to enclose the calls to close()
  // in a try block to make sure all three get called.
  try { r.close(); } catch (SQLException x) { ; }
  try { s.close(); } catch (SQLException x) { ; }
  try { c.close(); } catch (SQLException x) { ; }
}

To enable this, turn on the debug flag named "com.protomatter.jdbc.pool". If this is on, then the pool will return wrapped copies of the Statement, PreparedStatement, and CallableStatement classes. ResultSet objects are not wrapped. Messages will be logged to the com.protomatter.jdbc.pool channel using Syslog. See the JavaDoc for Syslog for more information. Logging is done on the "com.protomatter.jdbc.pool" channel in Syslog at the debug level.

Output in Syslog will look something like this:

JdbcConnectionPoolDriver        connect(jdbc:protomatter:pool:testPool, {})
ConnectionWrapper               ConnectionWrapper.createStatement() took 0ms
StatementWrapper                StatementWrapper.executeQuery("select * from test_table")
                                 = org.postgresql.jdbc2.ResultSet@40e45a call took 8ms
StatementWrapper                StatementWrapper.close() call took 0ms

ConnectionWrapper               ConnectionWrapper.prepareStatement()
                                 SQL="select * from test_table where some_column=?" took 0ms
PreparedStatementWrapper        PreparedStatementWrapper.setString(1, "foo") call took 0ms
PreparedStatementWrapper        PreparedStatementWrapper.executeQuery()
                                 = org.postgresql.jdbc2.ResultSet@4fec48 call took 10ms
PreparedStatementWrapper        PreparedStatementWrapper.close() call took 0ms

{@link com.protomatter.jdbc.pool.JdbcConnectionPool com.protomatter.jdbc.pool.JdbcConnectionPool}
{@link com.protomatter.jdbc.pool.JdbcConnectionPoolDriver com.protomatter.jdbc.pool.JdbcConnectionPoolDriver}
{@link com.protomatter.jdbc.pool.JdbcConnectionPoolConnection com.protomatter.jdbc.pool.JdbcConnectionPoolConnection}
{@link com.protomatter.jdbc.pool.PoolSQLException com.protomatter.jdbc.pool.PoolSQLException}
{@link com.protomatter.pool.GrowingObjectPool com.protomatter.pool.GrowingObjectPool}
{@link com.protomatter.pool.SimpleObjectPool com.protomatter.pool.SimpleObjectPool}
{@link com.protomatter.pool.ObjectPool com.protomatter.pool.ObjectPool}
{@link com.protomatter.pool.ObjectPoolObject com.protomatter.pool.ObjectPoolObject}