1. Abstract
The JDBC subsystem of WebLogic Server underwent a major rewrite in the 8.1 Release:

• Connection Pool implementation was rewritten from scratch
• New Dynamic Proxy generation technology was used for creating Wrapper objects
• Prepared Statement Cache implementation was rewritten from scratch
• JDBC Assistant was added to the Administration Console to ease connection pool and data source configuration

Many outstanding customer enhancement requests were also addressed.

This document describes the many new and enhanced features now available in WebLogic Server JDBC.

2. Introduction
JDBC is one of the longest-lived and arguably the most commonly used J2EE API in WebLogic Server. Over the course of the last few releases, customers have requested new features and enhancements to existing features. In WebLogic Server 8.1, the WebLogic engineering team made major changes in the JDBC subsystem to improve performance and to address many of these outstanding customer requests:

• The Connection Pool implementation was rewritten from scratch
• A new Dynamic Proxy generation technology was used for creating Wrapper Objects
• The Prepared Statement Cache implementation was rewritten from scratch
• The JDBC Assistant was added to the Administration Console to help configure connection pools and data sources

As a result, powerful new and enhanced features are now available to applications that use WebLogic Server JDBC.

Connection Pool
Connection Pools now include the following features:

• Connection objects leaked by an application can be automatically detected and reclaimed by the connection pool.
• Connection pools can be created even if the DBMS is unavailable.
• Connection requests can wait for a connection. You can configure the following options:
• • How long a connection request can wait while blocking a thread before timing out
  • How many connection requests can concurrently wait to get a connection, after which additional concurrent requests are rejected
• SQL statements to execute on a connection when it is created or refreshed
• Dynamic configuration – Most connection pool attributes (such as maximum capacity) are now dynamically reconfigurable. You can change configuration options without having to restart WebLogic Server or redeploying the connection pool for the new settings to take effect.
• WebLogic Server-specific extensions to java.sql.SQLException to indicate specific error conditions when connection requests fail.
• Connection pool Refresh functionality issues have been resolved:
• • The entire connection pool is no longer locked when connections are being refreshed
  • You can configure how many connections get taken out of the pool to be refreshed

Wrapper Objects

• Applications now have access to methods in ANY public interface implemented by the underlying driver-level object. It is no longer necessary to cast the object to WebLogic Server-specific interfaces such as weblogic.jdbc.vendor.oracle.OracleConnection, and you are no longer limited to methods defined in such WebLogic Server-specific interfaces.
• Applications now have access to the physical connection object
• Objects are not wrapped with RMI-wrappers if application code is executing in the same JVM as the connection pool

Prepared Statement Cache

• The prepared statement cache is now dynamic. It uses a Least Recently Used algorithm to replace currently cached statements.
• You can clear or disable the cache when desired
• The prepared statement cache is implemented as a single cache for both XA-enabled and non-XA-enabled JDBC drivers. In previous releases, there were separate implementations and configurations for XA and non-XA JDBC drivers.

JDBC Assistant

• Configuration wizard to help configure connection pools and data sources
• Contains a tunable knowledgebase of vendor-specific JDBC driver-related configuration information (URL syntax, driver class name, etc.)

3. Background
J2EE Applications typically use the JDBC API to access data stored in relational databases. It enables them to execute SQL queries independent of database vendor-specific syntax and protocol, while also providing mechanisms to access vendor extensions to the SQL standard.

JDBC drivers provide connectivity to the DBMS, typically by implementing database vendor-specific protocol. Applications get connections to the DBMS by either loading JDBC drivers directly into their JVM or from connection pools created and maintained by middle-tier application servers such as WebLogic Server.

Connection Pools
A connection pool is a named group of identical JDBC connections to a database that are created when the connection pool is deployed, either at WebLogic Server startup or dynamically during run time. An application "borrows" a connection from the pool, uses it and returns it to the pool by closing it.

Connection pools provide numerous performance and application design advantages:

• Using connection pools is far more efficient than creating a new connection for each client each time it needs to access the database.
• You do not need to hard-code details such as the DBMS username and password in your application.
• You can limit the number of connections to your DBMS. This can be useful for managing licensing restrictions on the number of connections to your DBMS.
• You can change the DBMS you are using without changing your application code.

Wrapper objects
When application code executes any JDBC API calls (such as getting a connection from a pool, or executing a statement and getting back a result set), WebLogic Server does not simply pass the object returned by the JDBC driver back to the application code. It first wraps the object with a WebLogic Server-specific "wrapper" object and then returns the wrapper object to the application.

This is done to satisfy the following contracts between WebLogic Server and the application deployed on top of it:

• Connection Management
• Transaction Management
• WebLogic Server value additions

Connection Management
Connection objects represent physical connections to the DBMS. These are expensive and scarce resources, so WebLogic Server assumes responsibility for pooling these objects. Hence, WebLogic Server JDBC needs to intercept all calls that the application makes to get or close these objects to correctly manage their pooling.

Transaction Management
WebLogic Server acts as the Transaction Manager for global (XA) transactions. Thus, it needs to intercept all calls that the application makes to ensure that all database operations are performed within correctly demarcated transaction boundaries so as to preserve the ACID properties of XA transactions.

WebLogic Server value additions
WebLogic Server value additions like Prepared Statement caching and JDBC access over RMI from standalone clients requires this interception of application calls.

Prepared Statement Cache
When an application uses a prepared statement or callable statement, there is considerable processing overhead for the communication between the application server and the DBMS and on the DBMS itself. To minimize the processing costs, WebLogic Server can cache prepared and callable statements used by the application. When the application calls any of the statements stored in the cache, WebLogic Server reuses the statement stored in the cache.

Reusing prepared and callable statements also reduces CPU usage on the DBMS, improving performance for the current statement and leaving CPU cycles for other tasks.

4. Connection Pool
The new JDBC connection pool in WebLogic Server 8.1 is built on top of a new generic resource pool implementation that is shared across multiple subsystems, including JDBC, Connector, and JMS. It now offers many new and enhanced features:

InactiveConnectionTimeoutSeconds
You can use this feature to configure the number of seconds of inactivity after which a reserved connection will automatically be reclaimed by the connection pool. This will help you circumvent the problem of application code leaking connections and eventually emptying the connection pool of all connections.

To enable this feature, set the attribute "InactiveConnectionTimeoutSeconds" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean.

ConnectionCreationRetryFrequencySeconds
You can use this feature to configure the connection pool to automatically retry creating connections to the DBMS if the DBMS is currently not available or accessible. This also enables you to create and deploy the connection pool even if the DBMS is currently not available or accessible.

Set the attribute "ConnectionsCreationRetryFrequencySeconds" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

ConnectionReserveTimeoutSeconds
You can use this feature to control how long a request to get a connection from a connection pool waits for a connection while blocking a thread before being timing out.

Set the attribute "ConnectionReserveTimeoutSeconds" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

HighestNumWaiters
You can use this feature to control the maximum number of concurrent requests to get a connection from a connection pool that will be enqueued, after which additional concurrent requests will be rejected.

Set the attribute "HighestNumWaiters" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

HighestNumUnavailable
You can use this feature to control the maximum number of connections in the pool that can be made unavailable (to applications) for the purpose of testing and (if required) refreshing the connection. This resolves the problem with the older pool implementation where the entire pool was locked while its connections were being tested and refreshed.

Set the attribute "HighestNumUnavailable" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

TestConnectionsOnCreate
You can use this feature to enable the testing of newly created connections. Every time a connection is created or refreshed, it is tested using the SQL query specified ion in the connection pool attribute "TestTableName".

Set the attribute "TestConnectionsOnCreate" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

InitSQL
You can use this feature to configure SQL statements to execute on a connection when it is created or refreshed. This can be used to do one-time initialization of physical connections as they get created.

Set the attribute "InitSQL" on the configuration MBean weblogic.management.configuration.JDBCConnectionPoolMBean to enable this feature.

Dynamic reconfiguration
Most connection pool configuration attributes are now dynamically tunable. You do not need to restart WebLogic Server or redeploy the connection pool for the new attribute values to take effect.

WebLogic Server Extensions to java.sql.SQLException
When JDBC drivers throw SQLExceptions, the reason for the exception is either embedded inside the exception message or in vendor-specific error codes. This requires applications to parse the exception messages to diagnose the failure. WebLogic Server now offers custom extension types to java.sql.SQLException to indicate specific failure conditions that can occur when the application tries to get a connection from a connection pool.

Please refer to WebLogic Server JDBC Javadocs at http://e-docs.bea.com/wls/docs81/javadocs/weblogic/jdbc/extensions/package-summary.html for the details on the extension types.

5. Dynamic Wrappers
Prior to WebLogic Server 8.1, wrapper objects returned to applications were static proxies to the underlying physical objects. For example, in WebLogic Server 7.0, wrapper object "weblogic.jdbc.pool.Connection" implemented the interface "weblogic.jdbc.vendor.oracle.OracleConnection," which contained a hard-coded list of proprietary extensions that Oracle provides for Connection objects. Applications could cast the wrapper connection object returned by WebLogic Server to "weblogic.jdbc.vendor.oracle.OracleConnection" and invoke one of the proprietary methods from that list.

The problem with this approach was that WebLogic Server JDBC wrapper objects were not capable of dynamically discovering and wrapping around methods in the underlying physical object that were not known at compile time.

In WebLogic Server 8.1, a new Dynamic Proxy generation technology was used to create the wrapper objects. By using this new technology, WebLogic Server JDBC is now able to offer the following features:

• Applications now have access to methods in ANY public interface implemented by the underlying physical object. You are no longer restricted to using only those extensions defined in WebLogic Server-specific interfaces (such as weblogic.jdbc.vendor.oracle.OracleConnection) nor do you need to cast objects to those interfaces.
• Applications now have access to the underlying physical connection object
• Wrapper objects are not wrapped with RMI-wrappers if application code is executing in the same JVM as the connection pool

6. Prepared Statement Cache
Previous releases of WebLogic Server contained two separate implementations of the statement cache, one for when a XA-enabled JDBC driver was in use, and another for when a non-XA-enabled JDBC driver was in use. These caches were configured using different attributes of the JDBCConnectionPoolMBean and used different schemes to replace the cache's contents - the cache for non-XA JDBC drivers did not allow cache entries to be replaced, whereas the one for XA JDBC drivers used a Least Recently Used algorithm.

In WebLogic Server 8.1, both caches have been replaced with a single cache implementation. The new implementation allows applications to:

• Select an algorithm (from a predefined list - "LRU", "FIXED") that controls the behavior of the cache
• Dynamically reconfigure the number of prepared statements that are cached
• Dynamically disable and clear the cache

If the "LRU" caching algorithm is in use, once the cache's maximum configured size is reached, additional statements that need to be cached replace the Least Recently Used ones. If a "FIXED" caching algorithm is in use, once the cache's maximum configured size is reached, additional statements are NOT added to the cache.

More information on the Prepared Statement Cache is available at
http://e-docs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.html#statementcache

7. JDBC Assistant
Every JDBC driver has a proprietary syntax for configuration attributes such as the URL, class name, driver properties, etc. In previous releases, you had to be aware of and specify the exact syntax when configuring WebLogic Server JDBC connection pools.

WebLogic Server 8.1 offers a configuration wizard called "JDBC Assistant" that contains a knowledgebase of vendor-specific JDBC driver configuration information. You can use the JDBC Assistant to easily configure connection pools; you no longer need to be concerned with vendor-specific configuration details. This knowledgebase is also user-tunable.

More information on the JDBC Assistant is available at http://edocs.bea.com/wls/docs81/ConsoleHelp/jdbc_connection_pools.html.