Recipe 20.10 Storing Results in a RowSet

Problem

You need to save some results in a JDBC form without maintaining a database connection. Or you want some JDBC results to have JavaBean semantics.

Solution

Use a RowSet in particular, a CachedRowSet .

Discussion

The RowSet interface, a subinterface of ResultSet , was introduced with JDBC 2. Because a RowSet is a ResultSet, you can use any of the ResultSet processing methods previously discussed. But RowSets tend to be more self-contained; you typically do not need to specify a driver, and performing queries is done in a new way. You call setCommand( ) to specify the query and execute( ) to perform the query (this takes the place of creating a Statement and calling its executeQuery( ) method).

There five subinterfaces are listed in Table 20-4. For each of these, a reference implementation is provided in the com.sun.rowset package.

Table 20-4. RowSet subinterfaces

Interface name	Implementation class	Purpose
CachedRowSet	CachedRowSetImpl	Caches results in memory; disconnected Rowset
FilteredRowSet	FilteredRowSetImpl	Implements lightweight querying, using javax.sql.rowset.Predicate
JdbcRowSet	JdbcRowSetImpl	Makes results available as a JavaBean component
JoinRowSet	JoinRowSetImpl	Combine multiple RowSets into one, like an SQL join
WebRowSet	WebRowSetImpl	Convert between XML data and RowSet

But these, like the JDBC-ODBC bridge driver mentioned in Recipe 20.5, are in the com.sun package hierarchy, meaning that they are not fully supported. So although the Javadoc suggests using the new keyword to instantiate them, I prefer to use Class.forName( ) to avoid importing from "unsupported" packages, and so I can compile even if these classes are not available.^[6]

^[6] Now that these classes have been added to JDK 1.5, this precaution is not as important.

The CachedRowSet looks the most interesting and useful. In Example 20-11, a CachedRowSet is created and populated with setCommand( ) and execute( ). Then (hypothetically some time later) the user changes some data. After that is completed, we call acceptChanges( ) , which tells the CachedRowSet to put the changes back into the JDBC database.

Example 20-11. CachedRowSetDemo

import javax.sql.*; /** Demonstrate simple use of the CachedRowSet.  * The RowSet family of interfaces is in JDK1.5, but the Implementation  * classes are (as of Beta 1) still in the unsupported "com.sun" package.  */ public class CachedRowSetDemo {     public static void main(String[] args) throws Exception {         CachedRowSet rs;         // Create the class with class.forName to avoid importing         // from the unsupported com.sun packages.         Class c = Class.forName("com.sun.rowset.CachedRowSetImpl");         rs = (CachedRowSet)c.newInstance( );         rs.setUrl("jdbc:postgresql:tmclub");         rs.setUsername("ian");         rs.setPassword("secret");         rs.setCommand("select * from members where name like ?");         rs.setString(1, "I%");         // This will cause the RowSet to connect, fetch its data, and         // disconnect         rs.execute( );         // Some time later, the client tries to do something.         // Suppose we want to update data:         while (rs.next( )) {             if (rs.getInt("id") == 42) {                 rs.setString("firstname", "Marvin");                 rs.updateRow( );    // Normal JDBC                 // This additional call tells the CachedRowSet to connect                 // to its database and send the updated data back.                 rs.acceptChanges( );             }         }              // If we're all done...         rs.close( );     } }

The WebRowSet has several uses that involve converting database results to or from XML. I have used a WebRowSet in conjunction with JUnit (see Recipe Recipe 1.14) to preload a ResultSet (since a RowSet is a ResultSet) to a known populated state before testing the SQL formatting code in Recipe 20.13. Because it writes data in a known format (public DTD), it could also be used with web services to exchange data across different vendors' systems.

See Also

The documentation for JDBC that accompanies the JDK provides more details on the various RowSet implementations and their usages.