6
Migrating JDBC Applications

This chapter introduces the JDBC (Java Database Connectivity) API and describes how to connect to, and access data from, a database with WebSphere Advanced Edition 3.5.3. It also discusses ways of migrating WebSphere applications to Oracle Containers for J2EE (OC4J). The sections in this chapter are:

The JDBC API

The JDBC API enables Java programs to create sessions, execute SQL statements, and retrieve results from relational databases, providing vendor-independent access to relational data. The JDBC specification delivers a call-level SQL interface for Java that is based on the X/Open SQL call level interface specification.

The JDBC API consists of four major components: JDBC drivers, connections, statements, and a result set. Database vendors deliver only the driver, which should comply with JDBC specifications (for a complete description, see "Database Drivers"). The connection, statement, and result set components are in the JDBC API package (that is, the java.sql package).

The JDBC API provides interface classes for working with these components:

The java.sql.Driver and java.sql.DriverManager for managing JDBC drivers
The java.sql.Connection for using connections
The java.sql.Statement, for constructing and executing SQL statements
The java.sql.ResultSet for processing the results

The JDBC 2.0 API includes many new features in the java.sql package as well as the new Standard Extension package, javax.sql Features in the java.sql package include support for SQL3 data types, scrollable result sets, programmatic updates, and batch updates.

The new JDBC standard extension APIs, an integral part of Enterprise JavaBeans (EJB) technology, allows you to write distributed transactions that use connection pooling and connect to virtually any tabular data source, including files and spreadsheets.

When you write a JDBC application, the only driver-specific information required is the database URL. You can build a JDBC application so that it derives the URL information at runtime. Using the database URL, a user name, and password, your application first requests a java.sql.Connection from the DriverManager.

A typical JDBC program follows this process:

Load the database driver, using the driver's class name
Obtain the connection, using the JDBC URL for connection
Create and execute statements
Use result sets to navigate through the results
Close the connection.

Database Drivers

JDBC defines standard API calls to a specified JDBC driver, a piece of software that performs the actual data interface commands. The driver is considered the lower level JDBC API. The interfaces to the driver are database client calls, or database network protocol commands that are serviced by a database server.

Depending on the interface type, there are four types of JDBC drivers that translate JDBC API calls:

Type 1, JDBC-ODBC bridge: Translates calls into ODBC API calls.
Type 2, Native API driver: Translates calls into database native API calls. As this driver uses native APIs, it is vendor dependent. The driver consists of two parts: a Java language part that performs the translation and a set of native API libraries.
Type 3, Network Protocol: Translates calls into DBMS-independent network protocol calls. The database server interprets these network protocol calls into specific DBMS operations.
Type 4, Native Protocol: Translates calls into DBMS native network protocol calls. The database server converts these calls into DBMS operations.

The `DriverManager` Class

Using different drivers, a Java program can create several connections to several different databases. To manage driver operations, JDBC provides a driver manager class, the java.sql.DriverManager, which loads drivers and creates new database connections.

Registering JDBC Drivers

The DriverManager registers any JDBC driver that is going to be used. If a Java program issues a JDBC operation on a non-registered driver, JDBC raises a "No Suitable Driver" exception.

There are several ways to register a driver:

Register the driver explicitly by using
```
DriverManager.registerDriver(driver-instance) 
```
where driver-instance is an instance of the JDBC driver class.
Load the driver class by using
```
Class.forName(driver-class) 
```
where driver-class is the JDBC driver class.This loads the driver into the Java Virtual Machine. When loaded, each driver must register itself implicitly by using the DriverManager.registerDriver method.

For example, to register the DB2 JDBC Type 2 driver in the COM.ibm.db2.jdbc.app package, you can use either:
```
DriverManager.registerDriver(new COM.ibm.db2.jdbc.app.DB2Driver());
```
or
```
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
```
For an Oracle database:
```
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver ());
```
or
```
Class.forName("oracle.jdbc.driver.OracleDriver")
```

A J2EE server implicitly loads the driver based on the JDBC driver configuration, so no client-specific code is needed to load the driver. The JNDI (Java Naming and Directory Interface) tree provides the datatsource object reference.

WebSphere Advanced Edition 3.5.3 supports DB2, Informix, Microsoft SQL Server, Oracle, Sybase, Versant, and others. However, WebSphere does not support the Oracle thick JDBC driver. To use the Oracle thick JDBC driver, configure the data sources in Oracle Containers for J2EE (OC4J), as described in "Configuring Data Sources". OC4J will automatically load the driver classes during server startup.

Note:

If you use the Type 3 JDBC driver (COM.ibm.db2.jdbc.app.DB2Driver - jdcb:db2:DBNAME), you must install the DB2 CAE (Client Application Enabler) and then catalog the remote database. OC4J will treat the cataloged database as a local database.

The `DataSource` Class

