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

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

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

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

Common Errors

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!