Processing Results with ResultSet in JDBC

Learn how to retrieve, navigate, and manipulate query results.

What is a ResultSet?

A ResultSet is a Java object that holds data returned from a database query (e.g., SELECT). Think of it as a virtual table with rows and columns. You can:

Navigate through rows (forward/backward).
Extract data by column name or index.
Check for NULL values.

1. Basic Navigation

Key Methods

Method	Description
`next()`	Move to the next row (returns `true` if successful).
`previous()`	Move to the previous row (requires scrollable `ResultSet`).
`beforeFirst()`	Move cursor before the first row.
`afterLast()`	Move cursor after the last row.
`absolute(int row)`	Jump to a specific row (e.g., `absolute(3)`).

Example: Looping Through Rows

String sql = "SELECT id, name, email FROM students";
try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

    while (rs.next()) { // Move to next row until end
        int id = rs.getInt("id");
        String name = rs.getString("name");
        String email = rs.getString("email");
        System.out.println(id + ": " + name + " - " + email);
    }
}

2. Retrieving Data

Use getXxx() methods to extract values by column name or index (1-based):

By Column Name

double salary = rs.getDouble("salary"); // Column name "salary"

By Column Index

double salary = rs.getDouble(3); // 3rd column in the ResultSet

Supported Data Types

Method	Return Type	SQL Type
`getInt()`	`int`	`INTEGER`, `SMALLINT`
`getDouble()`	`double`	`DOUBLE`, `FLOAT`
`getString()`	`String`	`VARCHAR`, `CHAR`
`getBoolean()`	`boolean`	`BOOLEAN`
`getDate()`	`Date`	`DATE`

3. Handling NULL Values

If a column allows NULL, use wasNull() to check after retrieval:

String email = rs.getString("email");
if (rs.wasNull()) {
    email = "N/A"; // Handle NULL
}

4. Scrollable and Updatable ResultSets

By default, ResultSet is forward-only. For advanced navigation/modification, specify type when creating the Statement:

// Create scrollable, updatable ResultSet
Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE, 
    ResultSet.CONCUR_UPDATABLE
);

ResultSet rs = stmt.executeQuery("SELECT * FROM students");
rs.absolute(2); // Jump to 2nd row
rs.updateString("name", "Charlie"); // Update value
rs.updateRow(); // Commit changes to the database

For detail example on Scrollable ResultSet and Updatable ResultSet you can visit these link.

5. Best Practices

Use Column Names: Avoid indexes (e.g., getString("name") over getString(2)).
Close Resources: Use try-with-resources for ResultSet, Statement, and Connection.
Check for NULLs: Always validate nullable columns.
Limit Data: Use WHERE clauses to avoid oversized ResultSets.

Common Errors

Error	Solution
`Invalid column name`	Verify column names in the query.
`ResultSet is closed`	Don’t close the `Statement` before processing `ResultSet`.
`Operation not supported for forward-only ResultSet`	Use scrollable `ResultSet`.

Full Example: Advanced ResultSet Usage

public class ResultSetDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/jdbc_practice";
        String user = "root";
        String password = "root123";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement(
                 ResultSet.TYPE_SCROLL_INSENSITIVE,
                 ResultSet.CONCUR_READ_ONLY
             );
             ResultSet rs = stmt.executeQuery("SELECT * FROM students")) {

            // Move to last row
            if (rs.last()) {
                System.out.println("Total rows: " + rs.getRow());
            }

            // Move back to first row
            rs.beforeFirst();
            while (rs.next()) {
                String name = rs.getString("name");
                System.out.println("Student: " + name);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Summary

ResultSet allows navigation and extraction of query results.
Use getXxx() methods to retrieve data safely.
Scrollable ResultSet enables bidirectional navigation.

Next Up: Transactions in JDBC – Learn how to group operations for atomicity and consistency!