Configuring WebLogic JDBC Features

 

This section covers the following JDBC connectivity topics:

• Using DataSources

• Using Connection Pools

• Using MultiPools

Using DataSources

DataSource objects, along with the JNDI, provide access to connection pools for database connectivity. Each data source requires a separate DataSource object, which may be implemented as a DataSource class that supports either:

• connection pooling, or

• distributed transactions.

DataSource Import Statements

To use the DataSource objects, import the following classes in your client code:

import java.sql.*;
import java.util.*;
import javax.naming.*;

Setting Up WebLogic Server to Use a DataSource

Define the DataSource in the Administration Console. You can define multiple DataSources that use a single connection pool. This allows you to define both transaction and non-transaction-enabled DataSource objects that share the same database.

Obtaining a Client Connection Using a DataSource

To obtain a connection from a JDBC client, use a Java Naming and Directory Interface (JDNI) look up to locate the DataSource object, as shown in this code fragment:

Context ctx = null;
  Hashtable ht = new Hashtable();
  ht.put(Context.INITIAL_CONTEXT_FACTORY,
         "weblogic.jndi.WLInitialContextFactory");
  ht.put(Context.PROVIDER_URL,
         "t3://hostname:port");

  try {
    ctx = new InitialContext(ht);
    javax.sql.DataSource ds 
      = (javax.sql.DataSource) ctx.lookup ("myJtsDataSource");
   java.sql.Connection conn = ds.getConnection();

// You can now use the conn object to create 
// Statements and retrieve result sets:

Statement stmt = conn.createStatement();
stmt.execute("select * from someTable");
ResultSet rs = stmt.getResultSet();  

// Close the statement and connection objects when you are finished:

   stmt.close();
   conn.close();
 }
  catch (NamingException e) {
    // a failure occurred
  }
  finally {
    try {ctx.close();}
    catch (Exception e) {
      // a failure occurred
    }
  }

(Substitute the correct hostname and port number for your WebLogic Server.

Note: The code above use one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming WebLogic JNDI.

Code Examples

See the DataSource code example in the samples/examples/jdbc/datasource directory of your WebLogic Server installation.

Using Connection Pools

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 . There is also an API that you can use to programmatically create connection pools in a running WebLogic Server.

Creating a Connection Pool at Startup

A startup connection pool is created in the Administration Console. For more information see Managing JDBC Connectivity in the Administration Guide. The WebLogic Server opens JDBC connections to the database during the startup process and adds the connections to the pool.

Properties

To define a specific property for your connection pool, be sure that you duplicate the exact spelling and case of the property type. You pair these types (keys) along with their values, shown in the table below, in a java.utilis.Properties object that is used when creating the pool.

Table 4-1 Connection Pool Properties

Property Type	Description	Property Value
poolName	Required. Unique name of pool.	myPool
aclName	Required. Identifies the different access lists within fileRealm.properties in the server config directory. Paired name must be dynaPool.	dynaPool
props	Database connection properties; typically in the format "database login name; database password; server network id".	user=scott;password=tiger; server=bay816
initialCapacity	Initial number of connections in a pool. If this property is defined and a positive number > 0, WebLogic Server creates these connections at boot time. Default is 0; cannot exceed maxCapacity.	1
maxCapacity	Maximum number of connections allowed in the pool. Default is 1; if defined, maxCapacity should be =>1.	10
capacityIncrement	Number of connections that can be added at one time. Default = 0.	1
allowShrinking	Indicates whether or not the pool can shrink when connections are detected to not be in use. Default = true.	True
shrinkPeriodMins	Interval between shrinking. If allowShrinking = True, then default = 15 minutes. Note: If you set a value for this attribute when AllowShrinking is set to false, WebLogic Server ignores the false setting and allows shrinking according to the value in ShrinkPeriodMins.	5
driver	Required. Name of JDBC drive. Only local (non-XA) drivers can participate.	weblogic.jdbc.oci.Driver
url	Required. URL of the JDBC driver.	jdbc:weblogic:oracle
testConnsOnReserve	Indicates reserved test connections. Default = False.	true
testConnsOnRelease	Indicates test connections when they are released. Default = False.	true
testTableName	Database table used when testing connections; must be present for tests to succeed. Required if either testConnOnReserve or testConOnRelease are defined.	myTestTable
refreshPeriod	Interval between connection testing. Must be non-zero if either testConnOnReserve or testConOnRelease are defined.	1
loginDelaySecs	Seconds between each login attempt. Default = 0.	1

Creating a Connection Pool Dynamically

A JNDI-based API 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.

Permissions for creating dynamic connection pools are set in the Administration Console. For more information see, Managing Security in the Administration Guide.You can also create ACLs for dynamic connection pools.

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 Administration Console, 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".

 weblogic.jdbc.common.Pool pool = null;

  try {
    // Set properties for the Connection Pool.  
       
    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.

Use any of the following methods to reset a connection pool:

• Through the Administration Console.

• 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.

• 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 flexibility. 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.

Using MultiPools

If you are using a single WebLogic Server configuration, consider using JDBC MultiPools for either backup pools or connection pool load balancing. JDBC Multipools, a new feature in WebLogic Server Version 6.0, are lists of connection pools used in single WebLogic Server configurations. A MultiPool is a "pool of pools." MultiPools contain a configurable algorithm for choosing among its pools, the connection that is returned to the user.

MultiPool Features

MultiPools are single-server, static lists of connection pools. All the connections in a particular connection pool are created identically with a single database, single user, and the same connection attributes; that is, they are attached to a single database. However, the connection pools within a MultiPool may be associated with different users or DBMSs.

Choosing the MultiPool Algorithm

Before you set up a MultiPool, you need to determine the primary purpose of the MultiPool--backup pool capability or load balancing. You can choose the algorithm that corresponds with your requirements:

Note: Capacity is not a failover reason, because users have the right to set capacity. MultiPools take effect only if loss of database connectivity has occurred.

Backup Pool

A backup MultiPool is an ordered list of connection pools. Normally, every connection request to this kind of MultiPool is served by the first pool in the list. If a database connection via that pool fails, then a connection is sought sequentially from the next pool on the list.

Load Balancing

Connection requests to a load balancing MultiPool are served from any connection pool in the list. The pool that is tapped by a connection request is chosen round-robin from a list of pools.

Guidelines to Setting Wait For Connection Times

Setting wait for connection times is a property of the connection attempt. If you are familiar with setting waiting time to pool connections, the wait for connection property applies to every connection tapped in a given connection attempt.

You can add any connection pool to a MultiPool. However, you optimize your resources depending on how you set the wait for connection time when you configure your connection pools.

Messages and Error Conditions

Users may request information regarding the connection pool from which the connection originated.

SQL Warnings

SQL Warnings are posted to the JDBC log under these circumstances:

• At boot time, when a connection pool is added to a MultiPool

• Whenever there is a switch to a new connection pool within the MultiPool, either during load balancing or high availability.

Capacity Issues

In a backup pool scenario, the fact that the first pool in the list is busy does not trigger an attempt to get a connection from a backup pool.