Level: Introductory Kulvir Singh Bhogal (kbhogal@us.ibm.com), Software Services for WebSphere, Fort Worth, TX, IBM
31 Mar 2005 The WebRowSet interface is a product of JSR-114, the efforts of which brought us the offering of the JDBC™ Rowset in Java™ 1.5.
RowSet objects allow for disconnected interaction with a backend database. This article sheds light on the WebRowSet,
which extends from the javax.sql.RowSet interface, and shows you how to implement it with IBM® DB2® Universal Database™ (DB2 UDB). Includes sample code.
The CachedRowSet recapped
Java 1.5 brings to us the offering of the CachedRowSet interface. CachedRowSet objects allow for us to grab data from a database. We can then disconnect from our database and manipulate and scroll through our data locally. To synchronize any changes we might have made to our CachedRowSet object, we can simply reconnect to our database and persist our changes. You can learn more about the CachedRowSet interface in "Using the Java CachedRowSet implementation with DB2 UDB/" (developerWorks, June 2004). In this article, we'll study WebRowSet objects. I suggest you familiarize yourself with the CachedRowSet by reading my CahedRowSet article before you delve into this one.
The WebRowSet
The WebRowSet interface extends from the CachedRowSet interface. Like its parent interface, the WebRowSet can be serialized. This ability to be serialized allows us to send WebRowSet objects across the wire (something not possible with the ResultSet objects of the past), making WebRowSet objects particularly attractive for use in an Enterprise Java Bean (EJB) setup. A major value add of the WebRowSet is the ability to write a WebRowSet object as an XML document. Also, as you will see shortly, you can populate a WebRowSet object from a properly formed XML document.
In this article, I'll show you how to use the WebRowSet interface. For our backend database, we'll use IBM DB2 Universal Database, Version 8. Download a trial version of IBM DB2 Universal Database, Version 8. As a reminder, for our study you will need Java 1.5. Alternatively, if you would like to use the WebRowSet interface and Sun's reference implementations showcased in this article, you can download the reference implementations separately. (See Resources.)
Get DB2 UDB ready for our study
For our study, let's go ahead and create a simple database consisting of one table. To do this, we'll use the DB2 command line processor and create a database named webrowex:
db2 => create database webrowex
|
Connect to the database using your user name and password (in my case, I had a user name of db2admin with a password of db2admin):
db2 => connect to webrowex user db2admin using db2admin
|
For our table, we'll have a simple table called convert which contains conversion factors for converting between metric and American units.
Table 1.
Conversion Factors
|
Conversion
|
Conversion Factor
| | CentimeterToInch | 0.39 | | KilometerToMile | 0.62 | | PoundToKilogram | 0.45 |
In SQL, we can translate this to:
db2 => create table convert (conversion varchar(80)
primary key not null, factor decimal (6,2) NOT NULL)
|
Let's go ahead and insert the values into our table:
db2 => insert into convert values('CentimeterToInch',0.39)
db2 => insert into convert values('KilometerToMile',0.62)
db2 => insert into convert values('PoundToKilogram',0.45)
|
Populate the WebRowSet
Now that we have our database in place, it is time to move on to working with the WebRowSet. javax.sql.rowset.WebRowSet is a Java interface. To instantiate a WebRowSet object, we can to do so with the com.sun.rowset.WebRowSetImpl implementation:
WebRowSet conversionFactors = new WebRowSetImpl();
|
At this point, our WebRowSet object is empty. We need to populate it with some data. This can be done in one of two ways. One way is to use the WebRowSet object to establish a database connection:
WebRowSet conversionFactors = new WebRowSetImpl();
conversionFactors.setUrl("jdbc:db2://localhost:50000/webrowex");
conversionFactors.setUsername("db2admin");
conversionFactors.setPassword("db2admin");
conversionFactors.setCommand("SELECT * FROM CONVERT");
conversionFactors.execute();
|
Alternatively, you can populate the WebRowSet object from a ResultSet object:
// load the DB2 Driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection db2Conn =
DriverManager.getConnection("jdbc:db2://localhost:50000/webrowex",
"db2admin","db2admin");
// use a statement to gather data from the database
Statement st = db2Conn.createStatement();
String myQuery = "SELECT * FROM CONVERT";
WebRowSet conversionFactors = new WebRowSetImpl();
// execute the query
ResultSet resultSet = st.executeQuery(myQuery);
conversionFactors.populate(resultSet);
|
Take a preemptive note that when using the acceptChanges method (which you'll be introduced to later in this article) of a WebRowSet object which was created by populating the object from a ResultSet object, you need to use the acceptChanges method with the java.sql.Connection object argument representing the database connection used to create the ResultSet.
Write to XML
Writing our WebRowSet object to XML couldn't be easier. We simply use the writeXML method, which can take in either an OutputStream object (if we want to write in bytes) or a Writer object (if we want to write in characters). The code snippet below writes to a file called conversions.xml using a FileWriter object:
java.io.FileWriter writer = new java.io.FileWriter("c:\\conversions.xml");
conversionFactors.writeXml(writer);
|
The code above is grabbed from the sample class WebRowSetExample.java, which you can download with this article. After executing this class, you should take time to look at the file conversions.xml to see how the data we grabbed and populated into our WebRowSet object is represented as XML. By analyzing the XML file, you should see that it includes metadata about the structure of the table and columns we grabbed our data from as well as our data. The XML file is actually structured according to the laws laid down by the WebRowSet XML Schema definition (see http://java.sun.com/xml/ns/jdbc/webrowset.xsd). The cool thing is that you do not have to understand the intricacies of XML schema; everything is handled for us behind the scenes.
As an overview our XML document will contain a properties section (shown below), which will contain information about our connection including our command associated to our WebRowSet: (SELECT * FROM CONVERT) as well as the URL used to connect to the database: jdbc:db2://localhost:50000/webrowex
<properties>
<command>SELECT * FROM CONVERT</command>
<concurrency>1008</concurrency>
<datasource><null/></datasource>
<escape-processing>true</escape-processing>
<fetch-direction>1000</fetch-direction>
<fetch-size>0</fetch-size>
<isolation-level>2</isolation-level>
<key-columns>
</key-columns>
<map>
</map>
<max-field-size>0</max-field-size>
<max-rows>0</max-rows>
<query-timeout>0</query-timeout>
<read-only>true</read-only>
<rowset-type>ResultSet.TYPE_SCROLL_INSENSITIVE</rowset-type>
<show-deleted>false</show-deleted>
<table-name>CONVERT</table-name>
<url>jdbc:db2://localhost:50000/webrowex</url>
<sync-provider>
<sync-provider-name>com.sun.rowset.providers.RIOptimisticProvider</sync-provider-name>
<sync-provider-vendor>Sun Microsystems Inc.</sync-provider-vendor>
<sync-provider-version>1.0</sync-provider-version>
<sync-provider-grade>2</sync-provider-grade>
<data-source-lock>1</data-source-lock>
</sync-provider>
</properties>
|
The metadata section (shown below) contains metadata about the structure of the columns which our WebRowSet object consists of. If you recall, our query was SELECT * FROM CONVERT. The CONVERT table contained columns named CONVERSION and FACTOR; the structure of both columns is described in the metadata section. You will see that for each column we have a <column-definition> element which describes the structure of the underlying column. For example, for our first column, our CONVERSION column, information such as the schema name (DB2ADMIN), column type (VARCHAR), and column precision (80) are all described.
<metadata>
<column-count>2</column-count>
<column-definition>
<column-index>1</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>true</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>true</searchable>
<column-display-size>80</column-display-size>
<column-label>CONVERSION</column-label>
<column-name>CONVERSION</column-name>
<schema-name>DB2ADMIN</schema-name>
<column-precision>80</column-precision>
<column-scale>0</column-scale>
<table-name>CONVERT</table-name>
<catalog-name>WEBROWEX</catalog-name>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
<column-definition>
<column-index>2</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>true</signed>
<searchable>true</searchable>
<column-display-size>8</column-display-size>
<column-label>FACTOR</column-label>
<column-name>FACTOR</column-name>
<schema-name>DB2ADMIN</schema-name>
<column-precision>6</column-precision>
<column-scale>2</column-scale>
<table-name>CONVERT</table-name>
<catalog-name>WEBROWEX</catalog-name>
<column-type>3</column-type>
<column-type-name>DECIMAL</column-type-name>
</column-definition>
</metadata>
|
Last but not least in our XML, we have a data section, which is shown below. As you can see, each <currentRow> element contains two <columnValue> elements as well as the data housed in them. The order of the child elements is critical as it corresponds to the order of columns described in the metadata section.
<data>
<currentRow>
<columnValue>CentimeterToInch</columnValue>
<columnValue>0.39</columnValue>
</currentRow>
<currentRow>
<columnValue>KilometerToMile</columnValue>
<columnValue>0.62</columnValue>
</currentRow>
<currentRow>
<columnValue>PoundToKilogram</columnValue>
<columnValue>0.45</columnValue>
</currentRow>
</data>
|
Making changes
If we make a change to our WebRowSet locally (e.g., insert, update, or delete rows), our resulting XML file representation will contain information about modifications. Let's go about seeing this first hand.
Row insertion
Just like with the CachedRowSet I covered in the prequel to this article, to perform an insert, we must move the cursor to a special position called "insert row". Then we can populate the new row using update methods given to us by our parent (or more appropriately, our grandparent) ResultSet interface. The WebRowSet object is updated when we use the insertRow() method. Our actual change is persisted later to the database when the acceptChanges() method is executed. The following code pulled from WebRowSetExample.java exemplifies this procedure:
// move the cursor to a blank row
conversionFactors.moveToInsertRow();
// populate the new row
conversionFactors.updateString(1,"MeterToYard");
conversionFactors.updateDouble(2,1.19d);
// insert the new row
conversionFactors.insertRow();
// move cursor back to previous position
conversionFactors.moveToCurrentRow();
|
In WebRowSetExample.java, I went ahead and wrote to a different XML file (conversionsinsert.xml) intentionally after row insertion and before calling the acceptChanges method so you could juxtapose the XML files produced after each use case.
<data>
<currentRow>
<columnValue>CentimeterToInch</columnValue>
<columnValue>0.39</columnValue>
</currentRow>
<currentRow>
<columnValue>KilometerToMile</columnValue>
<columnValue>0.62</columnValue>
</currentRow>
<currentRow>
<columnValue>PoundToKilogram</columnValue>
<columnValue>0.45</columnValue>
</currentRow>
<insertRow>
<columnValue>MeterToYard</columnValue>
<columnValue>1.19</columnValue>
</insertRow>
</data>
|
Notice how our <data> element contains an <insertRow> element (highlighted in blue above) which corresponds to the new row we were trying to add to the WebRowSet.
Row deletion
To delete a row from the WebRowSet, we can position our cursor where we want to delete using the absolute method and then use the deleteRow() method of our WebRowSet object. For example, to kill the KilometerToMile row (our second row in our WebRowSet), we use the code:
conversionFactors.absolute(2);
conversionFactors.deleteRow();
|
In my sample code, I write to yet another XML file (conversionsdelete.xml) to show you how the WebRowSet represents our modification of the data in XML form.
<data>
<currentRow>
<columnValue>CentimeterToInch</columnValue>
<columnValue>0.39</columnValue>
</currentRow>
<deleteRow>
<columnValue>KilometerToMile</columnValue>
<columnValue>0.62</columnValue>
</deleteRow>
<currentRow>
<columnValue>PoundToKilogram</columnValue>
<columnValue>0.45</columnValue>
</currentRow>
<insertRow>
<columnValue>MeterToYard</columnValue>
<columnValue>1.19</columnValue>
</insertRow>
</data>
|
Notice in the data element I grabbed from conversionsdelete.xml above that we have a new element called <deleteRow> (highlighted in red above) which expresses which row we want deleted. Also notice that we still have our <insertRow> element present, since at the point of execution of our sample code, we still had not used the acceptChanges method. In other words, we have still not synched our WebRowSet with the database.
Row modification
If you are one of those trivia buffs who keeps all the conversion factors in your head, you may have noticed that I accidentally told you that the conversion factor from meters to yards was 1.19, whereas it is supposed to be 1.09. I actually knew I made the mistake; I was just setting stage to introduce the update facility of the WebRowSet.
The code below updates the row with MeterToYard as its value for the CONVERSION column by setting the value for the FACTOR column to 1.09.
conversionFactors.absolute(4);
conversionFactors.updateDouble("factor",1.09d);
|
Take note that I had to move my cursor to 4 rather than 3 since we even though we deleted a row, we still had to iterate over its place because the change had not taken effect yet (again, because of the lack of a synch operation).
As before, I dumped the resulting XML to a file. In this case, the file was conversionsmodify.xml. The XML below was extracted from that file:
<data>
<currentRow>
<columnValue>CentimeterToInch</columnValue>
<columnValue>0.39</columnValue>
</currentRow>
<deleteRow>
<columnValue>KilometerToMile</columnValue>
<columnValue>0.62</columnValue>
</deleteRow>
<currentRow>
<columnValue>PoundToKilogram</columnValue>
<columnValue>0.45</columnValue>
</currentRow>
<insertRow>
<columnValue>MeterToYard</columnValue>
<columnValue>1.19</columnValue>
<updateRow>1.09</updateRow>
</insertRow>
</data>
|
Notice the <deleteRow> tag highlighted in red is still present because as just stated, we have not committed our changes. Also, if you recall, we added the row for meter to yard conversion. Since this change had not yet been persisted, our <updateRow> element ends up being embedded as part of the <insertRow> element (a compounding effect). If we had used the acceptChanges method to synchronize our changes then instead of our <updateRow> element being nested in the <insertRow> element we would have seen:
<currentRow>
<columnValue>MeterToYard</columnValue>
<columnValue>1.19</columnValue>
<updateRow>1.09</updateRow>
</currentRow>
|
Notice how the WebRowSet keeps track of the previous values as well as the new values of our data. This is critical for knowing if any other clients tried to change the same data we are trying to change while we had cached it locally.
Data integrity optimism
Just like the CachedRowSet implementation I covered in the article "Get Disconnected", the WebRowSet implementation provided by Sun takes an optimistic approach to data integrity. WebRowSet does not maintain locks on the database server. Accordingly, if for example you try modifying a row locally and that row is deleted or modified by another client application when you finally try to synchronize, then a synchronization exception will be thrown. If you take a look at the properties section of the XML files you've been studying, you'll see the element:
<sync-provider-name>
com.sun.rowset.providers.RIOptimisticProvider
</sync-provider-name>
|
com.sun.rowset.providers.RIOptimisticProvider refers to the optimistic provider which the WebRowset implementation will use.
Digesting XML
Up till now I have shown you how to write to XML from a WebRowSet object. With the WebRowSet, we can also populate a WebRowSet object from an XML file. The readXML method can take in a javai.io.FileReader object and populate a WebRowSet object. The code snippet below exemplifies how you could go about doing this:
// instantiate a WebRowSet object
WebRowSet webRowSet = new WebRowSetImpl();
FileReader reader = new FileReader("c:\\conversionsfinish.xml");
// populate the WebRowSet object with the contents of the XML file
webRowSet.readXML(reader);
|
As one should gather from the code above, being able to populate a WebRowSet object from a predefined XML format (as ordained by the WebRowSet XML Schema) gives us a very powerful option for being able to populating databases using a self describing format.
Conclusion
The self describing nature of the WebRowSet in XML makes it ideal for XML-centric technologies such as Web services. Also, being able to represent a WebRowSet as XML allows us to easily send the XML to a network client. You can use XSLT to transform the XML document into a format viewable by a Web browser or on mobile devices like PDAs or wireless phones. Also an end client can opt to store database data locally for archival reasons.
Download | Description | Name | Size | Download method |
|---|
| Example Code Showcasing usage of the WebRowSet | WebRowSetExample.java | 3 KB | FTP |HTTP |
|---|
Resources
About the author | 
| | Kulvir Singh Bhogal works as an IBM consultant, devising and implementing Java-centric solutions at customer sites across the nation. |
Rate this page
| 
