Extended Functions of Result Sets

This chapter describes the extended functions of scrollable and updatable result sets provided by the JDBC 2.0 standard.

JDBC 2.0 표준

The JDBC 2.0 standard provides extended functions for scrollability, positioning, sensitivity, and updatability.

• Scrollability, positioning, and sensitivity are determined by the result set type.

• Updatability is determined by the concurrency type.

Scrollability, Positioning, Sensitivity

Scrollability and positioning enable a result set to move forward or backward, or to a relative or absolute position. A relative position indicates moving forward or backward from the location of the current row, and an absolute position indicates moving from the beginning or the end of the result set.

When scrollability is used, all result set rows are loaded in memory. Using it is not recommended for result sets that contain a large quantity of data.

To create a scrollable or positionable result set, sensitivity must be set because it determines whether modifications made to the database are applied regardless of the current result set. Sensitivity enables users to see new data by immediately applying changes made to the database, while insensitivity enables users to only see data as it was when result set was created.

One of the following result set types can be chosen when a result set is created.

• ResultSet.TYPE_FORWARD_ONLY

• ResultSet.TYPE_SCROLL_SENSITIVE

• ResultSet.TYPE_SCROLL_INSENSITIVE

Updatability

Updatability is used to directly update data in a result set and to apply the changes to the database. The changes include adding new rows and deleting or modifying existing rows.

Updatability may require a lock to access the database.

One of the following concurrency types can be chosen when a result set is created.

• ResultSet.CONCUR_UPDATABLE

• ResultSet.CONCUR_READ_ONLY

---

Creating Scrollable or Updatable Result Sets

Creating a Statement Object

tbJDBC provides the following methods in the Connection class.

• Statement createStatement(int resultSetType, int resultSetConcurrency)

• PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

• CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)

The following example shows how to create a Statement object.

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                      ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT empno FROM emp");

Methods for Viewing Result Set Type and Concurrency Type

After creating a Statement, PreparedStatement, or CallableStatement object, the result set type and the concurrency type can be seen using the following methods.

• int getResultSetType() throws SQLException

• int getResultSetConcurrent() throws SQLException

Restrictions

The following restrictions apply when creating an updatable result set.

• Only a query for a single table can be used, and no join operations are available.

• 'SELECT *' cannot be used, but 'SELECT t.*' can.

• A query can only be used for table columns.

The following restrictions apply when creating a scroll-sensitive result set.

• Only a query for a single table can be used.

• 'SELECT *' cannot be used, but 'SELECT t.*' can.

---

Searching Scrollable Result Sets

The following methods are provided for scrollable result sets and used to move to a new position.

Method	Description
boolean next() throws SQLException	Moves from the current position to the next row. If the current row is the last row, this returns false.
boolean previous() throws SQLException	Moves from the current position to the previous row. If the current row is the first row, this returns false.
boolean first() throws SQLException	Moves to the first row of the result set. If there are no rows in the result set, this returns false.
boolean last() throws SQLException	Moves to the last row of the result set. If there are no rows in the result set, this returns false.
boolean absolute(int row) throws SQLException	Moves from the first or last row of the result set to an absolute position. If a positive number is entered, the position is moved forward from the first row. If a negative number is entered, the position is moved backward from the last row. If the entered number is a positive number greater than the number of the result sets, the position is moved to after the last row. This is the same result as calling afterLast(). If the number is a negative number greater than the number of the result sets, the result is the same as calling beforeFirst().
boolean relative(int row) throws SQLException	Moves from the position of the current row. If the entered number is positive, the position is moved forward, and if it is negative, the position is moved backward. If the entered number is a positive number that is greater than the number of results, the position is moved to the next of the last row. This produces the same result as afterLast(). If the number is a negative number greater than the number of results, the result is the same as beforeFirst(). Note that the position of the current row is important, because it is impossible to move from the previous position of the first row or the next position of the last row to a relative position. Those kinds of movement can cause an SQLException.
void beforeFirst() throws SQLException	Moves to before the first row of the result set. This is where searching a result set in the forward direction starts from. No row data can be immediately used.
void afterLast() throws SQLException	Moves to after the last row of the result set. This is where searching a result set in the reverse direction starts from. No row data can be immediately used.

tbJDBC provides the following methods for obtaining information about the current position.

Method	Description
int getRow() throws SQLException	Returns the position of the current row. If the position is invalid, this returns 0.
boolean isFirst() throws SQLException	Checks if the position of the current row is the first row.
boolean isLast() throws SQLException	Checks if the position of the current row is the last row.
boolean isBeforeFirst() throws SQLException	Checks if the position of the current row is before the first row.
boolean isAfterLast() throws SQLException	Checks if the position of the current row is after the last row.

---

Searching Updatable Result Sets

An updatable result set can be used to update, delete, or insert rows.

After performing UPDATE or INSERT, additional tasks are required to apply the changes to the database. Otherwise, all changes to the data are canceled. On the other hand, DELETE operations are immediately applied to the database without additional measures.

INSERT

An INSERT operation is applied to the database through the following steps.

1. Move to temporary storage to INSERT using the moveToInsertRow() method. The result set internally remembers the existing positions of rows, so it is possible to move to the original position of a row using the moveToCurrentRow() method.

2. Write data to the columns by performing an appropriate updateXXX() method. If no data is written on a certain column, it remains NULL.

3. Apply the changes to the database using the insertRow() method.

To cancel the changes after the INSERT operation has been completed, move to the position of a different row, then the original data is restored. In this case, note the following:

• If the insertRow() method has been called, the changes are applied to the database, so they are not canceled unless they are rolled back.

• Any result set type cannot see rows inserted by an INSERT operation.

The following is an example of an INSERT operation.

rs.moveToInsertRow(); 
rs.updateString(1, "tibero");
rs.insertRow(); 
rs.moveToCurrentRow();

UPDATE

An UPDATE operation is applied to the database through the following steps.

1. Call the updateXXX() method.

2. Apply the changes to the database by calling the updateRow() method. If auto-commit mode is enabled, a commit operation may occur automatically. To cancel the changes after the UPDATE operation has been completed, call the cancelRowUpdates() method, or move to the position of a different row, then the original data is restored. If the updateRow() method has already been called, the changes are applied to the database, thus they are not canceled unless they are rolled back.

The following example executes an UPDATE operation.

rs.absolute(5); 
rs.updateString(1, "tibero"); 
rs.updateRow();

DELETE

Note the following when performing a DELETE operation.

• When a DELETE operation has completed, the changes are immediately applied to the database, thus they may be immediately committed if auto-commit mode has been set.

• When a row has been deleted by the user, the deletion is applied to the database, but the deleted row remains in the result set. However, in a scrollable result set, the position of the current row immediately moves to the row just before the deleted row, and the positions of the succeeding rows are automatically changed accordingly.

A DELETE operation can be performed using the following method.

void deleteRow() throws SQLException

The following example executes a DELETE operation.

rs.absolute(5); 
rs.deleteRow();