PoolMan User's Guide
PoolMan version 2.1

Contact: neville@codestudio.com
Document Last Updated: 18-OCT-01



Contents

1.0    Overview

1.1    Summary
1.2    Object pooling and modern VM's
1.3    Features
1.4    Relevant technologies and API's
1.5    File and package structure


2.0    Installation
2.1    STEP ONE: PoolMan JAR file and your database driver
2.2    STEP TWO: Third-party JAR files
2.3    STEP THREE: Configure poolman.xml
2.4    Testing the installation
2.5    Upgrading from an earlier release

3.0    Usage

3.1    JDBC: JNDI and DataSource usage
3.2    JDBC: Driver usage
3.3    JDBC: Advanced scrollable, updatable ResultSet usage
3.4    Non-standard: SQLManager usage
3.5    Nonstandard and deprecated: SQLUtil usage
3.6    About the query/ResultSet cache
3.7    Pooling non-JDBC objects: The GenericPool
3.8    Programmatic usage: alternatives to poolman.xml
3.9    Starting PoolMan as a server


4.0    Administration

4.1    Enabling and Disabling JMX
4.2    JMX in PoolMan
4.3    The HTML JMX agent


5.0    Web App Integration
5.1    Tomcat and web applications
5.2    JRun
5.3    Struts
5.4    Velocity

6.0    Resources
6.1    The poolman.xml configuration file in detail
6.2    The PoolMan WAR
6.3    Recommended tutorials and relevant specifications


1.0    Overview

1.1    Summary

PoolMan is an embeddable object pooling and caching library. It optimizes resource usage by managing reusable instances across multiple requests. It can be used to pool any Java Class type, and contains specialized features -- such as a JDBC DataSource implementation and SQL query cache -- for JDBC resource pooling. It is designed to be embeddable, so it instantiates its underlying server lazily (that is, upon receipt of the first request) rather than requiring an explicit root configuration and startup semantics.

The JDBC connection pooling facility is exposed through a number of programmatic views, or API's. The differing API's all provide exactly the same underlying functionality, the variety of views exist merely to satisfy different programming styles and designs. The JDBC-related views are:

  •   JDBC Driver implementation
  •   JDBC DataSource implementation
  •   PoolMan-specific pool manager API
  •   A PoolMan-specific simple SQL utility

The non-JDBC generic object pool facility is accessed programmatically via the PoolMan-specific pool management API (similar to the third option above).

Multiple database connection and object pools are configured via the poolman.xml descriptor file. This XML file may be given a different name, which is typically useful when embedding the PoolMan libary into other products. Regardless, PoolMan reads its metadata information for all its pools from an XML file, and that file contains a summary of configurable features for each pool.

From a high level, usage of PoolMan involves the following:

  1. Installing the PoolMan JAR into the appropriate CLASSPATH and verifying that all dependent JAR's (such as an XML parser and the required Java JAR's from Sun) are also available;
  2. Configuring the poolman.xml file for each pool you wish to create and ensuring that its directory is included in all appropriate CLASSPATH entries;
  3. Accessing the pool in your code through one of the programmatic API views described above;
  4. Runtime operation, including JMX-based administration and monitoring of pool metrics

1.2    Object pooling and modern virtual machines

It is important to note that general Java instance pooling in advanced virtual machines is typically unnecessary. Modern virtual machines contain low-level resource optimization that app-level pooling may circumvent and interfere with. However, Java types that make use of underlying system resources -- such as file descriptors, and socket connections -- should still be pooled. In these cases, it is not simply the Java instance creation process in question, it is a larger resource management issue that spans beyond merely the VM. Thus socket pooling, thread pooling, and connection pooling remain a necessity even in systems deployed on advanced modern VM's.


1.3    Features

PoolMan contains the following high level features (see the poolman.xml resource for detailed info):

  •  JDBC 2.0/3.0: Implements advanced JDBC 2.0 functionality, including scrollable/updatable ResultSets, for any underlying driver. Pools automatically attempt to bind their DataSources to JNDI.

  •  Custom XML-based pool types: Pool any Class type, not just JDBC connections, using XML -- without writing a line of code.

  •  The Cache: SQL queries are automatically stored in a configurable cache that updates itself asynchronously in the background against the pool's underlying datastore.

  •  JMX: Administer live pools through a web browser, via the JMX Reference Implementation HTML agent.

  •  Lazy Initialization: To better suit embeddability use cases, pools aren't created until they are first requested. This feature can be disabled programmatically.

  •  Native access: Access to underlying native ResultSets and Statements is available both programmatically and via an XML-defined parameter.

  •  Multitude of configurable parameters: All pool parameters and definitions are exposed via standard XML.

  •  Automatic resource collection: Pooled objects automatically return to their pools after a configurable user timeout value.

  •  Emergency objects: Pools can temporarily be allowed to exceed their maximum limit under duress, and will automatically shrink back to their bounds as load decreases.

1.4    Related API's

PoolMan implements the JDBC API as a virtual driver, and will soon include a Connector and ConnectionManager implementation of the Java Connector API. It supports use of the Java Management Extension (JMX) for service management, and ships with the JMX Reference Implementation. It also makes use of SAX to parse its XML, and ships with the Xerces XML parsing library.


1.5    File and package structure

The distribution contains the following directories:

    Directory: root level
    CHANGELOG: A code-level description of recent changes
    LICENSE: The LGPL text covering legal usage of this library
    README: A brief set of instructions for quick installation and setup

    Directory: apps
    A directory containing the PoolMan WAR and separate JSP and servlet database clients used to manipulate databases while monitoring PoolMan metrics.

    Directory: docs
    This document and the XML quick reference for all poolman.xml parameters..

    Directory: javadocs
    The javadoc pages generated from the source code.

    Directory: lib
    The JAR files and configuration files necessary to support the build, runtime, and test suite in the PoolMan distribution.

    Directory: samples
    Executable sample code illustrating various aspects of JDBC usage.

    Directory: src/main
    The top level of the source code for the PoolMan library. The packages are structured as follows:

    • com.codestudio.util: The core data structures and pooling facilities
    • com.codestudio.sql: The JDBC implementation classes
    • com.codestudio.management: The PoolMan bootstrap, deployment, and JMX-based MBean management classes
    • com.codestudio.bean: Useful Javabeans and a Struts Action object for use in various web applications

    Directory: src/testsuite
    The top level of the source and runtime scripts for the JUnit-based PoolMan test suite.



2.0    Installation

Installation involves adding the appropriate JARs to the CLASSPATH, configuring poolman.xml, adding the directory containing poolman.xml to the CLASSPATH, and testing the install.

2.1    STEP ONE: Add PoolMan JAR and your Database Driver to the CLASSPATH

You have three options for installing the PoolMan JAR file and your database driver:

(1) System-wide option: Explicitly add the poolman.jar file and your driver JAR file to all relevant CLASSPATH variables. The JAR is located in the "lib" subdirectory of this distribution, but can be moved anywhere you like. Adding the JAR is OS-specific; consult your system help files if you have trouble. In the simplest case, you will merely need to append poolman.jar to your existing CLASSPATH environmental variable value. If you are using an application server, you must add poolman.jar to the app server's CLASSPATH or include it in a packaged application, such as a WAR file, where the app server's ClassLoaders can locate it.

(2) VM-wide option: To automatically include the JAR whenever Java is used, copy the poolman.jar file and your driver JAR file to the JDK_HOME/jre/lib/ext directory of your JDK installation, where JDK_HOME is the root directory where you installed Java on the machine. This makes the poolman.jar available to every invocation of that VM. If you use multiple virtual machines, then you will need to copy poolman.jar to each of them.

(3) Application option: To include the JARs in a specific application, such as a web application, follow the packaging rules of the app. For instance, if you're using it in a web application, include the JARsin WEB-INF/lib (see the notes on using PoolMan in web apps).



2.2    STEP TWO: Install third-party JAR files

The following additional JAR's or valid subsititues for them must be available to your CLASSPATH according to one of the three options outlined abovein step one. All of these JAR's can be found in the "lib" directory of the distribution.

  •  Required: jdbc2_0-stdext.jar. This JAR supplies the official advanced JDBC interfaces that PoolMan implements. Location: lib/jdbc2_0-stdext.jar

  •  Required: jaxp.jar and crimson.jar. The XML parser is required to handle the poolman.xml configuration file. Note that most servlet/JSP engines and app servers will already include these JARs, and if they do, you should copy those versions instead of using poolman's in order to avoid rare but possible sealing violations. For instance, if you are using PoolMan in a web application, copy the servlet engine's jaxp.jar and crimson.jar into the WEB-INF/lib directory instead of using PoolMan's. Otherwise use the versions found in the PoolMan distribution. Location: lib/jaxp.jar and lib/crimson.jar

  •  Required: log4j.jar. This is the Apache/Jakarta logging library that is quickly becoming the standard for logger output and handling. PoolMan makes use of it for all logging tasks. If you choose to log to a file, the files are rotated at midnight each day by default. Location: lib/log4j.jar

  •  Optional (required if you enable JMX): jmxri.jar. This JAR provides the JMX Reference Implementation and is required for PoolMan to load its metadata when you are using the JMX-based deployer (not used by default, and configurable via poolman.xml). Location: lib/jmxri.jar

  •  Optional (required if you enable JMX) : jmxtools.jar: If you have enabled JMX and wish to run the HTML Admin Agent -- if it is listed in your poolman.xml -- you must add this JMX JAR as well. If you don't wish to run the admin agent, be sure it is not included in your poolman.xml file. Location: lib/jmxtools.jar

  •  Optional (required if you wish to build poolman from source): ant.jar: If you wish to build PoolMan from source but do not have Ant installed, you must add this JAR to your CLASSPATH. Location: lib/ant.jar

  •  Optional (required if you wish to set up the test suite): junit.jar: If you wish to run the PoolMan test suite, you must have JUnit available. Location: lib/junit.jar

  •  Optional (required if you wish to set up the test suite): poolman-testsuite.jar. This archive contains the compiled PoolMan test cases. Location: lib/poolman-testsuite.jar


2.3    STEP THREE: Configure and install poolman.xml

Configure: The "poolman.xml" file contains the XML-defined details of the pools you wish to create. A template for this file is located in the "lib" directory of the distribution: lib/poolman.xml.template. An extremely basic example file is located in the same directory: lib/poolman.xml.example. See the Resources section for definitions ofall the possible configuration elements.

Install: Be sure that the directory containing the poolman.xml file is in all relevant CLASSPATH entries. For example, if you save your poolman.xml file as /poolman/lib/poolman.xml be sure that your CLASSPATH contains the /poolman/lib directory. Do not put the file itself in the CLASSPATH, put the directory containing it in your CLASSPATH.

Web App Install: If you are embedding PoolMan within a web application, put poolman.xml in the WEB-INF/classes directory (see the notes for web app usage).

2.4    Testing the installation

Once the appropriate CLASSPATH updates have been made and the poolman.xml file configured, enter the "samples" directory and execute the following command from a prompt:

ON WINDOWS:
./runSample.bat [sql statement] [database name]

ON UNIX:
./runSample.sh [sql statement] [database name]

For example, if you have named a database "testdb" in your poolman.xml file, and "select * from users" is a valid statement for that database, and if you are on Windows, execute the following:

./runSample.bat "select * from users" "testdb"

If PoolMan has been configured correctly, results should appear for each of the sample methods. If not, the reported error should provide a hint about the problem.

You can also deploy the poolman-webapp application in your JSP/servlet engine and run various PoolMan queries and administration through it (see The PoolMan web application).


2.5    Upgrading

Upgrading from another 2.x release

Upgrading from another 2.x version is merely a matter of replacing the existing poolman.jar file with the newly-released version. Backing up the existing JAR is advised in case reverting becomes necessary. You will also need to restart any applications (including app servers) currently using the previous version of PoolMan in order to load the new classes.

Upgrading from a pre-2.x release

Upgrading from pre-2.0 versions is not as simple. PoolMan 2.0 contains a new architecture that is not generally backwards compatible with previous versions of the library. In particular, you must replace your "poolman.props" file with a valid "poolman.xml" similar to the one included in the lib subdirectory of this distribution. We have provided very basic, rough support for the old Properties files, and in many cases they will continue to work; however, it is strongly recommended that you migrate to using poolman.xml in order to take advantage of newer features.

The good news is that after setting up poolman.xml in place of poolman.props, any code that uses previous versions of PoolMan should continue to function. There is one small exception: any code that passes a Properties object to the SQLManager.getInstance(Properties p) method will fail. There is a better programmatic alternative to using that faulty legacy method -- see Programmatic Usage below. Other means of accessing SQLManager, SQLUtil, the DataSource, or the Driver should continue to function even better than before.

 



3.0    Usage

3.1    JDBC: JNDI and DataSource usage

The preferred method of accessing JDBC Connections is through a DataSource object. DataSource usage should replace the original Driver usage in all J2EE code. There are two ways to access the PoolMan DataSource implementation:

JNDI

Standard Java Naming and Directory Interface (JNDI) server practice is to bootstrap the naming provider through a jndi.properties file that specifies basic settings such as which ContextFactory to use. Your JNDI provider will include such a properties file; you need only add its directory to your CLASSPATH.

When a connection pool is created, that pool will attempt to bind itself to the naming provider defined through the jndi.properties file. When a pool is stopped, it unbinds itself.

The name under which the pool is bound is specified in the poolman.xml file, through the <jndiName> element. If no <jndiName> element is specified, then the pool's DataSource will not be bound to JNDI, though it will still be available using any of the other views.

DataSources without JNDI

A DataSource may be retrieved even without a JNDI provider, but it is done in a PoolMan-specific manner. The following code illustrates retrieval of a DataSource using this method:

DataSource ds = PoolMan.findDataSource("JNDINameOfMyPool");

The name passed to this method should be the same as that specified in the <jndiName> element of the pool's entry in the poolman.xml file.

Code Samples

EXAMPLE 1: Below is a code snip illustrating client DataSource usage:

    // This method assumes use of the rmiregistry JNDI provider
    // and standard rmi host and port info

    String host = "localhost";
    int port = 1099;

    try {

        // Get an InitialContext from Sun's RMI JNDI Provider

        // If jndi.properties file is available, simply invoke:
        // Context ctx = new InitialContext();
        // Here we assume that jndi.properties is NOT available,
        // so we use a Hashtable to set up the InitialContext:
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY,
            "com.sun.jndi.rmi.registry.RegistryContextFactory");
        env.put(Context.PROVIDER_URL, "rmi://" + host + ":" + port);
        env.put("java.naming.rmi.security.manager", "1");
        Context ctx = new InitialContext(env);

        // Get a Connection
        DataSource ds = (DataSource) ctx.lookup("JNDINameOfMyPool");
        Connection con = null;
        try {

            con = ds.getConnection();
            // ... etc. ...

        } catch (SQLException sqle) { sqle.printStackTrace(); }
        finally {
            // "closing" the Connection closes its handle and
            // returns the actual connection to its pool:
            try { con.close(); catch (SQLException csqle) {}
        }


EXAMPLE 2: The following code snip illustrates retrieving a DataSource locally, without using JNDI:

    // Get a Connection
    DataSource ds = PoolMan.findDataSource("JNDINameOfMyPool");
    Connection con = null;
    try {

        con = ds.getConnection();
        // ... etc. ...

    } catch (SQLException sqle) { sqle.printStackTrace(); }
    finally {
        // "closing" the Connection closes its handle and
        // returns the actual connection to its pool:
        try { con.close(); catch (SQLException csqle) {}
    }



3.2    JDBC: Driver usage

The original standard means of accessing JDBC connections is through the local DriverManager, which locates JDBC Drivers appropriate for a particular connection request. PoolMan does contain a virtual JDBC Driver that wraps underlying drivers, so it can be accessed in this way.

Here is a relevant code snip detailing this approach. It should be familiar, as it is simply an example of PoolMan plugged into the standard pre-DataSource JDBC API. The important element is the Driver name ("com.codestudio.sql.PoolMan") and the URL passed to the DriverManager ("jdbc:poolman://" + database name as specified in the <dbname> element of poolman.xml).

    try {
        // load the PoolMan JDBC Driver
        Class.forName("com.codestudio.sql.PoolMan").newInstance();
    } catch (Exception ex) {
            System.out.println("Could Not Find the PoolMan Driver. "
            "Is poolman.jar in your CLASSPATH?");
    }

    Connection con = null;
    try {

        // establish a Connection to the database with
        // <dbname>testdb</dbname>
        //in the poolman.xml file
        con = DriverManager.getConnection("jdbc:poolman://testdb");

        // Use the Connection to create Statements and do JDBC work
        Statement stm = con.createStatement();
        // ... etc. ...

    } catch (SQLException sqle) { sqle.printStackTrace(); }
    finally {
        try { con.close(); } catch (SQLException csqle) {}
    }


NOTE: "closing" the Connection merely closes the handle to that Connection, while the actual PooledConnection is simply returned to its pool.

If a user forgets to close a Connection, Statement, or ResultSet, PoolMan will eventually do it automatically. In other words, pooled objects drift back to their pools after a configurable timeout value defined in the poolman.xml <userTimeout> element.


3.3    JDBC: Advanced scrollable, updatable ResultSet usage

Beginning with JDBC 2.0, we gained the ability to scroll backwards and forwards through ResultSets and to update and delete data in them. PoolMan implements this behavior on top of any underlying driver. Refer to the JDBC specification for details (see Resources for recommended tutorials).

The following code snips illustrate portions of this advanced behavior.

    // The examples assume that you have retrieved a Connection "con"...

    // EXAMPLE 1: Updating an existing row in the ResultSet:
    stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                              ResultSet.CONCUR_UPDATABLE);
    res = stm.executeQuery(selectSQL);
    res.first();
    res.updateInt(1, 5);
    res.updateString(2, "updateRowTest");
    res.updateRow();
    // the row is now updated, both in the ResultSet and in the database

    // EXAMPLE 2: Inserting a new row into the ResultSet
    stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                              ResultSet.CONCUR_UPDATABLE);
    res = stm.executeQuery(selectSQL);
    res.moveToInsertRow();
    res.updateInt(1, 518);
    res.updateString(2, "insertRowTest");
    res.insertRow();
    res.last();
    // the new row has been inserted into both the ResultSet
    // and the database

    // EXAMPLE 3: Deleting a row from the ResultSet
    stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                              ResultSet.CONCUR_UPDATABLE);
    res = stm.executeQuery("select * from customers where id=518");
    res.first();
    res.deleteRow();

Note that this advanced ResultSet usage succeeds regardless of whether the ResultSet has been obtained from the query/ResultSet cache or whether it is a newborn live ResultSet.


3.4    Non-standard: SQLManager usage

A popular but non-standard approach to implementing PoolMan JDBC pools is to use the SQLManager. The PoolMan architecture calls upon an abstract PoolManager to keep track of a set of pools; SQLManager is the JDBC-specific subclass of PoolManager. It polices JDBCPools and locates them for drivers and client callers. Here is an example of its usage:

    // The database name parameter maches that specified
    // in the <dbname> element in poolman.xml
    SQLManager manager = SQLManager.getInstance();
    Connection con = SQLManager.requestConnection("myDatabaseName");

You can also access the pools themselves through SQLManager, which is useful in gaining metrics information:

    SQLManager manager = SQLManager.getInstance();
    JDBCPool jpool = (JDBCPool) SQLManager.requestPool("myDatabaseName");     System.out.println("Number of connections currently in use in pool" +
                       jpool.getPoolname() + ": " +
                       jpool.numCheckedOutObjects());

    Connection con = jpool.requestConnection();
    // ... etc. ...

    // now return the object instead of waiting
    // for it to return by itself
    SQLManager.returnConnection("myDatabaseName", con);

    // note that invoking con.close() would have had EXACTLY
    // the same effect as returning the Connection to the SQLManager


3.5    Non-standard and deprecated: SQLUtil usage

A simple but completely non-standard utility for executing SQL statements can be found in the com.codestudio.util.SQLUtil Class. This utility does not use the JDBC API at all. It returns a PoolMan Result object, which can be iterated over to return Hashtable objects that represent a particular database cell. Here's a code snip of its usage:

    SQLResult result = SQLUtil.getInstance().execute("dbname", "select * from users");
    if (result.size() == 0)
        System.out.println("\tEmpty results");

    while (result.hasNext()) {
        Hashtable row = result.next();
        // in this "row", all keys are columns and all values are data
        for (Enumeration enum=row.keys(); enum.hasMoreElements(); ) {
            String column_name = enum.nextElement().toString();
            String data_field = row.get(column_name).toString();
            System.out.print("\t" + column_name + ": " + data_field);
        }
        System.out.print("\n");
    }


3.6    About the query/ResultSet cache

PoolMan maintains a cache of ResultSet objects mapped to specific "select" queries for each active JDBCPool. Configured in the poolman.xml file, this cache can be enabled/disabled for each pool, its maximum size configured, and the intervals at which it updates itself with the underlying database can be established.

Each time a "select" statement is issued with the cache enabled, the existing ResultSet for that query will be returned from memory. If there is no existing ResultSet for that query, then the ResultSet will be cached after the query executes and returns a new one. If the cache has reached its maximum size, no further queries can be added. Only Statement objects are examined by the cache, PreparedStatement and CallableStatement objects are ignored.

The cache updates itself with the underlying database asynchronously. This means that periodically (in an interval defined by the <cacheRefreshInterval> value in poolman.xml, which defines the value in seconds) the cache will reload its ResultSet objects from the underlying database regardless of user requests. Important: If data has been updated, the cache will not reflect the new values until it refreshes itself. Adjusting the refresh interval for the cache essentially determines the isolation level of select queries; for this reason, use of the cache is not appropriate for real-time or high-availability data systems, and should be disabled in such instances. Note that it is disabled by default.

It provides enormous boosts to typical web sites, however. Setting the refresh interval to a fairly small unit decreases the delay between updates to data and the visibility of updates in the cache, and that parameter can often be adjusted to suit specific use cases. In read-only or mostly-read-only systems, the cache is ideal, and the refresh interval can typically be increased to avoid frequent database traffic between the cache and the datastore.


3.7    Pooling non-JDBC objects: The GenericPool

Objects of any Java Class type with a default no-args constructor may be pooled through the Generic pool utility. These pools can be created and configured through poolman.xml, eliminating the need to write any new code to create a custom object pool.

The semantics involved in accessing the pooled objects are the same as those of the SQLManager metioned above. A GenericPoolManager subclasses PoolManager in order to provide the necessary functionality. Here is a usage example:

    // Assumes there is a GenericPool in poolman.xml with
    // a name "mypool" of type "java.lang.StringBuffer":

    GenericPoolManager manager = GenericPoolManager.getInstance();

    StringBuffer sb = (StringBuffer) manager.requestObject("mypool");

    // use the object however you wish...
    sb.append("test");
    System.out.println(sb.toString());

    // return it to its manager
    manager.returnObject(sb);

Again, generic pooled objects must have a default no-args constructor.


3.8    Programmatic usage: alternatives to poolman.xml

It is possible to programmatically create JDBC and Generic pools instead of configuring them through poolman.xml metadata.

All pools are described by a JavaBean-like metadata object that is created before the actual pool, so the process involves instantiating a new instance of this metadata object and configuring it, then passing that metadata object to a pool constructor.

Here's an illustrative code snip:


    // First create the metadata representation of a pool
    // this is usually created automatically using poolman.xml
    // but here we do it programmatically:
    JDBCPoolMetaData meta = new JDBCPoolMetaData();

    // set attributes programmatically
    meta.setDbname("mydatabase");
    meta.setDriver("some.driver");
    meta.setURL("jdbc:someurl://somehost:1212");
    meta.setUserName("someguy");
    meta.setPassword("somepasword");
    meta.setJNDIName("mydatabase");
    meta.setMaximumSize(20);
    // set any other desired attributes available in poolman.xml

    // now create the pool using that metadata
    JDBCPool newPool = new JDBCPool(meta);
    SQLManager.addPool(newPool);
    // The new pool is now available


Example of programatically creating a Generic pool:

    GenericPoolMetaData meta = new GenericPoolMetaData();

    meta.setName("mypool");
    meta.setObjectType("com.mypackage.beans.MyBean");
    meta.setLogFile("/home/log.txt");
    // set any other desired generic object pool attributes...

    // now create the new pool using that metadata
    GenericPool newPool = new GenericPool(meta);
    // add it to the manager
    GenericPoolManager.addPool(newPool.getPoolname(), newPool);


Example of creating a new "clone" JDBC pool with different username/password information:

    public void createPool(String username, String password) {

      JDBCPool jpool = (JDBCPool) SQLManager.getInstance().getPool("testdb");

      JDBCPoolMetaData meta = new JDBCPoolMetaData();
      meta.setDriver(jpool.getDriver());
      meta.setURL(jpool.getURL());
      meta.setUserName(username);
      meta.setPassword(password);
      meta.setName(jpool.getPoolname() + "-" + username);
      meta.setJNDIName(jpool.getPoolname() + "-" + username);

      JDBCPool newPool = new JDBCPool(meta);
      SQLManager.getInstance().addPool(newPool.getPoolname(), newPool);

      // the new pool is now available through the Driver, SQLManager and SQLUtil
      // and since we supplied a JNDIName in the metadata, it is also bound to JNDI


    }


