Corporate Info | News | Solutions | Products | Partners | Services | Events | Download | How To Buy

Creating and Using Connection Pools

Overview

Creating and Managing Connection Pools

Creating a Startup Connection Pool

Creating a start-up connection pool using the WebLogic jDrivers

Creating a start-up connection pool using third-party JDBC Drivers

Creating a dynamic connection pool

Managing connection pools

Retrieving information about a pool

Disabling a connection pool

Shrinking a connection pool

Shutting down a connection pool

Resetting a pool

Refreshing a single pool connection

Refreshing a single pool connection from a T3 client

Setting up ACLs for connection pools in the WebLogic Realm

Using a Connection Pool in an Application

Server-side Applications

Client-side Applications

Overview

A connection pool is a named group of identical JDBC connections to a database that are created when the connection pool is registered, usually when starting up WebLogic Server. Your application "borrows" a connection from the pool, uses it, then 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 they need to access the database.

- You do not need to hard-code details such as the DBMS 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.

The connection properties for a start-up connection pool are defined in the weblogic.properties file. There is also an API that you can use to programmatically create connection pools in a running WebLogic Server.

There are several ways to use a connection from a connection pool in your application. See Using a Connection Pool in an Application for more information.

Creating and Managing Connection Pools

Creating a Startup Connection Pool

A startup connection pool is declared in the weblogic.properties file. The WebLogic Server opens JDBC connections to the database during the startup process and adds the connections to the pool.

You define a startup connection pool with an entry using the following pattern in your weblogic.properties file. An example is commented out in the properties file that is shipped with the distribution, under the heading "JDBC Connection Pool Management:"

    weblogic.jdbc.connectionPool.VirtualName=\
      url=JDBC driver URL,\
      driver=full package name for JDBC driver,\
      loginDelaySecs=seconds between each login attempt,\
      initialCapacity=initial number of connections in the pool,\
      maxCapacity=max number of connections in the pool,\
      capacityIncrement=number of connections to add at a time,\
      allowShrinking=true to allow shrinking,\
      shrinkPeriodMins=interval before shrinking,\
      testTable=name of table for connection test,\
      refreshTestMinutes=interval for connection test,\
      testConnsOnReserve=true to test connection at reserve,\
      testConnsOnRelease=true to test connection at release,\
      props=DBMS connection properties

    weblogic.allow.reserve.weblogic.jdbc.connectionPool.name=\
             Users who can use this pool
    weblogic.allow.reset.weblogic.jdbc.connectionPool.name=\
             Users who can reset this pool
    weblogic.allow.shrink.weblogic.jdbc.connectionPool.name=\
             Users who can shrink this pool

The information that you supply is shown above in italics. Each argument is described in detail below. Some of the arguments are required. If you do not supply a value that is required, an exception is thrown when you start the WebLogic Server.

Here is a short description of the arguments for this property:

name
  (Required) Name of the connection pool. You will use the name to access a JDBC Connection from this pool.

url
  (Required) URL of the JDBC 2-tier driver for the connection between the WebLogic Server and the DBMS. You can use one of the WebLogic jDrivers or another JDBC driver that you have tested in a 2-tier environment. Check the documentation for the JDBC driver you choose to find the URL.

driver
  (Required) Full package name of the JDBC 2-tier driver class for the connection between the WebLogic Server and the DBMS. Check the documentation for the JDBC driver to find the full pathname.

loginDelaySecs
  (Optional) Number of seconds to wait between each attempt to open a connection to the database. Some database servers can't handle multiple requests for connections in rapid succession. This property allows you to build in a small delay to let the database server catch up.

initialCapacity
  (Optional) The initial size of the pool. If this value is unset, the default is the value you set for capacityIncrement.

maxCapacity
  (Required) The maximum size of the pool.

capacityIncrement
  The size by which the pool's capacity is enlarged. initialCapacity and capacityIncrement work somewhat like a Java Vector, which has an initial allocation (its "capacity") and is increased in increments as necessary (capacityIncrement), up to the pool maxCapacity. The default value is 1.

