As you have seen, the next method of the ResultSet interface iterates over the rows in a result set. That is certainly adequate for a program that needs to analyze the data. However, consider a visual data display that shows a table or query results (such as Figure 5.4). You usually want the user to be able to move both forward and backward in the result set. In a scrollable result, you can move forward and backward through a result set and even jump to any position.
Furthermore, once users see the contents of a result set displayed, they may be tempted to edit it. In an updatable result set, you can programmatically update entries so that the database is automatically updated. We discuss these capabilities in the following sections.
1. Scrollable Result Sets
By default, result sets are not scrollable or updatable. To obtain scrollable result sets from your queries, you must obtain a different Statement object with the method
Statement stat = conn.createStatement(type, concurrency);
For a prepared statement, use the call
PreparedStatement stat = conn.prepareStatement(command, type, concurrency);
The possible values of type and concurrency are listed in Tables 5.6 and 5.7. You have the following choices:
- Do you want the result set to be scrollable? If not, use ResuttSet .TYPE_FORWARD_ONLY.
- If the result set is scrollable, do you want it to reflect changes in the database that occurred after the query that yielded it? (In our discussion, we assume the TYPE_SCROLL_INSENSITIVE setting for scrollable result sets. This assumes that the result set does not “sense” database changes that occurred after execution of the query.)
- Do you want to be able to update the database by editing the result set? (See the next section for details.)
For example, if you simply want to be able to scroll through a result set but don’t want to edit its data, use
Statement stat = conn.createStatement(
ResuttSet.TYPE_SCROLL_INSENSITIVE, ResuttSet.CONCUR_READ_ONLY);
All result sets that are returned by calls
ResuttSet rs = stat.executeQuery(query);
are now scrollable. A scrollable result set has a cursor that indicates the current position.
Scrolling is very simple. Use
if (rs.previous()) . . .;
to scroll backward. The method returns true if the cursor is positioned on an actual row, or false if it is now positioned before the first row.
You can move the cursor backward or forward by any number of rows with the call
rs.retative(n);
If n is positive, the cursor moves forward. If n is negative, it moves backward. If n is zero, the call has no effect. If you attempt to move the cursor outside the current set of rows, it is set to point either after the last row or before the first row, depending on the sign of n. Then, the method returns fatse and the cursor does not move. The method returns true if the cursor is positioned on an actual row.
Alternatively, you can set the cursor to a particular row number:
rs.absolute(n);
To get the current row number, call int currentRow = rs.getRow();
The first row in the result set has number 1. If the return value is 0, the cursor is not currently on a row—it is either before the first row or after the last row.
The convenience methods first, last, beforeFirst, and afterLast move the cursor to the first, to the last, before the first, or after the last position.
Finally, the methods isFirst, isLast, isBeforeFirst, and isAfterLast test whether the cursor is at one of these special positions.
Using a scrollable result set is very simple. The hard work of caching the query data is carried out behind the scenes by the database driver.
2. Updatable Result Sets
If you want to edit the data in the result set and have the changes automatically reflected in the database, create an updatable result set. Updatable result sets don’t have to be scrollable, but if you present data to a user for editing, you usually want to allow scrolling as well.
To obtain updatable result sets, create a statement as follows:
Statement stat = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCURJJPDATABLE);
The result sets returned by a call to executeQuery are then updatable.
For example, suppose you want to raise the prices of some books, but you don’t have a simple criterion for issuing an UPDATE statement. Then, you can iterate through all books and update prices based on arbitrary conditions.
String query = “SELECT * FROM Books”;
ResultSet rs = stat.executeQuery(query);
while (rs.next())
{
if (. . .)
{
double increase = . . .;
double price = rs.getDouble(“Price”);
rs.updateDouble(“Price”, price + increase);
rs.updateRow(); // make sure to call updateRow after updating fields
}
}
There are updateXrx methods for all data types that correspond to SQL types, such as updateDouble, updateString, and so on; specify the name or the number of the column (as with the getXxx methods), then the new value for the field.
The updateXxx method changes only the row values, not the database. When you are done with the field updates in a row, you must call the updateRow method. That method sends all updates in the current row to the database. If you move the cursor to another row without calling updateRow, this row’s updates are discarded from the row set and never communicated to the database. You can also call the cancelRowUpdates method to cancel the updates to the current row.
The preceding example shows how to modify an existing row. If you want to add a new row to the database, first use the moveToInsertRow method to move the cursor to a special position, called the insert row. Then, build up a new row in the insert row position by issuing updateXxx instructions. When you are done, call the insertRow method to deliver the new row to the database. When you are done inserting, call moveToCurrentRow to move the cursor back to the position before the call to moveToInsertRow. Here is an example:
rs.moveToInsertRow();
rs.updateString(“Title”, title);
rs.updateString(“ISBN”, isbn);
rs.updateString(“Publisher_Id”, pubid);
rs.updateDouble(“Price”, price);
rs.insertRow();
rs.moveToCurrentRow();
Note that you cannot influence where the new data is added in the result set or the database.
If you don’t specify a column value in the insert row, it is set to a SQL NULL. However, if the column has a NOT NULL constraint, an exception is thrown and the row is not inserted.
Finally, you can delete the row under the cursor:
rs.deteteRow();
The deteteRow method immediately removes the row from both the result set and the database.
The updateRow, insertRow, and deteteRow methods of the ResuttSet interface give you the same power as executing UPDATE, INSERT, and DELETE SQL statements. However, Java programmers might find it more natural to manipulate the database contents through result sets than by constructing SQL statements.
Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.