3.9    Starting PoolMan as a server

Typically, PoolMan does not create its pools and make them available to calling clients until it receives its first request. It is possible to avoid using this lazy initialization, however. To create the pools explicitly before a user request, essentially starting PoolMan's underlying embedded server as a first-class server instance, invoke the following line of code:

PoolMan.start();




4.0    Administration

4.1    Enabling and Disabling JMX

The JMX reference implementation can cause ClassLoader conflicts in some deployment scenarios. We have created a simple workaround (a patched ClassLoader) but the problem persists in certain rare instances. Furthermore, JMX and its use of reflection is unnecessary overhead in use cases that don't take advantage of its management infrastructure. So we have made it optional and it is disabled by default.

To enable JMX, set the <management-mode> param in poolman.xml to "jmx" as in the following example:

<management-mode>jmx</management-mode>

If JMX is not enabled, then local creation and deployment of pools will be used. The bootstrap sequence occurs when one of the API views (the Driver, DataSource, or SQLManager) invoke PoolManBootstrap, which then creates a PoolManConfiguration object using the config file. The PoolManConfiguration object uses a ConfigParser to parse the XML file into usable properties. The bootstrap then either invokes the LocalPoolDeployer or JMXPoolDeployer, passing the PoolManConfiguration object as a parameter. Either way, a set of object pools will be created; the only difference is the degree of manageability of the pools at runtime. JMX offers such management, local deployment offers none at all. PoolManBootstrap springs into action only once during the lifetime of the VM in which PoolMan is running; after being invoked, it is no longer used or needed.

4.2    JMX in PoolMan

The Java Management Extension (JMX) is a J2EE-optional package aimed at providing a standard management API for all manageable components. It uses the concept of MBeans, or managed beans, to define any manageable resource in a JavaBeans-like manner. In the simplest scenario, every JMX MBean provides get/set methods for exposed attributes.

A layer removed from JMX, PoolMan makes use of a generic JavaBean metadata architecture; every pool is defined by a metadata object. It is this metadata object that JMX manages for PoolMan -- one MBean per PoolMan metadata object. Thus JMX doesn't directly administer pools, it administers the metadata for the pools. This allows other non-JMX management systems to be plugged into the PoolMan library.

When PoolMan loads its pools, it calls upon a bootstrap service to parse the poolman.xml file and create a set of MBeans for the entries -- one MBean per pool. The MBeans have methods that mirror the PoolMan metadata objects. During the parsing of the XML, the attributes are automatically set on the MBean and therefore on the underlying metadata object. After setting all the initial values, a pool is constructed; the pool constructor requires the metadata object that JMX has configured.

Subsequent changes in any MBean attribute result in a change in the pool's metadata, and after a restart of the MBean service, the changes will take effect.


4.3    The HTML JMX agent

The JMX Reference Implementation includes an HTML agent that can be used to administer MBeans. In PoolMan, this agent is configured like the pools -- through poolman.xml.

Once PoolMan has loaded its pools -- either after the first caller request or after invoking the PoolMan.start() method -- the HTML agent will be available. The port on which it runs is a configurable attribute in poolman.xml, but assuming the default port of 8082 on your localhost, you can administer PoolMan after its pools have loaded by browsing to the following URL:

http://localhost:8082

The agent will NOT be available until PoolMan has been started (you will receive a 404 error if it has not yet started). For more information on JMX and this HTML agent, please see Resources.

Important Notes:

  1. You should comment-out the HTML agent entries in poolman.xml before deploying PoolMan to a production environment! Database passwords are exposed through the HTML agent, and represent an obvious gaping security risk.
  2. Note that changes made through the HTML agent will not propogate to the poolman.xml file! This functionality may be available in a forthcoming release, but for the time being you must manually edit poolman.xml file to make persistent, long-lived changes.



5.0    Integration: Embeddable PoolMan

In general, PoolMan is automatically embeddable due to its lazy-initialization architecture. It will be started in the same process as its first invoking caller. Assuming the parent product includes the necessary PoolMan entries and dependencies in its CLASSPATH settings (detailed in Installation), PoolMan will be accessible as an embedded component regardless of which view -- DataSource, Driver, PoolManBean, SQLManager or GenericPoolManager -- is used.

The following product-specific notes are offered for further guidance and assistance:

5.1    Tomcat and Web Applications (WARs)

There are two approaches to adding PoolMan to Tomcat: (1) within a web application and (2) server-wide.

Web Applications (Tomcat 4.0 and better): Add your poolman.xml file to the WEB-INF/classes directory of your web application, and add the poolman.jar and all other required jars -- at minimum, this means crimson.jar, jaxp.jar, jdbc2_0-stdext.jar, log4j.jar, and your database driver JAR -- to your WEB-INF/lib directory.

Server-wide (Earlier versions of Tomcat): Establish the System CLASSPATH according to the above guidelines and be sure that the System Classpath is available to Tomcat. In some cases, this requires explicitly adding the System classpath to Tomcat by adding it to tomcat.bat. On older version of Tomcat, an easy way to make sure that all the necessary JARs are in the Tomcat CLASSPATH is to copy all of them to the TOMCAT_HOME/lib directory (tested with jakarta-tomcat-3.2.1). Even with this method, you will still need to be sure that your poolman.xml file is in the System's CLASSPATH.

To test the installation, deploy the PoolMan web application (poolman-webapp) into Tomcat.


5.2    JRun

Do not deploy PoolMan as a DataSource into the JRun Management Console. That console wraps drivers in a JRun-specific pooling mechanism. Instead, update JRun's server CLASSPATH settings to include the necessary JARs mentioned in the Installation section above; also include the directory containing the poolman.xml file in the CLASSPATH of each JRun server you have deployed.

The simplest way to do this is to simply copy the JARs and the poolman.xml file into the JRUN_HOME/lib/ext directory of your JRun distribution. Alternatively, select the Java settings option from the JMC for each JRun server you have deployed, and update the CLASSPATH to include the PoolMan requirements.

To test the installation, deploy the PoolMan web application (poolman-webapp) into JRun.


5.3    Struts

Do NOT simply add PoolMan to the <data-source> tag in a struts-config.xml file. Struts, like many frameworks, provides a simplistic DataSource that cannot take advantage of underlying drivers (such as the PoolMan virtual driver) that implement the JDBC 2.0 interfaces. That is, the Struts DataSource implementation does not take into account whether the Driver implements PooledConnection or XAConnection.

After verifying that PoolMan has been installed properly into your application server, the simplest means of using PoolMan from within Struts is to update your code:

// Replace the following Struts API:
javax.sql.DataSource ds = servlet.findDataSource(null);

// With this PoolMan API:
javax.sql.DataSource ds = PoolMan.findDataSource(dbname);

// Or, if you want the default DataSource (always the first one listed
// in poolman.xml), you don't need its name:
javax.sql.DataSource ds = PoolMan.findDataSource();

To package PoolMan with a Struts application, add poolman.jar, crimson.jar, jaxp.jar, jdbc2_0-stdext.jar, log4j.jar and struts.jar to the web app's lib directory and add the poolman.xml file to the web app's classes directory before collecting the application in WAR format.

5.4   Velocity

To package PoolMan with a Velocity-based web application, place the following files in the WEB-INF/lib directory: poolman.jar, jdbc2_0-stdext.jar, jaxp.jar, crimson.jar, log4j.jar and velocity-1.1.jar. Also place your configured poolman.xml file in your WEB-INF/classes directory, and place your database-specific database driver in the WEB-INF-lib directory. A valid Velocity JAR can be found in the PoolMan dustribution's lib directory.



6.0    Resources

 

6.1    The poolman.xml file in detail

The following tables describe each of the parameters open to configuration in the poolman.xml file.

DataSource Connection Pool Attributes

<datasource>

XML Attribute
Description
Required
<dbname>
The name of the JDBC Connection pool
yes
<jndiName>
The name used to bind the DataSource to JNDI
yes
<driver>
The Class of the underlying Driver implementation

EXAMPLE: org.postgresql.Driver

yes
<url>
The URL that describes the database (vendor-specific)

EXAMPLE: jdbc:postgresql://localhost:5432/empire

yes
<username>
A user name valid for the database
yes
<password>
The corresponding password valid for the user name
yes
<nativeResults>
Whether to use the PoolMan cachable, scrollable, updatable ResultSet implementation or use the ResultSet returned by the underlying driver.

DEFAULT: false

no
<poolPreparedStatements>
Whether to pool PreparedStatements instead of recreating them with each invocation.

DEFAULT: true

no
<txIsolationLevel>

The transaction isolation level that should be set on each Connection object as it is created. The value of this element must be one of the following: NONE, READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, SERIALIZABLE. For further explanation of these values, see the Sun javadocs for the isolation level fields in the java.sql.Connection class. These values are not case-sensitive.

DEFAULT: READ_COMMITTED

no
<initialConnections>
The number of connections to create when the pool is instantiated.

DEFAULT: 1

no
<minimumSize>
The minimum number of objects to retain in the pool.

DEFAULT: 0

no
<maximumSize>
The maximum number of objects that can possibly be maintained in the pool.

DEFAULT: Integer.MAX_VALUE

no
<maximumSoft>
If the maximum size of a pool is reached but requests are still waiting on objects, PoolMan will create new emergency objects if this value is set to true. This will temporarily increase the size of the pool, but the pool will shrink back down to acceptable size automatically when the skimmer activates. If this value is set to false, the requests will sit and wait until an object is available.

DEFAULT: true

no
<connectionTimeout>

The connection will be destroyed after living for this many seconds.

DEFAULT: 1200 seconds (20 minutes)

no
<userTimeout>

The length of time in seconds that user has to keep a connection before it is automatically returned to the pool.

DEFAULT: 20 seconds

no
<skimmerFrequency>

The length of time the pool skimmer waits between reap cycles. Each reap cycle involves evaluating all connections (both checked in and checked out) to determine whether to automatically return them to the pool and whether to destroy them if they have timed out.

DEFAULT: 660 seconds (11 minutes)

