WLSKB004 Accessing Database in WebLogic Workshop Using the RowSet Control

Bibhas Bhattacharya, Web Age Solutions Inc.

Introduction

WebLogic Workshop provides two different controls to facilitate database access.

  1. RowSet control. This uses the java.sql.RowSet API to perform select/inert/update/delete. RowSet is a recent addition to the JDBC API.
  2. Databse control. This uses traditional JDBC programming. That is, insert/update/delete operations are performed by executing SQL statements.

The RowSet approach has a few advantages.

  1. Unlike ResultSet, a RowSet is disconnected from the database. When you perform the next() operation with a ResultSet, a cursor is actually moved in the database server. For a RowSet, the entire data is fetched in the client side and iteration happens without any corresponding open cursor in the server. This makes it possible for a controller (Page Flow or Struts action) to pass a RowSet object to the view. That is, a RowSet can be used as a value object or a collection of value objects. Important: Not surprisingly, the <netui-data:repeater> tag can iterate through a RowSet.
  2. Insert/update/delete is performed using Java method calls instead of SQL. One needs to call methods such as deleteRow(), updateRow() or insertRow() to record the operation in the RowSet. Finally, one needs to call the acceptChanges() method to have the RowSet object actually perform the changes in the database server. Working with these methods can be easier than issuing INSERT, UPDATE or DELETE SQL statements.

In this article we will develop an application that implements the full Create Retrieve Update and Delete (CRUD) cycle.

Before You Get Started

This is an advanced tutorial. It assumes that you know how to work with Page Flow. If you are new to WebLogic Workshop, please complete the following tutorials before starting with this tutorial.

  1. WLSKB001 - Developing a Page Flow in WebLogic Workshop.
  2. WLSKB002 - Processing User Input from a Page Flow in WebLogic Workshop.

Create a new application called BasicApp unless it is already created from a previous tutorial.

The Database Schema

WebLogic Workshop comes with a sample PointBase database called WORKSHOP. A data source called cgDataSource is also defined in the WebLogic Server for that database. We will use that database (as opposed to setting up a new one) to avoid getting distracted by the WebLogic and PointBase administration activities.

In this step, we will get familiar with the ITEMS table in the WORKSHOP database. This table contains a lits of products. Our application will manage the products in this table.

First, launch WebLogic Workshop. Start the WebLogic Server (Tools->WebLogic Server->Start WebLogic Server). This will also start the PointBase database server. The database server is configured to listen on port 9093.

Open a command window. Change directory to c:\bea\weblogic81\common\eval\pointbase\tools. Run the startPointBaseConsole.cmd command.

In the Connect To Database dialog, enter the following values.

URL: jdbc:pointbase:server://localhost:9093/workshop
User: weblogic
Password: weblogic

Click on OK.

Expand SCHEMAS->WEBLOGIC->TABLES. Scroll down and locate the ITEMS table.

Expand ITEMS->COLUMNS. Expand each column and note the data type.

Right click on ITEMS and select SELECT * FROM "WEBLOGIC"."ITEMS". Make sure that you can see the sample data.

Create the Page Flow

In WebLogic Workshop, create a new application called BasicApp (unless it's already created from a previous tutorial).

Right click on BasicAppWeb and select New->Page Flow. Enter ItemManagement as the name of the Page Flow. Click on Next. Click on Create.

Add the RowSet Control

In the Application view, right click on the ItemManagement folder and select New->Other File Types. Select Business Logic and then RowSet Control.

Click on Create.

Set the name of the control to ItemDB. Make sure that the cgDataSource data source is selected. Click on Next.

Enter the following values.

Select The Methods To Create: Choose the Query and update a database table radio box.
Schema: WEBLOGIC
Table: ITEMS

Click on Next. Accept the default. Click on Next again.

Accept the default again. Click on Create.

System will create a control extension class called ItemDB.jcx.

Now, we must add the control to our Page Flow. Double click on ItemManagementController.jpf to open the Page Flow in the diagram editor. Click on the Action View tab at the bottom of the editor. Drag and drop ItemDB.jcx on to the editor.

Display Item List

In this step, we will display all the products in the ITEMS table. Click on the Flow View tab at the bottom of the Page Flow editor. Right click on the Page Flow diagram and select New Action.

Enter listItems as the name of the new action. Select the No Form Bean radio button. Click on OK.

Right click on the diagram and select New Page. Change the name of the page to list_items.jsp.

Draw an arrow from the listItems action to list_items.jsp.

Right click on index.jsp and select Delete to delete it. Draw an arrow from the begin action to the listItems action.

Click on the Source View tab at the bottom of the diagram editor. Add the following import statements to the Page Flow class.

import javax.sql.RowSet;
import com.bea.control.DatabaseFilter;

Scroll down and locate the control variable.

/**
 * @common:control
 */
private ItemManagement.ItemDB itemDB;

Note: ItemDB is an interface. An implementation class is generated behind the scene. System also instantiates an object of that class and initializes the itemDB member variable.

Scroll down further and locate the listItems() method. Change the method as follows.

protected Forward listItems() throws Exception
{
    RowSet rs = itemDB.getAllItems(new DatabaseFilter());
 
    getRequest().setAttribute("itemList", rs);

    return new Forward("success");
}

Here, we obtain all rows in the ITEMS table as a RowSet object. We save the RowSet object in the request scope using the name itemList.

Save changes (Control+S).

Now, we will need to work on the list_items.jsp page. Open the JSP file in the editor. Set the contents of the <body> tag to as follows.

<body>
 <h2>Item List</h2>
 <p>
 <netui:anchor href="add_item_form.jsp">Add Item</netui:anchor>
 </p>
 <netui-data:repeater dataSource="{request.itemList}">

 <netui-data:repeaterHeader>
 <table bgcolor="gray" cellspacing="1">
 <tr>
 <td bgcolor="white"><b>Number</b></td>
 <td bgcolor="white"><b>Name</b></td>
 <td bgcolor="white"><b>Price</b></td>
 <td bgcolor="white"><b>Manage</b></td>
 </tr>
 </netui-data:repeaterHeader>

 <netui-data:repeaterItem>
 <tr>
 <td bgcolor="white"><netui:label value="{container.item.ITEMNUMBER}"/></td>
 <td bgcolor="white"><netui:label value="{container.item.ITEMNAME}"/></td>
 <td bgcolor="white"><netui:label value="{container.item.PRICE}"/></td>
 <td bgcolor="white">Edit Delete
 </td>
 </tr>
 </netui-data:repeaterItem>

 <netui-data:repeaterFooter>
 </table>
 </netui-data:repeaterFooter>

 </netui-data:repeater> 

 </body>

Here, we use the <netui-data:repeater> tag to show the items in a table. Note, how each column value is shown (as in {container.item.ITEMNUMBER}). Save changes (Control+S).

Test

Open a web browser and enter the URL: http://localhost:7001/BasicAppWeb/ItemManagement/begin.do.

Create a Form Bean

Before we can implement the edit and add feature, we must create a form bean class. Open the Page Flow diagram editor form the ItemManagementController Page Flow. Create a new form bean called ItemFormBean with the following properties.

Click on OK. Save changes.

Add Edit Feature

Add a new action to the Page Flow called displayEditForm. This action does not require any form bean. Add a page called edit_item_form.jsp. Draw an arrow from displayEditForm to edit_item_form.jsp.

Add another action called editItem with the ItemFormBean associated with it.

Draw an arrow from the editItem action to listItems.

Set the code for the displayEditForm action to as follows.

protected Forward displayEditForm() throws Exception
{
    RowSet rs = itemDB.detailsItems(new Integer(getRequest().getParameter("itemNumber")));
 
    if (rs.next()) {
       ItemFormBean ib = new ItemFormBean();
 
       ib.setItemName(rs.getString("itemName"));
       ib.setItemNumber(rs.getInt("itemNumber"));
       ib.setQuantityAvailable(rs.getInt("quantityAvailable"));
       ib.setPrice(rs.getDouble("price"));
 
       getRequest().setAttribute("itemFormBean", ib);
    }
    return new Forward("success");
}

Note: It is important that we save the form bean in the request scope using a form bean's name.

Set the contents of the <body> tag of the edit_item_form.jsp to as follows.

<body>
     
<netui:form action="editItem">
 <p>Edit item number: <netui:label value="{actionForm.itemNumber}"/></p>

 <netui:hidden dataSource="{actionForm.itemNumber}"/>
 Name: <netui:textBox dataSource="{actionForm.itemName}"/><br>
 Price: <netui:textBox dataSource="{actionForm.price}"/><br>
 Quantity Available: <netui:textBox dataSource="{actionForm.quantityAvailable}"/><br>
 
 <netui:button value="Update" type="submit"/>
 </netui:form>
</body>

Change the implementation of the editItem action method as follows.

protected Forward editItem(ItemFormBean form) throws Exception
{
    RowSet rs = itemDB.detailsItems(new Integer(form.getItemNumber()));
 
    if (rs.next()) { 
        rs.updateString("itemName", form.getItemName());
        rs.updateInt("quantityAvailable", form.getQuantityAvailable());
        rs.updateDouble("price", form.getPrice());
 
        rs.updateRow();
 
        itemDB.updateItems(rs);
    }
 
    return new Forward("success");
}

Save all files.

Add the Create Feature

Add a new action called addItem with the ItemFormBean associated with it. Draw an arrow from the action to the listItems action. Set the implementation code for the addItem action to as follows.

protected Forward addItem(ItemFormBean form) throws Exception
{
    RowSet rs = itemDB.detailsItemsTemplate();
 
    rs.updateString("itemName", form.getItemName());
    rs.updateInt("quantityAvailable", form.getQuantityAvailable());
    rs.updateDouble("price", form.getPrice());
 
    rs.insertRow();
 
    itemDB.insertItems(rs);
    return new Forward("success");
}

Create a JSP page called add_item_form.jsp. Set the contents of the <body> tag as follows.

<body>
     
 <p>Add Item</p>
 
 <netui:form action="addItem">
 Name: <netui:textBox dataSource="{actionForm.itemName}"/><br>
 Price: <netui:textBox dataSource="{actionForm.price}"/><br>
 Quantity Available: <netui:textBox dataSource="{actionForm.quantityAvailable}"/><br>
 
 <netui:button value="Update" type="submit"/>
 </netui:form>
</body>

Save changes.

Add the Delete Feature

Add a new action called deleteItem. It does not require a form bean. Draw an arrow from the action to the listItems action.

Set the code for the deleteItem action as follows.

protected Forward deleteItem() throws Exception
{
    RowSet rs = itemDB.detailsItems(new Integer(getRequest().getParameter("itemNumber")));
 
    if (rs.next()) {
        rs.deleteRow();
 
        itemDB.deleteItems(rs);
    }
    return new Forward("success");
}

Create Links

Open list_items.jsp. Locate the line.

 <td bgcolor="white">Edit Delete
 </td>

Change it to:

<td bgcolor="white">
 <netui:anchor action="displayEditForm">
 Edit
 <netui:parameter name="itemNumber" value="{container.item.ITEMNUMBER}"/>
 </netui:anchor> 
 <netui:anchor action="deleteItem">
   Delete
 <netui:parameter name="itemNumber" value="{container.item.ITEMNUMBER}"/>
 </netui:anchor> 
</td>

Save changes.

Test

Open a browser. Enter the URL: http://localhost:7001/BasicAppWeb/ItemManagement/begin.do.

Make sure that you can add, edit and delete items.

Feedback

Your e-mail:


Rate this article:
Very useful Somewhat useful Not bad Needs many corrections

Comments: