Get disconnected with CachedRowSet

The new J2EE RowSet implementation provides updateable disconnected ResultSets in your JSPs

By Taylor G. Cowan, JavaWorld.com, 02/02/01

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: