Chapter 11 Using the JDBC API for Database Access

This chapter describes how to use the JavaTM Database
Connectivity (JDBCTM) API for database
access with the Sun Java System Application Server. This chapter also provides
high level JDBC implementation instructions for servlets and EJBTM components using the Application Server. The Application Server supports
the JDBC 3.0 API, which encompasses the JDBC 2.0 Optional Package
API.

Because the drivers and databases supported by the Application Server are
constantly being updated, and because database vendors continue to
upgrade their products, always check with Sun technical support for
the latest database support information.

Making the JDBC Driver JAR Files Accessible

To integrate the JDBC driver into a Application Server domain,
copy the JAR files into the domain-dir/lib/ext directory,
then restart the server. This makes classes accessible to any application
or module across the domain. For more information about Application Server classloaders,
see Classloaders.

Creating a Connection Pool

When you create a connection pool that uses JDBC technology
(a JDBC connection pool) in the Application Server,
you can define many of the characteristics of your database connections.

Sharing Connections

When multiple connections acquired by an application
use the same JDBC resource, the connection pool provides connection
sharing within the same transaction scope. For example, suppose Bean
A starts a transaction and obtains a connection, then calls a method
in Bean B. If Bean B acquires a connection to the same JDBC resource
with the same sign-on information, and if Bean A completes the transaction,
the connection can be shared.

Connections obtained through a resource are shared only if the
resource reference declared by the J2EE component allows it to be
shareable. This is specified in a component’s deployment descriptor
by setting the res-sharing-scope element
to Shareable for the particular resource reference.
To turn off connection sharing, set res-sharing-scope to Unshareable.

Obtaining a Physical Connection from a Wrapped
Connection

The DataSource implementation in the Application Server provides
a getConnection method that retrieves the JDBC
driver’s SQLConnection from the Application Server’s Connection wrapper.
The method signature is as follows:

Using Non-Transactional Connections

The DataSource implementation in the Application Server provides
a getNonTxConnection method, which retrieves a JDBC connection that is not in the scope of any transaction.
There are two variants, as follows:

Another way to get a non-transactional connection is to create
a resource with the JNDI name ending in __nontx.
This forces all connections looked up using this resource to be non
transactional.

Typically, a connection is enlisted in the context of the transaction
in which a getConnection call is invoked. However,
a non-transactional connection is not enlisted in a transaction context
even if a transaction is in progress.

The main advantage of using non-transactional connections is
that the overhead incurred in enlisting and delisting connections
in transaction contexts is avoided. However, use such connections
carefully. For example, if a non-transactional connection is used
to query the database while a transaction is in progress that modifies
the database, the query retrieves the unmodified data in the database.
This is because the in-progress transaction hasn’t committed.
For another example, if a non-transactional connection modifies the
database and a transaction that is running simultaneously rolls back,
the changes made by the non-transactional connection are not rolled
back.

Here is a typical use case for a non-transactional connection:
a component that is updating a database in a transaction context spanning
over several iterations of a loop can refresh cached data by using
a non-transactional connection to read data before the transaction
commits.

Not all database vendors support all transaction isolation levels available in the JDBC API.
The Application Server permits specifying any isolation level your database
supports. The following table defines transaction isolation levels.

To verify that a level is supported by your database management
system, test your database programmatically using the supportsTransactionIsolationLevel() method in java.sql.DatabaseMetaData, as shown in the following example:

For more information about these isolation levels and what they
mean, see the JDBC 3.0 API specification.

Note –

Applications that change the isolation level on a pooled
connection programmatically risk polluting the pool, which can lead
to errors.

Configurations for Specific JDBC Drivers

Application Server 8.1 is designed to support connectivity
to any database management system with a corresponding JDBC driver.
The following JDBC driver and database combinations are supported. These combinations have been tested with Application Server 8.1 and
are found to be J2EE compatible. They are also supported for CMP.

Other JDBC drivers can be used with Application Server 8.1,
but J2EE compliance tests have not been completed with these drivers.
Although Sun offers no product support for these drivers, Sun offers
limited support of the use of these drivers with Application Server 8.1.

An Oracle database user running the capture-schema command
needs ANALYZE ANY TABLE privileges if that user does not own the schema.
These privileges are granted to the user by the database administrator.
For information about capture-schema, see Using the capture-schema Utility.

PointBase Type 4 Driver

The PointBase JDBC driver is included with the Application Server by
default, except for the Solaris bundled installation, which does not
include PointBase. Therefore, unless you have the Solaris bundled
installation, you do not need to integrate this JDBC driver with the Application Server.

PointBase is intended for evaluation use only, not for production
or deployment use.

The JAR file for the PointBase driver is pbclient.jar.

Configure the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:PointBase

DataSource Classname:Specify
one of the following:

com.pointbase.jdbc.jdbcDataSource
com.pointbase.xa.xaDataSource

Properties:

user - Specify the database
user.

password - Specify the database
password.

databaseName - Specify the
URL of the database. The syntax is as follows:

jdbc:pointbase:server://server:port/dbname,new

Sun Java System JDBC Driver for DB2 Databases

The JAR files for this driver are smbase.jar, smdb2.jar, and smutil.jar. Configure
the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:DB2

DataSource Classname:com.sun.sql.jdbcx.db2.DB2DataSource

Properties:

serverName - Specify the host
name or IP address of the database server.

portNumber - Specify the port
number of the database server.

databaseName - Set as appropriate.

user - Set as appropriate.

password - Set as appropriate.

URL:jdbc:sun:db2://serverName:portNumber;databaseName=databaseName

Sun Java System JDBC Driver for Oracle 8.1.7
and 9.x Databases

The JAR files for this driver are smbase.jar, smoracle.jar, and smutil.jar. Configure
the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:Oracle

DataSource Classname:com.sun.sql.jdbcx.oracle.OracleDataSource

Properties:

serverName - Specify the host
name or IP address of the database server.

portNumber - Specify the port
number of the database server.

SID - Set as appropriate.

user - Set as appropriate.

password - Set as appropriate.

URL:jdbc:sun:oracle://serverName[:portNumber][;SID=databaseName]

Sun Java System JDBC Driver for Microsoft
SQL Server Databases

The JAR files for this driver are smbase.jar, smsqlserver.jar, and smutil.jar. Configure
the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:mssql

DataSource Classname:com.sun.sql.jdbcx.sqlserver.SQLServerDataSource

Properties:

serverName - Specify the host
name or IP address and the port of the database server.

portNumber - Specify the port
number of the database server.

user - Set as appropriate.

password - Set as appropriate.

selectMethod - Set to cursor.

URL:jdbc:sun:sqlserver://serverName[:portNumber]

Sun Java System JDBC Driver for Sybase Databases

The JAR files for this driver are smbase.jar, smsybase.jar, and smutil.jar. Configure
the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:Sybase

DataSource Classname:com.sun.sql.jdbcx.sybase.SybaseDataSource

Properties:

serverName - Specify the host
name or IP address of the database server.

portNumber - Specify the port
number of the database server.

databaseName - Set as appropriate.
This is optional.

user - Set as appropriate.

password - Set as appropriate.

URL:jdbc:sun:sybase://serverName[:portNumber]

IBM DB2 8.1 Type 2 Driver

The JAR files for the DB2 driver are db2jcc.jar, db2jcc_license_cu.jar, and db2java.zip.
Set environment variables as follows:

serverName - Specify the host
name or IP address of the database server.

portNumber - Specify the port
number of the database server.

user - Set as appropriate.

password - Set as appropriate.

databaseName - Set as appropriate.
Do not specify the complete URL, only the database name.

BE_AS_JDBC_COMPLIANT_AS_POSSIBLE -
Set to true.

FAKE_METADATA - Set to true.

Inet Oraxo JDBC Driver for Oracle 8.1.7 and
9.x Databases

The JAR file for the Inet Oracle driver is Oranxo.jar.
Configure the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:Oracle

DataSource Classname:com.inet.ora.OraDataSource

Properties:

user - Specify the database
user.

password - Specify the database
password.

serviceName - Specify the URL
of the database. The syntax is as follows:

jdbc:inetora:server:port:dbname

For example:

jdbc:inetora:localhost:1521:payrolldb

In this example,localhost is the host name
of the machine running the Oracle server, 1521 is
the Oracle server’s port number, and payrolldb is
the SID of the database. For more information about the syntax of
the database URL, see the Oracle documentation.

serverName - Specify the host
name or IP address of the database server.

port - Specify the port number
of the database server.

streamstolob - If the size
of BLOB or CLOB data types exceeds 4 KB and this driver is used for
CMP, this property must be set to true.

xa-driver-does-not-support-non-tx-operations -
Set to the value true. Optional: only needed if
both non-XA and XA connections are retrieved from the same connection
pool. Might degrade performance.

As an alternative to
setting this property, you can create two connection pools, one for
non-XA connections and one for XA connections.

Inet Merlia JDBC Driver for Microsoft SQL
Server Databases

The JAR file for the Inet Microsoft SQL Server driver is Merlia.jar. Configure the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:mssql

DataSource Classname:com.inet.tds.TdsDataSource

Properties:

serverName - Specify the host
name or IP address and the port of the database server.

port - Specify the port number
of the database server.

user - Set as appropriate.

password - Set as appropriate.

Inet Sybelux JDBC Driver for Sybase Databases

The JAR file for the Inet Sybase driver is Sybelux.jar.
Configure the connection pool using the following settings:

Name: Use this
name when you configure the JDBC resource later.

Resource Type: Specify
the appropriate value.

Database Vendor:Sybase

DataSource Classname:com.inet.syb.SybDataSource

Properties:

serverName - Specify the host
name or IP address of the database server.

portNumber - Specify the port
number of the database server.

user - Set as appropriate.

password - Set as appropriate.

databaseName - Set as appropriate.
Do not specify the complete URL, only the database name.

Oracle Thin Type 4 Driver for Oracle 8.1.7
and 9.x Databases

The JAR file for the Oracle driver is ojdbc14.jar.
Configure the connection pool using the following settings:

xa-driver-does-not-support-non-tx-operations -
Set to the value true. Optional: only needed if
both non-XA and XA connections are retrieved from the same connection
pool. Might degrade performance.

As an alternative to
setting this property, you can create two connection pools, one for
non-XA connections and one for XA connections.

Note –

You must set the oracle-xa-recovery-workaround property
in the Transaction Service for recovery of global transactions to
work correctly. For details, see Transaction Scope.

When using this driver, it is not possible
to insert more than 2000 bytes of data into a column. To circumvent
this problem, use the OCI driver (JDBC type 2).

OCI Oracle Type 2 Driver for Oracle 8.1.7
and 9.x Databases

The JAR file for the OCI Oracle driver is ojdbc14.jar.
Make sure that the shared library is available through LD_LIBRARY_PATH
and that the ORACLE_HOME property is set. Configure the connection
pool using the following settings: