A new approach to JDBC programming : RowSets

[Amit Ray]

RowSets are a JDBC 2.0 extension to the java.sql.ResultSet interface. Guess what, it makes life a lot easier for all JDBC programmers. No more Connection objects, statement objects, just a single RowSet will do everything for you.

Rowsets make it easy to send tabular data over a network. They can also be used to provide scrollable result sets or updatable result sets when the underlying JDBC driver does not support them.

A RowSet object contains a set of rows from a result set or some other source of tabular data, like a file or spreadsheet. Because a RowSet object follows the JavaBeans model for properties and event notification, it is a JavaBeans component that can be combined with other components in an application. As is true with other Beans, application programmers will probably use a development tool to create a RowSet object and set its properties.

Rowsets may have many different implementations to fill different needs. These implementations fall into two broad categories, rowsets that are connected and those that are disconnected.

The following code snippet might make the usage of RowSets a bit clear :

...

import javax.sql.RowSet;
import oracle.jdbc.rowset.OracleJDBCRowSet;

....

RowSet rowset = new OracleJDBCRowSet();
rowset.setUsername(username);
rowset.setPassword(password);
rowset.setUrl(url);
rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);
rowset.setCommand(query);
rowset.execute();

while ( rowset.next() )

{

// These variables have been declared earlier

callType = rowset.getString("CALL_TYPE");
otherNumber= rowset.getString("OTHER_NUMBER");
timestamp = rowset.getTimestamp("CHARGING_TIMESTAMP");
volume = rowset.getInt("VOLUME");

rowset.updateFloat("CHARGE", volume * 0.50);
rowset.updateRow();

}

// Closing the DB connection.
rowset.close();
...



The required jars : ojdbc14.jar, ocr12.zip, ocr12.jar comes along with Oracle 9i.

___________________

The following links will give you a headstart with RowSets :

Tutorials :

http://java.sun.com/developer/Books/.../chapter5.html
http://otn.oracle.com/sample_code/tu...rowsettoc.html
http://edocs.bea.com/wls/docs81/jdbc/rowsets.html

Implementation Downloads

http://java.sun.com/products/jdbc/download.html

Rowset implementations : Public Review :

http://www.jcp.org/en/jsr/detail?id=114
http://www.theserverside.com/news/th...hread_id=21198

Cheers,
Amit Ray.

Those people who think they know everything are a great annoyance to those of us who do. (Isaac Asimov)

[pallavdas]

Why should people go for Rowset instead of normal jdbc resultsets.? Or why has java introduced rowsets? You should have written the advantages & disadvantages also.

[Amit Ray]

Hey mate ... the article did talk about a few of the advantages though it didn't list them in as many words. I will list them once again :

Advantages :

• Certain implementations of rowset doesn't require the database connection to be maintained throughout the entire period of time it takes to process the data. So even if DB connection is lost in between it works all right. It makes a connection while finally inserting the processed (modified) data. Precisely it need to make the connection only twice once while reading and second while updating after processing is over.While in jdbc resultsets which need to hold the DB connection throughout its usage which might not be possible in many practical cases.

• Since it reads a set of rows from the table and loads it into memory, processes it in the memory and finally inserts the modified data into the DB, it is obviously much faster than resultsets which refer to the db for each row read.

• Since the rowset object contains the DB connection information all by itself, (No connection object or statement object ), Rowsets make it easy to send tabular data over a network, whereby if we sendf the Rowset object over the network it suffices and there isn't any need to provide the DB information separately.

• RowSet object follows the JavaBeans model for properties and event notification, it is a JavaBeans component that can be combined with other components in an application.

• Easy to use once you have imported the required jars.

As for disadvantages I am still not sure. I did not face any during my brief exposure to Rowsets. I will surely let you know as I come to know of any.

Amit Ray.

People that think logically are a nice contrast to the real world. (Matt Biershbach)

[pallavdas]

Disadvantage

1. As all the table rows are taken into memory, care should be taken with the query which retrieves the number of rows, as it will be very resourceful if huge set of rows is taken and the application will get very slow.

2. As rowsets will keep a connection to itself , hence the connection object will be kept at the client which make the client heavy.As it can modify data at the client side inconsistancy may arise with the server containing actual data.

[Amit Ray]

Yupp, you are right that disconnected Rowsets are not suitable for large datasets, but I'm not too sure about the second one. The following is a excerpt from the java.sun.com 's JDBC tutorial :

Rowsets may have many different implementations to fill different needs. These implementations fall into two broad categories, rowsets that are connected and those that are disconnected. A disconnected rowset gets a connection to a data source in order to fill itself with data or to propagate changes in data back to the data source, but most of the time it does not have a connection open. While it is disconnected, it does not need a JDBC driver or the full JDBC API, so its footprint is very small. Thus a rowset is an ideal format for sending data over a network to a thin client.

Because it is not continually connected to its data source, a disconnected rowset stores its data in memory. It needs to maintain metadata about the columns it contains and information about its internal state. It also needs a facility for making connections, for executing commands, and for reading and writing data to and from the data source. A connected rowset, by contrast, opens a connection and keeps it open for as long as the rowset is in use.

Although anyone can implement a rowset, most implementations will probably be provided by vendors offering RowSet classes designed for fairly specific purposes. To make writing an implementation easier, the Java Software division of Sun Microsystems, Inc., plans to provide reference implementations for three different styles of rowsets in the future. The following list of planned implementations gives you an idea of some of the possibilities.

A. A CachedRowSet class—a disconnected rowset that caches its data in memory; not suitable for very large data sets, but an ideal way to provide thin Java clients, such as a Personal Digital Assistant (PDA) or Network Computer (NC), with tabular data


B. A JDBCRowSet class—a connected rowset that serves mainly as a thin wrapper around a ResultSet object to make a JDBC driver look like a JavaBeans component


C. A WebRowSet class—a connected rowset that uses the HTTP protocol internally to talk to a Java servlet that provides data access; used to make it possible for thin web clients to retrieve and possibly update a set of rows

Amit Ray.