J2EE

This article describes the configuration of various JDBC-related features in the BEA WebLogic 8.1 Server. Although we use Oracle 8.1.7 as our example database management system (DBMS), the general concepts are easily transferable to other relational databases as long as the databases in question provide connectivity through JDBC.

Introduction
JDBC is a standard Java API that is used directly or indirectly by almost all J2EE applications that require database access. In BEA WebLogic 8.1, configuring JDBC connectivity consists of creating and configuring two main artifacts - JDBC connection pools and data sources. Related to these two principal artifacts are other secondary configurable artifacts, such as a Multi pool or a data source factory.

Connection Pool or Multi Pool?
Before we dive into how to create and configure a connection pool, we need to take a brief look at why anybody would need a connection pool.

If you didn't use a JDBC connection pool for access to a database, in order to establish a connection to a database you would need to follow these steps:

• Load a JDBC driver class.
• Create an instance of a JDBC driver and register it with a driver manager.
• Obtain a connection to a database from the driver manager by supplying a database URL and other required parameters.
• Close the connection when done.

In contrast to this single-connection-at-a-time approach, a connection pool is an extant collection of database connections that may be recycled among multiple active J2EE applications. The connections in a connection pool are open and available for access. After an application has used a connection, the connection, instead of being destroyed, is returned to the connection pool and becomes available for other applications. By saving on the computing resources associated with the frequent setup and tearing down of database connections, a connection pool enhances database access performance, especially during peak load.

A multi pool is a collection of connection pools. The main purpose for multi pools is to increase availability and load balancing across a set of connection pools.

Why a Data Source?
A data source is a Java Naming and Directory Interface (JNDI) object that provides access to a connection pool or a Multi Pool. A connection pool or a multi pool is required to configure a data source. A data source may be configured with transaction attributes. A nontransactional data source is for local transactions; a transactional data source is for distributed transactions.

A data source encapsulates access to a database and hides the details involved in accessing a connection pool or a multi pool. In addition, a data source makes it easy to configure the transactional nature of a database connection.

A JDBC Data Source Factory is a JNDI object used by an enterprise application to get a connection from an application-scoped connection pool.

JDBC Drivers Setup
The JDBC drivers required to establish a connection between a WebLogic Server and a DBMS are of two types: Type 2 and Type 4. The Type 2 JDBC drivers require native libraries. For Type 2 JDBC drivers add the path to the WebLogic shared library (or dynamic linked library) directory - <WEBLOGIC>\server\bin\oci817_8 - and the Oracle client library directory - <ORACLE>\bin - to the beginning of the system <PATH> variable.

The WebLogic 8.1 Server is installed in the <WEBLOGIC> directory. The Oracle 8.1.7 database is installed in the <ORACLE> directory. <PATH> is the WebLogic Server Path variable.

For both Type 2 and Type 4 drivers, relevant JDBC driver classes are required in the WebLogic Server CLASSPATH to configure a connection pool or a data source. Add the <ORACLE>\JDBC\lib\classes12.zip file containing the Oracle Type 2 JDBC driver classes to the <CLASSPATH> variable in the <domain>/startWebLogic script file.

• <CLASSPATH>: WebLogic Server Classpath variable
• <domain>: WebLogic Server Domain directory

Configuring a JDBC Connection Pool
Now we'll look at how to create a connection pool and configure the various parameters associated with a it.

To create a JDBC connection pool, right-click on the JDBC>Connection Pools node in the Administration Console and select "Configure a new JDBC Connection Pool".

A Configure a JDBC Connection Pool frame is displayed. Select a Database Type - Oracle for an Oracle database. Select a Database Driver and click the Continue button (see Figure 1).

A Define Connection Properties frame is displayed. In the frame, specify a Database Name, a Database User Name, and a Password. Click on the Continue button.

A Test DataBase Connection is displayed. In the frame, specify values for the Driver Classname, URL, and Properties fields. Driver Classname is the class name of the driver used to get a database connection. URL is the database URL used to get a connection. Properties is the list of properties to create a connection.

