ResultSet versus DataResultSet

ResultSet and DataResultSet are both Java classes which allow us to work with data sets. Most often they are used to work with either metadata or database tables. However, they are not limited to this. Think of them as a data structure that can have a very large number of columns and rows. To better understand these two classes, let’s examine them individually.

Let’s first examine the ResultSet interface. “The interface ResultSet creates a generic mechanism for managing database tables.” It provides methods such as first, getFieldInfo, getFieldName, getNumFields, getStringValue, isEmpty, isRowPresent and next.

The DataResultSet class is, “Used as the database independent representation of a table. It implements the intradoc.data.ResultSet interface so that database queries and internal application tables can be handled through the same interface.” DataResultSet provides methods such as addRow, appendFields, copy, createRow, deleteRow, getCurrentRow, getCurrentRowValues, getNumRows, getStringValue, insertRowAt, merge, setCurrentValue and setRowValues.

DataResultSets can be instantiated by using the default DataResultSet constructor. This will create a DataResultSet with no columns/fields. These can be added using the appendFields method. Or, a DataResultSet can be instantiated using the following constructor: DataResultSet(java.lang.String[] fieldNames). This constructor is handy because you can pass in the columns/fields right when the DataResultSet is instantiated.

ResultSets can be returned by methods in the Workspace class such as createResultSet and createResultSetSQL or from the DataBinder class using getResultSet. These can be converted to DataResultSets by using the copy method.

One very interesting thing to note, is that ResultSets are simply a reference to a cursor in the database. A great explanation of this, originally posted here by Fabian Scherpenzeel, Oracle Principal Consultant E2.0, can be found below.

A ResultSet is an object that has a direct reference to a cursor in the database (the data is not retrieved from the database yet*).
If you then create another ResultSet using the same database connection, the cursor changes in the database.

To counteract this behaviour you would need to copy over the data from the database into an object that stores the data in memory.
To do this, create a DataResultSet (Container Object for holding ResultSets in memory) and copy over the contents of the ResultSet.

See example below:

Note: using externalized queries is always a good idea and you can also use them if you have no parameters.

regards,
Fabian

* Some information may have been copied over, but this is not the point.

For the above reason, I’d almost always recommend using a DataResultSet instead of a ResultSet. In addition. there are several extra methods with the DataResultSet class that can be very helpful.

I am also going to briefly covering ResultSet looping as I see questions asked about it quite frequently. Generally, when we have a ResultSet/DataResultSet, we are interested in looping over all the results (e.g. SearchResults or data from a table). Looping can be achieved for ResultSets/DataResultSets using the following code:

If you have any questions about ResultSets or DataResultSets, feel free to ask in the comment section below.

Leave a Reply

8 Comments on "ResultSet versus DataResultSet"

Notify of
avatar
Sort by:   newest | oldest | most voted