Scrollable result sets are powerful, but they have a major drawback. You need to keep the database connection open during the entire user interaction. However, a user can walk away from the computer for a long time, leaving the connection occupied. That is not good—database connections are scarce resources. In this situation, use a row set. The RowSet interface extends the ResuttSet interface, but row sets don’t have to be tied to a database connection.
Row sets are also suitable if you need to move a query result to a different tier of a complex application, or to another device such as a cell phone. You would never want to move a result set—its data structures can be huge, and it is tethered to the database connection.
1. Constructing Row Sets
The javax.sqt.rowset package provides the following interfaces that extend the RowSet interface:
- A CachedRowSet allows disconnected operation. We will discuss cached row sets in the following section.
- A WebRowSet is a cached row set that can be saved to an XML file. The XML file can be moved to another tier of a web application where it is opened by another WebRowSet object.
- The FitteredRowSet and JoinRowSet interfaces support lightweight operations on row sets that are equivalent to SQL SELECT and JOIN operations. These operations are carried out on the data stored in row sets, without having to make a database connection.
- A JdbcRowSet is a thin wrapper around a ResuttSet. It adds useful methods from the RowSet interface.
As of Java 7, there is a standard way for obtaining a row set:
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();
There are similar methods for obtaining the other row set types.
2. Cached Row Sets
A cached row set contains all data from a result set. Since CachedRowSet is a subinterface of the ResuttSet interface, you can use a cached row set exactly as you would use a result set. Cached row sets confer an important benefit: You can close the connection and still use the row set. As you will see in our sample program in Listing 5.4, this greatly simplifies the implementation of interactive applications. Each user command simply opens the database connection, issues a query, puts the result in a cached row set, and then closes the database connection.
It is even possible to modify the data in a cached row set. Of course, the modifications are not immediately reflected in the database; you need to make an explicit request to accept the accumulated changes. The CachedRowSet then reconnects to the database and issues SQL statements to write the accumulated changes.
You can populate a CachedRowSet from a result set:
ResuttSet result = . .
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet(); crs.populate(result);
conn.close(); // now OK to close the database connection
Alternatively, you can let the CachedRowSet object establish a connection automatically. Set up the database parameters:
crs.setURL(“jdbc:derby://localhost:1527/COREJAVA”);
crs.setUsername(“dbuser”);
crs.setPassword(“secret”);
Then set the query statement and any parameters:
crs.setCommand(“SELECT * FROM Books WHERE Publisher_ID = ?”);
crs.setString(1, publisherId);
Finally, populate the row set with the query result:
crs.execute();
This call establishes a database connection, issues the query, populates the row set, and disconnects.
If your query result is very large, you would not want to put it into the row set in its entirety. After all, your users will probably only look at a few rows. In that case, specify a page size:
CachedRowSet crs = . . .;
crs.setCommand(command);
crs.setPageSize(20);
…
crs.execute();
Now you will only get 20 rows. To get the next batch of rows, call
crs.nextPage();
You can inspect and modify the row set with the same methods you use for result sets. If you modified the row set contents, you must write it back to the database by calling
crs.acceptChanges(conn);
or
crs.acceptChanges();
The second call works only if you configured the row set with the information required to connect to a database (such as the URL, user name, and password).
In Section 5.6.2, “Updatable Result Sets,” on p. 325, you saw that not all result sets are updatable. Similarly, a row set that contains the result of a complex query will not be able to write its changes back to the database. You should be safe if your row set contains data from a single table.
Another complexity arises if the data in the database have changed after you populated the row set. This is clearly a sign of trouble that could lead to inconsistent data. The reference implementation checks whether the original row set values (that is, the values before editing) are identical to the current values in the database. If so, they are replaced with the edited values; otherwise, a SyncProviderException is thrown and none of the changes are written. Other implementations may use other strategies for synchronization.
Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.