The Driver Classname and URL settings for the different Oracle JDBC drivers are specified in Table 1.

In the URL setting, <host> is the HOST value specified in the <ORACLE>/network/ADMIN/tnsnames.ora file, <port> is the PORT value specified in the tnsnames.ora file, and <database> is the database instance name.

In the Properties text box the settings are:

user=<user>
server=<database>

<user> is the user name specified to log in to the Oracle database. <database> is the Oracle database instance name. The server property should not be specified if an Oracle thin (Type 4) driver is used.

Click the Test Driver Configuration button to test the JDBC Connection Pool. If the driver configuration test is successful a "Connection successful" message is displayed. If the driver configuration has an error, an error message is displayed.

A Create and Deploy frame is displayed. In the frame, select a server to deploy the connection pool, and click the Create and Deploy button to create and deploy a JDBC connection pool. A connection pool is created and deployed on the selected server. It may be deployed on more than one server. A <JDBCConnection Pool> node is added to the JDBC>Connection Pools node in the Administration Console. <JDBCConnection Pool> is the connection pool name.

To modify the JDBC Connection Pool configuration, click on the JDBC>Connection Pools><JDBCConnection Pool> node and select the Configuration tab. To modify the target server select the Target and Deploy tab. Select a target server and click the Apply button. Select the Connections tab to configure the JDBC Connections properties.

Connection Properties

• Initial Capacity: Number of database connections created when the connection pool is created.
• Maximum Capacity: Maximum number of connections in the connection pool.
• Capacity Increment: Increment by which the connection pool capacity is increased within the limit of maximum capacity.
• LoginDelay: Number of seconds to delay before creating each physical database connection.
• Allow Shrinking: Set to true to enable a connection pool to reduce the capacity to InitialCapacity if additional connections are not being used.
• Shrink Frequency: Number of seconds to wait before reducing the connection pool capacity. If Shrink Frequency is set to true, Allow Shrinking must also be set to true.
• Test Frequency: Number of seconds between database connection tests. After every Refresh Period interval, unused database connections are tested using TestTableName if a TestTableName is set.
• Test Reserved Connections: If selected, the server tests a connection before giving it to the client.
• Test Created Connections: If selected, a JDBC connection is tested after being created and before being added to the list of available connections in the JDBC connection pool.
• Test Released Connections: If selected, the server tests a connection before returning it to the Connection Pool.
• Test Table Name: Database table name to be used for JDBC connection testing. Test Table Name is required if Test Frequency is specified, and Test Reserved Connections, Test Created Connections, or Test Released Connections is selected.

Select the Apply button after configuring the Connections frame.

Configuring a JDBC Multi Pool
A multi pool is a collection of connection pools. Configure connection pools for the multi pool before creating a multi pool. To create and configure a new multi pool right-click on the JDBC>Multi Pools node in the Administration Console and select Configure a New Multi Pool.

A Configuration frame is displayed. In the Multi Pool Configuration frame specify Algorithm Type and select the Create button. If Algorithm Type is set to "High availability", all available connections in one pool are used before connections in another pool are used. If Algorithm Type is set to "Load balancing", the multi pool distributes connection requests evenly to all the connection pools in the multi pool (see Figure 2).

Select the Pools tab and in the Pools frame select Connection Pools to be added to the Multi Pool. Click the Apply button. The selected connection pools are added to the multi pool. The connection pool used in a connection is selected based on the Algorithm Type.

Select the Target and Deploy tab and in the Target and Deploy frame select a target server. Click the Apply button. A multi pool may be deployed on more than one server. A <Multi Pool> node is added to the JDBC>Multi Pools node in the Administration Console. <Multi Pool> is the multi pool name.

Configuring a JDBC Data Source
A data source is a JNDI object to access a connection pool or a multi pool. A connection pool or a multi pool is required to create a data source. A connection pool is required to create a transactional data source. Create a Connection Pool or a Multi Pool before creating a Data Source.

To create and configure a data source right-click on the JDBC>Data Sources node in the Administration Console and select Configure a new JDBCTxData Source.

A Configuration frame is displayed. Specify the field settings in the Data Source Configuration frame. JNDI Name is the JNDI path of the Data Source.

Select Honor Global Transactions to create a transactional (Tx) Data Source. If Honor Global Transactions is not selected, the data source created is a non-Tx data source (see Figure 3).

A Connect to Connection Pool frame is displayed. In the Pool Name field select a JDBC Connection Pool from the available list of connection pools and click the Continue button. The data source is connected with the selected connection pool.

A Target the Data Source frame is displayed. In the frame select a target server and click the Continue button. The data source is deployed on the selected server but may be deployed on more than one server. A <JDBCData Source> node is added to the JDBC>Data Sources node in the Administration Console. <JDBCData Source> is the data source name. To modify the data source configuration, click on the JDBC>Data Sources><JDBCData Source> node in the administration console. Select the Configuration tab. To enable fetching of multiple rows for each Result Set from the server to an external client in one server access, select Row Prefetch Enabled and specify a Row Prefetch Size. To enable global transactions for a non-XA JDBC driver, select Emulate Two-Phase Commit for non-XA Driver, and select Honor Global Transactions. To modify the target server for the data source select the Target and Deploy tab. Select a target server and click the Apply button.

Comparison of Data Source and Tx Data Source
A data source is a Tx Data Source if Honor Global Transactions is selected in the data source configuration. A data source is used with a connection pool or a multi pool. A Tx data source is used with a connection pool. A data source is used for local transactions. A Tx Data Source is used for distributed transactions.

Tx Data Source should be used instead of a data source for:

• Container-managed persistence entity beans
• Access of multiple resources during a transaction
• Multiple database updates within a single transaction
• Use of the same connection pool on multiple servers

Configuring a JDBC Data Source Factory
A JDBC Data Source Factory is a JNDI data source resource. A Data Source Factory is used by an enterprise application to get connections from an application-scoped connection pool.

To create and configure a JDBC Data Source factory, right-click on the JDBC>Data Source Factories node in the Administration Console and select Configure a new JDBCDataSourceFactory.

A Configuration frame is displayed. In this frame specify values for the URL, Driver Class Name, Factory Name, and Properties fields and click on the Create button (see Figure 4).

A <JDBCData Source Factory> node is added to the JDBC>Data Source Factories node in the Administration Console. <JDBCData Source Factory> is the Data Source Factory name. To use the <JDBCData Source Factory>, restart the server.

JDBC Configuration and Server Performance
BEA's recommendations for the different JDBC configuration settings for WebLogic Server follow.

To enhance server performance in production mode:

• Set InitialCapacity value equal to the MaxCapacity in the JDBC Connection Pool Configuration. If the Initial Capacity value is less than MaxCapacity, the server creates additional database connections when load is increased. Under increased load, resources must be allocated to create additional database connections when resources are also required to complete jdbc client requests.
• Set MaxCapacity equal to the number of concurrent client sessions that require JDBC connections: To determine the concurrent client sessions in a Connection Pool, select the connection pool node. Select the "Monitoring" tab. The value in the "Active Connections" column is the average number of concurrent client sessions. The value of Connections High is the highest number of concurrent client sessions. The MaxCapacity value may be modified after deploying an application and monitoring the active connections.
• Select Row Prefetch Enabled in Data Source configuration when an external client accesses a database using JDBC through WebLogic Server: Row prefetching enhances performance by fetching multiple rows in one server access.

Conclusion
Following the procedures outlined here, a WebLogic developer should be able to configure a BEA WebLogic 8.1 Server with an Oracle 8.1 database. Configuring other databases would be similar to Oracle 8.1 Add the zip/jar file containing the driver classes for the corresponding database to the CLASSPATH variable, and specify the corresponding connection URL and driver classname in the JDBC Connection Pool configuration.

Resources

Programming WebLogic JDBC: http://e-docs.bea.com/wls/docs81/jdbc/

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...