Data Connectivity & Mainframe Inegration

Data Services Journal

Subscribe to Data Services Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get Data Services Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Data Services Authors: Scott Allen

Related Topics: Data Services Journal

Data Services: Article

Configuring WebLogic Server 9.x JDBC

Data source connections

WebLogic Server 9.x provides database connectivity with data sources. A data source is a pool of database connections from which a connection can be obtained. A data source can be configured separately or as a multi-datasource. A multi-data source is collection of data sources. A data source is configured with a JNDI binding. A DataSource object is obtained with a JNDI lookup. A Connection object can be obtained from a DataSource object with the getConnection() method. WebLogic Server provides an administration console to configure a data source. WebLogic Server 9.x includes Type 4 JDBC drivers from DataDirect for DB2, Informix, Microsoft SQL Server, Sybase, and Oracle databases. JDBC drivers for other databases can be incorporated in the server by including the JAR files for the JDBC drivers in the server classpath.

New JDBC features in WebLogic Server 9.0 include support for JDBC 3.0, multiple JNDI names for a data source, and support for a Logging Last Resource transaction option. SQL Statement Timeout has been added to the connection pool configuration. SecondsToTrustAnIdlePoolConnection and PinnedToThread connection pool properties, which we'll discuss below, have been added to improve data source performance. The connection request failover feature has been improved. Statistics collection has been added for the different connection parameters for performance diagnostics. New features have been added to WebLogic Type 4 JDBC drivers and there's new identity-based connection pooling. Transaction, diagnostic, and security tabs are now part of the administration console for configuring a data source.

Setting the Environment
Download WebLogic Server 9.1. To install the application server double-click on the server910_win32 application. Click on the Next button in the BEA Installer. Select Create a New BEA Home in the Choose BEA Home Directory frame and specify a directory in the BEA Home Directory field. Click on Next. In the Choose Install Type frame select Complete and click on the Next button. Select any optional tools if required in the Optional Tools frame and click on the Next button. In the Choose Product Directory frame select the default Product Installation Directory and click on the Next button. Click on Next in the Create shortcut locations frame. BEA WebLogic Server gets installed.

Download the JDBC driver for the database. We'll configure JDBC connectivity with the MySQL database. So install the MySQL database and download the JDBC driver Connector/J.

Extract the MySQL JDBC driver zip file to a directory. Add the MySQL JDBC driver JAR file mysql-connector-java-3.1.11-bin.jar file to the CLASSPATH variable of the <weblogic91>\samples\domains\wl_server\bin\startWebLogic script file. <weblogic91> is the directory in which WebLogic Server 9.1 is installed. Double-click on the <weblogic91>\samples\domains\wl_server\startWebLogic to start the WebLogic examples server.

Creating a Data Source
A data source is a pool of JDBC connections from which a connection can be obtained with the getConnection() method of a DataSource object. In this section we'll create a data source in the administration console. Access the administration console with the URL http://localhost:7001/console. In the administration console select the node Services>JDBC>DataSources.

To create a new JDBC data source click on New in the Data Sources table (see Figure 1).

Specify a data source name in the Create a New JDBC Data Source frame and a JNDI name for the data source. A data source is bound on a JNDI tree with a JNDI name. Select a Database Type. We'll create a data source with the MySQL database. Select MySQL as the Database Type. Select MySQL's driver (Type 4) as the database driver and click on Next (see Figure 2).

A data source can be configured with any of the commonly used databases. WebLogic provides Type 4 JDBC drivers from DataDirect for DB2, Informix, Oracle, SQL Server, and Sybase. The DataDirect drivers are pre-installed in the <weblogic9.1>/server/lib directory (see Figure 3). The different JDBC Type 4 drivers included with WebLogic Server are listed in Table 1.

Databases other than those for which a JDBC driver is included can also be selected. If a JDBC driver other than the WebLogic driver is selected add the driver zip/JAR file to the CLASSPATH variable in the startWebLogic script (see Figure 4).

