15.3 `ResultSet` Navigation, Data Types, and Transaction Management (Commit, Rollback, Savepoints)

ResultSet Navigation, Data Types, and Transaction Management

A ResultSet represents a tabular data stream returned by executing a database query. Navigating this stream, converting database columns to Java types, handling SQL NULL values, and controlling transaction boundaries are core competencies for database programming in Java.

---

ResultSet Cursor Architecture

A ResultSet maintains a cursor pointing to its current row of data. When a query executes, the cursor is initially positioned before the first row (the beforeFirst position):

 Cursor Position      Row Index      Data Row
+------------------+---------------+----------------------------+
| [beforeFirst]    |      0        | (Initial state before next)|
+------------------+---------------+----------------------------+
| rs.next() -> true|      1        | ID: 101, Name: "Alice"     |
+------------------+---------------+----------------------------+
| rs.next() -> true|      2        | ID: 102, Name: "Bob"       |
+------------------+---------------+----------------------------+
| rs.next() -> true|      3        | ID: 103, Name: "Charlie"   |
+------------------+---------------+----------------------------+
| rs.next() ->false|  [afterLast]  | (No more rows available)   |
+------------------+---------------+----------------------------+

To read data, you must advance the cursor by invoking rs.next(). If a row exists, next() positions the cursor on that row and returns true. When no more rows remain, it moves past the last row (the afterLast position) and returns false:

String sql = "SELECT id, name, balance FROM accounts";
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
        // Cursor is on a valid row
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double balance = rs.getDouble("balance");
        System.out.println(id + ": " + name + " ($ " + balance + ")");
    }
    // rs.next() returned false; cursor is now afterLast
}

Cursor Types and Concurrency

By default, a ResultSet is forward-only and read-only. You can request advanced scrolling and concurrency capabilities when creating the statement:

Statement stmt = conn.createStatement(resultSetType, resultSetConcurrency);
PreparedStatement pstmt = conn.prepareStatement(sql, resultSetType, resultSetConcurrency);

Result Set Types

1. ResultSet.TYPE_FORWARD_ONLY (Default):
   • The cursor moves exclusively forward row by row using next().
   • Offers maximum performance and minimal memory overhead.
2. ResultSet.TYPE_SCROLL_INSENSITIVE:
   • The cursor can navigate bidirectionally (previous(), first(), last(), absolute(), relative()).
   • The data view is a static snapshot; changes made to the underlying database by concurrent transactions while the ResultSet is open are not visible.
3. ResultSet.TYPE_SCROLL_SENSITIVE:
   • The cursor is scrollable bidirectionally.
   • The data view reflects dynamic changes made to underlying rows by other transactions while the ResultSet remains open (subject to driver and database engine capabilities).

Result Set Concurrency

1. ResultSet.CONCUR_READ_ONLY (Default): Rows cannot be updated through the ResultSet.
2. ResultSet.CONCUR_UPDATABLE: Rows can be modified, deleted, or inserted directly using ResultSet methods (such as updateString(), updateRow(), and insertRow()).

Navigating Scrollable Result Sets

When a ResultSet is scrollable (TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE), the cursor can be repositioned arbitrarily:

try (Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
     ResultSet rs = stmt.executeQuery("SELECT name FROM players ORDER BY score DESC")) {
    if (rs.last()) {
        System.out.println("Lowest scorer (last row): " + rs.getString("name"));
        int totalRows = rs.getRow(); // Returns current row index
        System.out.println("Total players: " + totalRows);
    }
    if (rs.first()) {
        System.out.println("Highest scorer (first row): " + rs.getString("name"));
    }
}

---

Column Retrieval and the wasNull() Check

Columns can be retrieved using either a 1-based column index (rs.getInt(1)) or a case-insensitive column label (rs.getString("username")).

int id = rs.getInt(1);             // 1-based numeric index
String name = rs.getString("name"); // Column label from SELECT list

The SQL NULL Dilemma and wasNull()

