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

Oracle Java SE 17 Developer Certified Professional 1Z0-829

Track your progress as you read

Finished sections get a check mark.

1 Introduction: The 1Z0-829 Exam

About the Java SE 17 Developer (1Z0-829) Exam

2 Chapter 1: Java Fundamentals & Language Basics

1.1 Primitive Types, Literals, Wrapper Classes, and Type Promotion/Casting 1.2 Local Variable Type Inference (`var`) Rules and Scoping 1.3 Operators, Precedence, Evaluation Order, and Short-Circuit Evaluation 1.4 Text Blocks, String Immutability, and StringBuilder APIs

3 Chapter 2: Program Flow & Decision Constructs

2.1 Conditional Branching with if/else and the Ternary Operator 2.2 Switch Statements and Switch Expressions 2.3 Iteration Constructs, Loop Control, and Branching Labels

4 Chapter 3: Object-Oriented Design & Class Mechanics

3.1 Class Declarations, Field/Method Signatures, and Access Modifiers 3.2 Constructors, Overloading, Initialization Order, and Garbage Collection 3.3 Inheritance, Method Overriding, Polymorphism, and `super`/`this` 3.4 Abstract Classes, Static Members, and Object Methods (`equals`, `hashCode`, `toString`)

5 Chapter 4: Advanced OOP: Interfaces, Records & Sealed Types

4.1 Interface Architecture: Default, Static, and Private Methods 4.2 Enumerations: Constants, Constructors, Methods, and Custom Bodies 4.3 Records: Canonical/Compact Constructors, Components, and Immutability 4.4 Sealed Classes and Interfaces: `permits`, Hierarchy Constraints, and Modifiers

6 Chapter 5: Pattern Matching & Nested Types

5.1 Pattern Matching for `instanceof` and Flow Scoping Mechanics 5.2 Nested Types: Member Inner, Static Nested, Local, and Anonymous Classes 5.3 Defensive Programming, Immutability Patterns, and Validation

7 Chapter 6: Generics, Collections & Arrays

6.1 Generic Classes, Interfaces, Methods, and Type Erasure 6.2 Bounded Type Parameters and Wildcards (PECS Rule) 6.3 Collections Framework: List, Set, Queue, and Deque APIs 6.4 The Map Interface, Sorting, Comparable, and Comparator 6.5 Arrays: Declaration, Initialization, Multidimensional Arrays, and the Arrays Utility Class

8 Chapter 7: Functional Programming & Lambda Expressions

7.1 Functional Interfaces and Lambda Syntax Rules 7.2 Standard Functional Interfaces in `java.util.function` 7.3 Method References: Static, Bound, Unbound, and Constructor References

9 Chapter 8: The Stream API

8.1 Stream Lifecycle: Creation, Pipeline Architecture, and Lazy Evaluation 8.2 Intermediate Stream Operations: Filtering, Mapping, Sorting, and Slicing 8.3 Terminal Stream Operations: Reduction, Matching, Finding, and Collecting 8.4 Primitive Streams (`IntStream`, `LongStream`, `DoubleStream`) and Numeric Statistics

10 Chapter 9: Advanced Streams & Collectors

9.1 The Optional Class: Safe Null Handling, Transformation, and Chaining 9.2 The Collectors API: Downstream Reductions, Grouping, and Partitioning 9.3 Parallel Streams: Fork/Join Pool, Spliterators, and Concurrency Pitfalls

11 Chapter 10: Exception Handling

10.1 The Java Exception Hierarchy: Checked vs. Unchecked Exceptions and Errors 10.2 Exception Constructs: `try-catch-finally`, Multi-Catch, and Rethrowing 10.3 Try-With-Resources, `AutoCloseable`, Suppressed Exceptions, and Custom Exceptions

12 Chapter 11: Date/Time API & Localization

11.1 Date and Time Core Types: `LocalDate`, `LocalTime`, `LocalDateTime` 11.2 Timestamps, Intervals, and Durations: `Instant`, `Period`, and `Duration` 11.3 Time Zones, Daylight Saving Time (DST) Offsets, and `ZonedDateTime` 11.4 Internationalization and Localization: `Locale`, `ResourceBundle`, and Formatters 11.5 Formatting and Parsing Messages, Numbers, Percentages, and Localized Dates

13 Chapter 12: Java Platform Module System (JPMS)

12.1 Module Architecture, `module-info.java`, and Module Paths 12.2 Module Directives: `requires`, `requires transitive`, `exports`, and Qualified Exports 12.3 Reflection, Deep Encapsulation, `opens`, and Open Modules 12.4 Services in JPMS (`uses`, `provides...with`), Migration Paths, and Tooling (`jlink`, `jdeps`) 12.5 Compiling and Packaging Code: Non-Modular JARs, Modular JARs, and Migration Strategies

14 Chapter 13: Concurrency & Multithreading

13.1 Thread Fundamentals, `Runnable`, `Callable`, and Thread Lifecycle 13.2 Memory Visibility, `volatile`, Intrinsic Locks, and Synchronization 13.3 The Concurrency Framework: `ExecutorService`, Thread Pools, and `Future` 13.4 Concurrent Collections, Atomic Variables, Explicit Locks, and `CompletableFuture`

15 Chapter 14: Java I/O and NIO.2

14.1 Classical I/O: Byte and Character Streams, Readers, Writers, and Buffering 14.2 Object Serialization, `Serializable`, `transient`, and `serialVersionUID` 14.3 NIO.2 Architecture: `Path`, `Paths`, and Basic `Files` Operations 14.4 Advanced NIO.2: Directory Walking, Streams (`Files.lines`, `walk`), and `WatchService`

16 Chapter 15: Accessing Databases with JDBC

15.1 JDBC Architecture, Drivers, `DriverManager`, and `DataSource` Connections 15.2 SQL Execution: `Statement`, `PreparedStatement`, and `CallableStatement` 15.3 `ResultSet` Navigation, Data Types, and Transaction Management (Commit, Rollback, Savepoints)

17 Chapter 16: Annotations & the Java Logging API

16.1 Standard Annotations: @Override, @FunctionalInterface, @Deprecated, @SuppressWarnings, and @SafeVarargs 16.2 Java Logging API Basics: Logger, Levels, Handlers, and Configuration

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

Key Takeaways Explain This Topic AI

A ResultSet cursor starts positioned before the first row (beforeFirst); calling next() advances the cursor and returns true if a row is present, or false when the end of the result set is reached.

Default result sets are TYPE_FORWARD_ONLY and CONCUR_READ_ONLY; scrollable result sets (TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE) allow bidirectional navigation via previous(), first(), last(), and absolute().

Primitive getter methods (such as getInt and getDouble) return 0 or false when reading SQL NULL; applications must call wasNull() immediately after reading the column to differentiate an actual 0 from SQL NULL.

Transactions are managed by disabling auto-commit via connection.setAutoCommit(false), grouping multiple operations atomically, and concluding with connection.commit() or connection.rollback().

Savepoints allow fine-grained, intermediate rollback checkpoints within a transaction via connection.setSavepoint() and connection.rollback(savepoint) without discarding preceding operations.

Last updated: October 2026

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
• }

Warning

The "Before First" Trap Attempting to read a column before calling rs.next() (or after rs.next() returns false ) throws a java.sql.SQLException: Invalid cursor state or Current position is not a valid row . Always call rs.next() at least once before accessing data.

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

ResultSet.TYPE_FORWARD_ONLY (Default) :

The cursor moves exclusively forward row by row using next() .

Offers maximum performance and minimal memory overhead.

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.

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

ResultSet.CONCUR_READ_ONLY (Default) : Rows cannot be updated through the ResultSet .

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:

• Method Movement Description Return Type
• next() Moves cursor forward one row. boolean (true if on a valid row)
• previous() Moves cursor backward one row. boolean (true if on a valid row)
• first() Moves cursor directly to the first row. boolean (false if result set is empty)
• last() Moves cursor directly to the last row. boolean (false if result set is empty)
• beforeFirst() Moves cursor before the first row (initial state). void
• afterLast() Moves cursor after the last row. void

absolute(int row) Moves cursor to the given row number. absolute(1) is first row; absolute(-1) is last row; absolute(0) moves before first row. boolean (true if on a valid row)

relative(int rows) Moves cursor relative to current position. relative(2) advances 2 rows; relative(-1) moves back 1 row. boolean (true if on a valid row) 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);
• }

Warning

The wasNull() Invocation Order Rule wasNull() checks whether the last column read on the ResultSet was SQL NULL . You must call the getter method first , and then immediately inspect rs.wasNull() . Calling wasNull() before reading any column produces undefined or driver-dependent behavior.

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