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
Laxmi
Guest
Laxmi

Hi Jonathan,

Your post was very helpful. Can we add DataResultSet values in to CSV? We are using the OpenCSV lib.

Carsten
Guest
Carsten

Hi

thanks – your post helped me quickly when looping over a DataResultSet.
I had used !isEmpty as a 2nd param of the for loop, which was wrong, and isRowPresent is correct.
I guess, Empty is only used to check if the CURRENT row has any data?

Thanks

Ananda Roy
Guest
Ananda Roy

How can I populate the oracle.stellent.ridc.model.DataResultSet() with values from a java client.I need to do this because I need to populate this resultset from ADF.

oracle.stellent.ridc.model.DataResultSet() is different from intradoc.data resultset().

I cannot populate this resultset with the methods shown above.

Any help will be appreciated.

webmonkeymagic
Guest
webmonkeymagic

I have a resultset that retrieves database rows but i want to add/remove rows and resort before returning it to the user. Is this something I should do directly in a dataresultset or would it be more/less efficient to copy to an array or hashtable for manipulating the data instead? I’m concerned about scalability & performance.

jonathanhult
Guest
jonathanhult

webmonkeymagic That may depend on how many fields/columns you have. However, I would keep it all in a DataResultSet object. Adding rows and sorting are exactly the type of operations a data structure like DataResultSet was built to handle.
There are classes and methods (such as those in intradoc.data.ResultSetUtils) to help you sort the rows.

wpDiscuz