Processing Data in a Result Set

After running SQL statements, you need to process the result set. This section describes how to set the result set type, locate the result set, obtain the cursor position from a result set, and obtain data from the result set.

Setting a Result Set Type

Different types of result sets apply to different application scenarios. Applications select proper types of result sets based on the actual situation. Before running an SQL statement, you must create a statement object. Some methods of creating statement objects can set the type of a result set. The java.sql.Connection API provides the following three methods for creating statement objects:

    
         // Create a Statement object. This object will generate a ResultSet object with a specified type and concurrency.
createStatement(int resultSetType, int resultSetConcurrency);

// Create a PreparedStatement object. This object will generate a ResultSet object with a specified type and concurrency.
prepareStatement(String sql, int resultSetType, int resultSetConcurrency);

// Create a CallableStatement object. This object will generate a ResultSet object with a specified type and concurrency.
prepareCall(String sql, int resultSetType, int resultSetConcurrency);

**Table 1** Result set types
Parameter	Description
resultSetType	Type of a result set. There are three types of result sets: ResultSet.TYPE_FORWARD_ONLY: The ResultSet object can only be navigated forward. It is the default value. ResultSet.TYPE_SCROLL_SENSITIVE: You can view the modified result by scrolling to the modified row. ResultSet.TYPE_SCROLL_INSENSITIVE: The ResultSet object is insensitive to changes in the underlying data source. NOTE: After a result set has obtained data from the database, the result set is insensitive to data changes made by other transactions, even if the result set type is ResultSet.TYPE_SCROLL_SENSITIVE. To obtain up-to-date data of the record pointed by the cursor from the database, call the refreshRow() method in a ResultSet object.
resultSetConcurrency	Concurrency type of a result set. There are two types of concurrency. ResultSet.CONCUR_READ_ONLY: Data in a result set cannot be updated except that an updated statement has been created in the result set data. ResultSet.CONCUR_UPDATEABLE: changeable result set. The concurrency type for a result set object can be updated if the result set is scrollable.

Positioning a Cursor in a Result Set

ResultSet objects include a cursor pointing to the current data row. The cursor is initially positioned before the first row. The next method moves the cursor to the next row from its current position. When a ResultSet object does not have a next row, a call to this method returns false. Therefore, this method is used in the while loop for result set iteration. However, the JDBC driver provides more cursor positioning methods for scrollable result sets, which allows positioning cursor in the specified row. Table 2 describes these methods.

**Table 2** Methods for positioning in a result set
Method	Description
next()	Moves cursor to the next row from its current position.
previous()	Moves cursor to the previous row from its current position.
beforeFirst()	Places cursor before the first row.
afterLast()	Places cursor after the last row.
first()	Places cursor to the first row.
last()	Places cursor to the last row.
absolute(int row)	Places cursor to a specified row.
relative(int rows)	Moves the result set downwards by the number of rows specified by rows when rows is set to a positive number, or moves the result set upwards by the number of rows specified by rows when rows is set to a negative number.

Obtaining the Cursor Position from a Result Set

This cursor positioning method can be used to change the cursor position for a scrollable result set. The JDBC driver provides a method to obtain the cursor position in a result set. Table 3 describes these methods.

**Table 3** Methods for obtaining a cursor position in a result set
Method	Description
isFirst()	Checks whether it is in the first row.
isLast()	Checks whether it is in the last row.
isBeforeFirst()	Checks whether it is before the first row.
isAfterLast()	Checks whether it is after the last row.
getRow()	Obtains its current row number.

Obtaining Data from a Result Set

ResultSet objects provide a variety of methods to obtain data from a result set. Table 4 describes the common methods for obtaining data. If you want to know more about other methods, see JDK official documents.

**Table 4** Common methods for obtaining data from a result set
Method	Description
getInt(int columnIndex)	Retrieves the value of the column designated by a column index in the current row as an integer.
getInt(String columnLabel)	Retrieves the value of the column designated by a column label in the current row as an integer.
getString(int columnIndex)	Retrieves the value of the column designated by a column index in the current row as a string.
getString(String columnLabel)	Retrieves the value of the column designated by a column label in the current row as a string.
getDate(int columnIndex)	Retrieves the value of the column designated by a column index in the current row as a date.
getDate(String columnLabel)	Retrieves the value of the column designated by a column name in the current row as a date.