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, you’ll create a JDBC resource for each database accessed by the applications deployed in a domain. (However, you may create more than one JDBC resource for a database.)

When you create a JDBC resource, you specify a unique JNDI name that identifies the resource. (See the section JNDI Names and Resources.) The JNDI name of a JDBC resource should be 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 you specify the JNDI name of a JDBC resource in the Admin Console, you enter only jdbc/name. For example, for a payroll database you would specify jdbc/payrolldb.

JDBC Connection Pools

When you create a JDBC resource, you specify the connection pool that it is associated with. Multiple JDBC resources may 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 may 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 application 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 that needs it.

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 Sun Java™ System Application Server Platform Edition 8, 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, you can 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 Application Server.

On the Application Server page, select the JVM Settings tab.

On the JVM General Settings page, click the Path Settings option.

On the JVM Classpath Settings page, 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 you create the pool with the Admin Console, you are actually defining the aspects of a connection to a specific database.

As you step through the Create Connection Pool pages, you’ll enter information that depends on the specific JDBC driver and database vendor. Before proceeding, gather the following information:

database vendor

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

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.

You will specify this name when you create 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 you 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, you may retain the default values.

You can change these settings at a later time. See Editing a JDBC Connection Pool.

In the Additonal 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 enables you to change all of the settings for an existing pool, except its name.

General Settings

The values of the general settings depend on the specific JDBC driver that you’ve 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.ConnectinPoolDataSource (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 will delay 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 will be removed from the pool.
Max Wait Time	The amount of time the application that has requested 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 will incur additional overhead and slightly reduce 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, meta-data, and table. auto-commit and meta-data: 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. You should check with your driver vendor to determine whether these calls are cached or not. table: The application queries a database table thay you’ve specified. The table must exist and be accessible, but it doesn't require any rows. You should 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 will close all connections in the pool and then re-establish them. If you do not select the checkbox, then individual connections will be 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	Allows you to select the transaction isolation level for the connections of this pool. If left unspecified, the connections will operate with default isolation level provided by the JDBC driver.
Guarantee Isolation Level	Only applicable if the isolation level has been specified. If you select the Guaranteed checkbox, then all connections taken from the pool will have the same isolation level. For example, if the isolation level for the connection was changed programatically (with con.setTransactionIsolation) when last used, this mechanism will change it back to the specified isolation level.

Properties

In the Additional Properties table, you can specify properties such as the database name (URL), user name, and password. Because the properties vary with database vendor, you should consult the vendor’s documentation for details.

Pinging the Database

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, you must first create a JDBC connection pool.

In the tree component, expand the JDBC node.

Expand the JDBC Resources node.

On the JDBC Resources page, click New.

On the Create JDBC Resources page, specify the resource’s settings:

In the JNDI Name field, type a uniqe name. By convention, the name should begin with the jdbc/ string. For example: jdbc/payrolldb. Don’t forget the forward slash.

From the Pool Name combo box, choose the connection pool that the new JDBC resource will belong to.

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.

Click OK.

Editing a JDBC Resource

Deleting a JDBC Resource

Admin Console Tasks for Persistence Manager Resources

Creating a Persistence Manager Resource

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

In the tree component, 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 uniqe name. gor 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 implmementation.

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

By default, the new peristence manager resource will be enabled. To disable it, deselct the Enabled check box.