Chapter 8   Using JDBC for Database Access


This chapter describes how to use the Java Database Connectivity (JDBC) API for database access with iPlanet Application Server.

This chapter provides high-level instructions for using the iAS implementation of JDBC in servlets and EJBs, and it indicates specific iAS resources affected by JDBC statements when those resources have clear programming ramifications. In iAS, Enterprise JavaBeans (EJBs) support database access primarily through the JDBC API. iAS supports all of JDBC 2.0 API as well as many of the emerging JDBC 2.0 extensions to JDBC, including result set enhancements, batch updates, distributed transactions, row sets, and JNDI support for datasource name lookups.

While this chapter assumes familiarity with JDBC 2.0, it also describes specific implementation issues that may have programming ramifications. For example, the JDBC specifications do not make it clear what constitute JDBC resources. In the specifications, some JDBC statements—such as any of the Connection class methods that close database connections—release resources without specifying exactly what those resources are.

This chapter contains the following sections:

• Introducing JDBC

• Using JDBC in Server Applications

• Handling Connections

• Working with JDBC Features

Introducing JDBC

---

From a programming perspective, JDBC is a set of Java classes and methods that let you embed database calls in your server applications. That's all you need to know in order to start using JDBC in your server applications.

More specifically, JDBC is a set of interfaces that every server vendor, such as iPlanet, must implement according to the JDBC specifications. iAS provides a JDBC type 2 driver which supports a variety of database backends. This driver processes the JDBC statements in your applications and routes the SQL arguments they contain to your database engines.

JDBC lets you write high-level, easy-to-use code that can operate seamlessly with and across many different databases without your needing to know most of the low-level database implementation details.

Supported Functionality

The JDBC specification is a broad, database-vendor independent set of guidelines that try to encompass the broadest range of database functionality possible in a simple framework.

At a minimum, JDBC assumes that all underlying database vendors support the SQL-2 database access language. Since its original inception, the JDBC specification has grown. It now has three parts:

• JDBC 2.0 describes the core set of database access and functionality that server vendors must implement in order to be JDBC compliant. The iPlanet Application Server (iAS) fully meets this compliance standard. From the database vendor's perspective, JDBC 2.0 describes a database access model that permits full access to the standard SQL-2 language, those portions of the standard language each vendor supports, and those extensions to the language that each vendor implements.

• JDBC 2.0 describes additional database access and functionality. Much of this functionality involves support for newly-defined SQL-3 features, data types, and mappings. Other parts of JDBC 2.0 extend JDBC 2.0 features. The iAS implementation of JDBC supports most of the JDBC feature enhancements, but omits support for the new SQL-3 data types, such as blobs, clobs, and arrays, which many database vendors do not, as yet, fully support in their relational database management systems. The iAS JDBC implementation also omits support for SQL-3 data type mapping

• JDBC 2.0 Standard Extension API describes advanced support features, many of which offer the promise of improved database performance. The iAS implementation of JDBC currently supports JNDI and rowsets.

Understanding Database Limitations

When you start using JDBC in your server applications, you may encounter situations when you do not get the results you desire or expect. You may think the problem lies in JDBC or in the iAS implementation of the JDBC driver. In fact, the vast majority of the time, the problems actually lie in the limitations of your database engine.

Because JDBC covers the broadest possible spectrum of database support, it enables you to attempt operations not every database supports. For example, most database vendors support most of the SQL-2 language, but no vendor provides fully unqualified support for all of the SQL-2 standard. Most vendors built SQL-2 support on top of their already existing proprietary relational database management systems, and either those proprietary systems offer features not in SQL-2, or SQL-2 offers features not available in those systems. Most vendors, too, have added non-standard SQL-2 extensions to their implementations of SQL in order to support their proprietary features. JDBC provides ways for you to access vendor-specific features, but it's important to realize that these features may not be available for all the databases you use. This is especially true when you build an application that uses databases from two or more different vendors.

As a result, not all vendors fully support all aspects of every available JDBC class, method, and method arguments. More importantly, a set of SQL statements embedded as an argument in a JDBC method call may or may not be supported by the database or databases your server application uses. In order to make maximum use of JDBC, you must consult your database vendors' documentation about which aspects of SQL and JDBC they support. Before you call iAS technical support for a database problem, make sure you first eliminate the possibility that your database is the cause of the problem.

