JDBC & Databases

Processing ResultSets

15 min Lesson 5 of 13

Processing ResultSets

A ResultSet is the object JDBC hands back after a SELECT query. Think of it as a scrollable table: it has a cursor that starts before the first row, and you advance it row by row (or jump around, if your driver supports scrollable sets). Understanding how to move through rows, extract typed values, and inspect the schema at runtime makes you efficient with JDBC and prepares you for the edge cases production code always surfaces.

The Cursor Model

When executeQuery() returns, the cursor sits before row 1. You must call next() to move to the first row. next() returns true as long as a row was reached, and false when the data is exhausted — which makes it a natural while loop condition:

String sql = "SELECT id, username, email FROM users";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

    while (rs.next()) {
        long   id       = rs.getLong("id");
        String username = rs.getString("username");
        String email    = rs.getString("email");
        System.out.printf("%-5d %-20s %s%n", id, username, email);
    }
}

Always close ResultSet and Statement. Wrap both in a try-with-resources block. A leaked ResultSet keeps a server-side cursor open, consuming memory on both client and database.

Typed Getter Methods

JDBC provides two variants of every getter: one that takes the column label (a String) and one that takes the column index (a 1-based int). Column labels are more readable and survive SELECT * column-order changes; column indexes are marginally faster in tight loops.

The most common typed getters:

getString(col) — maps to VARCHAR, TEXT, CHAR
getLong(col) / getInt(col) — maps to BIGINT / INT
getDouble(col) / getBigDecimal(col) — floating-point and exact decimal
getBoolean(col) — maps to BOOLEAN / TINYINT(1)
getTimestamp(col) — maps to DATETIME / TIMESTAMP
getDate(col) — maps to DATE (time portion is midnight)
getBytes(col) — binary data (BLOB)
getObject(col) — driver-chosen Java type; useful when the type is unknown at compile time

// Money columns should always be BigDecimal, never double
BigDecimal price = rs.getBigDecimal("price");

// Dates — prefer getTimestamp then convert to java.time
Timestamp ts = rs.getTimestamp("created_at");
LocalDateTime createdAt = ts != null ? ts.toLocalDateTime() : null;

Prefer BigDecimal for monetary values. getDouble introduces floating-point rounding errors. For financial or scientific columns, always use getBigDecimal.

Handling SQL NULL

SQL NULL is not the same as Java null. When you call a primitive getter like getLong() on a NULL column, JDBC returns 0 — not an exception. To distinguish a true zero from a missing value, call wasNull() immediately after the getter:

long score = rs.getLong("score");
if (rs.wasNull()) {
    System.out.println("score is NULL");
} else {
    System.out.println("score = " + score);
}

For Object getters (getString, getBigDecimal, getTimestamp) JDBC returns a proper Java null, so wasNull() is only necessary for primitive getters.

Do not call wasNull() before calling a getter. It reflects the status of the last column read, not any future one. The order of calls matters.

ResultSetMetaData: Inspecting Columns at Runtime

ResultSetMetaData describes the shape of the result — column count, names, types, nullability, display widths. This is indispensable when you are writing generic code (a CSV exporter, a data-diff tool, a test fixture printer) that must handle arbitrary queries:

ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();

// Print a header row
for (int i = 1; i <= columnCount; i++) {
    System.out.printf("%-20s", meta.getColumnLabel(i));
}
System.out.println();

// Print every row generically
while (rs.next()) {
    for (int i = 1; i <= columnCount; i++) {
        Object value = rs.getObject(i);
        System.out.printf("%-20s", value != null ? value : "NULL");
    }
    System.out.println();
}

Key ResultSetMetaData methods:

getColumnCount() — how many columns in the result
getColumnLabel(i) — the alias (AS label) or original name
getColumnName(i) — the original table column name (differs from label when aliased)
getColumnTypeName(i) — database-specific type string (e.g. VARCHAR)
getColumnType(i) — java.sql.Types constant (e.g. Types.VARCHAR == 12)
isNullable(i) — columnNoNulls, columnNullable, or columnNullableUnknown
getColumnDisplaySize(i) — max characters for display

Building a Generic Row Mapper

A common pattern is to abstract result-to-object mapping with a functional interface, which fits naturally with lambdas you already know:

@FunctionalInterface
public interface RowMapper<T> {
    T map(ResultSet rs) throws SQLException;
}

public <T> List<T> query(String sql, RowMapper<T> mapper) throws SQLException {
    List<T> results = new ArrayList<>();
    try (Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
        while (rs.next()) {
            results.add(mapper.map(rs));
        }
    }
    return results;
}

// Usage
record User(long id, String username) {}

List<User> users = query(
    "SELECT id, username FROM users",
    rs -> new User(rs.getLong("id"), rs.getString("username"))
);

This is exactly what Spring JdbcTemplate does. Understanding the raw JDBC version tells you why RowMapper<T> exists and what happens under the hood in every JDBC abstraction layer.

Scrollable and Updatable ResultSets

By default, a ResultSet is forward-only and read-only. If you need to move backwards or update rows in place, pass extra constants when creating the statement:

Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,  // allows rs.absolute(), rs.previous()
    ResultSet.CONCUR_UPDATABLE          // allows rs.updateString(), rs.updateRow()
);

ResultSet rs = stmt.executeQuery("SELECT id, status FROM orders WHERE status = 'PENDING'");

while (rs.next()) {
    rs.updateString("status", "PROCESSING");
    rs.updateRow();  // writes the change back to the database
}

Scroll types: TYPE_FORWARD_ONLY (default, most efficient), TYPE_SCROLL_INSENSITIVE (snapshot, does not see concurrent changes), TYPE_SCROLL_SENSITIVE (live view, driver-dependent and rarely used).

Updatable ResultSets are rarely the right tool. They require the query to map directly to a single table with a primary key, and not all drivers support them fully. Prefer an explicit UPDATE statement for clarity and portability.

Summary

A ResultSet cursor starts before row 1; advance it with next() in a while loop. Use typed getters by column label for clarity, BigDecimal for money, and wasNull() after primitive getters to distinguish zero from NULL. ResultSetMetaData gives you the column schema at runtime and powers generic mappers. Wrapping mapping logic in a RowMapper<T> functional interface is clean, testable, and the foundation of higher-level frameworks.