ramya bhaava chaitanyam

JDBC Connection Pooling Nirvana

javax.sql.PooledConnection is badly named. Instances of this class are merely "poolable" as opposed to "pooled". What makes a connection "poolable" as per the jdbc2 spec? If the connection object raises appropriate java bean events that a pool manager can listen to, then such a connection is "poolable".

Similarly, javax.sql.ConnectionPoolDataSource is also badly named. It is merely a factory for "poolable" connection objects (i.e., javax.sql.PooledConnection instances). It need not manage a cache of such connections. As the term "pool" has been so badly messed up by the jdbc2 spec, I shall herefater use the term "cache" when referring to a container of multiple "poolable" connections.

Oracle provides a non-caching implementation of javax.sql.ConnectionPoolDataSource in oracle.jdbc.pool.OracleConnectionPoolDataSource. The cache itself is managed by another class, oracle.jdbc.pool.OracleConnectionCacheImpl. The latter also implements the javax.sql.DataSource interface so that you can use it transparently wherever someone uses a javax.sql.DataSource. Internally of course, it is managing a cache of javax.sql.PooledConnection instances.

This seems to be a fairly common technique. In fact, http://jakarta.apache.org/commons/dbcp/ provides a Jdbc2PoolDataSource that like oracle.jdbc.pool.OracleConnectionCacheImpl, can be used transparently wherever a javax.sql.DataSource is needed. Use it if the jdbc driver for your database does not provide a caching implementation of javax.sql.DataSource. If you do use it, you should provide it with a factory for creating javax.sql.PooledConnection instances. Use your jdbc driver's implementation of javax.sql.ConnectionPoolDataSource for this purpose by passing it as a java bean property of Jdbc2PoolDataSource named "ConnectionPoolDataSource". We could have done the same for Oracle as well (instead of using oracle.jdbc.pool.OracleConnectionCacheImpl) but the latest oracle driver (ojdbc14.jar that works with 8i and above) does not seem to like anyone calling setReadOnly(trueOrFalse) (and that's something Jdbc2PoolDataSource does).

Here's a neat little trick that externalizes our choice of javax.sql.DataSource - be it a simple factory of poolable/non-poolable connections or be it a cache of poolable connections. XMLEncoder and XMLDecoder classes from the java.beans package provide a standard way of creating and persisting a bean. As JDBC specifies that javax.sql.DataSource implementations should use java beans property framework, we can load a choosen javax.sql.DataSource instance from a XML file. Here's an example that uses Oracle's cache implementation.

<?xml version="1.0" encoding="UTF-8"?>
<java version="1.0" class="java.beans.XMLDecoder">
  <object class="oracle.jdbc.pool.OracleConnectionCacheImpl">

    <!-- standard datasource properties -->
    <void property="user"><string>username</string></void>
    <void property="password"><string>password</string></void>
    <void property="serverName"><string>dbserver.domain.com</string></void>
    <void property="portNumber"><int>1521</int></void>            
    <void property="databaseName"><string>oracleSID</string></void>

    <!-- oracle specific properties -->
    <void property="driverType"><string>thin</string></void>
    <!-- enables statement caching. the property name in
         oracle's documentation is wrongly mentioned as
         implicitStatementCachingEnabled. -->
    <void property="implicitCachingEnabled"><boolean>true</boolean></void>

    <!-- in the dynamic scheme, under high loads, new pooled
    connections above and beyond the maximum limit are
    created as needed, but each one is automatically closed
    and freed as soon as the logical connection instance
    that it provided is no longer in use (as opposed to the
    normal scenario when a pooled connection instance is
    finished being used, where it is returned to the
    available cache). -->
    <void property="cacheScheme">
      <string>dynamic_scheme</string>
    </void>    
    <void property="MaxLimit"><int>5</int></void>
    <void property="MinLimit"><int>1</int></void>
  </object>
</java>

Here's another example that uses org.apache.commons.dbcp.jdbc2pool.Jdbc2PoolDataSource for caching Oracle's poolable connections.

<java version="1.0" class="java.beans.XMLDecoder">
  <object class="org.apache.commons.dbcp.jdbc2pool.Jdbc2PoolDataSource">
    <void property="ConnectionPoolDataSource">
      <object class="oracle.jdbc.pool.OracleConnectionPoolDataSource">
        <void property="user"><string>username</string></void>
        <void property="password"><string>password</string></void>
        <void property="serverName"><string>dbserver.domain.com</string></void>
        <void property="portNumber"><int>1521</int></void>            
        <void property="databaseName"><string>oracleSID</string></void>
        <void property="driverType"><string>thin</string></void>
        <void property="implicitCachingEnabled"><boolean>true</boolean></void>
      </object>
    </void>
    <void property="defaultMaxActive"><int>5</int></void>
    <void property="defaultMaxIdle"><int>5</int></void>
    <void property="defaultMaxWait"><int>300</int></void>
    <void property="defaultAutoCommit"><boolean>false</boolean></void>
  </object>
</java>

What can be neater! If we extend java.io.PrintWriter to publish to a configurable org.apache.log4j category, we can also say,

     <void property="logWriter">
       <object class="com.aalayance.logging.Log4jPrintWriter>
         <void property="category"><string>lib.jdbc</string></void>
       </object>
     </void>

Don't you like it? Let me know.

Posted by prasad at June 16, 2003 01:09 AM