Data access applications

We have just deployed an application that did not require database connectivity. Often, applications in the business world require access to a RDBMS to fulfill their business objective. If an application requires the ability to retrieve from, or store information in, a database, then you will need to create a data source which will allow the application to connect and use the database (DB).

Looking at the figure below, we can see the logical flow of the sample data access application that we are going to install. The basic idea of the application is to display a list of tables that exist in a database schema. Since the application requires a database connection, we need to configure WebSphere before we can deploy the application. We will now cover the preparation work before we install our application.

Data sources

Each data source is associated with a JDBC provider that is configured for access to a specific database type. The data source provides connectivity which allows an application to communicate with the database.

Preparing our sample database

Before you create a data source, you need to ensure that the appropriate client database driver software is installed. For our demonstration, we are going to use Oracle Express Edition (Oracle XE) for Linux which is the free version of Oracle. We are using version Oracle XE 10g for Linux and the download size is about 210MB, so it will take time to download. We installed Oracle XE using the default install option for installing an RPM. The administration process is fully documented on Oracle's web site and in the documentation which is installed with the product.

We could have chosen to use many open source/free databases, however their explanations and configurations would detract from the point. We have chosen to use Oracle's free RDBMS called Oracle XE, and JDBC with Oracle XE is quite easy to configure. By following these steps, you will be able to apply the same logic to any of the major vendors' full RDMS products, that is, DB/2, Oracle, SQL Server, and so on. Another reason why we chose Oracle XE is that it is an enterprise-ready DB and is administered by a simple web interface and comes with sample databases.

We need to test that we can connect to our database without WebSphere so that we can evaluate the DB design. To do this, we will need to install Oracle XE. We will now cover the following steps one by one.

1. Download Oracle XE from Oracle's web site using the following URL:http://www.oracle.com/technology/products/database/xe/index.html.
2. Transfer the oracle-xe-10.2.0.1-1.0.i386.rpm file to an appropriate directory on your Linux server using WinSCP (Secure Copy) or your chosen Secure FTP client.
3. Since the XE installer uses X Windows, ensure that you have Xming running. Then install Oracle XE by using the rpm command, as shown here:

   rpm -ivh oracle-xe-10.2.0.1-1.0.i386.rpm

4. Follow the installer steps as prompted:
   • HTTP port = 8080
   • Listener port = 1521
   • SYS & SYSTEM / password = oracle
   • Autostart = y

Oracle XE requires 1024 minimum swap space and requires 1.5 GB of disk space to install.

Ensure that Oracle XE is running. You can now access the web interface via a browser from the local machine; by default, XE will only accept a connection locally. As shown in the following figure, we have a screenshot of using Firefox to connect to OracleXE using the URL http://localhost:8080/apex. The reason we use Firefox on Linux is that this is the most commonly installed default browser on the newer Linux distributions.

When the administration application loads, you will be presented with a login screen as seen in the following screenshot. You can log in using the username SYSTEM and password oracle as set by your installation process.

Oracle XE comes with a pre-created user called HR which is granted ownership to the HR Schema. However, the account is locked by default for security reasons and so we need to unlock the HR user account. To unlock an account, we need to navigate to the Database Users | Manage Users screen, as demonstrated in the following screenshot:

You will notice that the icon for the HR user is locked. You will see a small padlock on the HR icon, as seen in this figure:

Click on the HR user icon and unlock the account as shown in the following figure. You need to reset the password and change Account Status to Unlocked, and then click Alter User to set the new password.

The following figure shows that the HR account is unlocked:

The HR account is now unlocked as seen above. Log out and log back into the administration interface using the HR user to ensure that the account is now unlocked. Another good test to perform to ensure connectivity to Oracle is to use an Oracle admin tool called sqlplus. Sqlplus is a command line tool which database administrators can use to administer Oracle. We are going to use sqlplus to do a simple query to list the tables in the HR schema. To run sqlplus, we need to set up an environment variable called $ORACLE_HOME which is required to run sqlplus. To set $ORACLE_HOME, type the following command in a Linux shell:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

If you have installed Oracle XE in a non-default location, then you may have to use a different path.

To run sqlplus, type the following command:

<oracle_home>/bin/sqlplus

The result will be a login screen as shown below:

You will be prompted for a username. Type the following command:

hr@xe<enter>

For the password, type the following command:

hr<enter>

When you have successfully logged in, you can type the following commands in the SQL prompt:

• SELECT TABLE_NAME FROM user_tables<enter>
• /<enter>

The / command means execute the command buffer. The result will be a list of tables in the HR schema, as shown in the following screenshot:

We have now successfully verified that Oracle works from a command line, and thus it is very likely that WebSphere will also be able to communicate with Oracle. Next, we will cover how to configure WebSphere to communicate with Oracle.

JDBC providers

Deployed applications use JDBC providers to communicate with RDBMS.

• The JDBC provider object provides the actual JDBC driver implementation class for access to a specific database type, that is, Oracle, SQL Server, DB/2, and so on.
• You associate a data source with a JDBC provider. A data source provides the connection to the RDBMS.
• The JDBC provider and the data source provide connectivity to a database.

Creating a JDBC provider

Before creating a JDBC provider, you will need to understand the application's resource requirements, that is, the data sources that the application references. You should know the answer to the following questions:

• Does your application require a data source? Not all applications use a database.
• The security credentials required to connect to the database. Often databases are secured and you will need a username and password to access a secure database.
• Are there any web components (Servlets, JSP, and so on) or EJBs which need to access a database.

Answering these questions will determine the amount of configuration required for your database connectivity configurations.

To create a JDBC provider, log into the administration console and click on the JDBC Provider link in the JDBC category of the Resources section located in the left-hand panel of the administration console as shown below.

We need to choose an appropriate scope from the Scope drop-down pick list. Scope determines how the provider will be seen by applications. We will talk more about scope in the JNDI section. For now, please choose the Cell scope as seen below.

Click New and the new JDBC provider wizard is displayed.

Select the Database type as Oracle, Provider type as Oracle JDBC Driver, Implementation type as Connection pool data source, and Name for the new JDBC provider. We are going to enter MyJDBCDriver as the provider name as seen in the previous screenshot. We also have to choose an Implementation type. There are two implementation types for Oracle JDBC Drivers. The table below explains the two different types.

Click Next to go to the database classpath screen.

As shown in the following screenshot, enter the database class path information for the JDBC provider.

As long as you have installed Oracle XE using the default paths, you will be able to use the following path in the Directory location field: /usr/lib/oracle/xe/oracle/product/10.2.0/server/jdbc/lib.

Click Next to proceed to the next step, where you will be presented with a summary as shown in the following screenshot. Review the JDBC provider information that you have entered and click Finish.

You will now be prompted to save the JDBC provider configuration. Click Save, as shown in the following screenshot. Saving this will persist the configuration to disk the resources to resources.xml.

Before we finish, we need to update the JDBC Provider with the correct JAR file as the default one is not the one that we wish to use as it was assuming a later Oracle driver which we are not using. To change the driver, we must first select the driver that we created earlier called MyJDBCDriver as shown in the following screenshot:

In the screen presented, we are going to change the Classpath field from:

${ORACLE_JDBC_DRIVER_PATH}/ojdbc6.jar

to

${ORACLE_JDBC_DRIVER_PATH}/ojdbc14.jar

Since WAS 7.0 is the latest version of WebSphere, the wizard already knows about the new version of the oracle 11g JDBC Driver. We are connecting to Oracle XE 10g and the driver for this is ojdbc14.jar.
The classpath file can contain a list of paths or JAR file names which together form the location for the resource provider classes. Class path entries are separated by using the ENTER key and must not contain path separator characters (such as ; or :). Class paths can contain variable (symbolic) names that can be substituted using a variable map. Check your driver installation notes for specific JAR file names that are required.

Click Apply and save the configuration.