Making the Most of JDBC with WebRowSetby Sharad Acharya
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.
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: 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
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.