ResultSet Extras

In previous tutorials, we have discussed how a ResultSet object stores the result of an executed SQL query. The following subsections contain information which may help you with using ResultSets.

Getting Information about a Result Set

The ResultSetMetaData class provides methods to get information about the columns in a result set. For example, this class provides methods to return the number of columns, the name of a given column, the maximum character width of a given column, the datatype of a given column, and whether or not null values are permitted in a given column. Refer to the ResultSetMetaData API for more details. The following example gets the number of columns in a ResultSet object:

// rs is a ResultSet object
ResultSetMetaData rsmd = rs.getMetaData();

int count = rsmd.getColumnCount();

Scrollable ResultSets

Recall that a ResultSet maintains a cursor that points to a particular record in a set of records. When creating a ResultSet, it defaults to only enabling forward movement (TYPE_FORWARD_ONLY); that is, you can only go to the next row of results using next() and can never move backwards to see previous results. Moveover, inserts, updates, and deletes can now be performed on the current row, which is the row to which the cursor is currently pointing. The nice thing about this feature is that you do not need to construct SQL statements to perform the changes.

If you wish to create a ResultSet that is scrollable (i.e,. it can move forwards and backwards), you have to explictly do this when creating a Statement or PreparedStatement object.

Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException

PreparedStatement prepareStatement(String sqlStmt, int resultSetType, int resultSetConcurrency) throws SQLException

We will discuss the resultSetType and resultSetConcurrency parameters in future sections.

Navigating through a ResultSet

Here are some methods that will help navigate a ResultSet. Each method is a link to its description in the ResultSet API.

• next()
• previous()
• absolute()
• relative(int rows)
• first()
• last()
• beforeFirst()
• afterLast()
• movetoInsertRow()
• movetoCurrentRow()

There is no method for returning the number of rows in a result set, but you can obtain the number of rows by using the last() method followed by getRow() (but you should check the return value of last() to make sure the result set is not empty).

For more information, see the "Cursors" section here.

resultSetType

There are three possible options for the resultSetType parameter:

• TYPE_FORWARD_ONLY: the cursor can only move forwards
• TYPE_SCROLL_INSENSITIVE: the cursor can move forwards and backwards but the result set contains all rows that match your query at the time the query is run. If the database is updated in a way that would affect the results of the query, the change(s) made will not affect the result set.
• TYPE_SCROLL_SENSITIVE: the cursor can move forwards and backwards but the result set contains all rows that match your query. If the database is updated in a way that would affect the results of your query, the change(s) made will appear in the result set.

Sensitivity refers to the changes automatically made visible to a result set. When discussing sensitivity, we want to consider the following questions:

• Can a result set see the changes that it makes?
• Can it see the changes made by other result sets and statements in the same transaction?
• Can it see the changes made by other transactions?
• What types of changes can it see? Updates, deletes, and/or inserts?

The first question refers to internal changes. The next two questions refer to external changes. A scroll sensitive result set can see external updates and scroll insensitive ones cannot. A forward-only result set may or may not be able to see external updates depending on the DBMS and the query. For example, a forward-only result set with sorted data generally cannot see external updates. A result set's ability to see external deletes and inserts, and also internal changes depends on the driver and DBMS. There is a way to find out what types of changes a particular driver allows a result set to see. The table below shows the internal and external changes that can be seen by each type of result set in Oracle JDBC.

Notes: An update that results in a row no longer satisfying the query is considered a delete. An update that results in a row changing its position in a sorted result set is considered a delete followed by an insert. A read-only result set cannot see internal changes because it cannot make them. An internal delete results in the previous row becoming the new current row. The row numbers are updated accordingly.

Result Set Type	Can See Internal DELETE	Can See Internal UPDATE	Can See Internal INSERT	Can See External DELETE	Can See External UPDATE	Can See External INSERT
forward-only	No	Yes	No	No	No	No
Scroll-Sensitive	Yes	Yes	No	No	Yes	No
Scroll-Insensitive	Yes	Yes	No	No	No	No

Source: Summary of Visibility of Internal and External Changes. Oracle 12c JDBC Developer's Guide Release 1 (12.1.0.2). 2014.

Don't confuse result set sensitivity with transaction isolation levels. Transaction isolation levels refer to sensitivity at the transaction level rather than at the result set level (you will learn about transaction isolation levels from the lectures and the textbook). A result set's ability to see changes is dependent on the transaction isolation level. For example, the default transaction isolation level in Oracle is READ COMMITTED. This means that transactions can read only committed data (no dirty reads). Therefore, before an update made by a transaction is visible to a result set in another transaction, the transaction that made the update must commit the changes.

If you wanted to see all the changes in a result set, you could re-execute a query or use the refreshRow() method. However, refreshRow() is not guaranteed to return the most recent value in the database because of how Oracle caches data. When you call getXXX(), the cache is checked for the row. This means that external updates to a row in cache will not be visible until that row is first replaced and then refetched (internal updates are always visible). To force the cache size to 1 (and thus, forcing Oracle to fetch data for each executed query), use setFetchSize(int size) on the Statement or PreparedStatement object before executing the query. The drawback of reducing the fetch size is that your application's performance will go down significantly.

Another issue to consider is the ability of a scroll-sensitive result set to see external updates. As the default transaction isolation level is READ COMMITTED, unrepeatable reads are possible. This means that you can get inconsistent results when you read a row more than once. To lock the rows selected by a SELECT statement so that other users cannot update those rows until you end your transaction, use the FOR UPDATE clause. For example, this SQL statement will lock all the rows in the branch table where branch_city = 'Vancouver':

SELECT b.* FROM branch b WHERE b.branch_city = 'Vancouver' FOR UPDATE;

You need to use a range variable if you expect to receive an updatable result set from a SELECT * statement. You can find more information about the FOR UPDATE clause in the Oracle SQL Reference.

resultSetConcurrency

The resultSetConcurrency parameter specifies whether a result set is read only or updatable. The following constants in the ResultSet class are used to specify a read only or updatable result set:

• CONCUR_READ_ONLY: Read only result sets generally increase the level of concurrency because only read only locks are used (Oracle actually does not use read locks; it uses Multiversion Concurrency Control which you can read about in the textbook). This means that many transactions can concurrently read the data represented by a result set.
• CONCUR_UPDATABLE: An updatable result set allows rows to be updated, deleted, and inserted. The changes are then passed onto the database.

If no parameters are specified in createStatement() and prepareStatement(), the result set returned by a query will be forward only and read only. The example below creates a statement that will return a scroll sensitive and updatable result set. It also creates a prepared statement that will return a scroll insensitive and read only result set.

// con is a Connection object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
  ResultSet.CONCUR_UPDATABLE);

PreparedStatement ps = con.prepareStatement("SELECT branch_id, branch_name "+
  "FROM branch WHERE branch_city = ?",
  ResultSet.TYPE_SCROLL_INSENSITIVE,
  ResultSet.CONCUR_READ_ONLY);


Result Set Constraints

When you specify a scroll and concurrency type for a result set, you may not get what you asked for. This is because some types are not compatible with certain queries, and some drivers do not support certain types. When you specify a type that a query or driver cannot accommodate, the driver will automatically select an alternative type. Fortunately, the Oracle JDBC driver supports all types. Nevertheless you still need to make sure that you specify result set types that are compatible with the query type. In particular, certain query types disallow updatable result sets and scroll-sensitive ones. The types of queries allowed are somewhat driver specific.

In general, a query that produces an updatable result set:

• can reference only one table
• cannot contain any joins
• must select the primary key of the table referenced (Oracle JDBC does not have this constraint)
• must select all non-nullable columns and all columns that don't have a default value if inserts will be performed

In addition, to produce an updatable result set, Oracle requires that a query:

• not use SELECT * (but there is a workaround which you have already seen)
• not contain aggregate values such as COUNT and MAX
• not use ORDER BY

To produce a scroll-sensitive result set, Oracle requires that a query:

• reference only one table
• not use SELECT *
• not use ORDER BY

For all scrollable and/or updatable result sets, you cannot use ORDER BY if you want to refetch rows.

Operating on a Scrollable ResultSet

Update a Row

1. Move the cursor to the target row
2. Use the updateXXX() methods of the ResultSet object to change the column values in the target row
3. Call the updateRow() method to propagate the changes in the result set to the database

Generally, the updateXXX() methods accept for the first parameter a column number (first column is 1) or a column name, and for the second parameter it accepts the new value. If the cursor is moved after updateXXX() but before updateRow(), the changes will be canceled. To cancel an update explicitly, call the cancelRowUpdates() method after updateXXX() but before updateRow().

Example:

// con is a Connection object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
  ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT branch_addr, branch_phone "+
  "FROM branch WHERE branch_id = 100");

// the query returns only 1 row
if (rs.next()) {
rs.updateString(1,"1221 Main St.");
rs.updateNull(2);
rs.updateRow();
}

Delete a Row

1. Move the cursor to the target row
2. Call the deleteRow() method

Insert a Row

1. Call the moveToInsertRow() method to move the cursor to the insert row. The insert row is a special row for setting up the new row.
2. Use the updateXXX() methods to make changes to the insert row
3. Call the insertRow() method to propagate the changes to the database
4. Call the moveToCurrentRow() method to move the cursor to the row remembered by moveToInsertRow() (if the result set is initially empty, moveToCurrentRow() will have no effect on the cursor)

To insert null, you can use updateNull() or omit the updateXXX() for the column. You must call updateXXX() for all non-null columns and columns that don't have a default value. You can use the getXXX() methods on the insert row, but the column must be set first with an updateXXX().

The following will insert the tuple (100, 'Central', null, 'Vancouver', null) into the branch table:

// con is a Connection object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
  ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT b.* FROM branch b");

rs.moveToInsertRow();
rs.updateInt(1, 100);
rs.updateString(2, "Central");
rs.updateString(4, "Vancouver");
rs.insertRow();

// current row is 0
rs.moveToCurrentRow();
. . .

Note: The Oracle JDBC driver that we are using will not give you an updatable result set if you use SELECT *, but a workaround for this is

SELECT rv.* FROM tableName rv

where rv is a range variable.