Database connections should be respected as a precious commodity, to be used wisely by applications. Especially in high traffic Web sites, it is important that clients use database connections from a pool of database connections managed by an application server (for example, IBM WebSphere® Application Server) and that they release the database connection after their necessary transactions have occurred.
In many cases, the session of transactions can take a good while, holding the database connection until complete. In the past, Java programmers using the JDBC API have not had a caching solution available to them which would allow users to cache result sets locally, release the connection, manipulate the result set data, and then synchronize with the database at a later point in time. In this article, I?ll cover an implementation of the javax.sql.RowSet interface which allows us to do just that.
The javax.sql.RowSet interface was introduced to the Java language in J2SE Version 1.4. With the shipping of the Java 2 Standard Edition version 1.5, we will see enhancements to this interface and implementations of the interface bundled with the language. The RowSet interface is the result of JSR 114, which outlined an initiative to "'disconnect' tabular data from its source?" thereby increasing ?the scalabilty of applications and the flexibility of the programming model." I used a beta version of Tiger (Java 1.5) to develop against. If you are using a previous version of Java and would like to try out the reference implementation of the RowSet, you can download the reference implementation from Sun Microsystems. For our example, we will be playing with a CachedRowSet implementation.
The CachedRowSetImpl class showcased in this article is an implementation of javax.sql.Rowset interface. javax.sql.Rowset extends the java.sql.ResultSet interface. Thus, if you are familiar with the javax.sql.ResultSet interface, which I am assuming you are, then you will recognize a number of methods of the CachedRowSetImpl offering. The java.sql.RowSet interface provides all the methods which you saw in the standard JDBC 2.0 ResultSet; the additional value add is that we don?t require continuous usage of a database connection. It is the equivalent of being able to go to the store, pick up a catalog, and come back to the store with our orders filled out.
The CachedRowSet is a JavaBean. You can populate a CachedRowSet using either an existing ResultSet object or by specifying connection information and an SQL query. Let?s take the latter approach. But first, let?s create a small DB2 database for our sandbox.
db2 => create database cachedex |
I am assuming that you have a user named db2admin on your machine with a password of db2admin:
db2=> connect to cachedex user db2admin using db2admin |
Create a table named cachetbl:
db2=> create table cachetbl (id int primary key not null, firstname varchar(40) not null, lastname varchar(40) not null) |
Go ahead and populate the table with the following rows:
insert into cachetbl values (12345,?Kulvir?,?Bhogal?) insert into cachetbl values (23456,?Meet?,?Feona?) insert into cachetbl values (34567,?Bicky?,?Singh?) |
The sample code I have provided makes a direct connection to DB2. If you are obtaining your database connections through a pool managed by an application server (such as IBM WebSphere Application Server), you can use the alternate form of the execute method, which takes a java.sql.Connection object as its argument. You can learn more about using data sources, a J2EE best practice by reading my article on the topic, Using Data Sources the Right Way.
Implementations of javax.sql.RowSet can be serialized. For programmers dealing with Enterprise Java Beans, this is some good news. Standard JDBC 2.0 ResultSets were not serializable, making it a hassle requiring the use of custom objects so that ResultSet data could be sent back to the client in an EJB setup for manipulation or mere viewing. With the advent of RowSet implementations, we can serialize ResultSets, send them to our clients, where our clients can then read and update the ResultSet and send it back to the server.
The CachedRowSetImpl implementation is scrollable, allowing you to scroll backwards and forwards amongst the set of records represented by a RowSet. This was allowed in the JDBC 2.0 ResultSet. However, previously a session had to be maintained during which the scrolling would take place. Now we can scroll through our data offline.
Being an optimist about data integrity
You may be wondering what happens if data that is cached on a client (let's say Client A) is manipulated by another client (Client B) before Client A syncs up changes with the database server. The default implementation of the CachedRowSet does not maintain locks on the database server. Optimistic synchronization is used by the reference implementation. To elaborate, if the data that Client A is trying to manipulate was not changed on the database server, the updates will be accepted by the database. If however, something changed with the target data in the interim, a synchronization exception will be thrown. Note that this optimistic approach is how concurrency is handled with the reference implementation. Other implementations might adopt a different concurrency strategy; the specification does not mandate a particular concurrency model.
I have provided a sample program which demonstrates some of the offerings of the CachedRowSetImpl. The code can all be found in the file DisconnectedExample.java. I?ll go over sections of the program, so you can digest what I am trying to convey. Note that to run the sample application, you will need to include the DB2 Universal JDBC driver (db2jcc.jar) in your runtime classpath. You will also need to include the db2jcc_license_cu.jar file. For more information about setting up your environment, refer to my article: Hooking Up with DB2 Universal Database Version 8 using Java.
Class.forName("com.ibm.db2.jcc.DB2Driver");
CachedRowSet crs = new CachedRowSetImpl();
crs.setUsername("db2admin");
crs.setPassword("db2admin");
crs.setUrl("jdbc:db2://localhost:50000/cachedex");
crs.setCommand("SELECT id,firstname,lastname from cachetbl");
crs.execute();
System.out.println("---------------------------");
// display size of cached row set
System.out.println("Size: " + crs.size() + " records");
// display records in cachedrowset
while (crs.next())
{
System.out.println(crs.getRow() + " - " +
crs.getString("firstname") + " " +
crs.getString("lastname"));
}
System.out.println("---------------------------");
|
In the code above, I create a com.sun.rowset.CachedRowSetImpl object. Note how I specified my database connection information for the CachedRowsetImpl object. I used the setCommand method to specify a query I want performed. When the execute method is issued, the CachedRowSetImpl object is populated. It is in this method call that the database connection is obtained and subsequently closed. I can then iterate through the object and use getter methods to extract and report the data it contains.
As mentioned earlier, the CachedRowSetImpl object is scrollable. This is exemplified with the code below, in which I use the last() and previous() methods to scroll backwards through the data.
System.out.println("Showcase scrollability");
// move backwards through rowset
// scroll to last row
crs.last();
// iterate to next row
while (crs.previous())
{
// report current row contents
System.out.println(crs.getRow() + " - " + crs.getString("lastname"));
}
|
One also has the methods first(), and next() available to them for scrolling through data.
To demonstrate the ability to disconnect from the database, the sample program is designed to pause and let you literally perform the dramatic operation of stopping DB2 to truly grasp the benefit of the CachedRowSet offering. The application waits for any key to be pressed before resuming operation (facilitated by a simple readLine off of a BufferedReader object). When the sample program prompts you to stop DB2, you can use the following commands in a new DB2 command line processor window:
db2 force applications all |
with a subsequent
db2stop |
to force a stop of DB2.
While the database is stopped, the following code is meant to run:
System.out.println("Demonstration of how to update cached row set");
crs.updateString("lastname","Kaur");
// commit changes to cached portion of rowset
crs.updateRow();
System.out.println(crs.getRow() + " - " + crs.getString("lastname"));
|
In the code above, we change the last name of the current row entry. Note that the updateRow() method is used on CachedRowSetImpl object to register the change with the object. At this point in time, the database has not been updated. As you recall, the database is not even running. The application will prompt you to start DB2 back up. You can do this by issuing the following command from the DB2 command line processor:
db2start |
After DB2 starts, press any key to resume the sample application.
At this point, the following code will be run, which will effectively synchronize the change in our database.
crs.acceptChanges(); |
You can confirm that the database has in fact been updated by performing a select query on the cachetbl as you can see in Figure 1:
Figure 1. Confirm that the update change has been synchronized with the database
To conclude our hands-on study of the CachedRowSet, the sample application also demonstrates how to insert a row and delete a row. To perform an insert, the cursor must be moved to a special position called "insert row". From there, we populate the new row using update methods. The CachedRowSetImpl object is updated when we use the insertRow() method. The change is persisted to the database when the acceptChanges() method is executed.
This is demonstrated with the following code:
// move the cursor to a blank row crs.moveToInsertRow(); // populate the new row crs.updateInt(1,01234); crs.updateString(2,"Judith"); crs.updateString(3,"Smith"); // insert the new row crs.insertRow(); // move cursor back to previous position crs.moveToCurrentRow(); // synchronize changes to database crs.acceptChanges(); |
The program will pause after the insert has occurred to allow you to query for the insertion in the database.
Deleting a row from the object is fairly straightforward. You do this by positioning the cursor where you want to delete and then use the deleteRow method. As before, a subsequent synchronization is required to persist the change to the database:
// delete row (where the cursor is currently positioned) crs.deleteRow(); // synchronize changes to database crs.acceptChanges(); |
The reader should take note that the CachedRowSetImpl implementation of the javax.sql.RowSet interface is only one implementation (a reference implementation to be more precise). Other vendors already have implementations of the RowSet, which might handle issues like data integrity differently than the reference implementation (for example, a third party implementation might maintain locks on the server).
Updateable ?disconnected" ResultSets have obvious implications in the pervasive world, where network connectivity can be intermittent. Using the CachedRowSet, one can allow clients to cache data locally when a connection is available and then reconnect to synch up changes they may have performed on the data in question.
Disconnection has its benefits. But the user must understand that disconnected objects are kept in memory. Thus, you should not use the approach with large result sets. The associated updating and scrolling of a large result set will be an expensive operation.
Unlike the standard JDBC 2.0 ResultSet, with the CachedRowSet, the continuous use of a database connection is not required. As database connections from your database connection pool are a precious commodity, the ability to connect to your database, disconnect, and then reconnect to synch up with your database is definitely a welcomed facility.
The author would like to thank Richard Dettinger of IBM Life Sciences Development for his help in the review of this article.
| Name | Size | Download method |
|---|---|---|
| DisconnectedExample.java | 4.03 KB |
FTP
 |
HTTP
|
Information about download methods
- Download the source code used in this article.
- The article Using Data Sources the Right Way (Java Pro, February, 2004) tells you how to set up a version 5.0 data source through IBM WebSphere Application Server.
- The article Hooking Up with DB2 Universal Database Version 8 using Java (InformIT, April, 2004) tells you how to get started with DB2 UDB and JDBC.
- The article Use the WebRowSet implementation with DB2 UDB (developerWorks, March 2005) introduces the WebRowSet interface (similar to CachedRowSet but with an eye toward XML data), and describes how to use the WebRowSet implementation with DB2 UDB.
- The article Using the Java JoinRowSet implementation with DB2 UDB (developerWorks, July 2005) presents the JoinRowSet interface -- another extension of the CachedRowSet which allows you to perform JOIN-type operations on offline data, and resynchronize the database at a later time.
- Learn more about the JSR 114 JDBC rowset implementation.
- Download the JDBC reference implementation from Sun Microsystems.
- Check out developerWorks Java technology for more information and resources on developing Java applications.
- Check out the developerWorks DB2 Java technology page for information and resources on developing Java applications specifically for DB2.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
