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

- Previous: Chapter 2 Deploying Applications
- Next: Chapter 4 Configuring Java Message Service Resources

# 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 and Connection Pools

- Setting Up Database Access

- About JDBC Connection Pools

- About JDBC Resources

- About Persistence Manager Resources

# About JDBC Resources and Connection Pools

- JDBC Resources

- JDBC Connection Pools

- How JDBC Resources and Connection Pools Work Together

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

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

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

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

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

- To Set Up Database Access

- To integrate a JDBC driver

## To Set Up Database Access

1. Install a supported database product.

   For a list of database products supported by the Application Server, see the Release Notes.

2. Install a JDBC driver for the database product.

3. Make the driver’s JAR file accessible to the domain’s server instance. See To integrate 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 To create a JDBC connection pool.

6. Create a JDBC resource that points to the connection pool. See To create a JDBC resource.

## To integrate a JDBC driver

A JDBC driver translates an application’s JDBC calls into the protocol of the database server.

1. To integrate the JDBC driver into an administrative domain, do either of the following:

   - Make the driver accessible to the common class loader.

     1. Copy the driver’s JAR and ZIP files into the domain-dir`/lib` directory or copy its class files into the domain-dir`/lib/ext` directory.

     2. Restart the domain.

   - Make the driver accessible to the system class loader.

     1. In the Admin Console’s tree view (left pane), select Configurations.

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

     3. Select JVM Settings.

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

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

     6. Click Save.

     7. Restart the server.

# About JDBC Connection Pools

- To create a JDBC connection pool

- To edit a JDBC connection pool

- To verify connection pool settings

- To delete a JDBC connection pool

## To create 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 Administrator is actually defining the aspects of a connection to a specific database.

##### Before You Begin

Before creating the pool, you must first install and integrate the JDBC driver. See To integrate a 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:

- 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

1. In the Admin Console’s tree view (left pane), 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.

      Specify this name when creating a JDBC resource.

   2. Select an entry from the Resource Type combo box.

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

   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.

8. Click Next.

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

      It is most convenient to change these settings at a later time. See To edit a JDBC connection pool.

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

10. Click Finish.

##### Equivalent asadmin command

`create-jdbc-connection-pool`

## To edit 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.

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 any necessary changes.

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

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

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

      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.

   3. Change connection validation settings.

      Optionally, the application server can validate connections before they are passed to applications. This validation allows the application server to automatically reestablish 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.

      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. auto-commit validation makes use of two methods for validating the connection. `getAutoCommit()` is used to retrieve the current state of auto-commit and `setAutoCommit()` to change the state of auto-commit. This allows actual contact with the database to take place. `getAutomCommit()` might or might not contact the database, depending on the implementation. Actual physical connection will be wrapped for different purposes, such as a connection pool. Note – Databases such as Oracle do some caching for `setAutoCommit()`. For such databases, actual connection validation might not happen, so table-based validation is recommended. 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 reestablish them. If you do not select the checkbox, then individual connections are reestablished only when they are used.

   4. Change transaction isolation settings.

      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.

      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 programmatically (with `con.setTransactionIsolation`) when last used, this mechanism changes the status back to the specified isolation level.

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

6. Click Save.

##### See Also

- To create a JDBC connection pool

- To verify connection pool settings

- To delete a JDBC connection pool

## To verify connection pool settings

1. Start the database server.

2. Click Ping.

   The Admin Console attempts to connect to the database. If an error message displays, check to see if the database server was restarted.

## To delete 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

- To create a JDBC resource

- To edit a JDBC resource

- To delete a JDBC resource

- To enable or disable a JDBC resource

## To create a JDBC resource

A JDBC resource (data source) provides applications with a means of connecting to a database.

##### Before You Begin

Before creating a JDBC resource, first create a JDBC connection pool. See 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 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. If you want the resource to be unavailable, deselect the Enabled checkbox.

      By default, the resource is available (enabled) as soon as it is created.

   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`

## To edit 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.

      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.

## To delete a JDBC resource

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.

## To enable or disable 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

- To create a persistence manager resource

- To edit a persistence manager resource

- To manage resource targets

- To delete a persistence manager resource

- To enable or Disable a persistence manager resource

## To create 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). Using a JDBC resource instead is recommended.

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. To disable the new persistence manager resource, deselect the Enabled check box.

      By default, the new persistence manager resource is enabled.

   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`

## To edit a persistence manager resource

1. From the Edit Persistence Manager Properties tab, select the Add Property button.

   A new row is added to the Additional Properties table.

2. Add the desired property and value.

## To manage resource targets

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.

## To delete 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`

## To enable or Disable 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: Chapter 2 Deploying Applications
- Next: Chapter 4 Configuring Java Message Service Resources

- © 2010, Oracle Corporation and/or its affiliates