ResultSet Interface

A ResultSet is a table of data, which is a database result set. The result set types, concurrency and holdability were discussed in the previous section. A ResultSet object can be created to scroll, update, and keep the cursors open, when a commit is done:

Statement stmt=connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs=stmt.execute("sql");

A ResultSet has a cursor, which points to the current row. Initially, the cursor points before the first row. The next() method moves the cursor to the next row. The previous() method shifts the cursor to the previous row. The ResultSet interface provides different methods to position the cursor. If the ResultSet is scrollable, then the result set type is TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE and the cursor can be shifted to a specified position. Some of the methods to position a ResultSet cursor are listed in following table:

For an updatable result set, the method moveToInsertRow() moves the cursor to the insert row, which is a buffer, to insert a new row. The cursor can be shifted back to the current row with the method, moveToCurrentRow(). The ResultSet interface has methods, which are used to obtain the position of the cursor, and are listed in following table:

The ResultSet column values are obtained with the help of getter methods. The ResultSet interface has a 'getter' method for each of the Java data types that map to the database data type. If the database data type is mappable to the Java data type, the Java data type is returned. A getter method with a column index position and column name are included for each of the data types. The getter method with the column index position is more efficient. An int column value is retrieved with the index position, and a String column value is retrieved with the column name as follows:

ResultSet rs;
Int intColumnValue=rs.getInt(1);
String stringColumnValue=rs.getString("column name");

The ResultSet interface has updater methods to update column values in a row. An 'updater' method is included for each of the Java data types that map to the database data type. If the ResultSet is updatable, then the column values in a row can be updated, or a new row can be added. To update a row, move the cursor to the row to be updated. For example, shift the cursor to the tenth row. Update a column value with an updater method. For example, update a String column, column1 to the value col1val. Also update the row in the database:

rs.absolue(10);
rs.updateString("column1", "col1val");
rs.updateRow();

The method updateRow() updates the database. To add a new row, shift the cursor to the insert row with the moveToInsertRow() method. Add column values with the updater methods, and insert a row in the database with the insertRow() method. Shift the cursor to the current row with the moveToCurrentRow() method:

rs.moveToInsertRow();
rs.updateString(1, "JDBC4.0");
rs.updateInt(2,16);
rs.updateBoolean(3, true);
rs.insertRow();
rs.moveToCurrentRow();

The current row in a ResultSet can be deleted with the deleteRow() method. A ResultSet object is automatically closed and the associated resources are released when the Statement object that had created the ResultSet object is being closed. However, it is recommended to close the ResultSet object using the close() method.

rs.close();

In JDBC 4.0, the methods discussed in following table have been added to the ResultSet interface:

The updateObject() method in the ResultSet interface has been modified to support the new data types, NClob and SQLXML in JDBC 4.0. The updater methods in the table do not update the underlying database. To update the database, the insertRow() or updateRow() method is required to be invoked.