Previous     Contents     Index     Next     
iPlanet Application Server Developer's Guide



Chapter 9   Using JDBC for Database Access


This chapter describes how to use the Java Database Connectivity (JDBC) API for database accesses with the iPlanet Application Server. This chapter provides high level JDBC implementation instructions for servlets and EJBs using the iPlanet Application Server; it also describes the specific iPlanet Application Server resources affected by JDBC statements when those resources have clear programming ramifications.

In an iPlanet Application Server, EJBs support database access primarily through the JDBC API. The iPlanet Application Server supports the entire JDBC 2.0 API, as well as, many of the emerging JDBC 2.0 extensions, including result set enhancements, batch updates, distributed transactions, row sets, and Java Naming and Directory Interface (JNDI) support for datasource name lookups.


Note Native drivers no longer support container, local, or global transaction management.

Although native JDBC drivers were deprecated in iPlanet Application Server 6.0 SP1, support for backward compatibility has been retained in this release.


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

This chapter contains the following sections:



Introducing JDBC

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

More specifically, JDBC is a set of interfaces that every server vendor, such as iPlanet, must implement according to the JDBC specifications. The iPlanet Application Server provides a JDBC type 2 driver which supports a variety of Enterprise Information Systems (EISs) databases. The driver processes the JDBC statements in your application and routes the SQL arguments they contain to your database engines.



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


Supported Functionality


Note This section describes the features of native JDBC drivers, which have been deprecated. For information about third party JDBC driver features, see the driver vendor's documentation.


The JDBC specification is a broad, database vendor independent set of guidelines. The guidelines encompass the broadest database functionality range possible in a simple framework. At a minimum, JDBC assumes the database supports the SQL-2 database access language. The JDBC specification has three parts:

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

  • JDBC 2.0 describes additional database access and functionality. Primarily, this functionality involves support for newly defined SQL-3 features, data types, and mappings. The iPlanet Application Server implementation of JDBC supports most JDBC feature enhancements, but omits support for the new SQL-3 data types, such as blobs, clobs, and arrays. Currently, many database vendors do not fully support them in their relational database management systems. The iPlanet Application Server 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 improved database performance. The iPlanet Application Server JDBC implementation currently supports Java Naming and Directory Interface (JNDI) and row sets.


Understanding Database Limitations

When using JDBC in your server applications, you may encounter situations where you do not obtain the results you desire or expect. You may think the problem lies in JDBC or in the iPlanet Application Server JDBC driver implementation. However, the vast majority of these problems are limitations in your database engine.

Because JDBC covers the broadest possible 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 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 have added non standard SQL-2 extensions to their SQL implementation to support their proprietary features. JDBC provides ways to access vendor specific features, but realize these features may not be available for all databases you use.

This is especially true when you build an application that uses databases from two or more 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 maximize JDBC usage, consult your database documentation about which SQL and JDBC aspects they support. Therefore, first eliminate your database as causing the problem before calling iPlanet technical support for database problems.


Understanding the iPlanet Application Server Limitations

Like JDBC, the iPlanet Application Server supports the broadest spectrum of database engines and features. In some cases, the iPlanet Application Server itself or the iPlanet Application Server JDBC driver may not fully support a particular database feature, or it may report incorrect information. If you cannot access a database feature from your iPlanet Application Server application and you have eliminated the database as the problem, check this section in the documentation and the Release Notes to determine if the problem you encounter is a documented iPlanet Application Server limitation. If not, fully document the problem and contact iPlanet 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 iPlanet Application Server JDBC driver. Almost all feature limitations apply to JDBC 2.0.


Table 9-1 lists the JDBC features that are not supported, either partially or completely in the iPlanet Application Server.


Table 9-1    JDBC Feature Limitations

Feature

Limitation

Escape sequences  

Supported only for Oracle databases.  

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 the iPlanet Application Server. 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.  

executeUpdate after a row insertion  

For DB2, returns 0 instead of 1.  

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


Supported Databases

As databases supported by the iPlanet Application Server are constantly being updated, along with the database vendors' upgrades, see iPlanet Application Server Installation Guide or the Release Notes for the most current information.



Migrating 6.x DD XML files to 6.5


iPlanet Application Server 6.5 contains enhancements to the database connection infrastructure. To take advantage of the new features and to continue to use you older datasource configuration you will have to migrate to the new datasource XML DTD.


To Migrate 6.x Deployment Descriptor XML File

  1. Choose Tools > Register Datasource.

    The datasource registration dialog will appear.

  2. Click Open > select the XML file which contains the datasource descriptions.

    The Deployment Tool will import the values present in the old XML file and add default values for the new fields available in 6.5. in the XML file.

  3. Accept the default values or modify them for your datasource.

  4. Enter the database driver parameters, connection pooling parameters, and connection sanity parameters.

    For detailed explanation of the fields, see Chapter 8, "Administering Database Connectivity" in iPlanet Application Server Administrator's Guide.

  5. Click Save.

    The Deployment Tool will overwrite the datasource deployment descriptors with the values you have specified, in the file you have opened.

    Note: Select Save As to save the updated XML file with another name, and in a different location.

  6. Click Register.

    The Deployment Tool will update the datasource deployment descriptors with the values you have specified. Now your datasource will be able to take advantage of the new features in iPlanet Application Server 6.5.



New XML Datasource Descriptors

With the change in the transaction manager infrastructure in iPlanet Application Server 6.5, the datasource descriptors have also been changed. The new descriptors offer more features and options.

Following are sample XML datasource descriptors for third party drivers:


Local Transactions

The following sample XML files are for databases using iPlanet Application Server's IASConnectionPoolDataSource, which wraps around the database specific Driver Manager.


Oracle

<ias-resource>

<resource>

<jndi-name>jdbc/estore/EstoreDB</jndi-name>

<jdbc>

<user>estore</user>

<password>estore</password>

<URL> jdbc:oracle:thin:@192.18.117.186:1521:orcl</URL>

<driver-name>oracle_xa</driver-name>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


Sybase

<ias-resource>

<resource>

<jndi-name>jdbc/estore/EstoreDB</jndi-name>

<jdbc>

<URL> jdbc:sybase:Tds:192.138.151.39:4444</URL>

<user>estore</user>

<password>estore</password>

<driver-name>jconnect</driver-name>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


Global Transactions

The sample XML files provided below are for database drivers that use driver provided XADataSource / ConnectionPoolDataSource. (JDBC 2.0 + extensions). We recommend that you use these XML formats if your application uses both global and local transactions.

In all the datasource XMLs', the connection pooling elements (conn-pooling) are optional. If skipped, default values will be used. For more information, see iPlanet Application server Administrator's Guide. XADatasource will be used only when global transactions are enabled . Please refer to the Administrator's Guide for more information on how to use global transactions.


DB2

<ias-resource>

<resource>

<jndi-name>jdbc/sample</jndi-name>

<jdbc>

<dataSourceName>friend</dataSourceName>

<user>db2inst</user>

<password>db2inst</password>

<driver-name>db2_xa</driver-name>

<databaseName>sample4</databaseName>

<portNumber>50001</portNumber>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>

Informix

<ias-resource>

<resource>

<jndi-name>jdbc/dshubble</jndi-name>

<jdbc>

<user>root</user>

<datasourceName>rna_tcp</datasourceName>

<databaseName>jts</databaseName>

<serverName>rna_tcp</serverName>

<portNumber>1528</portNumber>

<ifxIFXHOST>rna</ifxIFXHOST>

<password>abc123</password>

<driver-name>ifx</driver-name>

<URL>jdbc:informix-sqli://rna:1528/sample:INFORMIXSERVER=rna_tcp</U RL>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


MSSQL

<ias-resource>

<resource>

<jndi-name>jdbc/sample</jndi-name>

<jdbc>

<dataSourceName>lancer</dataSourceName>

<user>sa</user>

<password></password>

<driver-name>mssql</driver-name>

