Browse Our Free Resources
550 6th Av SW
821A Bloor Street West
409 Granville St
436 York Road
Web Age Solutions Inc.
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.
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:
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:\temp\ProductDB. 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:
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:
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:
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.
In this tutorial, we learned the following items:
Copyright © 2012-2016 Web Age Solutions Inc.