Package com.protomatter.jdbc.pool

A JDBC Driver that maintains a pool of connections.

See:
Description

Class Summary

JdbcConnectionPool	Provides a pool of pre-opened JDBC connections.
JdbcConnectionPoolConnection	A java.sql.Connection that's part of a pool of conections.
JdbcConnectionPoolDataSource	An implementation of the javax.sql.DataSource and javax.sql.ConnectionPoolDataSource interfaces.
JdbcConnectionPoolDriver	The driver for use with JDBC connection pools.
JdbcConnectionPoolPooledConnection	An implementation of the javax.sql.PooledConnection interface.
MessageConstants	Constants for messages loaded from resource bundles.
PoolTest	A standalone connection pool testing application.

Exception Summary

PoolSQLException

A SQLException from a JDBC Connection pool that can mask another exception.

Package com.protomatter.jdbc.pool Description

A JDBC Driver that maintains a pool of connections.

Architecture

The Protomatter JDBC Connection Pool Driver has three main components: the driver, the pool and the connection. The pool holds many open connections, and the driver simply asks the pool for a connection.

Driver Features

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.

Loading the Driver

To load the driver, simply execute this code:

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.

Creating a new Connection Pool

To create a new JDBC Connection Pool, you need to give it a name and initialization parameters.

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.

Getting a connection from a pool

Lets say that you have created a connection pool named "myConnectionPool" and you now want to get a connection from it. Simply do the following:

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

The URL given to the DriverManager is just "jdbc:protomatter:pool:poolName" where poolName is the name of the pool you want a connection from.

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

And then use the connection as you would normally.

Using a connection

Use a connection from the pool just like you would use a "normal" connection. The only difference is that when you call close() on it, it does not close the connection. Instead, the connection is marked closed and put back into the pool. When a connection is put back into the pool, the following is done to the underlying connecton:

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) { ; }
}

It's also important to make sure you close all Statement, PreparedStatement and ResultSet objects associated with a connection before you close the connection itself.

Debugging

Using the Debug class, you can enable logging of all "set" methods on the connection, and all methods on statements. This includes logging all SQL executed (and all parameters on PreparedStatements) and how long it takes to call prepareStatement(), etc.

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

Internationalization

Every error message shown by the connection pool can displayed in a different language. Simply add a new resource (named com/protomatter/jdbc/pool/Resources) to your classpath for the new language. For instance, to add French error messages, you would copy the existing file com/protomatter/jdbc/pool/Resources.properties and make a new file called com/protomatter/jdbc/pool/Resources_fr.properties and place it in the CLASSPATH. Please see the JavaDoc for the java.util.ResourceBundle class for more information.

Licenseing

This package (as are all the com.protomatter packages) is licensed under the Protomatter Software License, which is the Apache Software License with appropriate name changes.

For More Information

For more information on the Protomatter JDBC Connection Pool Driver, please consult the documentation for the following classes:

com.protomatter.jdbc.pool.JdbcConnectionPool
com.protomatter.jdbc.pool.JdbcConnectionPoolDriver
com.protomatter.jdbc.pool.JdbcConnectionPoolConnection
com.protomatter.jdbc.pool.PoolSQLException
com.protomatter.pool.GrowingObjectPool
com.protomatter.pool.SimpleObjectPool
com.protomatter.pool.ObjectPool
com.protomatter.pool.ObjectPoolObject