allowShrinking
  (Optional) Whether this connection pool should be allowed to shrink back to its initial capacity, after expanding to meet increased demand. Set shrinkPeriodMins if this property is set to true, or it will default to 15 minutes. Note that allowShrinking is set by default to false, for backwards compatibility.

shrinkPeriodMins
  (Optional) The number of minutes to wait before shrinking a connection pool that has incrementally increased to meet demand. You must set allowShrinking to true to use this property. The default shrink period is 15 minutes and the minimum is 1 minute.

testTable
  (Required only if you set refreshTestMinutes, testConnsOnReserve, or testConnsOnRelease.) The name of a table in the database that is used to test the viability of connections in the connection pool. The query select count(*) from testTable is used to test a connection. The testTable must exist and be accessible to the database user for the connection. Most database servers optimize this SQL to avoid a table scan, but it is still a good idea to set testTable to the name of a table that is known to have few rows, or even no rows.

refreshTestMinutes
  (Optional) This property, together with the testTable property, enables autorefresh of connections in the pools. At a specified interval, each unused connection in the connection pool is tested by executing a simple SQL query on the connection. If the test fails, the connection's resources are dropped and a new connection is created to replace the failed connection. The default value is 1.
  To enable autorefresh, set refreshTestMinutes to the number of minutes between connection test cycles - a value greater than or equal to 1. If you set an invalid refreshTestMinutes value, the value defaults to 5 minutes. Set testTable to the name of an existing database table to use for the test (required).

testConnsOnReserve
  (Optional) When set to true, the WebLogic Server tests a connection after removing it from the pool and before giving it to the client. The test adds a small delay in serving the client's request for a connection from the pool, but ensures that the client receives a working connection (assuming that the DBMS is available and accessible). The testTable parameter must be set to use this feature.

testConnsOnRelease
  (Optional) When set to true, the WebLogic Server tests a connection before returning it to the connection pool. If all connections in the pool are already in use and a client is waiting for a connection, the client's wait will be slightly longer while the connection is tested. The testTable parameter must be set to use this feature.

props
  (Required) The properties for connecting to the database, such as username, password, or server. The properties are defined by, and processed by, the 2-tier JDBC driver that you use. Check the documentation for the JDBC driver to find the properties required to connect to your DBMS.

allow
  This attribute was deprecated in 3.0. Set up access to a connection pool using "reserve" and "reset" Permissions as shown above.

This example, taken from the weblogic.properties file that is shipped with the WebLogic distribution, creates a connection pool named "eng," which is accessible to 3 users (Guest, Joe, and Jill). It allocates a minimum of 2 and a maximum of 10 JDBC connections for an Oracle database with a username of "SCOTT," password "tiger," and server name "DEMO." The WebLogic Server sleeps for 1 second between each connection attempt to prevent refused logins from a DBMS that may be under load or on a saturated network. The connection pool shrinks back to 2 connections when connections in the pool are unused for 15 minutes or more. Every 10 minutes, unused connections are tested and refreshed if they have gone stale.

    weblogic.jdbc.connectionPool.eng=\
           url=jdbc:weblogic:oracle,\
           driver=weblogic.jdbc.oci.Driver,\
           loginDelaySecs=1,\
           initialCapacity=2,\
           maxCapacity=10,\
           capacityIncrement=2,\
           allowShrinking=true,\
           shrinkPeriodMins=15,\
           refreshTestMinutes=10,\
           testTable=dual,\
           props=user=SCOTT;password=tiger;server=DEMO

    weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=\
           guest,joe,jill
    weblogic.allow.reset.weblogic.jdbc.connectionPool.eng=\
           joe,jill
    weblogic.allow.shrink.weblogic.jdbc.connectionPool.eng=\
           joe,jill

