Processing a Result Set

Setting the Result Set Type

Different types of result sets have their own application scenarios, and an application needs to select the appropriate result set type based on the actual situation. During SQL statement execution, the corresponding statement object must be created first, and some methods for creating statement objects provide the functionality to set the result set type. Table 1 describes the specific parameter settings. The involved Connection methods are as follows:

java

// Create a Statement object that will generate ResultSet objects with the given type and concurrency.
createStatement(int resultSetType, int resultSetConcurrency);

// Create a PreparedStatement object that will generate ResultSet objects with the given type and concurrency.
prepareStatement(String sql, int resultSetType, int resultSetConcurrency);

// Create a CallableStatement object that will generate ResultSet objects with the given type and concurrency.
prepareCall(String sql, int resultSetType, int resultSetConcurrency);

Table 1 Result set types

Parameter	Description
resultSetType	Indicates the result set type. There are three specific types: ResultSet.TYPE_FORWARD_ONLY: The ResultSet can only move forward. This is the default value. ResultSet.TYPE_SCROLL_SENSITIVE: After modifications are made, scrolling back to the modified row shows the modified results. ResultSet.TYPE_SCROLL_INSENSITIVE: Edits made to modifiable routines are not displayed. Note: After the result set reads data from the database, even if its type is ResultSet.TYPE_SCROLL_SENSITIVE, it will not reflect changes made by other transactions thereafter. Call the refreshRow() method of ResultSet to access the database and retrieve the latest data for the row currently pointed to by the cursor.
resultSetConcurrency	Indicates the result set concurrency. There are two specific types: ResultSet.CONCUR_READ_ONLY: Data in the result set cannot be updated unless a new update statement is created from the data in the result set. ResultSet.CONCUR_UPDATEABLE: An updateable result set. For scrollable result sets, appropriate changes can be made to the result set.

Parameter

Description

resultSetType

Indicates the result set type. There are three specific types:

ResultSet.TYPE_FORWARD_ONLY: The ResultSet can only move forward. This is the default value.
ResultSet.TYPE_SCROLL_SENSITIVE: After modifications are made, scrolling back to the modified row shows the modified results.
ResultSet.TYPE_SCROLL_INSENSITIVE: Edits made to modifiable routines are not displayed.

Note:

After the result set reads data from the database, even if its type is ResultSet.TYPE_SCROLL_SENSITIVE, it will not reflect changes made by other transactions thereafter. Call the refreshRow() method of ResultSet to access the database and retrieve the latest data for the row currently pointed to by the cursor.

resultSetConcurrency

Indicates the result set concurrency. There are two specific types:

ResultSet.CONCUR_READ_ONLY: Data in the result set cannot be updated unless a new update statement is created from the data in the result set.
ResultSet.CONCUR_UPDATEABLE: An updateable result set. For scrollable result sets, appropriate changes can be made to the result set.

Positioning in a Result Set

A result set object has a cursor that points to its current data row. Initially, the cursor is positioned before the first row. The next method moves the cursor to the next row; because this method returns false when there are no more rows in the result set object, it can be used in a while loop to iterate through the result set. However, for a scrollable result set, the JDBC driver provides more positioning methods to point the result set to a specific row. Table 2 describes the positioning methods.

Table 2 Methods for positioning in a result set

Method	Description
next()	Moves the result set down by one row.
previous()	Moves the result set up by one row.
beforeFirst()	Positions the result set before the first row.
afterLast()	Positions the result set after the last row.
first()	Positions the result set to the first row.
last()	Positions the result set to the last row.
absolute(int)	Moves the result set to the row specified by the parameter.
relative(int)	Moves forward (set to 1, equivalent to next()) or backward (set to -1, equivalent to previous()) by the number of rows specified by the parameter.

Obtaining the Cursor Position in a Result Set

For scrollable result sets, positioning methods may be called to change the cursor position. The JDBC driver provides methods for obtaining the cursor position in a result set. Table 3 describes the methods for obtaining the cursor position.

Table 3 Obtaining the cursor position in the result set

Method	Description
isFirst()	Whether it is in the first row.
isLast()	Whether it is in the last row.
isBeforeFirst()	Whether it is before the first row.
isAfterLast()	Whether it is after the last row.
getRow()	Obtains the current row number.

Fetching Data from a Result Set

Result set objects provide a rich set of methods for fetching data from a result set. Table 4 lists the common methods for fetching data. For other methods, refer to the official JDK documentation.

Table 4 Common methods for fetching data

Method	Description
int getInt(int columnIndex)	Fetches Int type data by column label.
int getInt(String columnLabel)	Fetches Int type data by column name.
String getString(int columnIndex)	Fetches String type data by column label.
String getString(String columnLabel)	Fetches String type data by column name.
Date getDate(int columnIndex)	Fetches Date type data by column label.
Date getDate(String columnLabel)	Fetches Date type data by column name.

Processing a Result Set ​

Setting the Result Set Type ​

Positioning in a Result Set ​

Obtaining the Cursor Position in a Result Set ​

Fetching Data from a Result Set ​

Processing a Result Set

Setting the Result Set Type

Positioning in a Result Set

Obtaining the Cursor Position in a Result Set

Fetching Data from a Result Set