The JDBC 2.0 specification introduced the java.sql.Datasource class to make the JDBC program 100% portable. In this version, the vendor-specific connection URL and machine and port dependencies were removed. This version also discourages using java.sql.DriverManager, Driver, and DriverPropertyInfo classes. The data source facility provides a complete replacement for the previous JDBC DriverManager facility. Instead of explictly loading the driver manager classes into the runtime of client applications, the centralized JNDI service lookup obtains the java.sql.Datasource object. The Datasource object can also be used to connect to the database.

According to the JDBC 2.0 API specification, a data source is registered under the JDBC subcontext or one of its child contexts. The JDBC context itself is registered under the root context. A Datasource object is a connection factory to a data source. WebSphere and OC4J both support the JDBC 2.0 DataSource API.

Configuring Data Sources

In WebSphere, you configure data sources using the Administrative Console to specify the data source name, database name, and JDBC URL string. This information is stored in a repository database.

OC4J uses flat files to configure data sources for all of its deployed applications. data sources are specified in the <ORACLE_HOME>/j2ee/home/config/data-sources.xml file.

Following is a sample data source configuration for an Oracle database. Each data source in data-sources.xml (xa-location, ejb-location and pooled-location) must be unique.

<data-source
class="com.evermind.sql.DriverManagerDataSource"
name="Oracle"
url="jdbc:oracle:thin@dlsun2058.us.oracle.com:1521:orcl"
xa-location="jdbc/xa/OracleXADS"
ejb-location="jdbc/OracleDS"
pooled-location="jdbc/OraclePoolDS"
connection-driver="oracle.jdbc.driver.OracleDriver"
username="scott"
password="tiger"
schema="database-schemas/oracle.xml"
inactivity-timeout="30"
max-connections="20"
/>

Table 6-1 describes all of the configuration parameters in data-sources.xml. (Not all of the parameters are shown in the example above).

Table 6-1 `data-sources.xml`* file*

Parameter	Description
`class`	Class name of the data source.
`connection-driver`	Class name of the JDBC driver
`connection-retry- interval`	Number of seconds to wait before retrying a failed connection. The default is 1.
`ejb-location`	JNDI path for binding an EJB-aware, pooled version of this data source; this version will participate in container-managed transactions. This is the type of data source to use from within EJBs and similar objects. This parameter only applies to a `ConnectionDataSource`.
`inactivity-timeout`	Number of seconds unused connections will be cached before being closed.
`location`	JNDI path for binding this data source.
`max-connect-attempts`	Number of times to retry a failed connection. The default is 3.
`max-connections`	Maximum number of open connections for pooling data sources.
`min-connections`	Minimum number of open connections for pooling data sources. The default is zero.
`name`	Displayed name of the data source.
`password`	User password for accessing the data source (optional).
`pooled-location`	JNDI path for binding a pooled version of this data source. This parameter only applies to a `ConnectionDataSource`.
`schema`	Relative or absolute path to a database-schema file for the database connection.
`source-location`	Underlying data source of this specialized data source.
`url`	JDBC URL for this data source (used by some data sources that deal with `java.sql.Connections`)
`username`	User name for accessing the data source (optional).
`wait-timeout`	Number of seconds to wait for a free connection if all connections are used. Default is 60.
`xa-location`	JNDI path for binding a transactional version of this data source. This parameter only applies to a `ConnectionDataSource`.
`xa-source-location`	Underlying `XADataSource` of this specialized data source (used by `OrionCMTDataSource`)

Note that WebSphere does not support subcontexts. For example, you cannot specify xa/OracleXADS, where xa is subcontext under the JDBC context. Morever, in WebSphere, the JDBC context is implicit, and you don't specify it (as you specify it explicitly for OC4J, in data-sources.xml). However, both WebSphere and OC4J automatically bind the data sources for you.

Configuring OC4J with DB2 Database

If you are using DB2 as your database, you need to create an additional file, db2.xml, in the
<ORACLE_HOME>/OC4J/j2ee/home/config/database-schema directory to define DB2 as a data source.

Below is an example of the schema file db2.xml:

<?xml version="1.0"?>
<!DOCTYPE database-schema PUBLIC "-//Evermind//- Database schema" 
"http://www.orionserver.com/dtds/database-schemas.dtd"> 
<database-scheme name="DB2" not-null="not null" null="default null" 
  primary-key="primary key">
  <type-mapping type="java.lang.String" name="varchar(255)" />
  <type-mapping type="int" name="integer" />
  <type-mapping type="long" name="bigint" />
  <type-mapping type="float" name="double" />
  <type-mapping type="double" name="double" />
  <type-mapping type="byte" name="smallint" />
  <type-mapping type="char" name="smallint" />
  <type-mapping type="short" name="smallint" />
  <type-mapping type="boolean" name="char(1)" />
  <type-mapping type="java.util.Date" name="timestamp" />
  <type-mapping type="java.io.Serializable" name="blob(1 M)" />
  <disallowed-field name="add" />
  <disallowed-field name="admin" />
  <disallowed-field name="wvarchar" />
</database-scheme>

The following is an example of a corresponding data-sources.xml file with the db2.xml file specified:

<data-source 
name="Default data-source"
class="com.evermind.sql.ConnectionDataSource"
location="jdbc/DefaultDS"
pooled-location="jdbc/DefaultPooledDS"
xa-location="jdbc/xa/DefaultXADS"
ejb-location="jdbc/DefaultEJBDS"
url="jdbc:db2:dbTest"
connection-driver="COM.ibm.db2.jdbc.app.DB2Driver"
username="myUserName"
password="myPwd"
inactivity-timeout="30"
schema="database-schemas/db2.xml"
/>

Obtaining a Data Source Object

Obtaining a data source object involves binding to the JNDI initial context and doing a lookup for the subcontext jdbc/sampleDB. To do this, you have to get a handle to the intial context javax.naming.InitialContext. IntialContext is the root context of the JNDI namespace. InitialContext has two constructors:

A default constructor that takes no parameters
A constructor that takes one parameter, java.util.Properties or java.util.HashTable

For OC4J, you must change your code to use the constructor that takes a parameter. The following code example illustrates this:

//WebSphere Code
try
{
  java.util.Properties parms = new java.util.Properties(); 
  parms.setProperty(Context.INITIAL_CONTEXT_FACTORY,

                "com.ibm.ejs.ns.jndi.CNInitialContextFactory");
  javax.naming.Context ctx = new javax.naming.InitialContext(parms);
  javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("jdbc/SampleDB");
  java.sql.Connection conn = ds.getConnection();

  // process the results
  ...
}

To migrate from WebSphere, you must change the class that implements the initial context factory (Context.INITIAL_CONTEXT_FACTORY) of the JNDI tree:

from the Websphere-specific class:

com.ibm.ejs.ns.jndi.CNInitialContextFactory

to the OC4J-specific class:

com.evermind.server.ApplicationClientInitialContextFactory

Connection Pooling

Most web-based resources, such as servlets and application servers, access information in a database. Each time a resource attempts to access a database, it must establish a connection to the database using system resources to create the connection, maintain it, and release it when it is no longer in use. The resource overhead is particularly high for web-based applications due to the frequency and volume of web users connecting and disconnecting. Often, more resources are consumed in connecting and disconnecting than in executing the business logic.

Connection pooling enables you to control connection resource usage by spreading the connection overhead across many user requests. A connection pool is is a cached set of connection objects that multiple clients can share when they need to access a database resource. The resources to create the connections in the pool are expended only once for a specified number of connections. The connections are left open and re-used by many client requests instead of each client request consuming resources to create and close its own connection. Connection pooling improves overall performance in the following ways:

Reducing the load on the middle-tier server
Minimizing resource usage by having session-wide create and close operations
Eliminating bottlenecks caused by socket and file descriptor limitations and 'n' user license limitations

The JDBC 2.0 specification allows you to define a pool of JDBC database connections, with the following objectives:

Maximize the availability of connections to resources
Minimize the idle connections in the pool
Return orphan connections to the pool and make them available for reuse by other servlets or application servers.

To meet these objectives, you should perform the following:

Set the maximum connection pool size property equal to the maximum number of concurrently active user requests expected
Set the minimum connection pool size property equal to the minimum number of concurrently active user requests expected.

The connection pooling properties ensure that as the number of user requests decreases, unused connections are gradually removed from the pool. Likewise, as the number of user requests begins to grow, new connections are created. The balance of connections is maintained so that connection re-use is maximized and connection creation overhead minimized. You can also use connection pooling to control the number of concurrent database connections.

Migrating WebSphere Connection Pooling to Oracle9i Application Server

WebSphere Advanced Edition 3.5.3 provides two options for accessing database connections:

Connection pooling (model based on JDBC 2.0)
Connection manager (model based on JDBC 1.0)

Migrating from Websphere JDBC 2.0 connection pooling:

WebSphere implements JDBC 2.0 connection pooling and data source objects using the following packages.

import com.ibm.db2.jdbc.app.stdext.javax.sql.*; 
import com.ibm.ejs.dbm.jdbcext.*;

To migrate from the WebSphere JDBC 2.0 connection to OC4J you must replace these import packages with javax.sql.* .

An application component that obtains two or more connections to the same database manager (using either the same data source or different data source) must use data sources with JTA-enabled drivers. For more information, refer to Oracle9iAS Containers for J2EE User's Guide.

IBM Extensions

WebSphere provides the following extension packages for data access. Applications using these packages require code level changes for migration.

Data Access Beans

WebSphere Advanced Edition 3.5.3 also provides data access beans (in addition to access beans for EJBs), which offer a set of features for working with relational database queries and result sets. The com.ibm.db package contains the data access JavaBean classes. The classes are in the databeans.jar file (found in the lib directory under the application server root install directory). You will need this JAR file in your classpath in order to compile a servlet using the data access JavaBeans.

If you have lot of code using data access beans that need to be migrated to OC4J, then put databeans.jar in the classpath of OC4J. However, Oracle recommends that you migrate to JDBC 2.0 APIs.

Connection Pool Manager

As mentioned, IBM WebSphere 3.5.x still supports connection pooling with a proprietary connection pool manager. Oracle recommends that you develop connection pooling using IBM's standard extensions for JDBC 2.0.

6Migrating JDBC Applications