Note that if you have a username with a null password, you shouldn't enter an empty string for the password in the connection pool registration; rather you should simply leave it blank. Here is an example taken from the WebLogic Administrators Guide document on properties:

    weblogic.jdbc.connectionPool.eng=\
           url=jdbc:weblogic:oracle,\
           driver=weblogic.jdbc.oci.Driver,\
           loginDelaySecs=1,\
           initialCapacity=2,\
           capacityIncrement=2,\
           maxCapacity=10,\
           props=user=sa;password=;server=demo
    weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=guest,joe,jill

Creating a start-up connection pool using the WebLogic jDrivers

The links below contain sample connection pool definitions for each of the WebLogic jDrivers. The WebLogic jDrivers are two-tier JDBC drivers.

- WebLogic jDriver for Oracle

- WebLogic jDriver for Informix

- WebLogic jDriver for Microsoft SQL Server

Creating a start-up connection pool using third-party JDBC Drivers

- Using the Oracle thin driver with WebLogic Server

- Using the Sybase jConnect driver

Creating a dynamic connection pool

A JNDI-based API introduced in WebLogic release 4.0 allows you to create a connection pool from within a Java application. With this API, you can create a connection pool in a WebLogic Server that is already running.

Dynamic pools can be temporarily disabled, which suspends communication with the database server through any connection in the pool. When a disabled pool is enabled, the state of each connection is the same as when the pool was disabled; clients can continue their database operations right where they left off.

A property in the weblogic.properties file, weblogic.allow.admin.weblogic.jdbc.connectionPoolcreate, determines who can create dynamic connection pools. If the property is not set, then only the "system" user can create a dynamic connection pool.

For example, the following property allows users "joe" and "jane" to create dynamic connection pools:

    weblogic.allow.admin.weblogic.jdbc.connectionPoolcreate=joe,jane

You can also create ACLs for dynamic connection pools by adding weblogic.allow.reserve.ACLname and weblogic.allow.admin.ACLname entries to the weblogic.properties file. For example, the following two properties define an ACL named "dynapool" that allows anyone (the "everyone" group) to use a connection pool, and users "joe" and "jane" to administer a connection pool:

    weblogic.allow.admin.dynapool=joe,jane
    weblogic.allow.reserve.dynapool=everyone

To allow users other than "system" to set dynamic connection pools, add the following properties:

weblogic.allow.modify.weblogic.jndi.weblogic.jdbc.connectionPool=joe,jane weblogic.allow.lookup.weblogic.jndi.weblogic.jdbc.connectionPool=joe,jane

You associate an ACL with a dynamic connection pool when you create the connection pool. The ACL and connection pool are not required to have the same name, and more than one connection pool can make use of a single ACL. If you do not specify an ACL, the "system" user is the default administrative user for the pool and any user can use a connection from the pool.

To create a dynamic connection pool in a Java application, you get an initial JNDI context to the WebLogic JNDI provider, and then look up "weblogic.jdbc.common.JdbcServices." This example shows how this is done:

    Hashtable env = new Hashtable();

    env.put(Context.INITIAL_CONTEXT_FACTORY,
             "weblogic.jndi.WLInitialContextFactory");
    // URL for the WebLogic Server
    env.put(Context.PROVIDER_URL, "t3://localhost:7001");
    env.put(Context.SECURITY_PRINCIPAL, "Fred");
    env.put(Context.SECURITY_CREDENTIALS, "secret");

    Context ctx = new InitialContext(env);

    // Look up weblogic.jdbc.JdbcServices
    weblogic.jdbc.common.JdbcServices jdbc =
     (weblogic.jdbc.common.JdbcServices)
       ctx.lookup("weblogic.jdbc.JdbcServices");

Once you have loaded weblogic.jdbc.JdbcServices, you pass the weblogic.jdbc.common.JdbcServices.createPool() method a Properties object that describes the pool. The Properties object contains the same properties you use to create a connection pool in the weblogic.properties file, except that the "aclName" property is specific to dynamic connection pools.

