Chapter 3 JDBC Resources

This chapter explains how to configure JDBC resources, which are required by applications that access databases. This chapter contains the following sections:

About JDBC Resources

JDBC Resources

To store, organize, and retrieve data, most applications use relational databases. J2EE applications access relational databases through the JDBC API.

A JDBC resource (data source) provides applications with a means of connecting to a database. Typically, the administrator creates a JDBC resource for each database accessed by the applications deployed in a domain. (However, more than one JDBC resource can be created for a database.)

To create a JDBC resource, specify a unique JNDI name that identifies the resource. (See the section JNDI Names and Resources.) Expect to find the JNDI name of a JDBC resource in java:comp/env/jdbc subcontext. For example, the JNDI name for the resource of a payroll database could be java:comp/env/jdbc/payrolldb. Because all resource JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Admin Console, enter only jdbc/name. For example, for a payroll database specify jdbc/payrolldb.

JDBC Connection Pools

To create a JDBC resource, specify the connection pool with which it is associated. Multiple JDBC resources can specify a single connection pool.

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, the server maintains a pool of available connections to increase performance. When an application requests a connection, it obtains one from the pool. When an application closes a connection, the connection is returned to the pool.

The properties of connection pools can vary with different database vendors. Some common properties are the database's name (URL), user name, and password.

How JDBC Resources and Connection Pools Work Together

To store, organize, and retrieve data, most applications use relational databases. J2EE applications access relational databases through the JDBC API. Before an application can access a database, it must get a connection.

The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.

Given the resource's JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

Via the JDBC resource, the application gets a database connection.

Behind the scenes, the application server retrieves a physical connection from the connection pool that corresponds to the database. The pool defines connection attributes such as the database name (URL), user name, and password.

Now that it's connected to the database, the application can read, modify, and add data to the database.

The applications access the database by making calls to the JDBC API. The JDBC driver translates the application's JDBC calls into the protocol of the database server.

When it's finished accessing the database, the application closes the connection.

The application server returns the connection to the connection pool. Once it's back in the pool, the connection is available for the next application.

Setting Up Database Access

General Steps for Setting Up Database Access

Install a supported database product. For a list of database products supported by the Application Server, see the link to the Release Notes in the section Further Information.

Install a JDBC driver for the database product.

Make the driver's JAR file accessible to the domain's server instance. See Integrating a JDBC Driver.

Create the database. Usually, the application provider delivers scripts for creating and populating the database.

Create a connection pool for the database. See Creating a JDBC Connection Pool.

Create a JDBC resource that points to the connection pool. See Creating a JDBC Resource.

Integrating a JDBC Driver

A JDBC driver translates an application's JDBC calls into the protocol of the database server. To integrate the JDBC driver into an administrative domain, do either of the following:

Make the driver accessible to the common class loader.

Copy the driver's JAR and ZIP files into the install_dir/domains/domain_dir/lib directory or copy its class files into the install_dir/domains/domain_dir/lib/classes directory.

Restart the domain.

Make the driver accessible to the system class loader.

In the Admin Console's tree view, select Configurations.

Select the desired configuration (for example, default-config).

Select JVM Settings.

On the JVM Settings page, click the Path Settings tab.

In the Classpath Suffix field, enter the fully-qualified path name for the driver's JAR file.

Click Save.

Restart the server.

Admin Console Tasks for JDBC Connection Pools

Creating a JDBC Connection Pool

A JDBC connection pool is a group of reusable connections for a particular database. When creating the pool with the Admin Console, the Administratior is actually defining the aspects of a connection to a specific database.

When building the Create Connection Pool pages, certain data specific to the JDBC driver and the database vendor must be entered. Before proceeding, gather the following information:

Database vendor name

Resource type, such as javax.sql.DataSource (local transactions only) javax.sql.XADataSource (global transactions)

Data source class name

Required properties, such as the database name (URL), user name, and password

In the tree component, expand the Resources node.

Under Resources, expand the JDBC node.

Under JDBC, select the Connection Pools node.

On the Connection Pools page, click New.

On the first Create Connection Pool page, specify the following general settings:

In the Name field, enter a logical name for the pool.

Specify this name when creating a JDBC resource.

Select an entry from the Resource Type combo box.

Select an entry from the Database Vendor combo box.

Click Next.

On the second Create Connection Pool page, specify the value for the DataSource Class Name field.

If the JDBC driver has a DataSource class for the resource type and database vendor specified in the previous page, then the value of the DataSource Class Name field is provided.

Click Next.

On the third and last Create Connection Pool page, perform these tasks:

In the General Settings section, verify that the values are correct.

For the fields in the Pool Settings, Connection Validation, and Transaction Isolation sections, retain the default values.

It is most convenient to change these settings at a later time. See Editing a JDBC Connection Pool.

In the Additional Properties table, add the required properties, such as database name (URL), user name, and password.

Click Finish.

Editing a JDBC Connection Pool

The Edit JDBC Connection Pool page provides the means to change all of the settings for an existing pool, except its name.

In the tree component, expand the Resources node.

Under Resources, expand the JDBC node.

Under JDBC, expand the Connection Pools node.

Select the node for the pool you want to edit.

On the Edit JDBC Connection Pool page, make the necessary changes.

See the following sections for explanations of the settings that might change.

Click Save.

General Settings

The values of the general settings depend on the specific JDBC driver that is installed. These settings are the names of classes or interfaces in the Java programming language.

Table 3-1 General Settings for a JDBC Connection Pool
Parameter	Description
DataSource Class Name	The vendor-specific class name that implements the DataSource and / or XADataSource APIs. This class is in the JDBC driver.
Resource Type	Choices include javax.sql.DataSource (local transactions only), javax.sql.XADataSource (global transactions), and java.sql.ConnectionPoolDataSource (local transactions, possible performance improvements).

Pool Settings

A set of physical database connections reside in the pool. When an application requests a connection, the connection is removed from the pool, and when the application releases the connection, it is returned to the pool.

Table 3-2 Pool Settings for a JDBC Connection Pool
Parameter	Description
Initial and Minimum Pool Size	The minimum number of connections in the pool. This value also determines the number of connections placed in the pool when the pool is first created or when application server starts.
Maximum Pool Size	The maximum number of connections in the pool.
Pool Resize Quantity	When the pool shrinks toward the minimum pool size it is resized in batches. This value determines the number of connections in the batch. Making this value too large delays connection recycling; making it too small will be less efficient.
Idle Timeout	The maximum time in seconds that a connection can remain idle in the pool. After this time expires, the connection is removed from the pool.
Max Wait Time	The amount of time the application requesting a connection will wait before getting a connection timeout. Because the default wait time is long, the application might appear to hang indefinitely.

Connection Validation

Optionally, the application server can validate connections before they are passed to applications. This validation allows the application server to automatically re-establish database connections if the database becomes unavailable due to network failure or database server crash. Validation of connections incurs additional overhead and slightly reduces performance.

Table 3-3 Connection Validation Settings for a JDBC Connection Pool
Parameter	Description
Connection Validation	Select the Required checkbox to enable connection validation.
Validation Method	The application server can validate database connections in three ways: auto-commit, metadata, and table. auto-commit and metadata - The application server validates a connection by calling the con.getAutoCommit() and con.getMetaData() methods. However, because many JDBC drivers cache the results of these calls, they do not always provide reliable validations. Check with the driver vendor to determine whether these calls are cached or not. table - The application queries a database table that are specified. The table must exist and be accessible, but it doesn't require any rows. Do not use an existing table that has a large number of rows or a table that is already frequently accessed.
Table Name	If you selected table from the Validation Method combo box, then specify the name of the database table here.
On Any Failure	If you select the checkbox labelled Close All Connections, if a single connection fails, then the application server closes all connections in the pool and then re-establish them. If you do not select the checkbox, then individual connections are re-established only when they are used.

Transaction Isolation

Because a database is usually accessed by many users concurrently, one transaction might update data while another attempts to read the same data. The isolation level of a transaction defines the degree to which the data being updated is visible to other transactions. For details on isolation levels, refer to the documentation of the database vendor.

