Java Oracle Database Development - page 51

RowSets

At the heart of every RowSet is a ResultSet . A RowSet can do everything that a ResultSet can and more, because the RowSet interface extends the ResultSet interface. One of the extensions is that a RowSet is a JavaBean component; this has important benefits. We can use RowSets together with visual JavaBean development tools to develop database applications without needing to do much (or any) coding directly with JDBC. We can also register listeners, such as windows , with a RowSet so they are notified to update themselves whenever data changes.

There are two general types of RowSets . Three specific implementations have been proposed for these so far:

• Connected RowSets: JDBCRowSet

• Disconnected RowSets: CachedRowSet , WebRowSet

A JDBC RowSet is a connected RowSet . It is connected at all times in the same way that a ResultSet is because it is, in effect, just a thin wrapper on ResultSet that provides JavaBean functionality. It is not serializable.

A cached RowSet is a disconnected RowSet . A cached RowSet connects initially to obtain its data but then disconnects. The ResultSet data is cached in memory. Changes can be made to the RowSet in memory, using the same update method that we used for updateable ResultSets . When we apply the changes to the database, the RowSet will reconnect to the database.

A cached RowSet is also serializable, which means that we can save it in a file or send it to another computer. We can browse it and change the RowSet using the same interface as a ResultSet , even though we no longer have access to a JDBC driver or the database. After we're finished making changes, we can serialize it again and send it back to a computer with access to the database in order to apply the changes we made off-line.

A WebRowSet is an extension of a CachedRowSet that can present its data as an XML document.

At the time of this writing, Sun has only Early Access implementations of each proposed type of RowSet ; they are described as experimental and unsupported technology.

Oracle 9i includes an implementation of two types: a JDBC RowSet , OracleJDBCRowSet ; and a cached RowSet , OracleCachedRowSet .

We will examine only the two Oracle implementations here.

RowSet Setup

To use Oracle's RowSet implementations we first need to add another entry to our CLASSPATH.

CLASSPATH=

current_entries

;{ORACLE_HOME}/ jdbc/lib/ocrs12.zip

In our source code we need to add two or three imports. To be able to access the Oracle RowSet implementations we need to add

import oracle.jdbc.rowset.*;

The RowSet interface is defined in the javax.sql package, so we'll need to add that too.

import javax.sql.*;

If we are going to be serializing RowSets to files, we'll also need to add

import java.io.*;

What we do next depends on which RowSet we'll be using. We'll take a look at both cached and JDBC RowSet examples.

Using a Cached RowSet

Assuming that our environment is set up and our source code imports the appropriate packages, the next step in using a RowSet is instantiating it. We instantiate Oracle's cached RowSet implementation, OracleCachedRowSet , like any other class, using the default constructor.

OracleCachedRowSet rowset = new OracleCachedRowSet();

We can populate the RowSet in two ways. We can use an existing ResultSet and call the populate() method. Assuming that we have a ResultSet rs , we could call populate like this:

rowset.populate(rs);

More typically, however, we provide the RowSet with the information it needs to connect to the database and the SQL query we want it to use. Because RowSet is a JavaBeans component, we provide it with information by calling setter methods. In particular, we need to set the database URL, username, and password. According to JavaBeans conventions, these methods start with set , followed by the property name , with the first letter of each property capitalized. Username and password each count as single words; the query goes into a property called command .

rowset.setUrl ("jdbc:oracle:thin:@noizmaker:1521:osiris");
rowset.setUsername ("david");
rowset.setPassword ("bigcat");
rowset.setCommand ("SELECT COL1 FROM TEST");

To connect and populate the RowSet , we call the execute() method.

rowset.execute();

Once we've done this, we can navigate the rowset exactly as though it were a ResultSet .

while(rowset.next())
{
   System.out.println(rowset.getInt(1));
}

In addition (or instead), we can serialize it and send it over the network, write it to a file and email it to a remote user , or download it to a handheld computer. The RowSet can be deserialized—turned back into an object—and used, even if there is no access to the database or JDBC. After making changes, a remote user could reserialize the updated RowSet and send it back so that the changes can be applied to the database.

To serialize a RowSet and write it to a file, we need to wrap FileOutputStream with the ObjectOutputStream class and call the ObjectOutputStream 's writeObject() method.

String filename = "test.ser";
FileOutputStream outStream = new FileOutputStream(filename);
ObjectOutputStream objOut = new ObjectOutputStream(outStream);
objOut.writeObject(rowset);
objOut.close();

To deserialize a RowSet —read a serialized RowSet into an object for use in a program—we use FileInputStream and ObjectInputStream and call the ObjectInputStream 's readObect() method.

FileInputStream inStream = new FileInputStream(filename);
ObjectInputStream objIn = new ObjectInputStream(inStream);
RowSet newrowset = (RowSet) objIn.readObject();
objIn.close();

Here, we've read the RowSet into a new object, newrowset . We can navigate this just like the original RowSet we've effectively cloned. Note that it preserves the state of the RowSet at the time we serialized; if we read through the original RowSet and left the cursor pointing after the last row, we'll need to move it back to the beginning by calling the RowSet 's beforeFirst() method if we want to read it from beginning to end again.

newrowset.beforeFirst();
for(int i = 0; newrowset.next(); i++)
{
   System.out.println("row " + i +": " + newrowset.getInt(1));
}

Updating through a Cached RowSet

By default, a RowSet is scrollable but is set to read-only. To be able to update, we need to set the readOnly property to false. (There is also a concurrency property that we can set to ResultSet.CONCUR_UPDATABLE , but this has no effect in OracleCachedRowSet .) Notice that we can do this even with a RowSet that was previously serialized.

newrowset.setReadOnly(false);

Now we can perform an update exactly as we did with an updateable ResultSet , position the cursor, update the column (or columns ), then update the row.

newrowset.last();
newrowset.updateInt(1,9999);
newrowset.updateRow();

We need to do one more thing to actually connect and store the changes in the database, call the OracleCachedRowSet 's acceptChanges() method.

newrowset.acceptChanges();

To execute this method, our program will once again need to use a JDBC driver and make a connection to the database. (Presumably our program would have different startup options that determine whether or not it should try to connect to the database.) After calling acceptChanges() , the database will no longer be serializable. If you need to update and serialize a number of times, you should call acceptChanges() only once, at the end.

In order for RowSets to be updateable they must comply with the same restrictions that apply to updateable ResultSets :

• We can select from only a single table.

• We cannot use an ORDER BY clause.

• We cannot use SELECT *. (But we can use a table alias, such as SELECT T.* FROM tablename T.)

• Our SELECT statement can include only columns from the table, not expressions or functions.

If we want to be able to perform inserts , we must also ensure that we meet the following condition:

• Our SELECT statement must include any columns that are necessary for the insert, such as key and non-nullable columns.

Finally, we also must remain attentive to the fact that cached RowSets are held in memory and make sure that they are not too large, especially if they are to be used in small devices, such as PDAs or handheld PCs with limited memory.

JDBC RowSets

A JDBC RowSet , like any RowSet , extends a ResultSet to provide a JavaBean interface. Unlike a CachedRowSet , however, it does not store the ResultSet in memory but instead keeps open a JDBC connection to the database for the life of the RowSet .

A JDBCRowSet is best suited for building applications that use Swing/AWT and other JavaBean components . A JDBCRowSet can potentially allow us to build a live data view. Not only can the user change the data in a JTable view (which is already possible with a ResultSet ), but the view can also be notified of changes in the database so that it can update the view.

As a JavaBean, a JDBCRowSet is more conveniently implemented using a visual builder tool, with a minimum amount of handwritten code.

Applications that include a graphical user interface (GUI) that presents data to a user and allows the user to manipulate it are commonly designed using the Model-View-Controller (MVC) pattern. In this pattern, the Model is a class that represents the data, the View is a class that displays the data, and the Controller is a class the provides the user with a means of manipulating the data by interacting with the display, keyboard, and mouse. Here, we are interested in the interaction of the Model and the View.

Java's toolkit for building graphical interfaces has a class for representing tabular data, JTable , that is well suited to displaying data from a database. The important thing to note about a JTable is that Java's designers intend for it to be used as the view: It does not contain any data. It expects a companion class, a model, to hold the data.

In order for the JTable to be able to obtain data from the model, the model needs to implement an interface, TableModel , that specifies the methods that the table will use to get information from the model. These include such things as: getColumnCount() , getRowCount() , getColumnClass() , getValueAt(int row, int col) , and setValueAt(Object value, int row, int col ). Our model will include a JDBCRowSet that it will use to implement each of these calls. Here is the beginning of the class, including the constructor. Note that we cache a few values, such as the number of rows, ahead of time:

