18 Row Set

18.1 Introduction

A row set is an object which encapsulates a set of rows. These rows are accessible though the javax.sql.RowSet interface. This interface supports component models of development, like JavaBeans, and is part of JDBC optional package by JavaSoft.

Three kinds of row set are supported by JavaSoft:

• Cached row set

• JDBC row set

• Web row set

The RowSet interface provides a set of properties which can be altered to access the data in the database through a single interface. It supports properties and events which forms the core of JavaBeans. It has various properties like connect string, user name, password, type of connection, the query string itself, and also the parameters passed to the query. The following code executes a simple query:

...
rowset.setUrl ("jdbc:oracle:oci:@");
rowset.setUsername ("SCOTT");
rowset.setPassword ("TIGER");
rowset.setCommand (
  "SELECT empno, ename, sal FROM emp WHERE empno = ?");

// empno of employee name "KING"
rowset.setInt (1, 7839); 
...

In this example, the URL, user name, password, SQL query, and bind parameter required for the query are set as the command properties to retrieve the employee name and salary. Also, the row set would contain empno, ename, and sal for the employee with the empno as 7839 and whose name is KING.

This release of JDBC provides an early implementation of JSR-114 WebRowSet (Public Draft). Its specification is available at the Web site http://jcp.org/aboutJava/communityprocess/first/jsr114/index.html. The WebRowSet API supports the production and consumption of result sets, and their synchronization with the data source, both in XML format and in disconnected fashion. This allows result sets to be shipped across tiers and over Internet protocols.

18.2 Row Set Setup and Configuration

The classes for the row set feature are found in a separate archive, ocrs12.jar. This file is located in the $ORACLE_HOME/jdbc directory. To use row set, you need to include this archive in your CLASSPATH.

For Unix (sh), the command is:

CLASSPATH=$CLASSPATH:$ORACLE_HOME/jdbc/lib/ocrs12.jar
export CLASSPATH

For Windows, the command is:

set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\ocrs12.jar

This might also be set in the project properties in case you are using an IDE like JDeveloper.

Oracle row set implementations are in the oracle.jdbc.rowset package. Import this package to use any of the Oracle row set implementations.

OracleCachedRowSet, OracleJDBCRowSet, and OracleWebRowset classes all implement the javax.sql.RowSet interface, which extends java.sql.ResultSet. Row set not only provides the interfaces of result set, but also some of the properties of the java.sql.Connection and java.sql.PreparedStatement interfaces. Connections and prepared statements are totally abstracted by this interface. Both OracleCachedRowSet and OracleWebRowSet are serializable. They implement the java.io.Serializable interface, which enables them to be moved across the network or JVM sessions.

18.3 Runtime Properties for Row Set

Typically, static properties for the applications can be set for a row set at the development time and the rest of the properties which are dynamic (are dependent on runtime) can be set at the runtime. The static properties may include the connection URL, username, password, connection type, concurrency type of the row set, or the query itself. The runtime properties, like the bind parameters for the query, could be bound at runtime. Scenarios where the query itself is a dynamic property is also common.

18.4 Row Set Listener

The row set feature supports multiple listeners to be registered with the RowSet object. Listeners can be registered using the addRowSetListener() method and unregistered through the removeRowSetListener() method. A listener should implement the javax.sql.RowSetListener interface to register itself as the row set listener. Three types of events are supported by the RowSet interface:

1. cursorMoved event : Generated whenever there is a cursor movement, which occurs when the next() or previous() methods are called

2. rowChanged event : Generated when a new row is inserted, updated, or deleted from the row set

3. rowsetChanged event : Generated when the whole row set is created or changed

The following code shows the registration of a row set listener:

MyRowSetListener rowsetListener = 
  new MyRowSetListener ();
// adding a rowset listener.
rowset.addRowSetListener (rowsetListener);


// implementation of a rowset listener
public class MyRowSetListener implements RowSetListener
{
  public void cursorMoved(RowSetEvent event) 
  {
   // action on cursor movement
  }

  public void rowChanged(RowSetEvent event) 
  {
    // action on change of row
  }

  public void rowSetChanged(RowSetEvent event) 
  {
    // action on changing of rowset
  }
}// end of class MyRowSetListener

Applications which handle only a few events can implement only the required events by using the OracleRowSetAdapter class, which is an abstract class with empty implementation for all the event handling methods.

In the following code, only the rowSetChanged event is handled. The remaining events are not handled by the application.

rowset.addRowSetListener (new OracleRowSetAdapter () 
  {
     public void rowSetChanged(RowSetEvent event)
     {
       // your action for rowsetChanged
     }
  } 
 );

18.5 Traversing Through the Rows

The RowSet interface provides various methods to traverse through the row, including absolute(), beforeFirst(), afterLast(), and so on. These methods are inherited directly from the java.sql.ResultSet interface. The RowSet interface could be used as a ResultSet interface for retrieval and updating of data. The RowSet interface provides an optional way to implement a scrolling and updatable result set if they are not provided by the result set implementation.

18.6 Cached Row Set

A cached row set is a row set implementation where the rows are cached and the row set does not maintain an active connection to the database. A cached row set is a serializable, disconnect row set, implementing the standard javax.sql.RowSet interface. OracleCachedRowSet is the Oracle implementation of CachedRowSet, and can interoperate with Sun's reference implementation.

In the following code, an OracleCachedRowSet object is created and the connection URL, username, password, and the SQL query for the row set is set as properties. The RowSet object is populated through the execute method. After the execute call, the RowSet object can be used as a java.sql.ResultSet object to retrieve, scroll, insert, delete, or update data.

...
RowSet rowset = new OracleCachedRowSet ();
rowset.setUrl ("jdbc:oracle:oci:@");
rowset.setUsername ("SCOTT");
rowset.setPassword ("TIGER");
rowset.setCommand ("SELECT empno, ename, sal FROM emp");
rowset.execute ();
while (rowset.next ())
{
  System.out.println ("empno: " +rowset.getInt (1));
  System.out.println ("ename: " +rowset.getString (2));
  System.out.println ("sal: "   +rowset.getInt (3));
}
...

To populate a CachedRowSet object with a query, complete the following steps:

1. Instantiate OracleCachedRowSet.

2. Set connection Url, Username, Password, connection type (optional), and the query string as properties for the RowSet object.

   Invoke the execute() method to populate the RowSet object. Invoking execute() executes the query set as a property on this row set.

OracleCachedRowSet rowset = new OracleCachedRowSet ();
  rowset.setUrl ("jdbc:oracle:oci:@");
  rowset.setUsername ("SCOTT");
  rowset.setPassword ("TIGER");
  rowset.setCommand ("SELECT empno, ename, sal FROM emp");
  rowset.execute ();

CachedRowSet can be populated with the existing ResultSet object, using the populate() method.

To populate a CachedRowSet object with an already available result set, complete the following steps:

1. Instantiate OracleCachedRowSet.

2. Pass the already available ResultSet object to the populate() method to populate the RowSet object.

// Executing a query to get the ResultSet object.
ResultSet rset = pstmt.executeQuery ();

OracleCachedRowSet rowset = new OracleCachedRowSet ();
// the obtained ResultSet object is passed to the
// populate method to populate the data in the 
// rowset object.
rowset.populate (rset);

In the above example, a ResultSet object is obtained by executing a query and the retrieved ResultSet object is passed to the populate() method of the cached row set to populate the contents of the result set into cached row set.

All the interfaces provided by the ResultSet interface are implemented in RowSet. The following code shows how to scroll through a row set:

/**
 *  Scrolling forward, and printing the empno in 
 *  the order in which it was fetched.
 */
// going to the first row of the rowset
rowset.beforeFirst ();
while (rowset.next ())
  System.out.println ("empno: " +rowset.getInt (1));

In the example above, the cursor position is initialized to the position before the first row of the row set by the beforeFirst() method. The rows are retrieved in forward direction using the next() method.

/**
  * Make rowset updatable
  */
rowset.setReadOnly (false); 
// Synchronize rowset with database
rowset.acceptChanges (); 

/**
 *  Scrolling backward, and printing the empno in 
 *  the reverse order as it was fetched.
 */
//going to the last row of the rowset
rowset.afterLast ();
while (rowset.previous ())
  System.out.println ("empno: " +rowset.getInt (1));

In the above example, the cursor position is initialized to the position after the last row of the RowSet. The rows are retrieved in reverse direction using the previous() method of RowSet.

Inserting, updating, and deleting rows are supported by the row set feature as they are in the result set feature. In order to make the rowset updatable, you must invoke setReadOnly(false); and acceptChanges().

The following code illustrates the insertion of a row at the fifth position of a row set:

/**
 * Inserting a row in the 5th position of the rowset.
 */
// moving the cursor to the 5th position in the rowset
if (rowset.absolute(5))
{
  rowset.moveToInsertRow ();
  rowset.updateInt (1, 193);
  rowset.updateString (2, "Ashok");
  rowset.updateInt (3, 7200);

  // inserting a row in the rowset
  rowset.insertRow ();

  // Synchronizing the data in RowSet with that in the
  // database.
  rowset.acceptChanges ();
}