Table 3-4 Transaction Isolation Settings for a JDBC Connection Pool
Parameter	Description
Transaction Isolation	Makes it possible to select the transaction isolation level for the connections of this pool. If left unspecified, the connections operate with default isolation levels provided by the JDBC driver.
Guaranteed Isolation Level	Only applicable if the isolation level has been specified. If you select the Guaranteed checkbox, then all connections taken from the pool have the same isolation level. For example, if the isolation level for the connection is changed programatically (with con.setTransactionIsolation) when last used, this mechanism changes the status back to the specified isolation level.

Properties

In the Additional Properties table, it is possible to specify properties, such as the database name (URL), user name, and password. Because the properties vary with database vendor, consult the vendor's documentation for details.

Verifying Connection Pool Settings

Deleting a JDBC Connection Pool

Admin Console Tasks for JDBC Resources

Creating a JDBC Resource

A JDBC resource (data source) provides applications with a means of connecting to a database. Before creating a JDBC resource, first create a JDBC connection pool.

In the tree component, expand the Resources node.

Under Resources, expand the JDBC node.

Under JDBC, select the JDBC Resources node.

On the JDBC Resources page, click New.

On the Create JDBC Resource page, specify the resource's settings:

In the JNDI Name field, type a unique name. By convention, the name begins with the jdbc/ string. For example: jdbc/payrolldb. Don't forget the forward slash.

From the Pool Name combo box, choose the connection pool to be associated with the new JDBC resource.

By default, the resource is available (enabled) as soon as it is created. If you want the resource to be unavailable, deselect the Enabled checkbox.

In the Description field, type a short description of the resource.

In the Targets section, specify the targets (clusters and standalone server instances) on which the resource is available. Select the desired target on the left, and click Add to add it to the list of selected targets.

Click OK.

Editing a JDBC Resource

In the tree component, expand the Resources node.

Under Resources, expand the JDBC node.

Under JDBC, expand the JDBC Resources node.

Select the node for the JDBC resource to be edited.

On the Edit JDBC Resource page, it is possible to perform these tasks:

From the Pool Name combo box, select a different connection pool.

In the Description field, change the short description of the resource.

Select or deselect the checkbox to enable or disable the resource.

Select the Targets tab to change the targets (clusters and standalone server instances) on which the resource is available.

Select the checkbox for an existing target in the list, then click Enable to enable the resource for that target or Disable to disable the resource for that target.

Click Manage Targets to add or remove targets to the list. In the Manage Targets page, select the desired target in the Available list on the left, and click Add to add it to the list of selected targets. Click Remove to remove a target from the Selected list.

Click OK to save the changes to the available targets.

Click Save to apply the edits.

Deleting a JDBC Resource

Admin Console Tasks for Persistence Manager Resources

Creating a Persistence Manager Resource

This feature is needed for backward compatibility. To run on version 7 of the Application Server, a persistent manager resource was required for applications with container-managed persistence beans (a type of EJB component).

In the tree component, expand the Resources node.

Under Resources, select the Persistence Managers node.

On the Persistence Managers page, click New.

On the Create Persistence Manager page, specify these settings:

In the JNDI Name field, type a unique name, for example: jdo/mypm. Don't forget the forward slash.

In the Factory Class field, retain the default class provided with this release, or type in the class of another implementation.

From the Connection Pool combo box, choose the connection pool that the new persistence manager resource will belong to.

By default, the new persistence manager resource is enabled. To disable it, deselect the Enabled check box.

Click OK.

Editing a Persistence Manager Resource

In the tree component, select the Persistence Managers node.

Select the node for the persistence manager resource to be edited.

On the Edit Persistence Manager page, it is possible to perform these tasks:

From the Connection Pool combo box, select a new connection pool to which the new persistence manager resource belongs.

Select or deselect the checkbox to enable or disable the resource.

In the Targets section, specify the targets (clusters and standalone server instances) where the resource resides. Select the desired target on the left, and click Add to add it to the list of selected targets.

Select the Targets tab to change the targets (clusters and standalone server instances) where the resource resides.

Select the checkbox for an existing target in the list, then click Enable to enable the resource for that target or Disable to disable the resource for that target.

Click OK to save the changes to the available targets.

Click Save.

Previous Contents Index Next
Sun Java System Application Server 8 2004Q4 Beta Platform Edition Administration Guide