In the Transaction Options frame the transaction attributes of the data source are specified. If a XA driver is selected global transactions are automatically supported with the two-phase commit transaction protocol. A global transaction, or a distributed transaction, is a transaction that involves two or more transactions over multiple (or single) resource managers (a RDBMS database is a resource manager). A global transaction is managed by a Transaction Manager using JTA. For a non-XA data source to support global transactions, check the Global Transactions checkbox. Select the protocol that supports global transactions. The different transaction protocols are listed in Table 2.

In the Transaction Options page click on Next (see Figure 5).

In the Connection Properties frame specify the Database Name, test for the default database instance. Specify Host Name as localhost, Port as 3306, and user name as root. A password isn't required for the root username. Click on the Next button (see Figure 6).

In the Test Database Connection frame the driver class name, connection URL, and username for the MySQL database are specified. Click on the Test Database Configuration button to test the connection with the database (see Figure 7).

A message gets displayed indicating if a connection has been established. If database doesn't get connected an error message gets displayed. Click on the Next button.

In the Select Targets frame select a server where the data source will be deployed. To deploy to the examples server, select examplesServer and click on the Finish button (see Figure 8).

A data source gets configured and added to the Data Sources table (see Figure 9).

Click on the Activate Changes button to make the data source available to applications in the server.

Configuring a Data Source
In this section the data source created in the last section will be configured. Select the data source to configure in the Data Sources table. Select the Configuration tab (selected by default). In the Configurations frame the data source JNDI name can be modified (see Figure 10).

The other configuration options for a data source are listed in Table 3.

To configure the connection pool associated with data source select the Connection Pool link. Initial capacity, maximum capacity, and capacity increment can be set in the connection pool configuration (see Figure 11).

Some of the connection pool settings are listed in Table 4.

The transaction protocol settings can be configured with the Transaction link. Monitoring statistics can be collected with the Diagnostics link (see Figure 12).

Some of the data source profiles that can may be collected are listed in Table 5.

To monitor a data source select the Monitoring tab. To administer the WebLogic Server instances to which the data source is deployed select the Control tab. In a deployed server instance the statement cache can be cleared and server can be suspended or shut down.

Creating a Multi-Data Source
A multi-data source is an abstract group of data sources, which provides failover and load balancing around data sources. A multi-data source has a JNDI binding similar to a data source. To create a multi-data source click on the Services>JDBC>Multi Data Sources link in the administration console (see Figure 13).

In the Multi Data Sources table click on the New button to create a new multi-data source (see Figure 14).

In the Configure the Multi Data Source frame specify a data source name and a JNDI name for the data source. Select the algorithm type as Failover or Load Balancing. Click on the Next button (see Figure 15).

In the select Targets frame select the examplesServer or another server to deploy the multi-data source to. Click on the Next button (see Figure 16).

In the Select Data Source Type frame select XA driver for an XA data source or a non-XA driver for a non-XA data source. Click on the Next button (see Figure 17).

In the Add Data Sources frame add data sources from the Available list to the Chosen list. If new data sources are required click on the Create a New Data Source button. Then click on the Finish button (see Figure 18).

A new data source gets configured and added to the Multi Data Sources table. Click on the Activate Changes button to make the data source available to applications (see Figure 19).

A multi-data source can be configured by selecting the multi-data source link. The targets to which the multi-data source is deployed can be configured with the Targets tab. The data sources in the multi-data source can be configured with the Data Sources link in the Configuration tab. The multi-data source JNDI name can be modified in the Configuration>General frame. The Algorithm Type specifies the algorithm used to select a data source from which a connection is obtained. If the algorithm type is Failover, connection requests are sent successively to the data sources in the list until a connection is obtained or the end of the data source list is reached. If the algorithm type is a Load Balancing connection request the load is distributed evenly over the data sources in the list. If load balancing is the selected connection, failover is also provided with the connection requests being sent to different data sources in the list until a connection gets established or the end of the data source list is reached (see Figure 20).

A multi-data source also provides the Failover Request If Busy, Failover Callback Handler, and Test Frequency settings. For a multi-data source with the Failover algorithm if Failover Request If Busy is selected the connection request is sent to the next data source if all the connections in a data source are busy. The Failover Callback Handler specifies the application class to handle the callback sent when a multi-data source is ready to send a Failover connection request to another data source. Test Frequency specifies the interval in x number of seconds after which connections are tested. If a connection fails the connection is closed and reopened. If the connection fails again the connection is closed (see Figure 21).

 

Performance Tuning JDBC
Some of the JDBC design considerations to improve performance include the selection of the JDBC driver. The WebLogic Type 4 JDBC drivers from DataDirect provide comparable performance. The connection pooling provided by WebLogic Server data sources improves performance by keeping a pool of connections available for JDBC applications. Connection don't have to be opened and closed for each client. Tune the number of connections by setting the Initial Capacity equal to Maximum capacity in a connection pool. Maximize the reuse of connections rather than closing and opening connections. A connection may be pinned to a thread by setting the PinnedToThread configuration pool attribute. Close connections after a connection is not required.

Test Connections on Reserve tests connections before making a connection available to a client, but connection testing can reduce performance. To prevent frequent connection testing set the connection pool attribute Seconds to Trust an Idle Pool Connection, which specifies the number of seconds for which a connection returned by a client isn't tested with a SQL query. Data source performance can be improved by selecting Row Prefetch Enabled and an optimal pre-fetch size in configuring a data source. Row pre-fetching improves performance by fetching multiple rows from the server to an external client. Caching statements improves performance by reusing statements rather than creating new ones. Statement caching is specified in the connection pool configuration.

Developing a JDBC JSP Application
In this section we'll develop a JSP application to retrieved data from a MySQL database table with the data source configured in the Creating a Data Source section. First, create an example database table in the MySQL database. Access the MySQL database with the command mysql.

>mysql

To create a database table in the test database instance login to the test database.

mysql>use test

Create a table, Catalog with the SQL script in Listing 1.

Create a JSP, catalog.jsp. In the JSP import the java.sql, javax.sql, java.util, and javax.naming packages.

<%@ page

import="java.sql.*,javax.sql.*,java.util.*,javax.naming.*"

%>

Create a InitialContext object.

InitialContext ctx=new InitialContext();

Create a DataSource object from the JNDI name of the MySQL data source.

DataSource ds=(DataSource)ctx.lookup("jdbc/MySQLDS");

Obtain a JDBC connection from the DataSource object.

Connection connection=ds.getConnection();

Create a Statement object from the Connection object.

Statement stmt=connection.createStatement();

Run a SQL query with the executeQuery() method to return a ResultSet object. Specify a SQL query that selects all of the columns in the example database table Catalog.

ResultSet resultSet=stmt.executeQuery("Select * from Catalog");

Create an HTML table with a column corresponding to each of the rows in the result set. Add a header row to the HTML table. Iterate over the result set and add row values to the HTML table.

The JSP, catalog.jsp, to generate a HTML table from the example database table with the data source configured in the WebLogic Server is in Listing 2.

To run the JSP in WebLogic Server copy the JSP to the <weblogic91>\samples\server\examples\build\mainWebApp directory. Invoke the JSP with the URL http://localhost:7001/catalog.jsp. The JSP runs in WebLogic Server and generates an HTML table (see Figure 22).

Conclusion
WebLogic Server 9.0/9.1 has a new feature, multi-data source, a group of data sources with a JNDI name binding. A multi-data source facilitates maximum data source availability. A separate connection pool configuration in WebLogic Server 8.1 has been removed. The data source configuration in WebLogic Server 9.x provides enhanced connection request failover and load balancing between data sources.

More Stories By Deepak Vohra

Deepak Vohra is a Sun Certified Java 1.4 Programmer and a Web developer.

Comments (1) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
Sai 12/05/07 05:29:21 PM EST

This Article even though explains about the configuring Multi-data sources in BEA, it fails to make use of the Multi-data-source configured. The JSP page used in the example uses the MySQL Data Source configured directly, rather than accessing it using the Multi-data-source.