Java JDBC ResultSet

A ResultSet is a cursor over the rows a query returned. It does not hold all the rows in memory at once; it points at one row at a time and you walk it forward. Critically, a fresh ResultSet is positioned before the first row — you must call next() once to land on the first one. That is why every read loop is while (rs.next()).

The read loop

String sql = "SELECT id, name, score FROM player ORDER BY score DESC";
try (Statement st = conn.createStatement();
     ResultSet rs = st.executeQuery(sql)) {
  while (rs.next()) {                       // advance; false when no more rows
    int id = rs.getInt("id");               // read by column name...
    String name = rs.getString(2);          // ...or by 1-based index
    int score = rs.getInt("score");
    System.out.println(id + " " + name + " " + score);
  }
}

Reading columns: by name or by index

Both work. Column names are clearer and survive a reordered SELECT; column indexes (1-based, like everything in JDBC) are marginally faster. Names win for maintainability in almost all code.

The NULL problem and wasNull()

The primitive getters cannot return null. getInt returns 0 for a SQL NULL, getDouble returns 0.0, and so on — indistinguishable from a real zero. When a column is nullable and the difference matters, call rs.wasNull() immediately after the getter:

int score = rs.getInt("score");
if (rs.wasNull()) { /* it was SQL NULL, not a zero */ }

(For object types, getObject returns null directly, which is often cleaner.)

Cursor type and concurrency

By default a ResultSet is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY — you move forward and you only read. Ask for TYPE_SCROLL_INSENSITIVE to call previous(), absolute(n), or first(), or CONCUR_UPDATABLE to edit rows in place. These cost more, so request them only when needed.

Close it (try-with-resources does)

A ResultSet holds a server-side cursor; leaving it open wastes database resources. Closing its Statement closes it, and try-with-resources handles both. Never return an open ResultSet from a method whose Connection has already closed.

A worked example: the forward-only cursor and wasNull

This program models the ResultSet cursor with a small in-memory list — starting before the first row, advancing with a next()-style step — and reproduces the getInt-returns-0-for-NULL behaviour with a wasNull check, alongside the cursor constants.

What to take from the run:

• The cursor starts at index -1 — before the first row — and the ++cursor step mirrors rs.next(): advance first, then read. This is exactly why a real loop is while (rs.next()) and never reads a column before the first next().
• Rows are read one at a time, not loaded as a list. The model uses a list for simplicity, but a real ResultSet streams rows from the server, which is what lets it handle result sets far larger than memory.
• Linus's null score printed as 0 — the same trap getInt sets. Without the flag you could not tell a missing score from a genuine zero.
• The (wasNull) marker is the disambiguator. In real JDBC you call rs.wasNull() right after the getter, because it reports on the most recent column read — read another column first and the answer changes.
• The constants (TYPE_FORWARD_ONLY, CONCUR_READ_ONLY, FETCH_FORWARD) describe the default, cheapest cursor: move forward, read only. You opt into scrollable or updatable cursors deliberately, because they ask the server to do more.

Practice