In SQL, columns can hold NULL. When reading nullable columns in JDBC:

• Object getter methods (getString(), getDate(), getBigDecimal()) return Java null.
• Primitive getter methods (getInt(), getDouble(), getBoolean(), getLong()) cannot return null. Instead, they return their primitive default values (0, 0.0, false).

If rs.getInt("bonus") returns 0, you cannot determine whether the employee had a bonus of $0 or if the database value was NULL. To resolve this ambiguity, JDBC provides the wasNull() method on ResultSet:

int bonus = rs.getInt("bonus");
if (rs.wasNull()) {
    System.out.println("Bonus is NOT ASSIGNED (SQL NULL)");
} else {
    System.out.println("Bonus earned: $" + bonus);
}

Transaction Management: Auto-Commit, Commit, and Rollback

By default, a new JDBC Connection operates in auto-commit mode (conn.getAutoCommit() == true). Under auto-commit, every individual SQL statement is treated as its own independent transaction that commits immediately upon execution.

To group multiple operations into an atomic business unit (where either all operations succeed or all are reverted), you must explicitly disable auto-commit:

public void transferFunds(Connection conn, int fromAcc, int toAcc, double amount) throws SQLException {
    boolean originalAutoCommit = conn.getAutoCommit();
    try {
        conn.setAutoCommit(false); // Begin explicit transaction boundary

        try (PreparedStatement debit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance - ? WHERE id = ?");
             PreparedStatement credit = conn.prepareStatement(
                "UPDATE accounts SET balance = balance + ? WHERE id = ?")) {

            debit.setDouble(1, amount);
            debit.setInt(2, fromAcc);
            debit.executeUpdate();

            credit.setDouble(1, amount);
            credit.setInt(2, toAcc);
            credit.executeUpdate();

            conn.commit(); // Atomically persist all modifications
            System.out.println("Transfer successful!");
        }
    } catch (SQLException e) {
        conn.rollback(); // Revert all modifications on failure
        throw e;
    } finally {
        conn.setAutoCommit(originalAutoCommit); // Restore original connection state
    }
}

---

Intermediate Checkpoints with Savepoint

A Savepoint provides fine-grained control within a transaction, allowing an application to roll back a subset of operations without aborting the entire transaction.

conn.setAutoCommit(false);
try {
    // Step 1: Create primary order
    orderStmt.executeUpdate();

    // Establish intermediate checkpoint
    Savepoint sp = conn.setSavepoint("OrderCreated");

    try {
        // Step 2: Attempt optional inventory reservation
        inventoryStmt.executeUpdate();
    } catch (SQLException e) {
        System.err.println("Inventory reservation failed; rolling back to checkpoint");
        conn.rollback(sp); // Reverts Step 2 while preserving Step 1!
    }

    // Step 3: Complete order processing
    paymentStmt.executeUpdate();

    conn.commit(); // Persists Step 1, rolled-back Step 2 state, and Step 3
} catch (SQLException e) {
    conn.rollback(); // Global rollback if primary steps fail
}

Savepoint Rules

• Setting a savepoint when autoCommit is true throws a runtime SQLException.
• Invoking conn.rollback(savepoint) reverts only those operations executed after that savepoint was established.
• Savepoints can be explicitly released using conn.releaseSavepoint(savepoint) to free server resources.
• Calling no-argument conn.commit() or conn.rollback() automatically releases and invalidates all active savepoints.

---

Proper Closing Order for JDBC Resources

JDBC resources consume both client memory and server-side cursors and locks. They should always be closed in reverse order of creation:

ResultSet ---> Statement ---> Connection

Using nested or multi-resource try-with-resources statements ensures clean, automatic closing in this exact order:

try (Connection conn = dataSource.getConnection();
     PreparedStatement pstmt = conn.prepareStatement(query);
     ResultSet rs = pstmt.executeQuery()) {
    while (rs.next()) {
        // process data
    }
} // rs closes first, then pstmt, then conn