Introduction
Application Overview
Application Design
Sample Application Files
Setting Up the Sample Application
Additional References

Introduction

• Prerequisite



• Technical Overview

Prerequisite

To understand more about the working of the CachedRowSet sample application, and the CachedRowSet feature of JDBC, we expect you to have Oracle relational database knowledge along with an understanding of the basics of JDBC. You can always read the extensive information (in the form of tutorials, articles or HowTo's) available on OTN, the JDBC specification, or download the latest JDBC API to get up to speed with the technology prerequistes for this sample application.

Technical Overview

This sample application illustrates the JDBC feature: CachedRowSet. CachedRowSet is a rowset implementation where the rows are cached and the rowset does not have a live connection to the database (disconnected). It is a serializable object, which provides the standard interface of the javax.sql.RowSet. OracleCachedRowSet is the implementation of CachedRowSet by Oracle and is used in this sample application.

Benefits of the feature :

• Active connection to the database need not be maintained when it is not in use. When a connection is not required, it should be released into the connection pool as soon as possible. The CachedRowSet offers this flexibility; it only needs the connection while it performs a query or update.
• For populating the CachedRowSet, a single select query will suffice. The same rowset can now be used to do any operations on the row. The user is not required to create another rowset to do the insert, delete or update operations.
• Provides ideal data-caching support for smaller Web applications where Enterprise JavaBeans (EJB) are an overkill.

Application Overview 

This sample application uses the table Employees in the HR schema. For more information on understanding and installing Oracle schemas, please click here.

Using this sample application, the user can add/delete an employee or update an employee's details. These changes take place in the CachedRowSet and will be displayed to the user. After the user is satisfied with all the changes, then they can be committed to the database within a single transaction. If, for some reason, the user is not satisfied with the changes, then the changes can be rolled back and the rowset will be restored to its original state. As the name implies, the data will be cached within the CachedRowSet and can be committed or rolled back on user's discretion using the appropriate methods. This enhances the performance of the application as the rowset need not be connected to the database at all times and the connection object will be used only as and when required. This is the reason, the CachedRowSet is sometimes also called as the Disconnected ResultSet.

The user has a choice to run this sample application from a normal browser or a mobile device. This Sample Application demonstrates the use of CachedRowset in a Wireless network also.

Application Design

• Design Notes
  
  
• Code Support

Design Notes 

The class CacheRowSet.java contains all the important code pertaining to the CachedRowSet. It contains methods for connecting to the database and populating the rowset. This class also contains methods to insert, delete or update an employee in the CachedRowSet. In addition to this, the user can commit or reset the changes made using the methods of this class. It will be right to say that this class forms the heart of this sample application. As already mentioned, this sample application supports two different kinds of User Interfaces. Both the Interfaces (JSP and Wireless) interact with the database and the rowset through the methods of this class.

The sample has two different sets of JSPs for the two sets of Interfaces. A CachedRowSet is of special use in the wireless world as only a limited amount of data can be passed and displayed to the User. So, the performance goes down drastically as this requires more number of pages which in turn means more round-trips to the database to retrieve the data. This can be avoided by using a CachedRowSet as it caches the data at the server level minimizing the trips to the database.
The JSP files under the /public_html contains normal HTML code and will be invoked when the request comes from a HTML browser. The JSP files under the /public_html/wireless folder contains code in MXML and will be invoked when the request is from a mobile device. For more information on Oracle Wireless, please click here.

Code Support 

This section provides an overview of the important methods of CachedRowSet which are used within this sample application. A CachedRowSet can be created using the following code snippet

..........

// Initialize the RowSet
OracleCachedRowSet rowset = new OracleCachedRowSet();

// Create and set the Connection URL to the database
rowset.setUrl("jdbc:oracle:thin:@152.23.45.32:1521:orcl");

// Set the Username of the schema to be connected to
rowset.setUsername("HR");

// Set the password of the schema to be connected to
rowset.setPassword("HR");

// Set the select query
rowset.setCommand(new StringBuffer("SELECT employee_id, first_name ")
                       .append("FROM Employees").toString());

// Execute the query in the database and populate the OracleCachedRowSet with the returned rows
rowset.execute();  

.............

Following is the code snippet for inserting a new Employee

..........

// Make the RowSet writable
rowset.setReadOnly(false);

// Move to the Insert row. This statement essentially moves the cursor
// to a blank row which may contain initial column values
rowset.moveToInsertRow();

// Enter the values for this row
rowset.updateString(1, emp.getEmployeeID());

..........

// Insert the row in the RowSet. Calling this statement inserts the row
// after the last valid cursor position
rowset.insertRow();

// Make the RowSet read-only
rowset.setReadOnly(true);

..........

Following is the code for updating the information about an existing employee

..........

// Make the RowSet writable
rowset.setReadOnly(false);

// Point the rowset cursor to the required row
rowset.absolute(Integer.parseInt(rowID));

// Update the values of the selected row
rowset.updateString(2, emp.getFirstName());

...........
// Update the row in the RowSet
rowset.updateRow();
// Set the rowset for read-only
rowset.setReadOnly(true);
............

Code snippet for deleting an existing employee from the CachedRowSet is as follows

..........

// Make the rowset writable
rowset.setReadOnly(false);

// Point the rowset cursor to the required row
rowset.absolute(Integer.parseInt(rowID));
// Delete the Row
rowset.deleteRow();
// Set the rowset for read-only
rowset.setReadOnly(true);
..........

Committing the changes to the database is done as follows

..........

rowset.acceptChanges();

.............

Resetting the CachedRowSet to its original state is done as follows

..........

rowset.restoreOriginal();

..........

You can find complete code and the application configuration files under the CachedRowSet folder. The above presented code snippets are present in the CacheRowSet.java class. Look into the Sample Application Files section for more details.

Sample Application Files

Readme file and Stylesheets

Java Server Page files for the Application

Configuration files for the Application

Java Source files for the Application

Setting Up the Sample Application

Additional References 

• Get disconnected with CachedRowSet
• Oracle CachedRowSet Documentation
• JDBC API
• JDBC WhitePaper
• JDBC Technology Page

We hope you find this README file helpful. Please enter your comments about this sample in the OTN Sample Code Discussion Forum.