public class RowSetModel extends AbstractTableModel {

int rows;
String[] columnNames = {};
OracleJDBCRowSet rowset = new OracleJDBCRowSet ();
ResultSetMetaData  metaData;

    public RowSetModel()
    {
         try
         {
  rowset.setUrl(
                "jdbc:oracle:thin:@noizmaker:1521:osiris");
              rowset.setUsername("david");
              rowset.setPassword("bigcat");
              rowset.setType(ResultSet.TYPE_SCROLL_SENSITIVE);
              rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);
              rowset.setCommand(
               "SELECT NAME, DESCRIPTION, PRICE FROM PRODUCTS");
              rowset.execute();
              metaData = rowset.getMetaData();
              int numberOfColumns =  metaData.getColumnCount();
              columnNames = new String[numberOfColumns];
              for(int column = 0;
                     column < numberOfColumns; column++)
              {
                  columnNames[column] =
                         metaData.getColumnLabel(column+1);
              }
              rowset.last();
              rows = rowset.getRow();
              fireTableChanged(null);
        }
        catch (SQLException e)
        {
            System.err.println("Caught: " + e);
        }
     }

Because we want our view to be live, we set type to TYPE_SCROLL_ SENSITIVE . And because we want to be able to update values, we need to set the concurrency to CONCUR_UPDATABLE .

Here are the methods we're required to implement. We use strings for all columns—this isn't necessarily recommended, but it works here, thanks to automatic conversions.

public String getColumnName(int column)
{
    if (columnNames[column] != null) {
        return columnNames[column];
    } else {
        return "";
    }
}

public Class getColumnClass(int column)
{
        return String.class;
}

public boolean isCellEditable(int row, int column)
{
    try {
        return metaData.isWritable(column+1);
    }
    catch (SQLException e) {
        return false;
    }
}

public int getColumnCount()
{
    return columnNames.length;
}

public int getRowCount() {
    return rows;
    }

    public Object getValueAt(int aRow, int aColumn)
    {
    String s = null;
    try
    {
    rowset.absolute(aRow+1);
    s = rowset.getString(aColumn+1);
    System.out.println("retrieved: " + s);
    }
    catch (SQLException e)
    {
    System.out.println("Caught: " + e);
    }
    return s;
    }

public void setValueAt(Object value, int row, int column)
{
    try
    {
     rowset.absolute(row+1);
     rowset.updateString(column + 1, (String) value);
     rowset.updateRow();
    }
    catch (SQLException e)
    {
        //     e.printStackTrace()**;
        System.err.println("Update failed: " + e);
    }
}

To create a JTable , our View class will first instantiate the RowSetModel , then will pass this model to the JTable constructor.

public class RowSetView implements RowSetListener {

    public RowSetView ()
    {
        JFrame frame = new JFrame("Table");
        frame.addWindowListener(new WindowAdapter() {
            public void windowClosing(WindowEvent e)
                                   {System.exit(0);}});

        RowSetModel rsm = new RowSetModel();

        // Create the table
        JTable tableView = new JTable(rsm);
        rsm.addRowSetListener((RowSetListener)this);

        JScrollPane scrollpane = new JScrollPane(tableView);
        scrollpane.setPreferredSize(new Dimension(700, 300));

        frame.getContentPane().add(scrollpane);
        frame.pack();
        frame.setVisible(true);
    }

So far, our example will update values in the database if we change them in our data window. The values in the data window will also change when we scroll through them with the cursor if they've changed in the database.

It is possible to register a listener with the RowSet so that we can update our view automatically. First, we need to create a listener.

class MyListener implements RowSetListener {

  public void cursorMoved(RowSetEvent event)
  {
  }
  public void rowChanged(RowSetEvent event)
  {
  }

  public void rowSetChanged(RowSetEvent event)
  {
       fireTableDataChanged();
  }

}

Then we need to register the listener in the constructor.

MyListener rowsetListener =  new MyListener ();
           rowset.addRowSetListener (rowsetListener);

There are still a number of user interface issues that need to be solved in order to fully implement this, however, and we won't consider this further except to note again that there are JavaBean development tools that facilitate building applications from components such as RowSets .