WORKING WITH DATABASES IN RAD7 AND WEBSPHERE

INTRODUCTION

Our clients tell us that their developers find it difficult to set up data source in RAD. WebSphere’s extended deployment descriptor has simplified the process but the steps are not very well documented. In this article, we will explore this technique.

RAD7 and Websphere 6.1 ships with the Derby database. It no longer has the friendly administration GUI of Cloudscape. In this tutorial, we will perform basic database administration using the RAD7 datbase tools.

ADDING A DATABASE CONNECTION TO RAD7

Before we can work with any database, we need to define a new connection object in RAD7.

Launch RAD7.

Switch to the Database perspective (Window->Open Perspective->Other).

In the Database Explorer view, right click on Connections and choose New Connection.

Carefully, enter the following configuration values:

Uncheck Use default naming convention.
Enter Product Database as the connection name.
Choose Derby 10.1 as the database manager. If you are using Oracle or DB2, you will need to select the appropriate database manager. This will automatically change the screen on the right hand side.
Choose Derby Embedded JDBC Driver as the JDBC driver. This will automatically set the driver class name and configure the connection URL. RAD7 comes preloaded with such preset values for most major database vendors.
As the database location, enter, C:tempProductDB. System will create a new Derby database in that folder. If the folder doesn’t exist, system will create it.
Check Create the database if required.
In the Class location text box, enter the location of the JDBC driver JAR file. For Derby the file is derby.jar. This file comes with the WebSphere 6.1 installation. In the development machine, it is located in <RAD7>runtimesbase_v61derbylibderby.jar. For example: C:Program FilesIBMSDP70runtimesbase_v61derbylibderby.jar.
Finally enter a user ID and password. For Derby, this can be anything. For DB2 or Oracle, you will need to enter a proper user ID and password. For this tutorial, we will enter wasadmin as the user ID and password.

Click on the Test Connection button. Make sure that you see the Connection to Derby is successful message.

Click on Finish to define the connection in RAD7.

This will add a new connection object in RAD7. It will also create a new Derby database in C:tempProductDB. Note: if you are connecting to DB2 or Oracle, the database must be already created. RAD7 will not create the database.

Expand Connections to view the newly added connection.

LOAD DATABASE SCHEMA

Now, we will load in the application schema into the database. We will do this by executing a batch of SQL commands from a script file. In RAD7, you can store the script files in a project. Alternatively, you can open an external SQL script file from anywhere in the hard drive (File->Open File) . The file name extension must be *.sql. Once a script is open in the editor, you will be able to execute the SQL commands against any one of the database connections defined in RAD7.

Download the schem SQL file products.sql and save it in your hard drive.

In RAD7, choose File->Open File and open the SQL file saved in the previous step.

Before we can execute the SQL commands, we must associate the script file with a specific database connection. Right click on the editor and choose Use Database Connection.

Choose the Product Database connection. Click on Finish. Note: this association is not permanently stored anywhere. Every time you open the script file, you will need to choose a connection.

By default, the SQL command terminator is “;”. Our sample script file follows this convention. If you are using a different character, you can right click on the editor and choose Set Statement Terminator and enter the termination character.

Now, we are all set to execute the commands. Right click on the script editor and choose Run SQL.

System will load the SQL commands and show the result of each command in the Data Output view.

Make sure that all commands are successfully completed.

Right click on the list of messages and select Delete All.

WORKING WITH DATA

In this step, we will learn how to view and edit data in the database.

First, we will run SQL select statements to view the data. This comes handy during development. You can first try out a complex SQL statement before actually using it in the code.

We will enter the SQL commands in the SQL script file that is already open. You can save the file and use it again later.

At the end of the SQL script file, enter this command:

select * from product;

Now, we need to execute just that command and not the other commands that were already in that file. To do that, select just the text for the new SQL command.

Right click on it and select Run SQL.

System will show the result in the Data Output view.

If you just want to view the contents of a table, you can run a select command as we have done above. Alternatively, you use the database tool. In the Database Explorer view, expand the Product Database entry, until you see the CATEGORY, PRODUCT and CATEGORY_PRO tables. They were created in the WASADMIN schema, since the user ID associated with the connection is wasadmin.

Right click on the PRODUCT table and select Data->Sample Contents. Once again, the Data Output view will show the results.

To edit the data, right click on PRODUCT and select Data->Edit. System will open the table’s data in a spreadsheet like interface. You can update, delete or insert new rows.

Change the price of the product to 124.99. Hit Control+S to save or apply the changes to the database.

Close all open editors.

DISCONNECT FROM THE DATABSE

In the embedded mode, only one connection can be opened to a Derby database at a time. For multiple connections, you will need to run the Derby server.

Before going forward, you must disconnect from the database. In the Database Explorer right click on Product Database and select Disconnect.

ACCESS DATABASE FROM AN APPLICATION

To access a database, we need to define a data source. RAD7 and WebSphere v6.1 simplifies this by means of extended deployment descriptor. In this approach, you can define the data source using RAD7 as a part of the application’s deployment descriptor. When the application is deployed to the server, system automatically defines a data source.

To speed up the tutorial, an application is already created for you. Download the ProductApp.zip file. It is a project interchange file. Import all the projects in it within RAD7.

System will create two new projects as shown above.

A Servlet called DsTester is already created in the web project. Locate its class and open it as shown below.

Study the doGet method. It opens a connection from a data source with the JNDI name “jdbc/ProductDB”. We have to define a data source by that JNDI name. First, let’s see how the Servlet behaves before the data source is defined.

Start the WebSphere 6.1 server. Deploy the ProductApp project to the server.

Open a browser. Enter the URL:
http://localhost:9080/ProductWeb/DsTester

A NameNotFoundException error will be reported in the browser as shown below.

Now, we will define the data source.

DEFINE THE DATA SOURCE

In the Project Explorer view, expand the enterprise application project – ProductApp. Double click on the Deployment Descriptor to open it in the editor.

At the bottom of the deployment descriptor editor, click on the Deployment tab. The extended deployment configuration is specified here.

First, we need to specify a JDBC provider for Derby. A provider configuration stores the JAR file location of the driver. Once a provider is defined, we can create data sources for it.

By default, RAD7 adds a provider for Derby. We will learn to add one from scratch. So, select the default provider and click on the Remove button to delete it.

Click on the Add button to add a new provider.

Choose Derby as the Database type. Then, as the JDBC provider type, select Derby JDBC Provider (XA).

Click on Next.

Enter Derby XA Provider as the name of the provider. Make the following observations:

System has automatically selected the correct driver class name. The same will apply, if you were creating a provider for Oracle or DB2.
System has also set up the class path automatically. Here, system is using a variable ${DERBY_JDBC_DRIVER_PATH} to indicate the folder name where derby.jar file located. For other types of databases, system will use variables such as ${ORACLE_JDBC_DRIVER_PATH} or ${DB2UNIVERSAL_JDBC_DRIVER_PATH }. For Derby, the value of the variable is already configured at the time of WebSphere installation. For other databases, you will need to manually enter the value of these variables. We will learn how to do that later.

Click on Finish to define the provider.

Now, we will add a data source for the Derby provider. Select the newly added provider.

Click on the Add button next to the list of data sources as shown above.

Choose Derby JDBC Provider (XA) and click on Next.

Enter these values:

Name: Product Data Source.
JNDI Name: jdbc/ProductDB. Note: this is the JNDI name used by the Servlet to lookup the data source.

Click on Next.

Select the databaseName property. Set its value to C:/temp/ProductDB. If you are using DB2 or Oracle, a different set of properties need to be set.

Click on Finish to define the data source.

Now, we will configure the user that will open the connection to the database.

In the Authentication section of the deployment descriptor, click on Add.

Enter a name for the user alias (Derby user). Then enter the user ID and password. Click on OK.

Now, select the data source you have just created (Product Data Source). Then, click on Edit.

Set the component managed and container managed autnetication alias to be Derby user. Component managed authentication alias is used when you open a connection from a Servlet or a session EJB. The container managed alias is used from a CMP entity bean.

Click on Finish.

Save the deployment descriptor. Wait for a few seconds for RAD7 to re-deploy the application.

TEST THE APPLICATION

Back in the browser, refresh the page. Now, the browser will show:

This proves that we have successfully defined the data source.

ENTERING WEBSPHERE VARIABLES

If you have defined a JDBC provider for DB2 or Oracle, you will need to enter the values for various variables as we mentioned before. Now, we will learn how to do that.

In RAD7, open the deployment descriptor of the enterprise application unless its already open. Go to the Deployment tab.

In the Substituition Variables section, click on Add.

Enter the name of the variable that is appropriate for your database type. Then enter the directory where the JAR files are located as the value of the variable.

Click on OK and then save the changes.

SUMMARY

In this tutorial, we learned the following items:

How to perform basic database administration and queries using RAD7. Derby doesn’t have any GUI based administration tool. RAD7 tools come very handy. For the other types of database, you can use RAD7 to run queries and edit data using an easy to use spreadsheet like database.
How to define a data source and use it from a J2EE application. We used the extended deployment descriptor feature to do this. We could do all the work using RAD7.

Working With Databases In Rad7 And Websphere 6.1 Tutorial