The following example creates a connection pool named "eng2" for the DEMO Oracle database. The connections log into the database as user "SCOTT" with password "tiger." When the pool is created, one database connection is opened. A maximum of ten connections can be created on this pool. The "aclName" property specifies that the connection pool will use the "dynapool" ACL in the weblogic.properties file.

     weblogic.jdbc.common.Pool pool = null;

      try {
        // Set properties for the Connection Pool.
        // The properties are the same as those used to define a startup
        // connection pool in the weblogic.properties file.
        Properties poolProps = new Properties();

        poolProps.put("poolName",        "eng2");
        poolProps.put("url",             "jdbc:weblogic:oracle");
        poolProps.put("driver",          "weblogic.jdbc.oci.Driver");
        poolProps.put("initialCapacity", "1");
        poolProps.put("maxCapacity",     "10");
        poolProps.put("props",           "user=SCOTT;
                                          password=tiger;server=DEMO");
        poolProps.put("aclName",         "dynapool");  // the ACL to use

        // Creation fails if there is an existing pool
        //  with the same name.
        jdbc.createPool(poolProps);
      }
      catch (Exception e) {
         system.out.Println("Error creating connection pool eng2.");
      }
      finally { // close the JNDI context
             ctx.close();
      }

Managing connection pools

The weblogic.jdbc.common.Pool and weblogic.jdbc.common.JdbcServices interfaces provide methods to manage connection pools and obtain information about them. Methods are provided for:

- Retrieving information about a pool

- Disabling a connection pool, which prevents clients from obtaining a connection from it

- Enabling a disabled pool

- Shrinking a pool, which releases unused connections until the pool has reached the minimum specified pool size

- Refreshing a pool, which closes and reopens its connections

- Shutting down a pool

Retrieving information about a pool

weblogic.jdbc.common.JdbcServices.poolExists()

weblogic.jdbc.common.Pool.getProperties()

The poolExists() method tests whether a connection pool with a specified name exists in the WebLogic Server. You can use this method to determine whether a dynamic connection pool has already been created or to ensure that you select a unique name for a dynamic connection pool you want to create.

The getProperties() method retrieves the properties for a connection pool.

Disabling a connection pool

weblogic.jdbc.common.Pool.disableDroppingUsers()

weblogic.jdbc.common.Pool.disableFreezingUsers()

weblogic.jdbc.common.pool.enable()

You can temporarily disable a connection pool, preventing any clients from obtaining a connection from the pool. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can disable or enable the pool.

After you call disableFreezingUsers(), clients that currently have a connection from the pool are suspended. Attempts to communicate with the database server throw an exception. Clients can, however, close their connections while the connection pool is disabled; the connections are then returned to the pool and cannot be reserved by another client until the pool is enabled.

Use disableDroppingUsers() to not only disable the connection pool, but to destroy the client's JDBC connection to the pool. Any transaction on the connection is rolled back and the connection is returned to the connection pool. The client's JDBC connection context is no longer valid.

When a pool is enabled after it has been disabled with disableFreezingUsers(), the JDBC connection states for each in-use connection are exactly as they were when the connection pool was disabled; clients can continue JDBC operations exactly where they left off.

You can also use the disable_pool and enable_pool commands of the weblogic.Admin class to disable and enable a pool.

Shrinking a connection pool

weblogic.jdbc.common.Pool.shrink()

A connection pool has a set of properties that define the initial and maximum number of connections in the pool (initialCapacity and maxCapacity), and the number of connections added to the pool when all connections are in use (capacityIncrement). When the pool reaches its maximum capacity, the maximum number of connections are opened, and they remain opened unless you shrink the pool.

You may want to drop some connections from the connection pool when a peak usage period has ended, freeing up resources on the WebLogic Server and DBMS.

Shutting down a connection pool

weblogic.jdbc.common.Pool.shutdownSoft()

weblogic.jdbc.common.Pool.shutdownHard()

These methods destroy a connection pool. Connections are closed and removed from the pool and the pool dies when it has no remaining connections. Only the "system" user or users granted "admin" permission by an ACL associated with a connection pool can destroy the pool.

The shutdownSoft() method waits for connections to be returned to the pool before closing them.

The shutdownHard() method kills all connections immediately. Clients using connections from the pool get exceptions if they attempt to use a connection after shutdownHard() is called.

You can also use the destroy_pool command of the weblogic.Admin class to destroy a pool.

Resetting a pool

weblogic.jdbc.common.Pool.reset()

You can configure a connection pool to test its connections either periodically, or every time a connection is reserved or released. Allowing the WebLogic Server to automatically maintain the integrity of pool connections should prevent most DBMS connection problems. In addition, WebLogic provides methods you can call from an application to refresh all connections in the pool or a single connection you have reserved from the pool.

The weblogic.jdbc.common.Pool.reset() method closes and reopens all allocated connections in a connection pool. This may be necessary after the DBMS has been restarted, for example. Often when one connection in a connection pool has failed, all of the connections in the pool are bad.

Refreshing a single pool connection

weblogic.jdbc.common.JdbcServicesDef

You can refresh a single connection from a connection pool, or reset the entire connection pool, if one or more connections in the pool go stale. For example, if the DBMS is taken down while the WebLogic Server is actively supporting a pool of connections. A connection pool autorefresh feature can also be enabled to periodically test and refresh connections.

There are only certain instances when resetting a pool is appropriate. You should never use this feature as a routine part of a user program. Usually, what makes resetting a connection pool necessary is that the DBMS has gone down and the connections in the pool are no longer viable. Attempting to reset the pool before you are certain that the DBMS is up and available again will cause an Exception to be thrown. Resetting a pool should always be a special operation that is carried out by a user with administrative privileges.

There are several ways to reset connections pools:

- You can use the weblogic.Admin command (as a user with administrative privileges) to reset a connection pool, as an administrator. Here is the pattern:

      $ java weblogic.Admin WebLogicURL RESET_POOL poolName system passwd

You might use this method from the command line on an infrequent basis. There are more efficient programmatic ways that are also discussed here. For more on the Admin commands, read the WebLogic Administrators Guide, Running and maintaining the WebLogic Server.

- You can use the reset() method from the JdbcServicesDef interface in your client application.

  The last case requires the most work for you, but also gives you more flexibility than the first two. Here how to reset a pool using the reset() method:

  1. In a try block, test a connection from the connection pool with a SQL statement that is guaranteed to succeed under any circumstances so long as there is a working connection to the DBMS. An example is the SQL statement "select 1 from dual" which is guaranteed to succeed for an Oracle DBMS.

  2. Catch the SQLException.

  3. Call the reset() method in the catch block.

Refreshing a single pool connection from a T3 client

weblogic.jdbc.t3.Connection

To refresh a single connection, use the refresh() method in the weblogic.jdbc.t3.Connection class. When you call this method, you lose any Statements and Resultsets you had on the connection, plus your application incurs the relatively high cost of opening the connection. Therefore, we recommend that you only refresh a single connection when you get an error that implies that the connection has gone bad.

You will need to explicitly cast the JDBC Connection as a (weblogic.jdbc.t3.Connection). Other than that, the refresh() method in the Connection class is used in the same way the reset() method is used for a connection pool. First try an execute action on the Connection that is guaranteed to succeed if the Connection itself is viable. Catch the exception and call the refresh() method.

Here is an example that uses a T3 client to test and, if necessary, refresh the pool. Notice that we cast the JDBC Connection as a weblogic.jdbc.t3.Connection in the last line of this example, in order to take advantage of the WebLogic extension to JDBC.

Note: The T3 client services are scheduled for deprecation in a future release of WebLogic Server.

    T3Client t3 = new T3Client("t3://localhost:7001");
    t3.connect();

    Properties t3props = new Properties();
    t3props.put("weblogic.t3", t3);
    t3props.put("weblogic.t3.connectionPoolID", "eng");

    Class.forName("weblogic.jdbc.t3.Driver").newInstance();
    Connection conn = DriverManager.getConnection
      ("jdbc:weblogic:t3", t3props);

    Connection conn = null;

    try {
      Statement stmt = conn.createStatement();

      // This SQL is guaranteed to succeed over a good connection
      // to an Oracle DBMS
      ResultSet rs = stmt.executeQuery("select 1 from dual");

      if (rs != null) {
        while (rs.next()) {;}
      }

      rs.close();
      stmt.close();
      conn.close();
    }

    catch(SQLException e) {

      // We cast the JDBC Connection as a
      // weblogic.jdbc.t3.Connection
      // and call the refresh() method on it
      ((weblogic.jdbc.t3.Connection)conn).refresh();
    }

Setting up ACLs for connection pools in the WebLogic Realm

weblogic.jdbc.connectionPool

weblogic.jdbc.connectionPool.poolID

WebLogic controls access to internal resources like JDBC connection pools through ACLs set up in the WebLogic Realm. Entries for ACLs in the WebLogic Realm are listed as properties in the weblogic.properties file.

You can set the Permissions "reserve," "reset," and "shrink" for JDBC connections in a connection pool by entering a property in the properties file. Setting a Permission for the ACL "weblogic.jdbc.connectionPool" limits access to all connection pools. Add Permissions for other users by adding an entry for the ACL name "weblogic.jdbc.connectionPool.poolID," which controls access to the connection pool poolID. The special user system always has Permissions "reserve," "reset," and "shrink" for every ACL, no matter what other Permissions have been set.

    Example:
    weblogic.allow.reserve.weblogic.jdbc.connectionPool.eng=
      margaret,joe,mary weblogic.allow.reset.weblogic.jdbc.connectionPool.eng=
      sysMonitor weblogic.allow.shrink.weblogic.jdbc.connectionPool.eng=
      sysMonitor

For backwards compatibility, you can also use the old-style property syntax to grant permission for "reserve" by setting a userlist for the property weblogic.jdbc.connectionPool.poolID=allow=. It is recommended that you upgrade your properties file as soon as possible to reflect the new usage, since WebLogic cannot guarantee how long it will support old-style properties.

Using a Connection Pool in an Application

Server-side Applications

For database access from server-side applications, such as HTTP servlets, use the WebLogic Pool or JTS driver. For database access using Enterprise JavaBeans (EJB) running in WebLogic Server, use the WebLogic JTS driver. The JTS driver provides transactional support for database operations.You can also access connection pools using the Java Naming and Directory Interface (JNDI) and a DataSource object. For more information, see the following documents:

- Using connection pools with server-side Java (in Using WebLogic HTTP Servlets)

- Using the JTS (Java Transaction Services) driver

- The WebLogic Server EJB Container

- Using a DataSource object to get a JDBC connection

Client-side Applications

BEA offers two possibilities for client-side, multitier JDBC. The WebLogic JDBC/RMI driver has the advantage of providing a standards-based approach using the Java 2 Enterprise Edition (J2EE) specifications, while the WebLogic T3 driver uses the proprietary WebLogic T3 client service. For new deployments, BEA recommends that you use the JDBC/RMI driver; the T3 client services are scheduled for deprecation in a future release of WebLogic Server.

- Using WebLogic JDBC/RMI and WebLogic Clustered JDBC

  The WebLogic JDBC/RMI driver is a type-3 JDBC driver using RMI and a DataSource object to create database connections. This driver also provides for clustered JDBC, leveraging the load balancing and failover features of WebLogic Clusters. DataSource objects may be defined to enable transactional support or not.

- Using the WebLogic JDBC T3 Driver

  Describes using the WebLogic T3 driver for multi-tier JDBC. and the use of connection pools.

  Note: The T3 client services are scheduled for deprecation in a future release of WebLogic Server.

Copyright © 2000 BEA Systems, Inc. All rights reserved. Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher.