Chapter 2
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™ ONE Application Server. This chapter also provides high level JDBC implementation instructions for servlets and EJB™ components using the J2EE 1.4 Application Server. The J2EE 1.4 Application Server supports the JDBC 3.0 API, which encompasses the JDBC 2.0 Optional Package API.

This chapter contains the following sections:

General Steps for Creating a JDBC Resource
Configurations for Specific JDBC Drivers

---

General Steps for Creating a JDBC Resource

To prepare a resource that uses JDBC technology (“JDBC resource”) for use in J2EE applications deployed to the J2EE 1.4 Application Server, perform the following tasks:

Integrating the JDBC Driver
Creating a Connection Pool
Creating a JDBC Resource

For information about how to configure a specific JDBC technology-enabled driver (“JDBC driver”), see "Configurations for Specific JDBC Drivers".

Integrating the JDBC Driver

To use features in the JDBC API, you must choose a JDBC driver to work with the J2EE 1.4 Application Server. Then you must set up the driver. This section covers these topics:

Supported Database Drivers
Making the JDBC Driver JAR Files Accessible

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 J2EE 1.4 Application Server, see the J2EE 1.4 Application Server Release Notes.

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

Note	Because the drivers and databases supported by the J2EE 1.4 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 J2EE 1.4 Application Server domain, you can do either of the following:

Make the driver’s class files accessible to the Common Classloader. Copy the JAR and ZIP files into the domain_dir/lib directory or copy the .class files into the domain_dir/lib/classes directory, then restart the server.
Make the driver’s class files accessible to the System Classloader.
Login to the Administration Console by going to the following URL in your web browser:

  http://localhost:4848/asadmin

Go to the Application Server page.
Click the JVM Settings tab and the General option.
Click the Path Settings option.
Edit the Classpath Suffix field.
Click Save.
Restart the server.

Using either classloader makes classes accessible to any application or module across the domain.

Creating a Connection Pool

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

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

Using the Administration Console
Using The Command Line Interface

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 J2EE 1.4 Application Server Administrator’s Guide.

Using the Administration Console

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

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

http://localhost:4848/asadmin

Open the JDBC component.
Click Connection Pools.
Click the New button.
Enter the following information:
Name (required) - Enter a name (or ID) for the connection pool.
Datasource Classname (required) - Enter the vendor-supplied DataSource class name.
Click the Next button.
Enter the following information:
Resource Type - Enter user or leave blank.
Description - Enter a text description if desired.
Click the Next button.
You can change the pool settings listed in the following table.

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

Click the Next button.
You can change the general settings listed in the following table. All of these settings are optional.

Table 2-2  General 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.ValidationMethod	auto-commit	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.
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.

Click the Next button.
Specify values for any properties your JDBC driver requires. If a property you need is not listed, use the Add button to add it. 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 2-3  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.

Click the Next button and review your selections. You can click the Previous button to go back and change settings.
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 admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [--target config_name] --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 general asadmin parameters (--user, --password, --passwordfile, --host, --port, and --secure), see the J2EE 1.4 Application Server Administrator’s Guide.

For example:

asadmin create-jdbc-connection-pool --user joeuser --password secret --datasourceclassname oracle.jdbc.pool.OracleDataSource --failconnection=true --isconnectvalidatereq=true --property url=jdbc\\:oracle\\:thin\\:@myhost\\:1521\\:V8i:user=staging_lookup _app:password=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 J2EE 1.4 Application Server Administrator’s Guide.

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

asadmin delete-jdbc-connection-pool --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [--target config_name] [--cascade=false] connection_pool_id

For example:

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

To list JDBC connection pools, use the following command:

asadmin list-jdbc-connection-pools --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [config_name]

For example:

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

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:

Using The Administration Console
Using The Command Line Interface

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 general information about JDBC resources, see the J2EE 1.4 Application Server Administrator’s Guide.

Using The Administration Console

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

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

http://localhost:4848/asadmin

Open the JDBC component.
Click JDBC Resources.
Click the New button.
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".
Object Type (optional) - Leave the value at its default, which is user.
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.

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 admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [--target config_name] --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 general asadmin parameters (--user, --password, --passwordfile, --host, --port, and --secure), see the J2EE 1.4 Application Server Administrator’s Guide.

For example:

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

To delete a JDBC resource, use the following command:

asadmin delete-jdbc-resource --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [--target config_name] jndi_name

For example:

asadmin delete-jdbc-resource --user joeuser --password secret OracleDSlookup

To list JDBC resources, use the following command:

asadmin list-jdbc-resources --user admin_user [--password admin_password] [--passwordfile password_file] [--host localhost] [--port 4848] [--secure | -s] [--terse=false] [--echo=false] [--interactive=true] [config_name]

For example:

asadmin list-jdbc-resources --user joeuser --password secret

---

Configurations for Specific JDBC Drivers

The following JDBC driver and database combinations are supported for J2EE 1.4 Application Server. The combinations listed here have been tested with the J2EE 1.4 Application Server and are found to be J2EE compatible.

Inet ORAXO JDBC Driver for Oracle 9.x
PointBase Type4 Driver

For an up to date list of the JDBC drivers currently supported by the J2EE 1.4 Application Server, see the J2EE 1.4 Application Server Release Notes. Other JDBC drivers have been used with J2EE 1.4 Application Server, but J2EE compliance tests have not been completed with these drivers.

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

Inet ORAXO JDBC Driver for Oracle 9.x

The JAR file for this driver is Oranxo.jar.

Configure the connection pool using the following settings:

Name: You will use this name when you configure the JDBC resource later.
Datasource Classname: com.inet.ora.OraDataSource
Properties:
user - Specify the database user.
password - Specify the database password.
serviceName - Specify the URL of the database. For example:

  jdbc:oracle:thin:@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.

Configure the JDBC resource using the following settings:

JNDI Name: You should begin the JNDI name with jdbc/.
Pool Name: Select the name of the connection pool you configured.
Status Enabled: Check this box.

Note	This JDBC driver limits the size of BLOB datatypes to 4 GB.

PointBase Type4 Driver

Configure the connection pool using the following settings:

Name: You will use this name when you configure the JDBC resource later.
Datasource Classname: com.pointbase.jdbc.jdbcDataSource or com.pointbase.xa.xaDataSource
Properties:
user - Specify the database user.
password - Specify the database password.
databaseName - Specify the URL of the database.

Configure the JDBC resource using the following settings:

JNDI Name: You should begin the JNDI name with jdbc/.
Pool Name: Select the name of the connection pool you configured.
Data Source Enabled: Check this box.

Previous      Contents      Index      Next

---

Copyright 2003 Sun Microsystems, Inc. All rights reserved.