The JDBC java.sql.ResultSet is
used to handle the result returned from the SQL select statement.
The SQL select statements reads data from a database and return the data in a result set.
The result from a select statement is in a tabular form. It has columns and rows.
A ResultSet object maintains a cursor that points to the current row in the result set.
For a certain row we can use methods from java.sql.ResultSet to get the data
column by column.
The methods of the ResultSet interface have three categories:
The cursor is movable based on the properties of the ResultSet. These properties are set when creating the JDBC Statement.
The Resultset types are listed below, the default is TYPE_FORWARD_ONLY.
| Type | Description |
|---|---|
| ResultSet.TYPE_FORWARD_ONLY | The cursor can only move forward. |
| ResultSet.TYPE_SCROLL_INSENSITIVE | The cursor can scroll forwards and backwards. The ResultSet is not sensitive to changes made to the database after the ResultSet was created. |
| ResultSet.TYPE_SCROLL_SENSITIVE | The cursor can scroll forwards and backwards. And the ResultSet is sensitive to changes made to the database after the result set was created. |
The ResultSet Concurrency types are listed below. The default concurrency type is CONCUR_READ_ONLY.
| Concurrency | Description |
|---|---|
| ResultSet.CONCUR_READ_ONLY | Read-only result set. This is the default |
| ResultSet.CONCUR_UPDATABLE | updateable result set. |
JDBC provides the following methods from Connection object to create statements with certain types of ResultSet.
createStatement(int resultSetType, int resultSetConcurrency);
prepareStatement(String SQL, int resultSetType, int resultSetConcurrency);
prepareCall(String sql, int resultSetType, int resultSetConcurrency);
The following code creates a Statement object to create a forward-only, read only ResultSet object
Statement stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
We can use the following methods from ResultSet interface to move the cursor.
| Methods | Description |
|---|---|
| beforeFirst() | Moves the cursor to before the first row |
| afterLast() | Moves the cursor to after the last row |
| first() | Moves the cursor to the first row |
| last() | Moves the cursor to the last row |
| absolute(int row) | Moves the cursor to the specified row |
| relative(int row) | Moves the cursor number of rows forward or backwards relative to where it is. |
| previous() | Moves the cursor to the previous row. |
| next() | Moves the cursor to the next row. |
| int getRow() | Returns the row number that the cursor is pointing to. |
| moveToInsertRow() | Moves the cursor to where we can insert a new row into the database. The current row number is not changed. |
| moveToCurrentRow() | Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing |
We have two ways to get data in the ResultSet.
For example, the following two methods get int value from a column. The first one is by column name and second one is by column index.
public int getInt(String columnName) public int getInt(int columnIndex)
The column index starts at 1.
We can update current row in ResultSet object.
We need to indicate the column name or index during the update.
For example, to update a String column of the current row we can use the the following methods.
public void updateString(int columnIndex, String s) throws SQLException public void updateString(String columnName, String s) throws SQLException
To push the update changes to the database, invoke one of the following methods.
| Methods | Description |
|---|---|
| updateRow() | Updates the corresponding row in the database. |
| deleteRow() | Deletes the current row from the database |
| refreshRow() | Refreshes the result set to reflect any changes in the database. |
| cancelRowUpdates() | Cancels any updates made on the current row. |
| insertRow() | Inserts a row into the database when the cursor is pointing to the insert row. |