no
<shrinkBy>

Each time the pool is sized down by the skimmer, this value determines the maximum number of objects that can be removed from it in any one reap cycle. It prevents backing off the pool too quickly at peak times.

DEFAULT: 5

no
<validationQuery>

Each time a connection is requested, it is validated by the pool to ensure that it is active. If this element exists, then it is treated as a SQL query that is executed on the raw Connection (bypassing the cache) in order to ensure the Connection is valid. If this element does NOT exist (meaning it is null), then the Connection.isClosed() method is used to validate it instead. The Connection.isClosed() method is unreliable on some drivers (such as some Oracle drivers), which makes this element necessary for those drivers.

DEFAULT: null

no
<logFile>

The PATH to a file that this pool will append logging information to.

DEFAULT: System.out

no
<debugging>

Whether or not verbose logging information will be printed.

DEFAULT: false

no
<cacheEnabled>

Whether or not the query/ResultSet cache is enabled

DEFAULT: false

no
<cacheSize>

The maximum number of query/ResultSet pairs the cache can contain.

DEFAULT: 5

no
<cacheRefreshInterval>

The interval, in seconds, between the cache's reloading of its ResultSets from the database.

DEFAULT: 30 seconds

no
<initialPoolSQL>

A SQL String to be executed as soon as the pool is instantiated

DEFAULT: none

no
<initialConnectionSQL>

A SQL String to be executed every time a Connection is pulled from the pool.

DEFAULT: none

no
<removeOnExceptions>

Whether a SQLException even causes the Connection on which it occurred to be removed from the pool.

DEFAULT: false

no


Generic Object Pool Attributes

<objectPool>

XML Attribute
Description
Required
<name>
The name of the pool.
yes
<objectType>
The Class type of the objects pooled (must have a default no-args constructor).
yes
<initialObjects>

The number of initial objects instances to create upon pool instantiation.

DEFAULT: 1

no
<minimumSize>

The minimum number of objects to maintain in this pool.

DEFAULT: 0

no
<maximumSize>

The maximum number of objects to maintain at any one time in this pool.

DEFAULT: Integer.MAX_VALUE

no
<maximumSoft>
If the maximum size of a pool is reached but requests are still waiting on objects, PoolMan will create new emergency objects if this value is set to true. This will temporarily increase the size of the pool, but the pool will shrink back down to acceptable size automatically when the skimmer activates. If this value is set to false, the requests will sit and wait until an object is available.

DEFAULT: true

no
<objectTimeout>

The length of time, in seconds, that each object has to live before being destroyed and removed from the pool.

DEFAULT: 1200 seconds (20 minutes)

no
<userTimeout>

The lenth of time, in seconds, that a client has to keep an object before it can automatically returned to its pool.

DEFAULT: 20 seconds

no
<skimmerFrequency>

The length of time the pool skimmer waits between reap cycles. Each reap cycle involves evaluating all objects (both checked in and checked out) to determine whether to automatically return them to the pool and whether to destroy them if they have timed out.

DEFAULT: 660 seconds (11 minutes)

no
<shrinkBy>

Each time the pool is sized down by the skimmer, this value determines the maximum number of objects that can be removed from it in any one reap cycle. It prevents backing off the pool too quickly at peak times.

DEFAULT: 5

no
<logFile>

The PATH to a file that this pool will append logging information to.

DEFAULT: System.out

no
<debugging>

Whether or not verbose logging information will be printed.

DEFAULT: false

no


Administrative JMX Agent Attributes

<admin-agent>

<class>

The Class of the HTML Agent implementation

yes
<name>

The JMX ObjectName of the agent

yes
<maxClients>

The maximum number of simultaneous browser-based users the Agent will support

yes
<port>

The port number on which the HTML Agent will run

yes


6.2    The PoolMan web application

The PoolMan web application, packaged in the poolman.war file located in the apps directory, contains a JSP-based database client and a link to the JMX HTML agent. It can be deployed on any JSP/Servlet engine. Instructions for two popular such engines follow:

To install the Web App on JRun:

  •  Start the Admin server
  •  Browse to the web-based JRun Management Console -- the default location for this is http://[your JRun host]:8000
  •  Click 'Deploy WAR'
  •  Browse to the poolman.war file
  •  Supply the mapping information requested, mapping the WAR to "/poolman"
  •  Restart the default server
  •  Point your browser to http://[your JRun host]/poolman

To install the Web App on Tomcat:

  •  Copy the poolman.war file to the $TOMCAT_HOME/webapps/ directory
  •  Restart Tomcat.
  •  Point your browser to http://[Tomcat Host]/poolman -- EXAMPLE: If you have installed Tomcat on your local machine and used its default port, browse to: http://localhost:8080/poolman

6.3    Recommended tutorials and relevant specifications


JDBC
Specifications: JDBC 1.2, JDBC 2.1 Update, JDBC 3.0 (Proposed Spec)
Tutorials: http://www.javasoft.com/products/jdbc/learning.html

JNDI
Specification: API Spec (for users of JNDI), SPI Spec (for creators of JNDI providers)
Tutorials: Sun's Online Tutorial

JMX
Specification: JMX Zip Collection
Site: JMX Web Site