In the above example, a call to the absolute() method with a parameter 5 takes the cursor to the fifth position of the row set and a call to the moveToInsertRow() method creates a place for the insertion of a new row into the row set. The updateXXX() methods are used to update the newly created row. When all the columns of the row are updated, the insertRow() is called to update the row set. The changes are committed through acceptChanges() method.

The following code shows how an OracleCachedRowSet object is serialized to a file and then retrieved:

// writing the serialized OracleCachedRowSet object
{
  FileOutputStream fileOutputStream = 
     new FileOutputStream ("emp_tab.dmp");
  ObjectOutputStream ostream = new 
    ObjectOutputStream (fileOutputStream);
  ostream.writeObject (rowset);
  ostream.close ();
  fileOutputStream.close ();
}

// reading the serialized OracleCachedRowSet object
{
  FileInputStream fileInputStream = new 
     FileInputStream ("emp_tab.dmp");
  ObjectInputStream istream = new 
     ObjectInputStream (fileInputStream);
  RowSet rowset1 = (RowSet) istream.readObject ();
  istream.close ();
  fileInputStream.close ();
}

In the above example, a FileOutputStream object is opened for a emp_tab.dmp file, and the populated OracleCachedRowSet object is written to the file using ObjectOutputStream. This is retrieved using FileInputStream and the ObjectInputStream objects.

OracleCachedRowSet takes care of the serialization of non-serializable form of data like InputStream, OutputStream, BLOBS and CLOBS. OracleCachedRowSets also implements meta data of its own, which could be obtained without any extra server roundtrip. The following code shows how you can obtain meta data for the row set:

ResultSetMetaData metaData = rowset.getMetaData ();
int maxCol = metaData.getColumnCount ();
for (int i = 1; i <= maxCol; ++i)
   System.out.println ("Column (" + i +") "
     +metaData.getColumnName (i));

The above example illustrates how to retrieve a ResultSetMetaData object and print the column names in the RowSet.

Since the OracleCachedRowSet class is serializable, it can be passed across a network or between JVMs, as done in Remote Method Invocation (RMI). Once the OracleCachedRowSet class is populated, it can move around any JVM, or any environment which does not have JDBC drivers. Committing the data in the row set (through the acceptChanges() method) requires the presence of JDBC drivers.

The complete process of retrieving the data and populating it in the OracleCachedRowSet class is performed on the server and the populated row set is passed on to the client using suitable architectures like RMI or Enterprise Java Beans (EJB). The client would be able to perform all the operations like retrieving, scrolling, inserting, updating, and deleting on the row set without any connection to the database. Whenever data is committed to the database, the acceptChanges() method is called which synchronizes the data in the row set to that in the database. This method makes use of JDBC drivers which require the JVM environment to contain JDBC implementation. This architecture would be suitable for systems involving a Thin client like a Personal Digital Assistant (PDA) or a Network Computer (NC).

After populating the CachedRowSet object, it can be used as a ResultSet object or any other object which can be passed over the network using RMI or any other suitable architecture.

Some of the other key-features of cached row set are the following:

• Cloning a row set

• Creating a copy of a row set

• Creating a shared copy of a row set

18.7 JDBC Row Set

A JDBC row set is another row set implementation. It is a simple, non-serializable connected row set which provides JDBC interfaces in the form of a Bean interface. Any call to JDBCRowSet percolates directly to the JDBC interface. The usage of the JDBC interface is the same as any other row set implementation.

Table 18-1 shows how the JDBCRowSet interface differs from CachedRowSet interface.

The JDBC row set is a connected row set which has a live connection to the database and all the calls on the JDBC row set are percolated to the mapping call in JDBC connection, statement, or result set. A cached row set does not have any connection to the database open.

JDBC row set requires the presence of JDBC drivers where a cached row set does not require JDBC drivers during manipulation, but during population of the row set and the committing the changes of the row set.

The following code shows how a JDBC row set is used:

RowSet rowset = new OracleJDBCRowSet ();
rowset.setUrl ("java:oracle:oci:@");
rowset.setUsername ("SCOTT");
rowset.setPassword ("TIGER");
rowset.setCommand (
  "SELECT empno, ename, sal FROM emp");
rowset.execute ();
while (rowset.next ())
{
  System.out.println ("empno: " + rowset.getInt (1));
  System.out.println ("ename: " 
    + rowset.getString (2));
  System.out.println ("sal: " + rowset.getInt (3));
}

In the above example, the connection URL, username, password, and the SQL query is set as the connection properties to the row set and the query is executed through the execute() method and the rows are retrieved and printed.