<databaseName>master</databaseName>

<networkProtocol>Tds</networkProtocol>

<resourceManagerName>testrm</resourceManagerName>

<serverName>lancer</serverName>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


Oracle

<ias-resource>

<resource>

<jndi-name>jdbc/sample</jndi-name>

<jdbc>

<URL>jdbc:oracle:oci8:@hubble</URL>

<user>estore</user>

<password>estore</password>

<databaseName>hubble</databaseName>

<driver-name>oracle_xa</driver-name>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


Sequelink

<ias-resource>

<resource>

<jndi-name>jdbc/sample</jndi-name>

<jdbc>

<datasourceName>mig</datasourceName>

<user>kdemo</user>

<password>kdemo</password>

<driver-name>sequelink</driver-name>

<databaseName>mig</databaseName>

<serverName>mig</serverName>

<portNumber>23003</portNumber>

<URL>jdbc:sequeliik://mig:23003</URL>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>


Sybase

<ias-resource>

<resource>

<jndi-name>jdbc/sample</jndi-name>

<jdbc>

<dataSourceName>prodigy</dataSourceName>

<user>iplanet</user>

<password>iplanet</password>

<driver-name>sybase</driver-name>

<databaseName>iplanet</databaseName>

<networkProtocol>Tds</networkProtocol>

<portNumber>4100</portNumber>

<serverName>prodigy</serverName>

<URL>jdbc:sybase:Tds:prodigy:4100</URL>

<conn-pooling>

<initialPoolSize>1</initialPoolSize>

<waitQueueEnabled>true</waitQueueEnabled>

<reclaimTime>600</reclaimTime>

<maxPoolSize>30</maxPoolSize>

<maxIdleTime>120</maxIdleTime>

<queueLength>30</queueLength>

<trace>disable</trace>

<stat>disable</stat>

<waitTimeInQueue>120</waitTimeInQueue>

<tableBasedSanity>false</tableBasedSanity>

<isSanityRequired>true</isSanityRequired>

<incrementPoolSize>1</incrementPoolSize>

<propertyCycle>0</propertyCycle>

<minPoolSize>1</minPoolSize>

</conn-pooling>

</jdbc>

</resource>

</ias-resource>



Using JDBC in Server Applications


JDBC is part of the iPlanet Application Server runtime environment. This means JDBC is always available any time you use Java to program an application. In a typical multi-tiered server application, you use JDBC to access an EIS database from a client, from the presentation layer, in servlets, and in EJBs.

However, in practice it makes sense—for security and portability reasons—to restrict database accesses to the middle layers of a multi-tiered server application. In the iPlanet Application Server programming model, this means placing all JDBC calls in servlets and EJBs, with the preference being towards EJBs.

There are two reasons for this programming preference:

  • Placing all JDBC calls inside EJBs makes your application more modular and more portable.

  • 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 Always use a globally available datasource to create a global (bean-wide) connection so that the EJB transaction manager controls the transaction.



Using JDBC in EJBs

Placing 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. Because EJBs are components, use them as building blocks for many applications with little or no changes, and maintain a common interface to your EIS database.


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 transaction management programming using JDBC or javax.transaction.UserTransaction is appropriate for an application. In these cases, program the 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 EJB Transaction Type property to Bean in the bean's deployment descriptor.

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

Do not program explicit transaction handling in EJBs when the Transaction Type property is not Bean. For more information about handling transactions with JDBC, see the JDBC 2.0 API specification.


Specifying Transaction Isolation Level

Specify or examine the transaction level for a connection using the setTransactionIsolation() and getTransactionIsolation() methods, respectively. Note that you cannot call setTransactionIsolation() during a transaction.

Table 9-2 defines the transaction isolation levels, as follows:


Table 9-2    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 specifying a bean's transaction isolation level, verify the level is supported by your database management system. Not all databases support all isolation levels. Test your database programmatically by using the method supportsTransactionIsolationLevel() in java.sql.DatabaseMetaData, as shown 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 JDBC 2.0 API specification.


