Moving to Production Mode

Tutorial 16: Using a Production Database Management System

This tutorial describes how to change the database used by the deployed MedRec application from one on a development relational database management system (PointBase) to a production DBMS (Oracle).

In particular, this tutorial shows you how to use the Administration Console to:

Create both XA and non-XA JDBC connection pools used to connect to an Oracle database.
Update the existing JDBC datasource used by the MedRec application to use the new Oracle XA JDBC connection pool.
Update the existing JMS JDBC store to use the new Oracle non-XA JDBC connection pool.

Note: It is assumed that you have already installed and configured the Oracle database management system and that you have created an Oracle database. Describing how to perform these tasks is beyond the scope of this tutorial.

The tutorial includes the following sections:

Prerequisites

Before starting this tutorial:

Install and configure the Oracle database management system. See the Oracle documentation.
Create an Oracle database. See the Oracle documentation.
Work through the MedRec tutorials up to Tutorial 15: Deploying the MedRec Package for Production, which describe how to compile, package, and deploy to the MedRec server the three applications that make up the MedRec application suite: medrecEar, physicianEar, and startupEar.

Procedure

Follow these steps:

Step 1: Create the Oracle tables and populate with MedRec application data.

BEA provides two SQL scripts that you can use to create and populate the tables of your Oracle database:

medrec_oracle.ddl—contains the SQL statements for creating the tables used by the MedRec application.
medrec_oracle.sql—contains the SQL statements for populating the tables with data.

These scripts are located in SAMPLES_HOME\server\medrec\setup\db directory, where SAMPLES_HOME refers to the main examples directory of your WebLogic Server installation, such as c:\beahome\weblogic81\samples.

Note: It is beyond the scope of this tutorial to describe exactly how to create and populate Oracle tables. See the Oracle documentation.

Step 2: Invoke the Administration Console.

You use the Administration Console to create and update the WebLogic Server resources used by the MedRec application suite.

Enter the following URL in your browser:
```
http://127.0.0.1:7101/console
```
Enter weblogic as the username and password, then click Sign In.

Step 3: Create an Oracle XA JDBC connection pool.

The JDBC connection pool configuration describes how to connect physically from WebLogic Server to a database, in this case an Oracle database. This procedure describes how to create a JDBC connection pool that uses an XA JDBC driver, which is a BEA best practice.

The procedure also shows how to specify support for SQL without global transactions.

In the left pane of the Administration Console, expand Services—>JDBC.
Click Connection Pools.
In the right pane, click Configure a new JDBC Connection Pool.
For the Database Type, select Oracle.
For the Database Driver, select Oracle's Driver (Thin XA) Versions: 8.1.7,9.0.1,9.2.0.
Click Continue.
In the Name field, enter MedRecPool-Oracle-XA.
In the Database Name field, enter the name of your Oracle database.
In the Host Name field, enter the name of the computer that is hosting the Oracle database management system.
In the Port field, enter the port of the Oracle server.
In the Database User Name field, enter the name of the Oracle database user.
In the Password and Confirm Password fields, enter the password of the database user.
Click Continue.
Ensure that the information to test the connection to the Oracle database is correct, then click Test Driver Configuration.
Note: Be sure you have started the Oracle database management system and that the database is accessible, or the test of its driver configuration will fail.

After verifying that the connection succeeded, click Create and Deploy.
In the left pane of the Administration Console, click MedRecPool-Oracle-XA under the Services—>JDBC—>Connection Pools node.
In the right pane, select the Configuration—>Connections tab.
Click the Show link to the right of the Advanced Options label.
Scroll down to the end of the page and click Supports Local Transactions.
Click Apply.

Step 4: Create a non-XA Oracle JDBC connection pool.

This procedure describes how to create a JDBC connection pool that does not use an XA JDBC driver.

Typically you use an XA JDBC driver when creating a connection pool. However, because JMS JDBC stores do not support XA resource drivers (WebLogic JMS implements its own XA resource), you need an additional connection pool that is non-XA. Later procedures show how to associate the XA connection pool to a JDBC DataSource and the non-XA connection pool to a JMS JDBC store.

In the left pane of the Administration Console, expand Services—>JDBC.
Click Connection Pools.
Click Configure a new JDBC Connection Pool.
For the Database Type, select Oracle.
For the Database Driver, select Oracle's Driver (Thin) Versions: 8.1.7,9.0.1,9.2.0.
Click Continue.
In the Name field, enter MedRecPool-Oracle.
In the Database Name field, enter the name of your Oracle database.
In the Host Name field, enter the name of the computer that is hosting the Oracle database management system.
In the Port field, enter the port of the Oracle Server.
In the Database User Name field, enter the name of the Oracle database user.
In the Password and Confirm Password fields, enter the password of the database user.
Click Continue.
Ensure that the information to test the connection to the Oracle database is correct, then click Test Driver Configuration.
After verifying that the connection succeeded, click Create and Deploy.