Understanding iAS Limitations

Like JDBC, iAS attempts to support the broadest possible spectrum of database engines and features. In some cases, iAS itself or the iAS JDBC driver may not fully support a particular feature of a database you use, or they may report incorrect information. If you can't access a database feature from your iAS application and you have eliminated the database as the source of the problem, check this section of the documentation and the product release notes to see if the problem you encounter is a documented iAS limitation. If not, document the problem fully and contact iAS technical support.

Note Some JDBC access problems can result if you attempt to access JDBC features that are either partially supported or not supported by the iAS JDBC driver. Almost all of these feature limitations apply to JDBC 2.0.

The following table lists JDBC features that are either partially or completely unsupported in iAS 6.0:

Table 8-1 JDBC Feature Limitations

Feature	Limitation
Connection.setTransactionIsolation	Works only with isolation levels supported by your database vendors.
Connection.getTypeMap	Type maps are not supported.
Connection.setTypeMap	Type maps are not supported.
Connection.cancel	Works only with databases that support it.
PreparedStatement.setObject	Works only with simple data types.
PreparedStatement.addBatch	Works only with supported data manipulation statements that return a count of records changed.
PreparedStatement.setRef	References are not supported.
PreparedStatement.setBlob	Blobs are not supported. Use setBinaryStream() instead.
PreparedStatement.setClob	Clobs are not supported. Use setBinaryStream() instead.
PreparedStatement.setArray	Arrays are not supported. Use setBinaryStream() instead.
PreparedStatement.getMetaData	Not supported.
CallableStatement.getObject	Works only with scalar types. JDBC 2.0 offers a second version of this method that includes a map argument. The map argument is ignored.
CallableStatement.getRef	References are not supported.
CallableStatement.getBlob	SQL3-style blobs are not supported.
CallableStatement.getClob	SQL3-style clobs are not supported.
CallableStatement.getArray	Arrays are not supported.
CallableStatement	Updatable ResultSet is not supported.
ResultSet.getCursorName	Behavior differs depending on database: For Oracle, if user does not specify a cursor name with SetCursorName, an empty string is returned. For Sybase, if the result set is not updatable, a cursor name is automatically generated by iAS. Otherwise an empty string is returned. For ODBC, Informix, and DB2, the driver returns a cursor name if none is specified.
ResultSet.getObject	Works only with scalar types. JDBC 2.0 offers two other versions of this method that includes a map argument. The map argument is ignored.
ResultSet.updateObject	Works only with scalar types.
ResultSet.getRef	References are not supported.
ResultSet.getBlob	SQL3-style blobs are not supported.
ResultSet.getClob	SQL-style clobs are not supported.
ResultSet.getArray	Arrays are not supported.
ResultSetMetaData.getTableName	Returns an empty string for non-ODBC database access.
DatabaseMetaData.getUDTs	Not supported.

For more information about working with ResultSet, ResultSetMetaData, and PreparedStatement, see the appropriate sections later in this chapter.

Supported Databases

iAS currently connects to many different relational databases. The following tables lists the most commonly used databases that are supported.

Table 8-2 Supported Databases

Database	Notes
Oracle	Support is offered through the Oracle OCI interface. Both Oracle 7 and Oracle 8 database instances are supported in a fully multi-threaded environment. iAS also coexists with all Oracle RDBMS tools and utilities, such as SQL*Plus, Server Manager, and Oracle Backup.
Informix	Support is offered through Informix CLI interface. Both Informix Online Dynamic Server and Informix Universal Server are supported.
Sybase	Support is offered through Sybase CTLIB.
Microsoft SQLServer	Support is offered through the Microsoft ODBC interface. Microsoft SQLServer on Windows NT only is supported.
DB2	Support is offered through the DB2 CLI client interface. DB2 versions 5.2 and 6.1 are supported.
ODBC	iAS does not specifically support or certify any ODBC 2.0 or 3.0 compliant driver set, though they may work.

Both because the databases supported by iAS are constantly updated, and because database vendors consistently upgrade their products, you should always check with iAS technical support for the latest database support information.

Using JDBC in Server Applications

---

JDBC is part of the iAS run-time environment. In theory, this means that JDBC is always available to you any time you use Java to program an application. In a typical multi-tiered server application, it is theoretically possible to use JDBC to access a database backend from the client, from the presentation layer, in servlets, and in Enterprise Java Beans (EJBs).

In practice, however, it usually makes sense—for security and portability reasons—to restrict database access to the middle layers of a multi-tiered server application. In the iAS programming model, this means placing all JDBC calls in servlets and EJBs, with a strong preference toward EJBs.

There are two reasons for this programming preference. One is that placing all JDBC calls inside EJBs makes your application more modular and more portable. Another is that EJBs provide built-in mechanisms for transaction control. Placing JDBC calls in well-designed EJBs frees you from programming explicit transaction control using JDBC or java.transaction.UserTransaction that provide low-level transaction support under JDBC.

Note Make sure to use a globally available datasource to create a global (bean-wide) connection so that the EJB transaction manager can control the transaction.

Using JDBC in EJBs

Placing all your JDBC calls in EJBs ensures a high degree of server application portability. It also frees you from having to manage transaction control with explicit JDBC calls unless you so desire. Because EJBs are components, you can use them as building blocks for many applications with little or no recoding, and maintain a common interface to your database backends.

Managing Transactions with JDBC or javax.transaction.UserTransaction

Using the EJB transaction attribute property to manage transactions is recommended, but not mandatory. There may be times when explicit coding of transaction management using JDBC or javax.transaction.UserTransaction is appropriate for your application. In these cases, you code transaction management in the bean yourself. Using an explicit transaction in an EJB is called a bean-managed transactions.

Transactions can be local to a specific method (method-specific) or they can encompass the entire bean (bean-wide).

There are two steps for creating a bean managed transaction:

1. Set the transaction attribute property of the EJB to TX_BEAN_MANAGED in the bean's deployment descriptor.

2. Code the appropriate JDBC or transaction management statements in the bean, including statements to start the transaction, and to commit it or roll it back.

It is an error to code explicit transaction handling in EJBs for which the transaction attribute property is not TX_BEAN_MANAGED. For more information about handling transactions with JDBC, see the JDBC 2.0 API specification.

Specifying Transaction Isolation Level

You can specify or examine the transaction level for a connection using the methods setTransactionIsolation() and getTransactionIsolation(), respectively. Note that you can not call setTransactionIsolation() during a transaction.

Transaction isolation levels are defined as follows:

Table 8-3 Transaction Isolation Levels

Transaction Isolation Level	Description
TRANSACTION_NONE	Transactions are not supported. Only used with Connection.getTransactionIsolation()
TRANSACTION_READ_COMMITTED	Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
TRANSACTION_READ_UNCOMMITTED	Dirty reads, non-repeatable reads and phantom reads can occur.
TRANSACTION_REPEATABLE_READ	Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
TRANSACTION_SERIALIZABLE	Dirty reads, non-repeatable reads and phantom reads are prevented.

Before you specify a transaction isolation level for a bean, make sure the level is supported by your relational database management system. Not all databases support all isolation levels. You can test your database programmatically by using the method supportsTransactionIsolationLevel() in java.sql.DatabaseMetaData, as in the following example:

java.sql.DatabaseMetaData db;
if (db.supportsTransactionIsolationLevel(TRANSACTION_SERIALIZABLE) {
    Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
}

For more information about these isolation levels and what they mean, see the Java Database Connectivity (JDBC) 2.0 API Specification.

Using JDBC in Servlets

Servlets are at the heart of your iAS server applications. They stand between a client interface, such as an HTML page on a browser, the JSP that generated the HTML, and the EJBs that do the bulk of your application's work.

iAS applications use JDBC embedded in EJBs for most database access. This is the preferred arrangement for database access using iAS because it enables you to take advantage of the transaction control built into EJBs and their containers. Servlets, however, can also provide database access through JDBC.

In some situations, accessing a database directly from a servlet can offer a speed advantage over accessing databases from EJBs. There is less call overhead, especially if your application is spread across servers so that your EJBs are accessible only through the Java Remote Method Interface (RMI). Use direct database service through servlets sparingly. If you do provide database access from servlets, restrict access to those situations where access is very short duration, the transaction is read-only, and you can take advantage of the new JDBC 2.0 rowset class.

If you choose to access a database from a servlet, use the new JDBC 2.0 rowset interface to interact with a database. A rowset is a Java object that encapsulates a set of rows that have been retrieved from a database or other tabular datasource, such as a spreadsheet. The rowset interface provides JavaBean properties that allow a rowset instance to be configured to connect to a datasource and retrieve a set of rows. For more information about working with rowsets, see Working with Rowsets.

Handling Connections

---

iAS implements the JDBC 2.0 compliant interface java.sql.Connection. The behavior of the connection depends on whether it is a local, global or container managed local connection.

Local Connections

A Connection object is called a local connection if its transaction context is not managed by an EJB container. The transaction context in a local connection can not propagate across processes or across datasources; it is local to the current process and to the current datasource.

The transaction context on this type of connection is managed using the methods setAutoCommit(), commit(), and rollback().

Registering a Local Datasource

The first step in creating a local connection is to register the datasource with iAS. Once the datasource is registered, the datasource can be used to make connections to the listed database using getConnection().

You can register the datasource by creating an XML resource descriptor file that describes the properties of the datasource. Next, register the properties with iAS using the Administration Tool or the provided utility resReg. resReg takes as its argument, the name of the resource descriptor file describing the datasource.

Note When run, resReg overwrites existing entries.

For example, to register a datasource called SampleDS which connects to an Oracle database using the username kdemo, password kdemo, database ksample and server ksample, create a XML descriptor file like the following, and name it SampleDS.xml (use the iAS Deployment Tool to create this XML file):

<ias-resource>
   <resource>
      <jndi-name>jdbc/SampleDS</jndi-name>
      <jdbc>
         <database>ksample</database>
         <datasource>ksample</datasource>
         <username>kdemo</username>
         <password>kdemo</password>
         <driver-type>ORACLE_OCI</driver-type>
      </jdbc>
   </resource>
</ias-resource>

You can then use this resource descriptor file to register the datasource with the following command:

resReg GlobalSampleDS.xml

For more information about resource descirptor files, see Chapter 10 "Packaging for Deployment". For more information about the iAS Administration Tool, see the Administration and Deployment Guide.

Global Connections

A Connection object is called a global connection if its transaction context is managed by EJB container. The transaction context in a global connection can be propagated across datasources. The transaction context is managed implicitly by the EJB container for container-managed transactions, or explicitly in case of bean-managed transactions. For more information about transactions, see Chapter 7 "Handling Transactions with EJBs".

Transaction management methods, e.g. setAutoCommit(), commit(), and rollback(), are disabled for global connections.

Using Resouce Managers

The collection of datasources in which a global transaction participates is known as a resource manager. All resources managers needs to be registered with iAS and be enabled so that they participate in global transactions. Resource managers can be set up at install time or they can also be set up using the iAS Administration Tool (see the Administration and Deployment Guide). A global connection must be associated with a resource manager.

Registering a Global Datasource

The first step in creating a local connection is to register the datasource with iAS. Once the datasource is registered, the datasource can be used to make connections to the listed database using getConnection().

You can register the datasource by creating an XML resource descriptor file that describes the properties of the datasource. Next, register the properties with iAS using the Administration Tool or the provided utility resReg. resReg takes as its argument, the name of the resource descriptor file describing the datasource.

Note When run, resReg overwrites existing entries.

For example, to register a datasource called GlobalSampleDS which connects to an Oracle database using the username kdemo, password kdemo, database ksample and server ksample, create a XML descriptor file like the following, and name it GlobalSampleDS.xml (use the iAS Deployment Tool to create this XML file):

<ias-resource>
   <resource>
      <jndi-name>jdbc/GlobalSampleDS</jndi-name>
      <jdbc>
         <database>ksample</database>
         <datasource>ksample</datasource>
         <username>kdemo</username>
         <password>kdemo</password>
         <driver-type>ORACLE_OCI</driver-type>
         <resource-mgr>ksample_rm</resource-mgr>
      </jdbc>
   </resource>
</ias-resource>

You can then use this resource descriptor file to register the datasource with the following command:

resReg GlobalSampleDS.xml

For more information about resource descirptor files, see Chapter 10 "Packaging for Deployment". For more information about the iAS Administration Tool, see the Administration and Deployment Guide.

Creating a Global Connection

The following code demonstrates how a datasource is looked up and a connection created from it. As is illustrated by the code, the string that is looked up is the same as specified in the <jndi-name> tag in the resource descriptor file.

InitialContext ctx = null;
String dsName1 = "jdbc/GlobalSampleDS";
DataSource ds1 = null;

try
{
    ctx = new InitialContext();
    ds1 = (DataSource)ctx.lookup(dsName1);

    UserTransaction tx = ejbContext.getUserTransaction();

    tx.begin();

    Connection conn1 = ds1.getConnection();

    // use conn1 to do some database work -- note that conn1.commit(),
    // conn1.rollback() and conn1.setAutoCommit() can not used here

    tx.commit();

} catch(Exception e) {
    e.printStackTrace(System.out);
}

Container Managed Local Connections

A Connection object is considered a container managed local connection when its' transaction context is managed by the EJB container and global transactions are disabled. In the case of container managed transactions, the transaction context is managed implicitly by the EJB container and in the case of bean managed transactions the transaction context is handled explicitly.

Connection object methods setAutoCommit(), commit(), and rollback() are disabled for this type of connection.

For more information on how to enable or disable global transactions in an EJB container, please refer to the iAS Administration and Deployment Guide.

Registering a Container Managed Local Datasource

The registering process for container managed local datasources is the same as for the local and global datasources, see Registering a Local Datasource.

Working with JDBC Features

---

While this chapter is not a JDBC primer, it does introduce you to using JDBC in EJBs with iAS 6.0. The following sections describe various JDBC interfaces and classes that have either have special requirements in the iAS environment, or that are new JDBC 2.0 features that you are especially encouraged to use when developing iAS server applications.

For example, "Working With Connections" describes what resources iAS releases when a connection is closed because that information differs among different JDBC implementations. On the other hand, "Pooling Connections" and "Working with Rowsets" offer more extensive coverage because these are new JDBC 2.0 features that offer increased power, flexibility, and speed for your server applications.

Working With Connections

When you open a connection in JDBC, iAS allocates resources for the connection. If you call Connection.close() when a connection is no longer needed, the connection resources are freed. Always reestablish connections before continuing database operations after you call Connection.close().

You can use Connection.isClosed() to test whether the connection is closed. This method returns false if the connection is open, and returns true only after Connection.close() is called.

You can determine if a database connection is invalid by catching the exception that is thrown when a JDBC operation is attempted on a closed connection.

Finally, opening and closing connections is an expensive operation. If your application uses several connections, and if connections are frequently opened and closed, iAS automatically provides connection pooling. Connection pooling provides a cache of connections that are automatically closed when necessary.

Note Connection pooling is an automatic feature of iAS; the API is not exposed.

setTransactionIsolation
Not all database vendors support all levels of transaction isolation available in JDBC. iAS permits you to specify any isolation level your database supports, but throws an exception against values your database does not support. For more information, see Specifying Transaction Isolation Level.

getTypeMap, setTypeMap
The iAS implementation of the JDBC driver does not support type mapping, a new SQL-3 feature that most database vendors also do not support. The methods exist, but currently do nothing.

cancel
cancel() is supported for all databases.

Pooling Connections

Two of the costlier database operations you can execute in JDBC are for creating and destroying database connections. Connection pooling permits a single connection cache to be used for connection requests. When you use connection pooling, a connection is returned to the pool for later reuse without actually destroying it. A later call to create a connection merely retrieves an available connection from the pool instead of actually creating a new one.

iAS automatically provides JDBC connection pooling wherever you make JDBC calls. The process of pooling database connections works differently for each type of connection.

• For local connections, the database connections are pooled when they are closed by the application.

• For global connections, the database connections are tied to the thread that initiated the transaction. These connections are later resused by transactions that get executed on that thread.

• For container managed local connections, the connection.close() method does not release the connection to the connection pool immediately. Once the transaction that the connection is participating in is finished, the connection is released back to the connection pool by the application server.

In each java engine, each driver (Oracle, Sybase, Informix and DB2) has its own connection pools. Each connection pool can be sized according to the application requirements. See the Administration and Deployment Guide for more information on the connection pool settings (such as maximum number of connections, connection timeout and so on).

Working with ResultSet

ResultSet is a class that encapsulates the data returned by a database query. Be aware of the following behaviors or limitations associated with this class.

Concurrency Support

iAS supports concurrency for FORWARD-ONLY READ-ONLY result sets. On callable statements, iAS also supports concurrency for FORWARD-ONLY UPDATABLE result sets.

iAS also supports concurrency for SCROLL-INSENSITIVE READ-ONLY result sets.

SCROLL-SENSITIVE concurrency is not supported.

Updatable Result Set Support

In iAS, creation of updatable result sets is restricted to queries on a single table. The SELECT query for an updatable result set must include the FOR UPDATE clause:

SELECT...FOR UPDATE [OF column_name_list]

Note You can use join clauses to create read-only result sets against multiple tables; these result sets are not updateable.

For Sybase, the select list must include a unique index column. Sybase also permits you to call execute() or executeQuery() to create an updatable result set, but the statement must be then be closed before you can execute any other SQL statements.

To use an updatable result set with Oracle 8, you must wrap the result set query in a transaction:

conn.setAutoCommit(false);
ResultSet rs =
   stmt.executeQuery("SELECT...FOR UPDATE...");

...

rs.updateRows();

...

conn.commit();

For Microsoft SQL Server, if concurrency for a result set is CONCUR_UPDATABLE, the SELECT statement in the execute() or executeQuery() methods must not include the ORDER BY clause.

getCursorName
One method of ResultSet, getCursorName(), enables you to determine the name of the cursor used to fetch a result set. If a cursor name is not specified by the query itself, different database vendors return different information. iAS attempts to handle these differences as transparently as possible. The following table indicates the name of a cursor returned by different database vendors if no cursor name is specified in the initial query.

Table 8-4

Database Vendor	getCursorName Value Returned
Oracle	If a cursor name is not specified with setCursorName(), an empty string is returned.
Sybase	If a cursor name is not specified with setCursorName(), and the result set is not updatable, a unique cursor name is automatically generated by iAS. Otherwise an empty string is returned.
Informix, DB2, ODBC	If a cursor name is not specified with setCursorName(), the driver automatically generates a unique cursor name.

getObject
iAS implements this JDBC method in a manner that only works with scalar data types. JDBC 2.0 adds additional versions of this method that include a map argument. iAS does not implement maps, and ignores the map argument if it is supplied.

getRef, getBlob, getClob, and getArray
References, blobs, clobs, and arrays are new SQL-3 data types. iAS does not implement these data objects or the methods that work with them. You can, however, work with references, blobs, clobs, and arrays using getBinaryStream() and setBinaryStream().

Working with ResultSetMetaData

The getTableName() method only returns meaningful information for OBDC-compliant databases. For all other databases, this method returns an empty string.

Working with PreparedStatement

PreparedStatement is a class that encapsulates a query, update, or insert statement that is used repeatedly to fetch data. Be aware of the following behaviors or limitations associated with this class.

Note You can use the iAS feature SqlUtil.loadQuery() to load a iASRowSet with a prepared statement. For more information, see the entry for the SqlUtil class in the iAS Foundation Class Reference.

setObject
This method may only be used with scalar data types.

addBatch
This method enables you to gang a set of data manipulation statements together to pass to the database as if it were a single statement. addBatch() only works with SQL data manipulation statements that return a count of the number of rows updated or inserted. Contrary to the claims of the JDBC 2.0 specification, addBatch() does not work with any SQL data definition statements such as CREATE TABLE.

setRef, setBlob, setClob, setArray
References, blobs, clobs, and arrays are new SQL-3 data types. iAS does not implement these data objects or the methods that work with them. You can, however, work with references, blobs, clobs, and arrays using getBinaryStream() and setBinaryStream().

getMetaData
Not all database systems return complete metadata information. See your database vendor's documentation to determine what kind of metadata your database provides to clients.

Working with CallableStatement

CallableStatement is a class that encapsulates a database procedure or function call for databases that support returning result sets from stored procedures. Be aware of the following limitation associated with this class. The JDBC 2.0 specfication states that callable statements can return an updatable result set. This feature is not supported in iAS.

getRef, getBlob, getClob, getArray
References, blobs, clobs, and arrays are new SQL-3 data types. iAS does not implement these data objects or the methods that work with them. You can, however, work with references, blobs, clobs, and arrays using getBinaryStream() and setBinaryStream().

Handling Batch Updates

The JDBC 2.0 Specification provides for a batch update feature that allows for an application to pass multiple SQL update statements (INSERT, UPDATE, DELETE) in a single request to a database. This ganging of statements can result in a significant increase in performance when a large number of update statements are pending.

The Statement class includes two new methods for executing batch updates:

• addBatch() permits you to add an SQL update statement (INSERT, UPDATE, DELETE) to a group of such statements prior to execution. Only update statements that return a simple update count can be grouped using this method.

• executeBatch() permits you to execute a collection of SQL update statements as a single database request.

In order to use batch updates, your application must disable auto commit options:

...
// turn off autocommit to prevent each statement from commiting separately
con.setAutoCommit(false);

Statement stmt = con.createStatement();

stmt.addBatch("INSERT INTO employees VALUES(4671, 'James Williams')");
stmt.addBatch("INSERT INTO departments VALUES(560, 'Produce')");
stmt.addBatch("INSERT INTO emp_dept VALUES( 4671, 560)");

//submit the batch of updates for execution
int[] updateCounts = stmt.executeBatch();
con.commit();

To remove all ganged statements from a batch operation before executeBatch() is called, (for example, because an error is detected), call clearBatch().

Note The JDBC 2.0 specification erroneously implies that batch updates can include data definition language (DDL) statements such as CREATE TABLE. DDL statements do not return a simple update count, and so cannot be grouped for a batch operation. Also, some databases do not allow data definintion statements in transactions.

Creating Distributed Transactions

The JDBC 2.0 Specification provides the capability for handling distributed transactions. A distributed transaction is a single transaction that applies to multiple, heterogeneous databases that may reside on separate server machines.

Distributed transaction support is already built into the iAS EJB container, so if you use EJBs that do not specify the TX_BEAN_MANAGED transaction attribute, you get automatic support for distributed transactions in your application.

In servlets and in EJBs that specify the TX_BEAN_MANAGED transaction attribute, you can still use distributed transactions, but you must manage transactions using the JTS UserTransaction class. For example:

InitialContext ctx = null;
String dsName1 = "jdbc/SampleDS1";
String dsName2 = "jdbc/SampleDS2";
DataSource ds1 = null;
DataSource ds2 = null;

    try {
        ctx = new InitialContext();
        ds1 = (DataSource)ctx.lookup(dsName1);
        ds2 = (DataSource)ctx.lookup(dsName2);

    } catch(Exception e) {
      e.printStackTrace(System.out);
    }

UserTransaction tx = ejbContext.getUserTransaction();

tx.begin();

Connection conn1 = ds1.getConnection();
Connection conn2 = ds2.getConnection();

// do some work here

tx.commit();

In this example, ds1 and ds2 must be registered with iAS as global datasources. In other words, their datasource properties files must include a ResourceMgr entry whose value must be configured at install time.

For example a global database properties file looks like:

DataBase=ksample
DataSource=ksample
UserName=kdemo
PassWord=kdemo
DriverType=ORACLE_OCI
ResourceMgr=orarm

In this example, orarm must be a valid ResourceMgr entry and must be enabled to get a global connection successfully. In order to be a valid ResourceMgr entry, an resource manager must be listed the registry in CCS0\RESOURCEMGR, and the entry itself must have the following properties.

DatabaseType (string key)
IsEnabled (integer type)
Openstring ( string type key)
ThreadMode ( string type key)

Working with Rowsets

A rowset is an object that encapsulates a set of rows retrieved from a database or other tabular data store, such as a spreadsheet. To implement a rowset, your code must import javax.sql, and implement the RowSet interface. RowSet extends the java.sql.ResultSet interface, permitting it to act as a JavaBeans component.

Because a RowSet is a JavaBean, you can implement events for the rowset, and you can set properties on the rowset. Furthermore, because RowSet is an extension of ResultSet, you can iterate through a rowset just as you would iterate through a result set.

You fill a rowset by calling the RowSet.execute() method. The execute() method uses property values to determine the datasource and retrieve data. The actual properties you must set and examine depends upon the implementation of RowSet you invoke.

For more information about the RowSet interface, see the JDBC 2.0 Standard Extension API Specification.

Using iASRowSet

iAS provides a rowset class called iASRowSet. iASRowSet extends ResultSet, so call methods are inherited from the ResultSet object. iASRowSet overrides the getMetaData() and close() methods of ResultSet.

The RowSet interface is fully supported except as noted in the following table.

Table 8-5 RowSet Interface Support Exceptions

Method	Argument	Exception Thrown	Reason
setReadOnly()	false	SQLException	iASRowSet is already read-only.
setType()	TYPE_SCROLL_INSENSITIVE	SQLException	SCROLL_INSENSITIVE is not supported.
setConcurrency()	CONCUR_UPDATABLE	SQLException	iASRowSet is read-only.
addRowSetListener()	any	None	Not supported.
removeRowSetListener()	any	None	Not supported.
setNull()	any type name	Arguments ignored	Not supported.
setTypeMap()	java.util.Map	None	Map is a JDBC 2.0 feature that is not currently supported.

RowSetReader
iASRowSet provides a full implementation of the RowSetReader class.

RowSetWriter
iASRowSet is read-only, but an interface for this class is provided for future expansion. At present, its only method, writeData() throws SQLException.

RowSetInternal
This internal class is used by RowSetReader to retrieve information about the RowSet. It has a single method, getOriginalRow(), which returns the original result set instead of a single row.

Using CachedRowSet

The JDBC specification provides a rowset class called CachedRowSet. CachedRowSet permits you to retrieve data from a datasource, then detach from the datasource while you examine, and modify the data. A cached rowset keeps track both of the original data retrieved, and any changes made to the data by your application. If the application attempts to update the original datasource, the rowset is reconnected to the datasource, and only those rows that have changed are merged back into the database.

Creating a RowSet

To create a rowset in an iAS server application:

iASRowSet rs = new iASRowSet();

Using JNDI

JDBC 2.0 specifies that you can use the Java Naming and Directory Interface (JNDI) to provide a uniform, platform and JDBC vendor-independent way for your applications to find and access remote services over the network. For example, all JDBC driver managers, such as the JDBC driver manager implemented in iAS, must find and access a JDBC driver by looking up the driver and a JDBC URL for connecting to the database. For example:

Class.forName("SomeJDBCDriverClassName");

Connection con =
    DriverManager.getConnection("jdbc:iAS_subprotocol:machineY:portZ");

This code illustrates how a JDBC URL may not only be specific to a particular vendor's JDBC implementation, but also to a specific machine and port number. Such hard-coded dependencies make it hard to write portable applications that can easily be shifted to different JDBC implementations and machines at a later time.

In place of this hard-coded information, JNDI permits you to assign a logical name to a particular datasource. Once you establish the logical name, you need only modify it a single time to change the deployment and location of your application.

JDBC 2.0 specifies that all JDBC datasources should be registered in the jdbc naming subcontext of a JNDI namespace, or in one of its child subcontexts. The JNDI namespace is hierarchical, like a file system's directory structure, so it is easy to find and nest references. A datasource is bound to a logical JNDI name. The name identifies a subcontext, "jdbc", of the root context, and a logical name. In order to change the datasource, all you need to do is change its entry in the JNDI namespace without having to modify a line of code in your application.

For more information about JNDI, see the JDBC 2.0 Standard Extension API.