Get disconnected with CachedRowSet

In spite of the ever-growing access to wireless and physical networks, it is impossible to guarantee uninterrupted database connectivity at all times. Even when reliable database connections are available, as with any other limited resource, they must be used wisely. Caching the updates will solve the problem, a solution that until recently required Java developers to design and implement the caching layer themselves.

In this article I will introduce you to a new JDBC 2.0-related class that can simplify database access code, reduce JDBC connection usage, and provide scrolling to otherwise unscrollable JDBC 1.0 result sets. The class is available from Sun as an early access release and consists of three implementations of the JDBC RowSet interface, one of which I will present in this article. That class, CachedRowSet, will be used here as the data model for a simple JSP application. You will find that it provides ideal data-caching support for smaller Web applications where Enterprise JavaBeans (EJB) are overkill.

You could consider the CachedRowSet as a “disconnected” ResultSet. It is an early implementation of the javax.sql.RowSet, and because javax.sql.RowSet extends the java.sql.ResultSet interface, you are probably already familiar with many of its methods. The RowSet interface supports the JDBC API from the JavaBeans component model perspective. It provides all the methods and features of a standard JDBC 2.0 ResultSet, but doesn’t require the continuous use of a database connection. Just like any other JavaBean, implementations of javax.sql.RowSet may be serialized. This allows for ResultSets to be serialized, sent to remote clients, updated, and then sent back to the server.

The release of the CachedRowSet is timely, since many Web developers are now familiar with updateable result sets such as Microsoft’s ActiveX Data Objects (ADO). Many entry/update forms require the ability to scroll backward and forward through a set of records. Ideally the user will perform updates to the records in their cached state and then apply all the changes at once using a Save command. Though the JDBC 2.0 ResultSet interface supports this type of scrolling, because it requires each session to maintain an open connection to the database while the user browses through records, you probably would not want to use it. To maximize database resources, you should utilize a connection only when absolutely necessary. When a connection is not required, you should release it into the connection pool as soon as possible. The CachedRowSet offers this flexibility; it only needs a connection while it performs a query or update.

I fully expect that third-party implementations of javax.sql.RowSet will appear shortly, but in the meantime, the CachedRowSet is available to developers who want to familiarize themselves with the new Java 2 Platform, Enterprise Edition (J2EE) javax.sql.RowSet interface.

Currently you can download the CachedRowSet implementation from the Java Developer Connection as an early access release. After downloading and unzipping the installation files, add the file "rowset.jar" to your classpath. The CachedRowSet object is located in package sun.jdbc.rowset.

Create a CachedRowSet

The CachedRowSet is, in fact, a JavaBean. It supports properties that allow it to connect to a database and retrieve data on its own. The table below describes some of the properties necessary to initialize a CachedRowSet without a pre-existing database connection:

Because it is a JavaBean, you can simply use the default constructor when creating a new instance of the CachedRowSet object:

CachedRowSet crs = new CachedRowSet();

Once you have created the instance, call its property set methods to initialize the bean. The fact that the CachedRowSet is a bean also simplifies its use within a JSP. To create an instance of CachedRowSet on any JSP, simply use the standard JSP useBean tag. It is the most common and convenient method of exposing JavaBeans to a JSP. The useBean tag will create a new instance, which should be placed into the session as a session value and exposed to the JSP as a scripting variable. You can initialize a CachedRowSet from within a useBean tag through various approaches. Some approaches depend on whether your application server has a connection pool available. Others only require a JDBC 1.0 driver. Listing 1 illustrates the most explicit way to create and initialize a CachedRowSet within a JSP page:

Listing 1: Explicit CachedRowSet initialization

<jsp:useBean id="Contacts" 
             class="sun.jdbc.rowset.CachedRowSet" 
             scope="session">
<%
  // load database driver
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  // initialize our CachedRowSet bean
  Contacts.setUsername("dbuser");         // example userid 
  Contacts.setPassword("dbpassword");     // example password
  Contacts.setUrl("jdbc:odbc:ContactDB"); // example DSN
  Contacts.setCommand("SELECT name, telephone from Contacts");
  Contacts.execute();
%>
</jsp:useBean>

When created in this manner, the CachedRowSet will obtain its own database connections as needed. The Java code scriptlet between the useBean begin and end tags will only execute once, when the useBean first places the bean in session scope. Consecutive page accesses will skip over the useBean tag and its contents since our CachedRowSet is in the session.

For JSP containers with J2EE connection-pooling support, you may use the dataSourceName property instead of setting the username, password, and URL properties. If you set dataSourceName, the CachedRowSet will attempt to obtain a connection from JNDI rather than create its own. This style requires less code since the application server’s connection pool has already established the connection.

Listing 2: Initialization from a J2EE connection pool

<%
  // initialize our CachedRowSet bean using dataSourceName
  // property
  Contacts.setDataSourceName("Databases/ContactsDB/Datasource");
  Contacts.setCommand("SELECT name, telephone from Contacts");
  Contacts.execute();
%>

Since the amount of users accessing a Website can reach a high volume, creating a CachedRowSet from a J2EE connection pool is preferable to creating your own connections. The dataSourceName property tells the CachedRowSet where to find its connection in the JNDI namespace. When the execute() method invokes, CachedRowSet retrieves an instance of javax.sql.DataSource from JNDI. The DataSource object provides a JDBC connection to the RowSet. Instead of relying on the CachedRowSet to obtain a connection, you may supply an existing connection as an argument to the execute() method. The example in Listing 3 obtains the connection from a J2EE connection pool as in Listing 2, however, this time the JNDI lookup is completed explicitly:

Listing 3: Initialization by passing an existing connection

<%
  // get connection from pool
  InitialContext ctx = new InitialContext(); 
  javax.sql.DataSource ds =
    (javax.sql.DataSource)ctx.lookup("Databases/ContactsDB/DataSource");
  java.sql.Connection con = ds.getConnection();
  Contacts.setCommand("SELECT name, telephone from Contacts");
  // supply the connection to the RowSet
  Contacts.execute(con);
%>

Connection pool JNDI lookup names vary between application servers. Consult your application server documentation for the proper JNDI lookup patterns regarding connection pools.

Display CachedRowSet properties in a JSP

After you have initialized the CachedRowSet, you may use it as you would any other java.sql.ResultSet object. As noted earlier, the CachedRowSet is just another implementation of the java.sql.ResultSet interface. You can access CachedRowSet properties by positioning the cursor on the target row and then using the getString() method — provided the underlying database type is varchar. Some get methods exist for each JDBC data type. If you do not know the data type, use getObject(). Specify the column by its index or its original ResultSet name:

    Contacts.getString(1);  

If you assume that your page includes the useBean tag as shown in Listing 1, you can access the CachedRowSet anywhere on your page using its scripting ID. Below, you will find an example of how to display a CachedRowSet column using an HTML form field:

  <input type="text"
         name="ContactName"
         value="<%=Contacts.getString(1)%>"/>

In addition to obtaining its properties, you can display other information about the CachedRowSet, such as its current row number:

  Record <%=Contacts.getRow()%> of <%=Contacts.size()%>

Navigation is also important for displaying CachedRowSet properties. The CachedRowSet supports all navigation commands declared in the ResultSet interface. That includes the new JDBC 2.0 methods, such as previous(), first(), and last().

The standard JDBC version currently in use is JDBC 1.0; Sun hasn’t updated its ODBC driver to the 2.0 level yet. Because the client caches its data, CachedRowSet provides scrollability — forward and backward cursor movement — for JDBC 1.0 drivers. This nice feature relieves developers from having to write their own ResultSet cache objects for record-browsing applications. To navigate from your JSP, simply “listen” for command parameters in the request and respond as needed:

  // process navigation commands
  if ( req.getParameter("next") != null ) {
    if (! Contacts.next() )
      Contacts.last();
  } else if ( req.getParameter("prev") != null) {
    if (! Contacts.previous())
      Contacts.first();
  }

Each navigation method returns true or false, depending on its success. If it fails, the cursor must return to a valid position. Simple submit buttons — with names that reflect their purpose, such as "next" — send the navigation parameters:

<input type="submit" name="next" value=">" />

Perform updates to a CachedRowSet

My favorite feature of the CachedRowSet is that it performs its own updates to the database. Anyone familiar with ASP development and the Microsoft ADO will appreciate that as well. CachedRowSet greatly reduces the number of SQL statements developers need to write and helps combine updates within one operation. Combining updates is a key benefit for Web applications, which have limited database connections and can become bottlenecks. For most CachedRowSet applications, you only need to provide the initial query SQL statement. At a minimum, you must call three methods on a CachedRowSet to complete an update, and you must invoke them in a specific order. First, assuming that the cursor is located in the correct position, call one of ResultSet‘s update methods. Based on the column types in the example database, the update will look like this:

   Contacts.updateString(1, "new value"); 

You may repeat the code above for each column in the RowSet if necessary.

Second, before you move the cursor position, the RowSet must know that you wish to commit the changes to the current row:

   Contacts.updateRow();

The code above doesn’t send the changes to the database. It only commits the changes to the cached portion of the RowSet, an important feature of the CachedRowSet. As users record changes, they won’t tie up a database connection. Users need the connection only when they are ready to commit to the database all changes made to the RowSet.

The third and last step is to call acceptChanges(), which submits all changes in a batch to the database. Again, assuming you created your CachedRowSet according to Listing 1, the following code will commit all updates to the database that were made since your last call:

   Contacts.acceptChanges();

If you initialized and created CachedRowSet as in Listing 3, you must pass in a connection object:

   Contacts.acceptChanges(connection);

Within a JSP, that action may be associated with a request parameter, which allows the user to decide when to commit the updates:

  if ( req.getParameter("save") != null ) {
    Contacts.acceptChanges();
  }

The CachedRowSet will also perform inserts and deletes as well. Though inserting into a CachedRowSet is quite simple, it is also counterintuitive. Before inserting a row you must place the cursor in a special position called the “insert row.”

The following code demonstrates how to insert a populated row into a CachedRowSet:

     rowSet.moveToInsertRow(); // move to construct an insert row
     rowSet.updateString(1, "New Contact");    // initialize name
     rowSet.updateString(2, "(111) 111-1111"); // initialize phone
     rowSet.insertRow(); // insert the row
     rowSet.moveToCurrentRow(); // move back to previous cursor position

moveToInsertRow() essentially moves the cursor to a blank row that may contain initial column values. Calling insertRow() inserts the row after the last valid cursor position. Then, call moveToCurrentRow() to reposition the cursor before calling any other navigation commands. Finally, you must call acceptChanges() for RowSet to update the database with the new row. Applying this to your JSP will require a few modifications. Most record entry/edit forms follow these steps when inserting rows:

1. Insert a new row after the current cursor position
2. Initialize all fields to their empty state
3. Display the newly inserted row in the form, ready for the user to edit and save

To add record-insert functionality, your JSP will need to listen for a new request parameter, "insert". The insert handler code for the page:

  } else if ( req.getParameter("insert") != null) {
    Contacts.moveToInsertRow();
    Contacts.updateString(1, "");
    Contacts.updateString(2, "");
    Contacts.insertRow();
    Contacts.moveToCurrentRow();
    Contacts.next();
  }

Other uses of the CachedRowSet

The need for a cached ResultSet extends beyond JSP development. CachedRowSet fits almost any situation that requires tabular data to be serialized and sent to a client. Consider wireless computer devices. Though users may disconnect from network data sources periodically, they still must add and maintain data. You can also use the CachedRowSet as a way to retrieve and update tabular data from an EJB session bean.

Keep in mind that the CachedRowSet stores completely in memory. If your application works with extremely large data sets, a CachedRowSet may not offer the best choice.

Listing 4 shows the completed JSP entry/edit page code using CachedRowSet as its data model. For clarity I’ve placed the data model initialization code within the JSP page. The code appears within the body of the useBean tag. For larger projects I recommend following MVC (Model-View-Controller) guidelines by placing data model initialization code in a controller servlet.

Listing 4: JSP example update/entry form

   Contacts.acceptChanges();
  } else if ( req.getParameter("insert") != null) {
    Contacts.moveToInsertRow();
    Contacts.updateString(1, "");
    Contacts.updateString(2, "");
    Contacts.insertRow();
    Contacts.moveToCurrentRow();
    Contacts.next();
  } else if ( req.getParameter("delete") != null) {
    Contacts.deleteRow();
    if (!Contacts.next()) Contacts.last();
  }
%>




Contacts

The figure below shows how the above JSP will appear in a browser.

Conclusion

JSP applications require a data-cache middle layer to improve connection resource sharing. Because EJB is overkill for most small to medium-sized applications, CachedRowSet fills in nicely by providing uniform access to JDBC data sources without tying up database connections. CachedRowSet also simplifies data-access code within a typical entry/edit JSP application. Remember that the example page only required one SQL statement to perform updates, inserts, and deletes on a database. In addition, CachedRowSet can disconnect and reconnect to its primary data source, its most useful functionality.

Source: www.infoworld.com