JDBC/Oracle Tutorial 2

JDBC 2.0

Result Set Enhancements

Scrollable Results Sets

Statement createStatement(int resultSetType, int resultSetConcurrency)
                          throws SQLException

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

This section will discuss the resultSetType parameter while the next will discuss the resultSetConcurrency parameter.  The resultSetType parameter represents one of the following 3 scroll types for a result set (the JDBC 2.0 Specification refers to scroll types as result set types):
 

• TYPE_FORWARD_ONLY
• TYPE_SCROLL_INSENSITIVE
• TYPE_SCROLL_SENSITIVE

These are defined as constants in the ResultSet class.  A result set that is forward only can scroll only forwards.  A result set that is scroll insensitive or scroll sensitive can scroll forwards and backwards.  The "Change Visibility in a Result Set" section will discuss the difference between the two.  The ability to scroll forwards and backwards depends on the DBMS.  The Oracle8i server does not support scrollable cursors, but this feature is available because it is implemented as a separate layer above the server.  The table below describes some of the ResultSet methods for moving a cursor.  Note that a forward only result set can use next() only; otherwise an SQLException is thrown.
 

Table 1.  Methods for Result Set Scrolling
 

Method	Description
next()	Moves the cursor to the next row
previous()	Moves the cursor to the previous row
absolute( int rowNumber)	Moves the cursor to the given row number.  If rowNumber is positive, the cursor is positioned relative to the position just before the first row, e.g., absolute(1) moves the cursor to the first row, and absolute(2) moves it to the second row.  If rowNumber is negative, the cursor is positioned relative to the position just after the last row, e.g., absolute(-1) moves the cursor to the last row, and absolute(-2) moves it to the second last row.  rowNumber cannot be zero.
relative(int rows)	Moves the cursor the given number of rows relative to the current row.  If rows is positive, the cursor moves forward and if rows is negative, the cursor moves backwards, e.g., relative(1) moves the cursor 1 row forward from the current row, and relative(-1) moves the cursor 1 row backwards from the current row.  If rows = 0, the cursor position does not change.  If there is no current row (i.e., the cursor is beyond the first/last row), an SQLException is thrown.
first()	Moves the cursor to the first row
last()	Moves the cursor to the last row
beforeFirst()	Moves the cursor to just before the first row
afterLast()	Moves the cursor to just after the last row
moveToInsertRow()	Moves the cursor to the insert row (see "Inserts" in the "Updatable Result Sets" section).  The current cursor position is remembered before the cursor is moved.
moveToCurrentRow()	Moves the cursor to the row that was remembered by moveToInsertRow().  The cursor must be in the insert row before this method can be called (see "Inserts" in the "Updatable Result Sets" section).

All the methods except beforeFirst(), afterLast(), moveToInsertRow(), and moveToCurrentRow() return true if the destination row exists and false if there are no more rows.  The other methods return nothing.  Any attempts to move a cursor beyond the position before the first row or beyond the position after the last row will have no effect on the cursor.

The method listing that follows allows you to determine the cursor's current position:

• boolean isBeforeFirst()
• boolean isAfterLast()
• boolean isFirst()
• boolean isLast()
• int getRow()

The isXXX() methods return true if the cursor is at position XXX.  getRow() returns the row number of the current row (i.e., 1 for the first row, 2 for the second, and so on).  It returns 0 if the current row is before the first row or after the last row.

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).
 

Updatable Result Sets

• CONCUR_READ_ONLY
• CONCUR_UPDATABLE

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);

While a result set is open, other result sets and statements in the same transaction, and other transactions can make changes to the database.  Whether or not a result set "sees" these changes and changes made by itself is discussed in the "Change Visibility in a Result Set" section.
 

Updates

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.  Use "Table 2. setXXX() Methods" from the JDBC 1.0 tutorial as a guide for determining the appropriate updateXXX() method to use.  Use the updateNull() method to update a column with a null value.  This method accepts a column number or column name as the sole parameter.  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();
}

Deletes

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

Inserts

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

Change Visibility in a Result Set

• 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 result set can see an update if getXXX() returns data that reflects the update (assuming the updated row still satisfies the query).  It can see an insert if the inserted row is in the result set.  It can see a delete if the deleted row is no longer in the result set.

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.

Generally, 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.  You can find out by checking out the JDBC 2.0 Specification link in the "Links" section.  The table below shows the internal and external changes that can be seen by each type of result set in Oracle JDBC.
 

Table 2.  Visibility of Internal and External Changes for Oracle JDBC.
 

Source:  Seeing Database Changes Made Internally and Externally.  Oracle10g JDBC Developer's Guide and Reference Release 2 (10.2).  March 2010.
 

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.

When we were talking about sensitivity, we were actually talking about the changes that were automatically made visible to a result set.  However, you can manually see all the changes by reexecuting the query to obtain a new result set.  You can also use the refreshRow() method in the ResultSet class to refresh the current row with the most recent value in the database.  However, this method is not guaranteed to return the most recent value in the database because of how Oracle fetches data.  By default, Oracle fetches 10 rows at a time and places them in a cache.  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 into cache (internal updates are always visible).  You can, however, force the cache to be refreshed every time the cursor is moved by changing the fetch size from 10 to 1.  This can be done by calling the setFetchSize(int size) method 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.  Because the default transaction isolation level is READ COMMITTED, unrepeatable reads are possible (you will learn more about this in the lectures and in the textbook).  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;

Recall that 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.
 

Result Set Constraints

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

• 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

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

Batch Updates

Example 1.  Using a Statement Object to Perform a Batch Update
 

try
{
  . . .
  // con is a Connection object
  con.setAutoCommit(false);

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

  stmt.addBatch("INSERT INTO branch VALUES (10, 'Main', "+
                "'1234 Main St.', 'Vancouver', 5551234)");
  stmt.addBatch("INSERT INTO branch VALUES (20, 'Richmond', "+
                "'23 No.3 Road', 'Richmond', 5552331)");
  stmt.addBatch("DELETE FROM branch WHERE branch_id = 50");

  int[] updateCounts = stmt.executeBatch();
  con.commit();

  ResultSet rs = stmt.executeQuery("SELECT * FROM branch");
  . . .
}
catch (BatchUpdateException ex)
{
  System.out.println("Message: " + ex.getMessage());

  int[] updateCounts = ex.getUpdateCounts();

  System.out.println("Update Counts:");

  for (int i = 0; i < updateCounts.length; i++)
  {
     System.out.println(updateCounts[i]);
  }
}
catch (SQLException ex)
{
  System.out.println("Message: " + ex.getMessage());
}

Since auto commit is enabled by default, you should disable it to give yourself control over the transactions.  So if an error occurs, you can rollback all executed statements in the batch (if auto commit is not disabled, each statement is committed automatically after it is executed, so a rollback will only rollback a single statement).  The Statement method addBatch() is used to add an SQL statement to the batch.  The method executeBatch() submits the batch of statements to the database for execution.  The statements are executed in the order that they were added to the batch.  executeBatch() returns an array of integers representing the status of each statement execution.  The array is ordered according to the order in which the statements were added.  The "Batch Update Errors" section will describe the possible integer values. executeBatch() throws a BatchUpdateException when any statement in the batch fails to execute properly.  BatchUpdateException is subclass of SQLException.  The BatchUpdateException method getUpdateCounts() returns an array of integers containing status information.  The "Batch Update Errors" section will describe the possible integer values.  Note that after the batch is executed, you can reuse the Statement object for more batch updates, nonbatch updates, or queries.

If you need to clear a batch, you can use the clearBatch() method.  The batch is automatically cleared after executeBatch() is executed.
 

Example 2.  Using a PreparedStatement Object to Perform a Batch Update
 

try
{
  . . .
  //  con is a Connection object
  con.setAutoCommit(false);

  // ps is a PreparedStatement object
  ps = con.prepareStatement("UPDATE branch "+
       "SET branch_phone = ? WHERE branch_id = ?");

  ps.setInt(1, 6042552715);
  ps.setInt(2, 10);
  ps.addBatch();

  ps.setInt(1, 6047330880);
  ps.setInt(2, 20);
  ps.addBatch();

  int[] updateCounts = ps.executeBatch();
  con.commit();
  . . .
}
catch (BatchUpdateException ex)
{
  System.out.println("Message: " + ex.getMessage());

  int[] updateCounts = ex.getUpdateCounts();

  System.out.println("Update Counts:");

  for (int i = 0; i < updateCounts.length; i++)
  {
     System.out.println(updateCounts[i]);
  }
}
catch (SQLException ex)
{
  System.out.println("Message: " + ex.getMessage());
}

Batch Update Errors

When any statement in the batch fails to execute properly, a BatchUpdateException is thrown.  Some JDBC drivers continue to process the remaining statements in the batch while others stop.  The Oracle JDBC driver does the latter.  Generally, for these types of drivers, the getUpdateCounts() method returns an array of size n corresponding to the first n successfully executed statements.  However, in Oracle JDBC, this is not the case with a prepared statement batch (see below).  For drivers that continue processing, the getUpdateCounts() method returns an array of size n, where n equals the number of statements in the batch.  For identifying the statements that executed unsuccessfully, a value of -3 is returned by these types of drivers.

Oracle specific information regarding the return values in the update counts array and the array returned by getUpdateCounts() is listed below:

• for a prepared statement batch, -2 is always returned for each successfully executed statement
• for a statement batch, the update count is returned for each successfully executed statement
• for a prepared statement batch, getUpdateCounts() returns -3, rather than the update count or -2, for each successfully executed statement.  Unlike what we mentioned about drivers that stop processing after a BatchUpdateException, return values are also returned for each unsuccessfully executed statement.  Again -3 is returned.  You should always perform a rollback in this situation since there is no way to determine which statement executed successfully and which did not.
• for a statement batch, getUpdateCounts() returns the update count for each successfully executed statement.  The array represents only the statements that executed successfully.  You can either do a commit or rollback in your exception handling code.

Links

• Oracle10g JDBC Developer's Guide and Reference
• Sun's JDBC 2.0 Tutorial
• JDBC 2.0 Specification
• Java 2 API Documentation
• javax.sql package

JDBC/Oracle Tutorial 2
Last updated May 8 2011