Introduction
RowSet interface is basically an extension of JDBC ResultSet and is a part of the javax.sql package. Built on the standard contextual structure of the JavaBeans component model, the core design structure is inherently reflected in the subjectivity of RowSet interface. It makes sense that RowSet has a set of JavaBeans properties, which can be set and retrieved with the available setter and getter methods. In addition to that RowSet also implements the JavaBeans mechanism of event notification that allows other components registered to the instance to receive notification when a certain event is triggered. These additional capabilities make it more flexible and leverage productivity when used effectively. In this article we shall try to get a glimpse of what RowSet is all about and how to implement one in Java.
Types of RowSet
There are two types of RowSet objects – connected and disconnected. Connected RowSet are those which establish a connection with the database and retain it until the application terminates. Disconnected RowSet on the other hand establishes a connection, executes a query such as retrieving data from the database and closes the connection.
Note: RowSet and its extended family are all interfaces; that means we need a reference implementation class of the specific RowSet extension to create an instance. RowSet is built on the top of JDBC driver; as a layered software, its implementation is open to everyone including all JDBC driver vendors. They generally package them in a jar file and supply them along with the JDBC driver. There are several such third party vendors who provide the implementation package of RowSet along with JDBC driver, Oracle being one of them. Oracle JDBC supports all five types of RowSets through the interfaces and classes present in the oracle.jdbc.rowset package. In this article we shall use Sun’s rowset implementation for the purpose of simplicity. A word of caution though, Sun’s implementation is deprecated and may be not be available in future releases. In that case we have to rely on our own implementation or use those provided by third party vendors. In any case the implementation details have to follow the standard JSR specification; as a result, the API declaration would remain the same.
Types of Implementation
According to the implementation there are five different categories of RowSet – JdbcRowSet, CachedRowSet, WebRowSet, FilteredRowSet, JoinRowSet.
JdbcRowSet interface is an extension RowSet interface and is the only connected RowSet in the family. It basically acts as a wrapper around the ResultSet object with some additional functionality. The primary advantage of using JdbcRowSet is that it enables the ResultSet object to be used as a JavaBeans component. This component then can be used in much the same way we use any other JavaBeans components in an application. Another advantage is that it adds scrollable and updatable capabilities to the ResultSet object. This means we can now move back and forth the list of records fetched from the database through the ResultSet object, which otherwise would not have been easily possible.
CachedRowSet also extends RowSet interface and is a disconnected RowSet that acts as a container for database records and caches them in memory. In a sense we may think of it as a subset of actual records maintained in memory. This reminds us somewhat of the concept of ‘VIEW’ we create from the actual table through SQL. Being disconnected, CachedRowSet is more lightweight. The connection is established for a brief period, only when certain changes need to be reflected on cached data and ultimately propagated back to the actual database. However, this does not mean it lacks the capability of JdbcRowSet; on the contrary, it is equipped with the additional capability of caching. On the downside, connection has to be established every time to reflect any changes, performance may be slower than JdbcRowSet but on the upside it is lightweight and we can get a better efficiency when working with large amount of data.
WebRowSet extends CachedRowSet capabilities but is very special in the sense that in addition to providing all the features of CachedRowSet, it can read and write XML document. This is useful where communication between disparate component is established via XML. This is particularly seen in an enterprise application scenario or in web service communication. Records fetched from the database can be shipped across tiers over internet protocol in an enterprise application without having to maintain an active connection. This RowSet also provide support for synchronized production and consumption of result sets across tier whether be it in an XML format or in a disconnected manner.
FilteredRowSet is an extension of WebRowSet. So it has all the capabilities of WebRowSet as well as CachedRowSet. With this RowSet we can apply filtering criteria to fetch selected rows from the data source so that we can work with the relevant data. This RowSet object paves the way for a disconnected yet live filtering mechanism without firing a query into the database every time. We can limit what we want to visualize from the record list without the involvement of a database connection. It is something like using the WHERE clause without writing an SQL.
JoinRowSet also an extends WebRowSet. In addition to the capabilities of WebRowSet and CachedRowSet, we can perform a SQL JOIN operation without connecting to a data source. This RowSet enables us to create SQL JOIN between RowSet objects. Related records from different RowSets can be combined to form this RowSet object.
A Sample Code for JdbcRowSet
This example shows how to create JdbcRowSet objects and perform some of the CRUD operations with the help of Sun’s JdbcRowSet implementation class.
MySQL table for the code below:
CREAT TABLE emp(empId int, empName varchar(20), phone varchar(20), email varchar(20), salary float, desig varchar(20), primary key(empId));
Import Sun’s JdbcRowSet implementation class and other packages…
import com.sun.rowset.JdbcRowSetImpl;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.rowset.JdbcRowSet;
public class JdbcRowSetDemo{
private JdbcRowSet rowSet = null;
JdbcRowSet object created in the constructor…
public JdbcRowSetDemo() {
try {
Class.forName("com.mysql.jdbc.Driver");
rowSet = new JdbcRowSetImpl();
rowSet.setUrl("jdbc:mysql://localhost/hr");
rowSet.setUsername(“user1”);
rowSet.setPassword(“secret”);
rowSet.setCommand("SELECT * FROM emp");
rowSet.execute();
} catch (ClassNotFoundException | SQLException ex) {
ex.printStackTrace();
}
}
Records fetched from the data source and necessary formatting done for the purpose of viewing in console.
public void showEmployees() {
try {
ResultSetMetaData metaData = rowSet.getMetaData();
int noOfCols = metaData.getColumnCount();
System.out.println("Employees");
for (int i = 1; i <= noOfCols; i++) {
System.out.printf("%-20st", metaData.getColumnName(i));
}
System.out.println();
while (rowSet.next()) {
for (int i = 1; i <= noOfCols; i++) {
System.out.printf("%-20st", rowSet.getObject(i));
}
System.out.println();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
Demonstrating how to insert a new record…
public void addEmployee(int id, String name, String phone, String email, float salary, String designation) {
try {
rowSet.moveToInsertRow();
rowSet.updateInt("empId", id);
rowSet.updateString("empName", name);
rowSet.updateString("phone", phone);
rowSet.updateString("email", email);
rowSet.updateFloat("salary", salary);
rowSet.updateString("desig", designation);
rowSet.insertRow();
rowSet.first();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
Demonstrating how to delete yjr last record…
public void deleteLastEmployee(){
try {
rowSet.last();
rowSet.deleteRow();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
For CachedRowSet example…
Implementing CachedRowSet is also similar, the only significant changes to be made in the above code are as follows.
import com.sun.rowset.CachedRowSetImpl; ... private CachedRowSet rowSet = null; ... rowSet = new CachedRowSetImpl(); // replace rowSet = new JdbcRowSetImpl(); in the constructor ...
Change addEmployee function as follows.
public void addEmployee(int id, String name, String phone, String email, float salary, String designation) {
try {
rowSet.moveToInsertRow();
rowSet.updateInt("empId", id);
rowSet.updateString("empName", name);
rowSet.updateString("phone", phone);
rowSet.updateString("email", email);
rowSet.updateFloat("salary", salary);
rowSet.updateString("desig", designation);
rowSet.insertRow();
rowSet.first();
rowSet.acceptChanges(); //function to propagate changes to the database
} catch (SQLException ex) {
ex.printStackTrace();
}
}
Change deleteLastEmployee function as follows.
public void deleteLastEmployee(){
try {
rowSet.last();
rowSet.deleteRow();
rowSet.acceptChanges(); //function to propagate changes to the database
} catch (SQLException ex) {
ex.printStackTrace();
}
}
Main function to test the above code…
...
public static void main(String[] args) {
JdbcRowSetDemo d1=new JdbcRowSetDemo();
d1.showEmployees();
//d1.addEmployee(24, "Hannible", "983765789", "han@some.com", 43000, "DBA");
//d1.deleteLastEmployee();
//d1.showEmployees();
//CachedRowSetDemo d2=new CachedRowSetDemo();
//d2.showEmployees();
//d2.addEmployee(25, "Thor", "12345679", "thor@somewhere.com", 63000, "System Analyst");
//d2.showEmployees();
//d2.deleteLastEmployee();
//d2.showEmployees();
}
Conclusion
RowSet objects are derived from the ResultSet interface; as a result the capabilities of ResultSet objects are inherently derived. But RowSet objects are special for their new capabilities. The implementation classes of the RowSet interface helps lose much of the boilerplate code needed for database programming. The code is not only cleaner but also efficient. The limitation of ResultSet is reduced to a great extent if we use RowSet. This excellent feature can be actually realized and appreciated when we start using this feature in JDBC programming.
