Sun Java logo     Previous      Contents      Index      Next     

Sun logo
Sun Java System Application Server Platform Edition 8 Developer's Guide 

Chapter 11
Using the JDBC API for Database Access

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

The JDBC specifications are available here:

http://java.sun.com/products/jdbc/download.html

A useful JDBC tutorial is located here:

http://java.sun.com/docs/books/tutorial/jdbc/index.html

For explanations of two-tier and three-tier database access models, see the Sun Java System Application Server Administration Guide.

Note

Sun Java System Application Server does not support connection pooling or transactions for an application’s database access if it does not use standard J2EE™ DataSource objects.

General Steps for Creating a JDBC Resource

To prepare a JDBC resource for use in J2EE applications deployed to the Sun Java System Application Server, perform the following tasks:

For information about how to configure some specific JDBC drivers, see Configurations for Specific JDBC Drivers.

Integrating the JDBC Driver

To use JDBC features, you must choose a JDBC driver to work with the Sun Java System Application Server, then you must set up the driver. This section covers these topics:

Supported Database Drivers

Supported JDBC drivers are those that have been fully tested by Sun. For a list of the JDBC drivers currently supported by the Sun Java System Application Server, see the Sun Java System Application Server Platform Edition 8 Release Notes.

For configurations of supported and other drivers, see Configurations for Specific JDBC Drivers.

Note

Because the drivers and databases supported by the Sun Java System 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 Sun Java System 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 Sun Java System Application Server classloaders, see “Classloaders” on page 85.

Creating a Connection Pool

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

You can create a connection pool in one of these ways:

The “Using the Administration Console” section describes each connection pool setting. The “Using the Command Line Interface” section merely lists syntax and default values.

For additional information about connection pools, including connection pool monitoring, see the Sun Java System Application Server Administration Guide.

Using the Administration Console

To create a JDBC connection pool using the Administration Console, perform these tasks:

  1. Login to the Administration Console by going to the following URL in your web browser:

    2. http://host:port/asadmin

    For example:

    http://localhost:4848/asadmin

  2. Open the JDBC component.
  3. Click Connection Pools.
  4. Click the New button.
  5. Enter the following information:
    • Name (required) - Enter a name (or ID) for the connection pool.
    • Resource Type - Select the JDBC driver’s DataSource interface name if the DataSource class implements more than one DataSource type (DataSource, XADataSource, or ConnectionPoolDataSource). Leave blank if the class implements only one interface.
    • Database Vendor - Select the database vendor from the drop-down list.
  6. Click the Next button.
  7. Enter the following information:
    • Datasource Classname (required) - Enter the vendor-supplied DataSource class name.
  8. Click the Next button.
  9. Enter a text Description if desired.
  10. You can change the pool settings listed in the following table.

    11. Table 11-1  Pool Settings 

    Setting

    Default

    Description

    Steady Pool Size

    8

    Specifies the initial and minimum number of connections maintained in the pool.

    Max Pool Size

    32

    Specifies the maximum number of connections that can be created to satisfy client requests.

    Pool Resize Quantity

    2

    Specifies the number of connections to be destroyed if the existing number of connections is above the Steady Pool Size (subject to the Max Pool Size limit). This is enforced periodically at the Idle Timeout interval. An idle connection is one that has not been used for a period specified by Idle Timeout.

    Idle Timeout (secs)

    300

    Specifies the minimum time that a connection can remain idle in the free pool. After this amount of time, the pool can close this connection.

    Max Wait Time

    60000

    Specifies the amount of time, in milliseconds, that the caller is willing to wait to acquire a connection. If 0, the caller is blocked indefinitely until a resource is available or an error occurs.

  11. You can change the connection validation settings listed in the following table. All of these settings are optional.

    12. Table 11-2  Connection Validation Settings 

    Setting

    Default

    Description

    Connection Validation Required

    Unchecked

    Specifies whether connections have to be validated before being given to the application. If a resource’s validation fails, it is destroyed, and a new resource is created and returned.

    connectionPool Validation Method

    auto-commit

    Specifies the method used to validate connections if Connection Validation Required is selected. Legal values are as follows:

    • auto-commit (default), which uses Connection.setAutoCommit()
    • meta-data, which uses Connection.getMetaData()
    • table, which performs a query on the table specified in the Table Name setting

    Table Name

    none

    Specifies the table name to be used to perform a query to validate a connection. This setting is mandatory if and only if the Validation Method is set to table.

    On Any Failure Close All Connections

    Unchecked

    If checked, closes all connections in the pool if a single validation check fails. Recovery of a minimum number of connections (specified by the Steady Pool Size setting) is attempted.

    This setting is mandatory if and only if Connection Validation Required is checked. If Connection Validation Required is unchecked, this setting is ignored.

  12. You can change the transaction isolation settings listed in the following table. All of these settings are optional.

    13. Table 11-3  Transaction Isolation Settings 

    Setting

    Default

    Description

    Transaction Isolation

    default JDBC driver isolation level

    Specifies the transaction isolation level on the pooled database connections. Allowed values are read-uncommitted, read-committed, repeatable-read, or serializable. Not all databases support all these values.

    Applications that change the isolation level on a pooled connection programmatically risk polluting the pool, which can lead to errors. See Guarantee Isolation Level for more details.

    Isolation Level Guaranteed

    Checked

    Applicable only when the Transaction Isolation level is explicitly set. If checked, every connection obtained from the pool is guaranteed to have the desired isolation level. This may impact performance on some JDBC drivers. You can uncheck this setting if you are certain that the hosted applications do not return connections with altered isolation levels.

  13. To add a property, click the Add Property button and enter the property name and value. To delete properties, check the properties you want to delete, then click the Delete Properties button.

    14. Specify values for any properties your JDBC driver requires. The following table lists some standard and commonly used properties. For information about the specific properties your database requires, see your database vendor’s documentation.

    Table 11-4  Common Connection Pool Properties 

    Property

    Description

    User

    Specifies the user name for this connection pool.

    Password

    Specifies the password for this connection pool.

    databaseName

    Specifies the database for this connection pool.

    serverName

    Specifies the database server for this connection pool.

    port

    Specifies the port on which the database server listens for requests.

    networkProtocol

    Specifies the communication protocol.

    roleName

    Specifies the initial SQL role name.

    datasourceName

    Specifies an underlying XADataSource, or a ConnectionPoolDataSource if connection pooling is done.

    description

    Specifies a text description.

  14. Click the Finish button.

Using the Command Line Interface

To create a JDBC connection pool using the command line, use the asadmin create-jdbc-connection-pool command. The syntax is as follows, with defaults shown for optional parameters that have them:

asadmin create-jdbc-connection-pool --user user --datasourceclassname class_name [--restype javax.sql.DataSource] [--steadypoolsize=8] [--maxpoolsize=32] [--maxwait=60000] [--poolresize=2] [--idletimeout=300] [--isolationlevel isolation_level] [--isisolationguaranteed=true] [--isconnectvalidatereq=false] [--validationmethod=auto-commit] [--validationtable table_name] [--failconnection=false] [--description text] [--property (name=value)[:name=value]*] connection_pool_id

For more information about the parameters specific to asadmin create-jdbc-connection-pool, see Using the Administration Console. For more information about the optional general asadmin parameters (--password, --passwordfile, --host, --port, --secure, --terse, --echo, and --interactive), see the Sun Java System Application Server Administration Guide.

For example:

asadmin create-jdbc-connection-pool --user joeuser --datasourceclassname oracle.jdbc.pool.OracleDataSource --failconnection=true --isconnectvalidatereq=true --property url=jdbc\\:oracle\\:thin\\:@myhost\\:1521\\:V8i:user=staging_lookup_app:pa ssword=staging_lookup_app OraclePoollookup

Note that the colon characters (:) within property values must be escaped with double backslashes (\\) on Solaris™ platforms as shown, because otherwise they are interpreted as property delimiters. On Windows platforms, colon characters (:) must be escaped with single backslashes (\). For details about using escape characters, see the Sun Java System Application Server Administration Guide.

To delete a JDBC connection pool, use the following command:

asadmin delete-jdbc-connection-pool --user user [--cascade=false] connection_pool_id

For example:

asadmin delete-jdbc-connection-pool --user joeuser OraclePoollookup

To list JDBC connection pools, use the following command:

asadmin list-jdbc-connection-pools --user user [config_name]

For example:

asadmin list-jdbc-connection-pools --user joeuser

Testing a Connection Pool

To test a JDBC connection pool for usability, use the following command:

asadmin ping-connection-pool --user user connection_pool_id

For more information about the optional general asadmin parameters (--password, --passwordfile, --host, --port, --secure, --terse, --echo, and --interactive), see the Sun Java System Application Server Administration Guide.

For example:

asadmin ping-connection-pool --user joeuser OraclePoollookup

This command fails and displays an error message unless it successfully connects to the connection pool.

Creating a JDBC Resource

A JDBC resource, also called a data source, lets you make connections to a database using getConnection(). Create a JDBC resource in one of these ways:

The “Using the Administration Console” section describes each JDBC resource setting. The “Using the Command Line Interface” section merely lists syntax and default values.

For general information about JDBC resources, see the Sun Java System Application Server Administration Guide.

Using the Administration Console

To create a JDBC resource using the Administration Console, perform these tasks:

  1. Login to the Administration Console by going to the following URL in your web browser:

    2. http://host:port/asadmin

    For example:

    http://localhost:4848/asadmin

  2. Open the JDBC component.
  3. Click JDBC Resources.
  4. Click the New button.
  5. Enter the following information:
    • JNDI Name (required) - Enter the JNDI name that application components must use to access the JDBC resource.
    • Pool Name (required) - Select from the list the name (or ID) of the connection pool used by this JDBC resource. For more information, see Creating a Connection Pool.
    • Description (optional) - You can enter a text description of the JDBC resource.
    • Status - Check the Enabled box to enable the JDBC resource.

      • If a JDBC resource is disabled, no application component can connect to it, but its configuration remains in the domain.

  6. Click the OK button.

Using the Command Line Interface

To create a JDBC resource using the command line, use the asadmin create-jdbc-resource command. The syntax is as follows, with defaults shown for optional parameters that have them:

asadmin create-jdbc-resource --user user --connectionpoolid connection_pool_id [--enabled=true] [--description text] [--property (name=value)[:name=value]*] jndi_name

For more information about the parameters specific to asadmin create-jdbc-resource, see Using the Administration Console.

For more information about the optional general asadmin parameters (--password, --passwordfile, --host, --port, --secure, --terse, --echo, and --interactive), see the Sun Java System Application Server Administration Guide.

For example:

asadmin create-jdbc-resource --user joeuser --connectionpoolid OraclePoollookup OracleDSlookup

To delete a JDBC resource, use the following command:

asadmin delete-jdbc-resource --user user jndi_name

For example:

asadmin delete-jdbc-resource --user joeuser OracleDSlookup

To list JDBC resources, use the following command:

asadmin list-jdbc-resources --user user [config_name]

For example:

asadmin list-jdbc-resources --user joeuser

Configurations for Specific JDBC Drivers

Sun Java System Application Server Platform Edition 8 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 Sun Java System Application Server Platform Edition 8 and are found to be J2EE compatible. They are also supported for CMP.

For an up to date list of currently supported JDBC drivers, see the Sun Java System Application Server Platform Edition 8 Release Notes.

Other JDBC drivers can be used with Sun Java System Application Server Platform Edition 8, 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 the Sun Java System Application Server.

For details about how to integrate a JDBC driver and how to use the Administration Console or the command line interface to implement the configuration, see General Steps for Creating a JDBC Resource.

Note

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 Type4 Driver

The PointBase JDBC driver is included with the Sun Java System 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 Sun Java System Application Server. This driver is verified J2EE compatible and supported.

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

Configure the connection pool using the following settings:

IBM DB2 8.1 Type2 Driver

This driver is verified J2EE compatible and supported. The JAR files for the DB2 driver are db2jcc.jar, db2jcc_license_cu.jar, and db2java.zip. Set environment variables as follows:

LD_LIBRARY_PATH=/usr/db2user/sqllib/lib:${j2ee.home}/lib

DB2DIR=/opt/IBM/db2/V8.1

DB2INSTANCE=db2user

INSTHOME=/usr/db2user

VWSPATH=/usr/db2user/sqllib

THREADS_FLAG=native

Configure the connection pool using the following settings:

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

This driver is verified J2EE compatible and supported. The JAR file for the Inet Oracle driver is Oranxo.jar. Configure the connection pool using the following settings:

Inet Merlia JDBC Driver for Microsoft SQL Server Databases

This driver is verified J2EE compatible and supported. The JAR file for the Inet Microsoft SQL Server driver is Merlia.jar. Configure the connection pool using the following settings:

Inet Sybelux JDBC Driver for Sybase Databases

This driver is verified J2EE compatible and supported. The JAR file for the Inet Sybase driver is Sybelux.jar. Configure the connection pool using the following settings:

Data Direct Connect JDBC3.0/ Type4 Driver for IBM DB2 Databases

This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

Oracle Thin/Type4 Driver for Oracle 8.1.7 and 9.x Databases

This driver is not verified J2EE compatible and not fully supported. The JAR file for the Oracle driver is ojdbc14.jar. Configure the connection pool using the following settings:

OCI Oracle Type2 Driver for Oracle 8.1.7 and 9.x Databases

This driver is not verified J2EE compatible and not fully supported. 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:

Data Direct Connect JDBC3.0/ Type4 Driver for Oracle 8.1.7 and 9.x Databases

This driver is not verified J2EE compatible and not fully supported. The JAR files for the Oracle driver are base.jar, oracle.jar, and util.jar. Configure the connection pool using the following settings:

Data Direct Connect JDBC3.0/ Type4 Driver for Microsoft SQL Server Databases

This driver is not verified J2EE compatible and not fully supported. The JAR files for the Microsoft SQL Server driver are util.jar, sqlserver.jar, and base.jar. Configure the connection pool using the following settings:

Sybase JConnect/Type4 Driver

This driver is not verified J2EE compatible and not fully supported. The JAR file for the Sybase driver is jconn2.jar. Configure the connection pool using the following settings:

Data Direct Connect JDBC3.0/ Type4 Driver for Sybase Databases

This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

Data Direct Connect JDBC3.0/ Type4 Driver for Informix Databases

This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

IBM Informix Type4 Driver

This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

MM MySQL Type4 Driver

This driver is not verified J2EE compatible and not fully supported. Configure the connection pool using the following settings:

CloudScape 5.1 Type4 Driver

This driver is not verified J2EE compatible and not fully supported. The JAR files for the CloudScape driver are db2j.jar, db2jtools.jar, db2jcview.jar, jh.jar, db2jcc.jar, and db2jnet.jar. Configure the connection pool using the following settings:

Creating Applications That Use the JDBC API

An application that uses the JDBC API is an application that looks up and connects to one or more databases. This section covers these topics:

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.

For general information about connections and JDBC URLs, see the Sun Java System Application Server Administration Guide.

Using JDBC Transaction Isolation Levels

For general information about transactions, see Chapter 12, "Using the Transaction Service," and the Sun Java System Application Server Administration Guide.

Not all database vendors support all transaction isolation levels available in the JDBC API. The Sun Java System Application Server permits specifying any isolation level your database supports, but throws an exception against values your database does not support.

The following table defines transaction isolation levels.

Table 11-5  Transaction Isolation Levels 

Transaction Isolation Level

Description

TRANSACTION_READ_UNCOMMITTED

Dirty reads, non-repeatable reads and phantom reads can occur.

TRANSACTION_READ_COMMITTED

Dirty reads are prevented; 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.

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

You can set the default transaction isolation level for a JDBC connection pool. For details, see Creating a Connection Pool.

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:

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



Previous      Contents      Index      Next     

Copyright 2004 Sun Microsystems, Inc. All rights reserved.