Testing whether the current row of a ResultSet is a delete hole or update hole in a JDBC application

If a ResultSet has the TYPE_SCROLL_SENSITIVE attribute, and the underlying cursor is SENSITIVE STATIC, you need to test for delete holes or update holes before you attempt to retrieve rows of the ResultSet.

About this task

After a SENSITIVE STATIC ResultSet is opened, it does not change size. This means that deleted rows are replaced by placeholders, which are also called holes. If updated rows no longer fit the criteria for the ResultSet, those rows also become holes. You cannot retrieve rows that are holes.

Procedure

To test whether the current row in a ResultSet is a delete hole or update hole, follow these steps:

  1. Call the DatabaseMetaData.deletesAreDetected or DatabaseMetaData.updatesAreDetected method with the TYPE_SCROLL_SENSITIVE argument to determine whether the data source creates holes for a TYPE_SCROLL_SENSITIVE ResultSet.
  2. If DatabaseMetaData.deletesAreDetected or DatabaseMetaData.updatesAreDetected returns true, which means that the data source can create holes, call the ResultSet.rowDeleted or ResultSet.rowUpdated method to determine whether the current row is a delete or update hole.
    If the method returns true, the current row is a hole.

Example

The following code tests whether the current row is a delete hole.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
  ResultSet.CONCUR_UPDATABLE);                 
                                        // Create a Statement object
                                        // for a scrollable, updatable
                                        // ResultSet
ResultSet rs = 
  stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE FOR UPDATE OF PHONENO"); 
                                        // Create the ResultSet
DatabaseMetaData dbmd = con.getMetaData();     
                                        // Create the DatabaseMetaData object
boolean dbSeesDeletes = 
  dbmd.deletesAreDetected(ResultSet.TYPESCROLL_SENSITIVE);
                                        // Can the database see delete holes?
rs.afterLast();                         // Position the cursor at the end of
                                        // the ResultSet
while (rs.previous()) {                 // Position the cursor backward
 if (dbSeesDeletes) {                   // If delete holes can be detected
   if (!(rs.rowDeleted()))              // If this row is not a delete hole
   {
     s = rs.getString("EMPNO");         // Retrieve the employee number
     System.out.println("Employee number = " + s);
                                        // Print the column value
   }
  }
}
rs.close();                             // Close the ResultSet
stmt.close();                           // Close the Statement