Step 5: Update the MedRecTXDataSource to use the new Oracle XA connection pool.

In the left pane of the Administration Console, expand Services—>JDBC—>Data Sources.
Click MedRecTxDataSource.
In the right pane, select MedRecPool-Oracle-XA in the Pool Name drop-down choice box.
Click Apply.

Step 6: Update the JMS JDBC store to use the new Oracle non-XA connection pool.

In the left pane of the Administration Console, expand Services—>JMS—>Stores.
Click MedRecJMSJDBCStore.
In the right pane, select MedRecPool-Oracle in the Connection Pool drop-down choice box.
Click Apply.

Step 7: Shut down and restart the MedRec server.

Changing the connection pool associated with a datasource requires that WebLogic Server be restarted so that the changes take effect. In a real-life situation, you would very likely restart WebLogic Server when moving from development to production mode anyway.

In the left pane of the Administration Console, expand the Servers node.
Right-click MedRecServer and choose Start/Stop This Server.
In the right pane, click Graceful Shutdown of this Server.
Click Yes.
Once the MedRec server shuts down, open a new command window and change to the MedRecDomain directory:
```
cd C:\bea\user_projects\domains\MedRecDomain
```
Restart the MedRec Server by executing its start script:
```
startWebLogic.cmd
```
Invoke the Administration Console in a browser.
Verify that the three applications (medrecEar, physicianEar, and startupEar) are deployed by expanding, in the left pane, Deployments—>Applications—>AppName, then selecting the Deploy tab in the right pane. If the Module Status for any application is anything other than Active, click Deploy Application.

Step 8: Test the MedRec application using the Oracle database.

Shut down the PointBase database by closing the command window from which you started it. This step ensures that the application is unable to get data from the PointBase database.
In a browser, navigate to http://127.0.0.1:7101/physician, and log in using the username and password supplied in the text fields.
Enter Couples in the Last Name field and click Search. If you see an entry for Fred Couples, the data has come from your Oracle database.

Best Practices

Use JDBC DataSources to separate the details about connecting to a database from an application. This makes it easy to change the database to which an application connects without having to update the application itself.
WebLogic Server uses JDBC drivers to create the physical database connection in a connection pool. When using the Administration Console to create a JDBC connection pool, you specify the driver you want to use. BEA offers the following drivers to connect to an Oracle database:

WebLogic jDriver for Oracle (Type 2, which requires native libraries)
WebLogic jDriver for Oracle XA (Type 2, which requires native libraries)
Oracle Thin driver (Type 4, pure Java)
Oracle Thin XA driver (Type 4, pure Java)

The WebLogic jDrivers use native libraries, but may perform faster than the pure Java Type 4 drivers from Oracle. For additional details about deciding which driver to use in your connection pool, see Introduction to WebLogic JDBC.

The Big Picture

A JDBC DataSource makes it easy to change the database management system to which a WebLogic Server application connects because the DataSource provides a layer of abstraction between the application and the details of a connection to the database.

One DataSource is associated with one JDBC connection pool, which describes the details about how to connect to a database, such as the host and port of the database server, the name of the database, the database user, and so on. The deployment descriptor of the component that needs database access, such as an entity EJB, lists the DataSource that it will use to connect to a database. Therefore, to change the database to which an application connects, you simply create a new connection pool, and use the Administration Console to update the DataSource, changing the connection pool to which it is associated.

For example, AddressEJB is an entity EJB in the medrecEar application. It uses container-managed persistence (CMP) to persist its data to a database. The WebLogic-specific deployment descriptor file that contains CMP information about AddressEJB, weblogic-cmp-rdbms-jar.xml, specifies that the EJB uses the MedRecTxDataSource when connecting to a database, as shown in the following excerpt:

<weblogic-rdbms-jar>

  <weblogic-rdbms-bean>

    <ejb-name>AddressEJB</ejb-name>

    <data-source-name>MedRecTxDataSource</data-source-name>

    ...	

  </weblogic-rdbms-bean>

</weblogic-rdbms-jar>

The deployment descriptor does not include specific details about how to connect to the database, making the application more portable.

Changing the database to which the JMS JDBC store persists data is very similar: you use the Administration Console to change the connection pool to which the store is associated.

You must restart WebLogic Server after making these changes to ensure that all connections to the old database are ended and the application starts connecting to the new database.