Sunday, September 03, 2006

Manipulating Tabular Data using JDBC RowSet API

If you Google "Tabular Data", you will get more than 2 millions hits, which is an illustration of the pervasive and broad scope of the concept. So what are tabular data? The following proposal might not be the most well thought definition but tabular data can be defined as data organized or presented as items/columns and records/rows that can be stored in (or retrieved from) various persistence repositores including file system, spreadsheet, relational tables, XML documents and so on.

This blog will introduce the goals the JSR-114 RowSet, briefly describe the various models and furnish pointers to get further details and code samples.

Why JDBC RowSet?

When dealing with tabular data, a number of Java SE/EE and Web Services requirements (such as JavaBean properties, XML documents properties) that are not addressed by the existing JDBC interfaces.

The RowSet specification (http://www.jcp.org/en/jsr/detail?id=114) addresses the following requirements and shortcomings of JDBC specifications:

  • Make query results scrollable and updatable without relying on the underlying JDBC driver
  • Manipulate data (e.g., filtering and joining) and make changes while disconnected from data sources
  • Associate properties and event listeners to query results to facilitate manipulation by tools
  • Consume data results in chunks and ship result set to thin clients, handheld devices, and PDAs which are most of the time not connected and do not have a full-fledged JDBC driver
  • Convert data results to or from XML documents.

The RowSets Models

The RowSet Specification (JSR-114) formalizes and completes the concept of RowSet introduced in JDBC 2.0. It defines five standard JDBC RowSet interfaces including: JdbcRowSet, CachedRowSet, WebRowSet, JoinRowSet, and FilteredRowSet (see Figure 1 below).

The RowSet Hierarchy

The base JDBC RowSet interface (javax.sql.Rowset) encapsulate/extend ResultSets (java.sql.ResultSet) through a set of properties including: datasource, datasourcename, url, username, password, typeMap, maxFiedSize, maxRows, queryTimeout, fetchsize, transactionisolation, escapeProcessing, command, concurrency, readOnly, fetchDirection, as well as events and an event listener mechanism.
In general, each rowset model is populated from a repository using the repositorey-specific APi (i.e., JDBC for RDBMS), then manipulated using the towset-model-specific methods.
In the rest of this blog, I'll give you a brief description of each model excerpted from my book (a full coverage of the RowSet API is beyond the scope of a blog).

JDBCRowSet

The JDBCRowSet object is a JavaBean equivalent of ResultSet; as such, it maintains a connection to its datasource, has a set of properties,and a listener notification mechanism. The contents are identical to those of a ResultSet object, and it inherits all of the result set methods for rows manipulation, cursor movement, and data manipulation.
The OracleJDBCRowSet class implements the javax.sql.rowset.JdbcRowSet interface.

CahedRowSet

The CachedRowSet object represents disconnected RowSet such that the row data and metadata can persist beyond the connection. When the connection to the database is restored, any modifications to the RowSet are propagated back to the database. It caches its rows in memory and allows operating on these without being connected to the datasource, resulting in increased scalability. In addition, it turns any result set object (even the read-only ones) into scrollable and updatable rowsets.
The OracleCachedRowSet class implements the javax.sql.rowset.CachedRowSet interface.

WebRowSet

A WebRowSet object is a CachedRowSet object, augmented with a default WebRowSetXmlReader, which reads an XML document into the rowset, and a default WebRowSetXmlWriter, which writes the rowset as an XML document.

You can:
  • Create a WebRowSet object and dump its contents to an XML document.
  • Read an XML document into a WebRowSet object.
  • Make changes to a WebRowSet object (Insert, Update, Delete rows) and synchronize it back to the datasource.

The W3C XML schema for WebRowSet is defined by Sun, and available at http://java.sun.com/xml/ns/jdbc/webrowset.xsd.

The oracle.jdbc.rowset.OracleWebRowSet class implements the javax.sql.rowset.WebRowSet interface. The oracle.jdbc.rowset.OracleWebRowSetXmlWriter class extends the javax.sql.rowset.spi.XmlWriter.

FilteredRowSet

A FilteredRowSet object simulates a SQL WHERE clause by applying user-defined filtering criteria on rows in a disconnected rowset; in other words, it returns/retrieves only rows that meet the defined criteria, and inserts/updates only rows that meet the constraints defined in the filter(s). The constraints are defined within a predicate class then assign this predicate to the FilteredRowSet object; as a result, the constraints defined in the predicate class will be evaluated against each row in the rowset object.In order to use filtered rowsets the JDBC applications must:

  • Define and implement the Predicate interface.
  • Create an instance of FilteredRowSet object and set properties.
  • Populate the FilteredRowSet.
  • Set/enable filters.
  • Retrieve the filtered rows.

The oracle.jdbc.rowset.OracleFileteredRowSet class implements the javax.sql.rowset.FilteredRowSet; in addition the oracle.jdbc.rowset.OraclePredicate interface inherits from the javax.sql.rowset.Predicate.


JoinRowSet

The JoinRowSet object represents a SQL JOIN operation between disconnected RowSet objects.A JoinRowSet object combines data from multiple rowsets. In order to use Join rowsets, the JDBC applications must:
  • Create an empty instance of JoinRowSet object and set properties.
  • Create and add RowSets objects to the JoinRowSet.
  • Define the match column (similar to SQL matching columns).
  • Navigate and consume the JoinRowSet Object.

The oracle.jdbc.rowset.OracleJonRowSet class implements the javax.sql.rowset.JoinRowSet interface.


Further Details

The JSR-114 furnishes a reference implementation of each rowset type as part of Java/JDK 5.0, or as stand-alone Jars (i.e., rowsetjsr114.jar) that can be used on top of any standard JDBC driver. Oracle implements JSR-114 specification as part of the JDBC drivers (i.e., ojdbc14.jar).

For more details, see: