Types of ResultSets

ResultSets are used to scan the results of a SQL query. The basic way of scanning results is done using the next() method on a result set. However, it is also possible to navigate result sets and to update individual rows within a result set. This section provides details on different types of result sets for these advanced techniques.

Navigating a ResultSet

JDBC provides several methods to set the current position within a result set. This position is managed by using a cursor; however, there is no need to deal with SQL cursors in JDBC. When the underlying database permits, the following methods can be used to navigate results.

ResultSet’s Methods for Navigating Rows

boolean next(); boolean previous(); boolean first(); boolean last(); void beforeFirst(); void afterLast(); boolean relative(int n); boolean absolute(int n);

boolean next() makes the current position within the result set go to the next row. If no data row is available after the current position, a false value is returned; otherwise, a true value is returned.

boolean previous() makes the current position within the result set go to the previous row. If this row is the first row within the result set, false is returned; otherwise, true is returned.

boolean first() makes the current position within the result set go to the first row of data. If no such row exists (that is, the result set is empty), false is returned.

boolean last() makes the current position within the result set go to the last row of data. If the result set doesn’t contain any rows, false is returned.

void beforeFirst() makes the current position within the result set go before the first row of data, so it can be fetched with the next call to next().

void afterLast() makes the current position within the result set go after the last row of data.

boolean relative(int n) jumps to n rows forward or backward within the result set, depending on the sign of n: Negative values scroll backward from the current position, and positive values scroll forward from the current position. If the new position is located before the first row or after the last row of data, false is returned; otherwise, true is returned.

boolean absolute(int n) jumps to row number n within the result set if n is positive, or to row n counted backward from the end of the result set if n is negative. false is returned if the current position is either after the last row or before the first row of data within the result set.

Note that except next(), all other methods used to navigate result sets require that a special option is set on the ResultSet. These are discussed in the next section.

Three types of ResultSets

One version of the createStatement(), prepareStatement(), and prepareCall() methods of java.sql.Connection specifies a type for the result set that is used to access the

results of SQL statements such as queries. These types specify whether the result set is scrollable and if the result set rows are a snapshot of database rows at one point in time or if result set rows reflect the actual data in the table at various times when the result set is being navigated.

There are three types of options for ResultSets:

• TYPE_FORWARD_ONLY: Navigation is prohibited; the result set can be scanned only with the next() method.

• TYPE_SCROLL_INSENSITIVE: Navigating the result set is allowed, but the rows that it contains are static: They reflect the database state at the time the query is made or when the data is first fetched.

• TYPE_SCROLL_SENSITIVE: Navigating the result set is allowed, and the rows that it contains are dynamic: If the database content matching with the query changes, such changes will be visible when navigating the result set.

Preserving ResultSets across transactions

ResultSets are normally closed after a commit or rollback occurs within a transaction. However, as it may be useful to continue using the result set’s data after a transaction commits or aborts, JDBC provides an option in the form of methods of java.sql.Connection: createStatement(), prepareStatement(), and prepareCall(). The result set holdability values for this option are as follows:

• HOLD_CURSORS_OVER_COMMIT: ResultSet’s content is still accessible outside the transaction boundaries.

• CLOSE_CURSORS_AT_COMMIT: ResultSet’s content is discarded after a transaction commits or rollbacks.

Setting the concurrency level

As explained in the sections “Updating a ResultSet without using cursors" and "Updating a ResultSet using cursors," rows in a result set can be updated, and the changes will be propagated to the underlying database columns. In order to do so, it is necessary to tell the JDBC Driver that it must maintain a link between data in the database and rows in a result set, but more important, also use some locking mechanism to protect this data while an update on a result set occurs. Concurrency levels are used for this purpose and are set with overloaded methods of java.sql.Connection: createStatement(), prepareStatement(), and prepareCall().

There are two possible concurrency levels:

• CONCUR_READ_ONLY: Disallows data updates on ResultSets

• CONCUR_UPDATABLE: Allows data updates on ResultSets

Setting ResultSet options

All the options described in this section (“Types of ResultSets”) are set using overloaded createStatement(), prepareStatement(), and prepareCall() methods of java.sql.Connection. The following is the detailed interface:

Connection’s Methods for Specifying a Type to ResultSets

Statement createStatement(int resultSetType, int concurrencyLevel) Statement createStatement(int resultSetType, int concurrencyLevel,  int resultSetHoldability) PreparedStatement prepareStatement(String sql, int resultSetType,  int concurrencyLevel) PreparedStatement prepareStatement(String sql, int resultSetType,  int concurrencyLevel, int resultSetHoldability) CallableStatement prepareCall(String sql, int resultSetType, int concurrencyLevel) CallableStatement prepareCall(String sql, int resultSetType,  int concurrencyLevel, int resultSetHoldability)

The integer parameters in the six preceding methods support the constant values TYPE_FORWARD_ONLY and such discussed earlier. Such values are static data members in the ResultSet interface.

Updating a ResultSet without using cursors

A row contained in a result set can be updated with the updateXXX() methods of the ResultSet interface. An updateXXX() method exists for types such as Array, AsciiStream, BigDecimal, BinaryStream , Blob, and String. After the update is done, an invocation of updateRow() on ResultSet will make it durable. The method rowUpdated() returns true if an update occurred.

The example provided in Listing 7-8 illustrates how to update a ResultSet using cursors.

Listing 7-8: Updating a ResultSet without Using Cursors

// updating resultsets import java.sql.*; class SimpleExample {          public static void main(String args[])          {                  String url = "jdbc:odbc:mysource";                  try                  {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                                   DriverManager.getConnection(url, "javauser",                                    "hotjava");                           Statement stmt = myConnection.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);                           ResultSet rs = stmt.executeQuery("SELECT phone FROM                            employees WHERE location = ‘Paris’");                           while (rs.next()) {                                   String phone = rs.getString(1);                                   if (!phone.equals("")) {                                            // add a telephone prefix                                            rs.updateString("phone", "+31 " +                                             phone);                                            rs.updateRow();                                   }                           }                           rs.close();                           stmt.close();                           myConnection.close();                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } }

It is also possible to delete rows and insert rows using the ResultSet interface. Deleting a row is performed with ResultSet.deleteRow(). The current position within the ResultSet will be set after the row just deleted. Inserting a row is performed with ResultSet.moveToInsertRow(), which actually creates a logical row in the result set. Using the updateXXX() methods on this row sets values for each column in that row. ResultSet.insertRow() is then used to physically send that row to the database.

Updating a ResultSet using cursors

Cursors are used to access a set of rows returned by a SQL SELECT statement. They are associated with query statements and have a symbolic name that can be used to access individual rows of data. Associated with cursors are cursor result sets and cursor positions. Note that some DBMSs don’t support cursors, such as Microsoft Access, but you wouldn’t use that as a real DBMS.

Thus, a cursor

• Is associated with a SELECT statement

• Has a name

• Has a position

• May affect ResultSets by positioned update/positioned delete statements using the cursor name

The cursor mechanism is illustrated in Figure 7-1.

Figure 7-1: The cursor mechanism.

How to use a cursor

JDBC supports simple cursors that can be used in positioned update or positioned delete statements. They remain valid until the ResultSets or their parent Statements are closed. The JDBC methods for this purpose are part of the Statement interface and are listed in the following:

Statement’s Methods for Cursor Support

void ResultSet.setCursorName(String name); String ResultSet.getCursorName(); boolean DatabaseMetaData.supportsPositionedDelete(); boolean DatabaseMetaData.supportsPositionedUpdate();

The method in the first line of the preceding is used to give a statement a specific cursor name. Cursor names should be unique. Note that a cursor name is automatically provided by default, which is often sufficient. You can see the default name by using the getCursorName() method.

The getCursorName() method in the second line of the preceding returns the current ResultSet’s cursor name. This cursor name can be used later for positioned updates and positioned deletes.

The DatabaseMetaData method in the third line of the preceding returns true when the database supports positioned deletes.

The method in the last line of the preceding (boolean DatabaseMetaData. supportsPositionedUpdate();) should be used prior to performing a positioned update. It returns true when the database supports it.

Practical examples

The examples in Listings 7-9 and 7-10 are somewhat simplified. They illustrate the cursor mechanism for positioned updates and positioned deletes. In a real-world application, the SELECT statement would have a WHERE clause to limit the cursor scope. The condition that is tested before doing the positioned delete or positioned update is usually more elaborate than what is done here.

Listing 7-9: A Positioned Delete

// cursors: positioned delete import java.sql.*; class SimpleExample {         public static void main(String args[])         {                 String url = "jdbc:odbc:mysource";                 try                 {                          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                          Connection myConnection =                          DriverManager.getConnection(url, "javauser", "hotjava");                          Statement firstStmt = myConnection.createStatement();                          Statement secondStmt = myConnection.createStatement();                          ResultSet rs = firstStmt.executeQuery(                          "SELECT * FROM employees FOR UPDATE");                          String cursor = rs.getCursorName();                          // scan the resultset                           while (rs.next())                          {                                  String phone = rs.getString("phone");                                  // activate positioned delete                                  // for employees outside of Belgium and France                                  if (!phone.startsWith("+31") ||                                   !phone.startsWith("+32"))                                  {                                           secondStmt.executeUpdate(                                           "DELETE employees WHERE CURRENT OF " +                                            cursor);                                  }                          }                          rs.close();                          firstStmt.close();                          secondStmt.close();                           myConnection.close();                  }                  catch(java.lang.Exception ex)                  {                           ex.printStackTrace();                  }          } }

The same remark concerning the WHERE clause is valid for the positioned update example shown in Listing 7-10. However, the example still illustrates how a positioned update can be performed.

Listing 7-10: A Positioned Update

// cursors: positioned update import java.sql.*; class SimpleExample {         public static void main(String args[])         {                 String url = "jdbc:odbc:mysource";                 try                 {                          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                          Connection myConnection =                           DriverManager.getConnection(url,                          "javauser", "hotjava");                          Statement firstStmt = myConnection.createStatement();                          Statement secondStmt = myConnection.createStatement();                          ResultSet rs = firstStmt.executeQuery("SELECT * FROM                           employees FOR UPDATE");                          String cursor = rs.getCursorName();                          // scan the resultset                           while (rs.next())                          {                                  String phone = rs.getString("phone");                                  // activate positioned update                                  // to convert salary to EURO currency                                  if (phone.startsWith("+31"))                                  {                                           secondStmt.executeUpdate(                                           "UPDATE employees SET salary = salary /                                            6.666 WHERE CURRENT OF " + cursor);                                  }                                  else if (phone.startsWith("+32"))                                  {                                           secondStmt.executeUpdate(                                           "UPDATE employees SET salary = salary /                                            40.000 WHERE CURRENT OF " + cursor);                                  }                          }                          rs.close();                          firstStmt.close();                          secondStmt.close();                          myConnection.close();                 }                 catch(java.lang.Exception ex)                 {                          ex.printStackTrace();                 }         } }