Using JDBC in Servlets

Servlets are at the heart of an iPlanet Application Server application. 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 an application's work.

The iPlanet Application Server applications use JDBC embedded in EJBs for most database accesses. This is the preferred method for database accesses using the iPlanet Application Server because it enables you to take advantage of the transaction control built-in to 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 a database from EJBs. There is less call overhead, if an application is spread across servers so that EJBs are accessible only through the Java Remote Method Interface (RMI). Use direct database service through servlets sparingly. If providing database access from servlets, restrict access to very short durations, the transaction is read-only, and take advantage of the JDBC 2.0 RowSet class.

If access to a database is from a servlet, use the JDBC 2.0 RowSet interface to interact with the database. A row set 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 row sets, see "Working with RowSet."



Handling Connections


The iPlanet Application Server implements the JDBC 2.0 compliant interface java.sql.Connection. The connection behavior depends on if 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 cannot propagate across processes or datasources; it is local to the current process and to the current datasource.

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


Registering a Local Datasource

The first step to create a local connection is to register the datasource with the iPlanet Application Server. Once the datasource is registered, the datasource can be used to make connections to the listed database using getConnection().

Register the datasource by creating an XML resource descriptor file that describes the datasource properties. Next, register the properties with the iPlanet Application Server using the Administration Tool or the resreg utility. resreg takes as its argument, the resource descriptor file name 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 an XML descriptor file like the following, and name it SampleDS.xml (use the iPlanet Application Server Deployment Tool to create an 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>

Then use this resource descriptor file to register the datasource with the following command:

resreg SampleDS.xml

For more information about resource descriptor files, see Chapter 11 "Packaging for Deployment." For more information about the iPlanet Application Server Administration Tool, see the Administrator's Guide.


Global Connections

A Connection object is called a global connection if its transaction context is managed by the 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 for bean managed transactions. For more information about transactions, see Chapter 8 "Handling Transactions with EJBs."

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


Registering a Global Datasource

The first step in creating a global connection is to register the datasource with the iPlanet Application Server. Once the datasource is registered, the datasource is used to make connections to the listed database using getConnection().

Register the datasource by creating an XML resource descriptor file that describes the datasource properties. Next, register the properties with the iPlanet Application Server using the Administration Tool or the resreg utility. resreg takes as its argument, the resource descriptor file name 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 iPlanet Application Server Deployment Tool to create the 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>

Use the resource descriptor file to register the datasource with the following command:

resreg GlobalSampleDS.xml

For more information about resource descriptor files, see Chapter 11 "Packaging for Deployment." For more information about the iPlanet Application Server Administration Tool, see the Administrator's Guide.


Creating a Global Connection

The following program demonstrates how a datasource is looked up and a connection created from it. As illustrated, 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 the transaction context is managed by the EJB container and global transactions are disabled. With container managed transactions, the transaction context is managed implicitly by the EJB container and with bean managed transactions the transaction context is handled explicitly.

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

For more information on how to enable or disable global transactions in an EJB container, see the Administrator's Guide.


Registering a Container Managed Local Datasource

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



Working with JDBC Features


While this chapter is not a JDBC primer, it does introduce how to use JDBC in EJBs with the iPlanet Application Server. The following sections describe various JDBC interfaces and classes that either have special requirements in the iPlanet Application Server environment, or are new JDBC 2.0 features you are encouraged to use when developing an iPlanet Application Server application.

For example, "Working with Connections" describes the resources the iPlanet Application Server releases when a connection is closed because this information differs among different JDBC implementations. On the other hand, "Pooling Connections" and "Working with RowSet" offer more extensive coverage because these are new JDBC 2.0 features that offer increased power, flexibility, and server application speed.

This section covers the following topics:


Working with Connections

When opening a JDBC connection, the iPlanet Application Server allocates connection resources. Call Connection.close() when a connection is no longer needed, to free the connection resources. Always reestablish connections before continuing database operations after you call Connection.close().

Use Connection.isClose() 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. To 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 an application uses several connections, and if connections are frequently opened and closed, the iPlanet Application Server automatically provides connection pooling. Connection pooling provides a connection cache that automatically closes when necessary.


Note Connection pooling is an automatic feature of the iPlanet Application Server; the API is not exposed.



setTransactionIsolation
Not all database vendors support all transaction isolation levels available in JDBC. The iPlanet Application Server permits specifying 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 iPlanet Application Server Native JDBC driver implementation does not support type mapping, a new SQL-3 feature that most database vendors do not support.


cancel
cancel() is supported for all databases that support cancel().


Pooling Connections

Two costlier database operations to execute in JDBC are for creating and destroying database connections. Connection pooling permits a single connection cache for connection requests. 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.

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

  • 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 reused by transactions that execute on the thread.

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

In each Java engine, each driver (Oracle, Sybase, Informix and DB2) has its own connection pool. Each connection pool size is according to the application requirements. For more information on the connection pool settings (such as, maximum number of connections, connection timeout and so on), see the Administrator's Guide.


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.


Note This section describes the functionality of native JDBC drivers. For more information about the options supported by third party JDBC drivers, see the driver vendor's documentation.



Concurrency Support

The iPlanet Application Server supports concurrency for FORWARD-ONLY READ-ONLY and for SCROLL-INSENSITIVE READ-ONLY result sets. On callable statements, the iPlanet Application Server also supports concurrency for FORWARD-ONLY UPDATABLE result sets.

SCROLL-SENSITIVE concurrency is not supported.


Updatable Result Set Support

In the iPlanet Application Server, 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 Use join clauses to create read-only result sets against multiple tables; however, these result sets are not updatable.


For Sybase, the select list must include a unique index column. Sybase also permits calling execute() or executeQuery() to create an updatable result set. However, the statement must 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, as follows:

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 result set method, getCursorName(), enables the determining of the cursor name used to fetch a result set. If a cursor name is not specified by the query itself, different database vendors return different information. The iPlanet Application Server attempts to handle these differences as transparently as possible. Table 9-3 indicates the cursor name returned by different database vendors if no cursor name is specified in the initial query.


Table 9-3    Cursor Name

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 the iPlanet Application Server. 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
The iPlanet Application Server implements this JDBC method and it only works with scalar data types. JDBC 2.0 adds additional method versions that include a map argument. The iPlanet Application Server does not implement maps and ignores map arguments.


getRef, getBlob, getClob, and getArray
References, blobs, clobs, and arrays are new SQL-3 data types. The iPlanet Application Server does not implement these data objects or their methods. However, to work with references, blobs, clobs, and arrays use 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 Use the iPlanet Application Server feature SqlUtil.loadQuery() to load an iASRowSet with a prepared statement. For more information, see the SqlUtil class entry in the Foundation Class Reference (Java).



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


addBatch
This method enables ganging of 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. The iPlanet Application Server does not implement these data objects or the methods that work with them. However, to work with references, blobs, clobs, and arrays use getBinaryStream() and setBinaryStream().


getMetaData
Not all database systems return complete metadata information. See your database 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 the iPlanet Application Server.


getRef, getBlob, getClob, getArray
References, blobs, clobs, and arrays are new SQL-3 data types. The iPlanet Application Server does not implement these data objects or the methods that work with them. However, to work with references, blobs, clobs, and arrays use getBinaryStream() and setBinaryStream().


Handling Batch Updates

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

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

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

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

In order to use batch updates, an application must disable auto commit options, as follows:

...
// 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();

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


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 cannot be grouped for a batch operation. Also, some databases do not allow data definition 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-in to the iPlanet Application Server EJB container. If an EJB does not specify the TX_BEAN_MANAGED transaction attribute, automatic support for distributed transactions in an application is enabled.

In servlets and 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 the iPlanet Application Server as global datasources. In other words, their datasource properties files must include a ResourceMgr entry whose value must be configured at install time.

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 obtain 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 RowSet

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, a program must import javax.sql, and implement the RowSet interface. RowSet extends the java.sql.ResultSet interface, permitting it to act as a JavaBean component.

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

To fill a RowSet call the RowSet.execute() method. The execute() method uses property values to determine the datasource and retrieve data. The actual properties to set and examine depends upon the implementation of RowSet invoked.

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


Using iASRowSet

The iPlanet Application Server provides a RowSet class called iASRowSet for convenience. iASRowSet extends ResultSet, therefore call methods are inherited from the ResultSet object. iASRowSet overrides the getMetaData() and close() methods of ResultSet. Because iASRowSet is not a driver-level class, it is easier to use than ResultSet.

The RowSet interface is fully supported except as noted in Table 9-4.


Table 9-4    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 RowSetReader class implementation.


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 ResultSet instead of a single row.


Using CachedRowSet

The JDBC specification provides a RowSet class called CachedRowSet. CachedRowSet permits data retrieval from a datasource, then detaches from the datasource while examining, and modifying the data. A cached row set keeps track of the original data retrieved and any data changes made by an application. If the application attempts to update the original datasource, the row set is reconnected to the datasource, and only those rows that have changed are merged back into the database.


Creating a RowSet

To create a row set in an the iPlanet Application Server application:

iASRowSet rs = new iASRowSet();


Using JNDI for a Database Driver

All JDBC driver managers, such as the JDBC driver manager implemented in the iPlanet Application Server, must find and access a JDBC driver by looking up the driver and a JDBC URL for connecting to the database. However, 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.

JDBC 2.0 specifies using JNDI to provide a uniform, platform and JDBC vendor independent way for an application to find and access remote services over the network. In place of this hard-coded information, JNDI permits assigning a logical name to a particular datasource. Once the logical name is established, you need only modify it a single time to change the deployment and application location.

JDBC 2.0 specifies that all JDBC datasources are 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, just change its entry in the JNDI namespace without having to modify the application.

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

The rest of this section uses an example of a datasource lookup to describe how to reference resource factories. The same principle is applicable to all resources (such as JavaMail references).

The resource lookup in the application code looks like this:

String dsName = "java:comp/env/HelloDbDataSource";
DataSource ds = (javax.sql.DataSource)initContext.lookup(dsName);
Connection conn = ds.getConnection();

The resource being queried is listed in the res-ref-name attribute of the web.xml file as follows:

<resource-ref>
   <description>Datasource Reference</description>
   <res-ref-name>HelloDbDataSource</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>

The resource-ref section in the iPlanet specific deployment descriptor, ias-web.xml, maps the res-ref-name (the name being queried in the application code) to the JNDI name of the datasource. The JNDI name is the same as the name of the datasource as defined in the resource file when the resource is registered with the server.

<resource-ref>
   <res-ref-name>HelloDbDataSource</res-ref-name>
   <jndi-name>jdbc/hellodb/HelloDbDB</jndi-name>
</resource-ref>

The resource registration file is an XML file that lists the JNDI name of the datasource and maps it to a driver that has been registered with the iPlanet server. The JNDI name should be the same as the one listed in the jndi-name attribute of the resource-ref section of the ias-web.xml file.

<ias-resource>
   <resource>
      <jndi-name>jdbc/hellodb/HelloDbDB</jndi-name>
      <jdbc>
         <driver-type>PointBaseDriver</driver-type>
         <database-url>
            jdbc:pointbase://localhost/iassamples
         </database-url>
         <username>hellodb</username>
         <password>hellodb</password>
      </jdbc>
   </resource>
</ias-resource>

For more information about the web.xml, ias-web.xml, and resource XML files, see Chapter 11 "Packaging for Deployment."

When the resource is registered, it is entered in the following section of the registry (in the LDAP namespace):

SOFTWARE\iPlanet\Application Server\6.5\DataSource

Figure 9-1 shows the registry entry.

Figure 9-1    Datasource registry entry




Previous     Contents     Index     Next     
Copyright © 2002 Sun Microsystems, Inc. All rights reserved.

Last Updated March 06, 2002