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.

6.1 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

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

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

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

6.2.2 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. JBoss and OC4J both support the JDBC 2.0 DataSource API.

6.2.3 Configuring Data Sources

OC4J uses flat files to configure data sources for all of its deployed applications. data sources are specified in the following descriptor file:

UNIX: <ORACLE_HOME>/j2ee/home/config/data-sources.xml

NT: <ORACLE_HOME>\j2ee\home\config\data-sources.xml

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@node2058.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. (need clarification on this)
`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`)

6.2.4 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:

//JBoss 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
  ...
}

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