Sun logo      Previous      Contents      Next     

Sun Java System Application Server Enterprise Edition 8.1 Administration Guide 2005Q1

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

At runtime, here’s what happens when an application connects to a database:

  1. The application gets the JDBC resource (data source) associated with the database by making a call through the JNDI API.
  2. Given the resource’s JNDI name, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

  3. Via the JDBC resource, the application gets a database connection.
  4. 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.

  5. Now that it’s connected to the database, the application can read, modify, and add data to the database.
  6. 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.

  7. When it’s finished accessing the database, the application closes the connection.
  8. 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

  1. 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.
  2. Install a JDBC driver for the database product.
  3. Make the driver’s JAR file accessible to the domain’s server instance. See Integrating a JDBC Driver.
  4. Create the database. Usually, the application provider delivers scripts for creating and populating the database.
  5. Create a connection pool for the database. See Creating a JDBC Connection Pool.
  6. 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:


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

Before creating the pool, you must first install and integrate the JDBC driver.

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:

To create a JDBC connection pool:

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, select the Connection Pools node.
  4. On the Connection Pools page, click New.
  5. On the first Create Connection Pool page, specify the following general settings:
    1. In the Name field, enter a logical name for the pool.
    2. Specify this name when creating a JDBC resource.

    3. Select an entry from the Resource Type combo box.
    4. Select an entry from the Database Vendor combo box.
  6. Click Next.
  7. On the second Create Connection Pool page, specify the value for the DataSource Class Name field.
  8. 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.

  9. Click Next.
  10. On the third and last Create Connection Pool page, perform these tasks:
    1. In the General Settings section, verify that the values are correct.
    2. For the fields in the Pool Settings, Connection Validation, and Transaction Isolation sections, retain the default values.
    3. It is most convenient to change these settings at a later time. See Editing a JDBC Connection Pool.

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

Equivalent asadmin command: create-jdbc-connection-pool

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.

To access the Edit JDBC Connection Pool page:

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, expand the Connection Pools node.
  4. Select the node for the pool you want to edit.
  5. On the Edit JDBC Connection Pool page, make the necessary changes.
  6. See the following sections for explanations of the settings that might change.

  7. 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 6-1  General Settings for a JDBC Connection Pool

Parameter

Description

DataSource Class Name

The vendor-specific class name that implements the DataSource/ConnectionPoolDataSource/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 6-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 6-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 is 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 6-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

To verify the connection pool settings:

  1. Start the database server.
  2. Click Ping.
  3. The Admin Console attempts to connect to the database. If an error message displays, check to see if the database server was restarted.

Deleting a JDBC Connection Pool

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, select the Connection Pools node.
  4. On the Connection Pools page, select the checkbox for the pool to be deleted.
  5. Click Delete.

Equivalent asadmin command: delete-jdbc-connection-pool


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

To create a JDBC resource:

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, select the JDBC Resources node.
  4. On the JDBC Resources page, click New.
  5. On the Create JDBC Resource page, specify the resource’s settings:
    1. 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.
    2. From the Pool Name combo box, choose the connection pool to be associated with the new JDBC resource.
    3. 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.
    4. In the Description field, type a short description of the resource.
    5. 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.
  6. Click OK.

Equivalent asadmin command:create-jdbc-resource

Editing a JDBC Resource

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, expand the JDBC Resources node.
  4. Select the node for the JDBC resource to be edited.
  5. On the Edit JDBC Resource page, it is possible to perform these tasks:
    1. From the Pool Name combo box, select a different connection pool.
    2. In the Description field, change the short description of the resource.
    3. Select or deselect the checkbox to enable or disable the resource.
    4. Select the Targets tab to change the targets (clusters and standalone server instances) on which the resource is available.
    5. 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.

  6. Click Save to apply the edits.

Deleting a JDBC Resource

  1. In the tree component, expand the Resources node.
  2. Under Resources, expand the JDBC node.
  3. Under JDBC, select the JDBC Resources node.
  4. On the JDBC Resources page, select the checkbox for the resource to be deleted.
  5. Click Delete.

Enabling and Disabling a JDBC Resource

  1. In the tree component, expand the JDBC Resources node or expand the Standalone Instances to select the Server Instance node Resource tab.
  2. On the Resources page, select the checkbox for the resource to be enabled or disabled.
  3. Click Enable or Disable.


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

To create a persistence manager resource:

  1. In the tree component, expand the Resources node.
  2. Under Resources, select the Persistence Managers node.
  3. On the Persistence Managers page, click New.
  4. On the Create Persistence Manager page, specify these settings:
    1. In the JNDI Name field, type a unique name, for example: jdo/mypm. Don’t forget the forward slash.
    2. In the Factory Class field, retain the default class provided with this release, or type in the class of another implementation.
    3. From the Connection Pool combo box, choose the connection pool that the new persistence manager resource will belong to.
    4. By default, the new persistence manager resource is enabled. To disable it, deselect the Enabled check box.
    5. 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.
  5. Click OK.

Equivalent asadmin command: create-persistence-resource

Editing a Persistence Manager Resource

To edit an existing persistence manager resource property:

  1. From the Edit Persistence Manager Properties tab, select the Add Property button.
  2. A new row is added to the Additional Properties table.

  3. Add the desired property and value.

Managing Resource Targets

To manage a resource target:

  1. Select the Targets tab to change the targets (clusters and standalone server instances) where the resource resides.
  2. 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.
  3. 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.
  4. Click OK to save the changes to the available targets.
  5. Click Save.

Deleting a Persistence Manager Resource

  1. In the tree component, expand the Persistence Managers node.
  2. Select the Persistence Managers node.
  3. On the Persistence Managers page, select the checkbox for the persistence manager that you want to delete.
  4. Click Delete.

Equivalent asadmin command: delete-persistence-resource

Enabling and Disabling a Persistence Manager Resource

  1. In the tree component, expand the Persistence Managers node .
  2. Select the checkbox for the resource to be enabled or disabled.
  3. Click Enable or Disable.



Previous      Contents      Next     


Copyright 2004 - 2005 Sun Microsystems, Inc. All rights reserved.