Configuring Access to the Database

After establishing the database, you are ready to set up access for Enterprise Server applications. Before an application can access a database, the application must get a connection. At runtime, the following sequence occurs 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.

   Using the JNDI name of the resource, the naming and directory service locates the JDBC resource. Each JDBC resource specifies a connection pool.

2. Using the JDBC resource, the application gets a database connection.

   Enterprise 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. After the database connection is established, the application can read, modify, and add data to the database.

   The application accesses 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 the application is finished accessing the database, the application closes the connection and returns the connection to the connection pool.

The following topics are addressed here:

• Administering JDBC Connection Pools

• Administering JDBC Resources

• Integrating the JDBC Driver

Administering JDBC Connection Pools

A JDBC connection pool is a group of reusable connections for a particular database. Because creating each new physical connection is time consuming, Enterprise Server maintains a pool of available connections. 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.

A JDBC resource is created by specifying the connection pool with which the resource is associated. Multiple JDBC resources can specify a single connection pool. The properties of connection pools can vary with different database vendors. Some common properties are the database name (URL), the user name, and the password.

The following tasks and information are used to administer JDBC connection pools:

• To Create a JDBC Connection Pool

• To List JDBC Connection Pools

• To Contact (Ping) a Connection Pool

• To Delete a JDBC Connection Pool

To Create a JDBC Connection Pool

The remote create-jdbc-connection-pool command enables you to register a new JDBC connection pool with the specified JDBC connection pool name. A JDBC connection pool or a connector connection pool can be created with authentication. You can either use a command option to specify user, password, or other connection information using the asadmin utility, or specify the connection information in the XML descriptor file.

When you are building the connection pool, certain data specific to the JDBC driver and the database vendor will be required. You can find some of the following specifics inConfiguration Specifics for JDBC Drivers:

• 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

Creating a JDBC connection pool is a dynamic event and does not require server restart.

Before You Begin

Before creating the connection pool, you must first install and integrate the database and its associated JDBC driver. For instructions, see Setting Up the Database.

1. Ensure that the server is running.

   Remote commands require a running server.

2. Create the JDBC connection pool by using the create-jdbc-connection-pool(1) command.

Example 5–3 Creating a JDBC Connection Pool

The following example command creates a JDBC connection pool named sample_derby_pool on localhost:

asadmin create-jdbc-connection-pool 
--datasourceclassname org.apache.derby.jdbc.ClientDataSource 
--restype javax.sql.XADataSource 
--property portNumber=1527:password=APP:user=APP:serverName=
localhost:databaseName=sun-appserv-samples:connectionAttribut
es=\;create\\=true sample_derby_pool

Information similar to the following is displayed:

Command create-jdbc-connection-pool executed successfully.

See Also

To see the full syntax and options of the command, type asadmin create-jdbc-connection-pool --help at the command line.

To List JDBC Connection Pools

The remote list-jdbc-connection-pools command enables you to list all existing JDBC connection pools.

1. Ensure that the server is running.

   Remote commands require a running server.

2. List the JDBC connection pools by using the list-jdbc-connection-pools(1) command.

Example 5–4 Listing JDBC Connection Pools

The following example command lists the JDBC connection pools that are on localhost:

asadmin list-jdbc-connection-pools

Information similar to the following is displayed:

sample_derby_pool2
poolA
__TimerPool
DerbyPool
sample_derby_pool
Command list-jdbc-connection-pools executed successfully.

See Also

To see the full syntax and options of the command, type asadmin list-jdbc-connection-pools --help at the command line.

To Contact (Ping) a Connection Pool

The remote ping-connection-pool command tests if a connection pool is usable. For example, if you create a new JDBC connection pool for an application that is expected to be deployed later, you can test the JDBC pool with this command before the application is deployed.

Before You Begin

Before you can contact a connection pool, the connection pool must be created with authentication, and the server or database must be running.

1. Ensure that the server is running.

   Remote commands require a running server.

2. Ping a connection pool by using the ping-connection-pool(1) command.

Example 5–5 Contacting a Connection Pool

The following example command tests to see if the DerbyPool connection pool is usable:

asadmin ping-connection-pool DerbyPool

Information similar to the following is displayed if the connection pool is usable:

Command ping-connection-pool executed successfully

See Also

To see the full syntax and options of the command, type asadmin ping-connection-pool --help at the command line.

To Delete a JDBC Connection Pool

The remote delete-jdbc-connection-pool command enables you to delete an existing JDBC connection pool. Deleting a JDBC connection pool is a dynamic event and does not require server restart.

Before You Begin

Before deleting a JDBC connection pool, all associations to the resource must be removed.

1. Ensure that the server is running.

   Remote commands require a running server.

2. Obtain the exact name of the JDBC connection pool that you are deleting.

   To list the existing JDBC connection pools:

   asadmin list-jdbc-connection-pools

3. If necessary, notify users that the JDBC connection pool is being deleted.

4. Delete the connection pool by using the delete-jdbc-connection-pool(1) command.

Example 5–6 Deleting a JDBC Connection Pool

The following example command deletes the JDBC connection pool named DerbyPool:

asadmin delete-jdbc-connection-pool jdbc/DerbyPool

Information similar to the following is displayed if the connection pool is usable:

Command delete-jdbc-connection-pool executed successfully.

See Also

To see the full syntax and options of the command, type asadmin delete-jdbc-connection-pool --help at the command line.

Administering JDBC Resources

A JDBC resource, also known as a data source, provides an application with a means of connecting to a database. Typically, you create a JDBC resource for each database that is accessed by the applications deployed in a domain. Multiple JDBC resources can be specified for a database.

A JDBC resource is created by specifying the connection pool with which the resource will be associated . Unse a unique Java Naming and Directory Interface (JNDI) name to identify the resource. For example, the JNDI name for the resource of a payroll database might be java:comp/env/jdbc/payrolldb.

The following tasks and information are used to administer JDBC resources:

• To Create a JDBC Resource

• To List JDBC Resources

• To Delete a JDBC Resource

To Create a JDBC Resource

The remote create-jdbc-resource command enables you to create a JDBC resource. Creating a JDBC resource is a dynamic event and does not require server restart.

Because all JNDI names are in the java:comp/env subcontext, when specifying the JNDI name of a JDBC resource in the Administration Console, use only the jdbc/name format is used. For example, a payroll database might be specified as jdbc/payrolldb.

Before You Begin

Before creating a JDBC resource, you must first create a JDBC connection pool. For instructions, see To Create a JDBC Connection Pool.

1. Ensure that the server is running.

   Remote commands require a running server.

2. Create a JDBC resource by using the create-jdbc-resource(1) command.

3. If necessary, notify users that the new resource has been created.

Example 5–7 Creating a JDBC Resource

The following example command creates a JDBC resource named DerbyPool:

asadmin create-jdbc-resource --connectionpoolid DerbyPool jdbc/DerbyPool

Information similar to the following is displayed if the connection pool is usable:

Command create-jdbc-resource executed successfully.

See Also

To see the full syntax and options of the command, type asadmin create-jdbc-resource --help at the command line.

To List JDBC Resources

The remote list-jdbc-resources command enables you to list the existing JDBC resources.

1. Ensure that the server is running.

   Remote commands require a running server.

2. List JDBC resources by using the list-jdbc-resources(1) command.

Example 5–8 Listing JDBC Resources

The following example command lists JDBC resources for localhost:

asadmin list-jdbc-resources

Information similar to the following is displayed:

jdbc/__TimerPool
jdbc/DerbyPool
jdbc/__default
jdbc1
Command list-jdbc-resources executed successfully.

See Also

To see the full syntax and options of the command, type asadmin list-jdbc-resources --help at the command line.

To Delete a JDBC Resource

This remote command enables you to delete an existing JDBC resource. Deleting a JDBC resource is a dynamic event and does not require server restart.

Before You Begin

Before deleting a JDBC resource, all associations with this resource must be removed.

1. Ensure that the server is running.

   Remote commands require a running server.

2. Obtain the exact name of the JDBC resource that you are deleting.

   To list the existing JDBC resources:

   asadmin list-jdbc-resources

3. If necessary, notify users that the JDBC resource is being deleted.

4. Delete a JDBC resource by using the delete-jdbc-resource(1) command.

Example 5–9 Deleting a JDBC Resource

The following example command deletes a JDBC resource named DerbyPool:

asadmin delete-jdbc-resource jdbc/DerbyPool

Information similar to the following is displayed:

Command delete-jdbc-resource executed successfully.

See Also

To see the full syntax and options of the command, type asadmin delete-jdbc-resource --help at the command line.

Integrating the JDBC Driver

After setting up the connection pool and resources, integrate the JDBC driver in either of the following ways:

• Make the driver accessible to the common class loader, and restart the domain.

  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.

• Identify the fully-qualified path name for the driver's JAR file.