ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Making the Most of JDBC with WebRowSet

by Sharad Acharya
06/21/2006

Any real-world, enterprise-scale application requires access to some sort of persistent storage. The Relational Database Management System (RDBMS) is the most widely-used persistence storage mechanism that supports SQL for data query and update. Java DataBase Connectivity (JDBC) is a set of APIs that provide a framework for Java programs to retrieve and update an RDBMS data using SQL.

Retrieving data from or updating to a database from a Java program involves multiple steps. First, the program should establish a connection, which enables communication to the target database. There are a few different approaches to establish a connection. In the conventional approach, the Java program calls the appropriate getConnection() method of java.sql.DriverManager class after loading a vendor-provided JDBC driver. Another (and preferred) way is to instantiate and initialize the connection object either by a vendor-provided implementation or by writing a custom class implementing the javax.sql.Connection interface for the target database. More controlled environments, such as Java EE containers, provide a way to retrieve a connection using the Java Naming and Directory Interface (JNDI) lookup. Whichever method the program uses to establish a connection, it should have a reference to an instance of java.sql.Connection interface if the connection is successful. From this connection, the program creates an instance of java.sql.Statement or some subinterface with an appropriate query to the database. Finally, the program executes the statement to retrieve an instance of java.sql.ResultSet that represents the actual data from the database satisfying the query.

At the time of this writing, JDK version 5.0 is the most recent release version of Java that supports JDBC version 3.0. This version of JDBC provides several features for ease of use and flexibility of data manipulation and transformation. In this article, I will first briefly explain its inheritance hierarchy, followed by an example that shows how we can use some of the features of the javax.sql.rowset.WebRowSet interface to easily transform data from database to XML files and vice versa. Then I will discuss some of the benefits and liabilities of this interface. I will conclude after explaining some of the scenarios in which this interface is better choice.

WebRowSet Hierarchy

Let me begin with a brief explanation of inheritance hierarchy of the WebRowSet interface. Figure 1 shows the related Java elements (packages, interfaces, and classes) that will be part of the discussion.

Figure 1
Figure 1: WebRowSet inheritance hierarchy

At the root of inheritance hierarchy is the java.sql.ResultSet interface. An instance of this interface represents tabular data, obtained by executing a java.sql.Statement containing some query to the underlying database. The default result set can be traversed only in a forward direction and is not updatable. You cannot have precise control over the way you traverse default result set.

So what choices do you have then? It depends on what you want to do with the result set. For example, if you want a result set that supports JDBC API for a JavaBeans component model, then you have to use javax.sql.RowSet which is a subinterface of java.sql.ResultSet.

Because database access from a Java program is a heavyweight operation, in-memory data caching is a key factor for application performance. If you want to use your result set as a container for rows of data cached in memory, then you can use javax.sql.rowset.CachedRowSet, which is a subinterface of javax.sql.RowSet. An instance of this interface provides the ability to operate on it without always being connected to the data source. Further, it is scrollable, updatable, and serializable. In addition to databases, it can work with other data sources with a tabular format, such as spreadsheets.

If you want all the features mentioned so far, plus the ability to output the result set to XML and input valid XML as a result set, then javax.sql.rowset.WebRowSet is your choice. One implementation that is already available is Sun's reference implementation class, com.sun.rowset.WebRowSetImpl, shipped as part of JDK 5.0.

At the bottom of this hierarchy is javax.sql.rowset.JoinRowSet, a subinterface of javax.sql.rowset.WebRowSet that provides a way to join multiple but related result set objects as if SQL JOIN is constructed on underlying tables.

Using Simple ResultSet

With this introduction in mind, let me show you a simple example. All examples discussed in this article are developed with Eclipse 3.1.0 using JDK 5.0 with Oracle database version 10.2 in Windows XP. See the resources section for the sample code and actual output file.

Let's consider a simple database table that defines four columns to store some information, called "student." The following table shows the query used to retrieve already populated results.

SQL> select * from student:

ID FNAME LNAME AGE
200 Jack Dakota 21
100 John Doe 26

Now for starters, let's write a Java class that connects to the database containing the student table and retrieve its records.

 
public class DatasourceConnector {
 public static void main(String[] args) {
   Connection con =null;
   OracleDataSource ds =null;
   try {
     ds = new OracleDataSource(); 
       ds.setUser("<dbuser>");
       ds.setPassword("<password>");
       ds.setURL(
       "jdbc:oracle:thin:@localhost:1521:<sid>");
     } catch (SQLException e) {
       e.printStackTrace();
   }
   try {
     con = ds.getConnection();
     Statement stmt = con.createStatement();
     ResultSet rs = 
       stmt.executeQuery(
         "select * from student ");
     for (int j=0; 
           j<rs.getMetaData().getColumnCount(); 
           j++)
         {
       System.out.print(
         rs.getMetaData().
           getColumnName(j+1)+"\t");
     }
     while (rs.next()) {
       System.out.print("\n");
           for (int i=0; 
             i< rs.getMetaData().getColumnCount(); 
             i++)
           {
         System.out.print(
           rs.getString(i+1)+"\t");
        }
     }
     rs.close();
     stmt.close();
     con.close();
   } catch (SQLException e) {
   e.printStackTrace();
   }
  }
}

The Java class in this example instantiates and initializes a connection object, creates a statement containing the query string, and retrieves the result set. You have to supply database user ID, password, and the schema name of the underlying database that are marked within '<…>' for your settings. Save this file as DatasourceConnector.java, compile, and run:

>javac DatasourceConnector.java
>java DatasourceConnector

Should everything go fine, you should see a result similar to this:

ID FNAME LNAME AGE
200 Jack Dakota 21
100 John Doe 26

Exporting ResultSet

Let me now take this example bit further by assuming that this program is part of an application that requires data conversion from database to XML for some further processing. I will use implementation class com.sun.rowset.WebRowSetImpl for this purpose. To see this in action, let's add some code to construct an instance of javax.sql.rowset.WebRowSet and export the result set to XML file. Here's how you do this:

... ... ... 
ResultSet rs = 
  stmt.executeQuery("select * from student");
WebRowSet wrs = new WebRowSetImpl();
wrs.populate(rs);
try {
  wrs.writeXml(
  new FileOutputStream("student.xml"));
} catch (FileNotFoundException e) {
  e.printStackTrace();
} catch (IOException e) {
  e.printStackTrace();
}
... ... ...

The output from wrs.writeXML() results in an XML document that conforms to the WebRowSet schema definition. It contains three parts: properties, metadata and data. The general layout is below; see the resources section for the complete output file.

<?xml version="1.0"?> 
<webRowSet xmlns=
 "http://java.sun.com/xml/ns/jdbc" 
xmlns:xsi=
 "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=
 "http://java.sun.com/xml/ns/jdbc 
http://java.sun.com/xml/ns/jdbc/webrowset.xsd"> 
 < properties> 
... ... ... 
 </properties> 
 <metadata> 
       ... ... ...
 </metadata> 
 <data> 
... ... ...
</data> 
</webRowSet>

The <properties> tag provides details of the synchronization provider, such as isolation level, RowSet type, etc. The <metadata> tag provides information about the underlying database table, such as numbers, name, and type of columns, etc. The <data> tag holds actual data that has following information for the table above.

 <data>
   <currentRow>
     <columnValue>200 < /columnValue>
     <columnValue>Jack</columnValue>
     <columnValue>Dakota</columnValue>
     <columnValue>21</columnValue>
   </currentRow>
   <currentRow>
     <columnValue>100</columnValue>
     <columnValue>John</columnValue>
     <columnValue>Doe</columnValue>
     <columnValue>26</columnValue>
   </currentRow>
 </data>

The <currentRow> tag in the above example shows the values of each column for the WebRowSet object currently mapped to an underlying data source without any modification. Any data manipulation operations such as insert, update, or delete are marked appropriately in the output XML that I will explain in the next section.

Manipulating ResultSet

You can delete a row by moving cursor to the row position and calling the deleteRow() method on the WebRowSet instance. Similarly, you can update one or more values in a row by moving the cursor to the row position and calling the appropriate update method. The following code example does both.

... ... ...
ResultSet rs = 
  stmt.executeQuery("select * from student ");
WebRowSet wrs = new WebRowSetImpl();
wrs.populate(rs);
wrs.absolute(1); //move to first row
wrs.deleteRow(); //delete current row
wrs.absolute(2); //move to second row
wrs.updateInt(4,20);//update current row
... ... ...

The first four lines of the code snippet are obvious: instantiate the object and populate it with the result set. The next two lines try to delete the first row. The final two lines try to update the value at fourth column of second row to 20. Outputting this WebRowSet object produces the following <data> element.

 <data>
   <deleteRow> 
     <columnValue>200</columnValue> 
     <columnValue>Jack</columnValue> 
     <columnValue>Dakota</columnValue> 
     <columnValue>21</columnValue> 
   </deleteRow> 
   <currentRow> 
     <columnValue>100</columnValue> 
     <columnValue>John</columnValue> 
     <columnValue>Doe</columnValue> 
     <columnValue>26</columnValue> 
     <updateRow>20</updateRow> 
   </currentRow> 
 </data>

This XML snippet shows that the first row is marked for deletion with the <deleteRow> tag. The value in the fourth column position of the second row is marked for update from current value of 26 to a new value of 20.

Committing the Changes

So far I've only manipulated the result. These changes will not be reflected in the underlying database until I commit. The following code, if executes successfully, makes the changes permanent by committing them.

... ... ...
wrs.acceptChanges();
... ... ...

Reading from XML

WebRowSet also provides several readXML() methods to read data from valid XML using java.io.InputStream or java.io.Reader instances. You will find this option helpful if your input data is in the form of valid XML that you need to persist to the underlying database before (optionally) doing some other processing.

Benefits and Liabilities

Having shown off the basic operation of the WebRowSet, let's tally up the advantages and disadvantages of using this approach with your data.

Benefits

Liabilities

Applicability

The WebRowSet interface is appropriate in following scenarios:

  1. Application integration with XML messaging, which is one of the top contemporary architectural choices. A Java EE application can use JMS-based integration to communicate with external systems while using JDBC to save its own transactional states. In such a scenario, the WebRowSet API reduces programming complexity as well as the amount of code to write.
  2. Content presentation from persistence data sources is usually achieved by retrieving the result set and traversing it in the component (such as JavaServer Pages) that generates them. An alternative to this approach is to transform the result set to XML before applying XSLT and then generate the presentation content. This choice provides better maintainability since it decouples the formatting of the content from the component that presents them.

Conclusion

Until recently, converting data back and forth between database and XML representations used to be a major task that would take a large chunk of application development and testing resources. Fortunately, the newest version of the JDBC API provides features such as precise navigation through the result set, caching of result sets, writing result sets to XML, etc. Given the importance of RDBMS to the enterprise programmer and with the current and ever-growing popularity of Java and XML applications, the features provided by interfaces like WebRowSet will be very useful. So, it is time to rethink if you are still planning to use conventional data access approaches in your next Java/XML application that uses RDBMS for data persistence; WebRowSet may offer a real improvement.

Resources

Sharad Acharya has more than eight years of experience in the software engineering field in multiple business domains including supply chain, insurance, banking, and mortgage.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.