ResultSet and mapping rows to objects

Q: What are the most common mistakes with ResultSet and mapping rows to objects?

Forgetting next() before the first read. Treating NULL as 0 or empty without a check. Returning a ResultSet from a method after closing the connection. Using SELECT * with index access.

A ResultSet is a cursor over the rows a query returned: while (rs.next()) moves to the next row and rs.getString("name") reads a column.

🔎 Unravel this example Prerequisites (1)

Learn these first

JDBC and prepared statements

At a glance

Mental model

Reading a printed table with a ruler under the current row: next() moves the ruler down one line, and getString reads one cell on that line.

What happens, in order

1try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { list.add(new User(rs.getLong("id"), rs.getString("name"))); } }The cursor starts before the first row, so next() is needed before the first read.
2Read by column name (clear) or index (1-based, a bit faster): rs.getInt(1)
3SQL NULL: getInt returns 0 for NULL, so check with rs.wasNull() or use getObject("age", Integer.class)Money: getBigDecimal. Time: getTimestamp("created").toInstant() or getObject("created", OffsetDateTime.class).
4Spring JdbcTemplate uses a RowMapper<T> ((rs, rowNum) -> new User(...)) so you do not write the loop

Remember

Loading millions of rows: set ps.setFetchSize(1000) so the driver streams instead of loading everything (PostgreSQL also needs auto-commit off for cursors)
Always close ResultSet, Statement and Connection (try-with-resources closes all in reverse order)
The driver fetches rows from the network in blocks (the fetch size) into a buffer; next() moves within the buffer and requests the next block when it is used up

Watch out

Forgetting next() before the first readTreating NULL as 0 or empty without a checkReturning a ResultSet from a method after closing the connectionUsing SELECT * with index access

Example (highlighted lines explain it)

import java.sql.*;import java.util.*;public class Users {  record User(long id, String name, Integer age) {}  static List<User> all(Connection c) throws SQLException {    List<User> out = new ArrayList<>();    try (PreparedStatement ps = c.prepareStatement("SELECT id, name, age FROM users");         ResultSet rs = ps.executeQuery()) {      while (rs.next()) {        int age = rs.getInt("age");        out.add(new User(rs.getLong("id"), rs.getString("name"), rs.wasNull() ? null : age));      }    }    return out;  }}

line 8 uses ResultSet, the core of ResultSet and mapping rows to objects
line 10 uses getInt, the core of ResultSet and mapping rows to objects
line 11 uses getLong, the core of ResultSet and mapping rows to objects

Read the full explanation (about 2 min)

A ResultSet is a cursor over the rows a query returned: while (rs.next()) moves to the next row and rs.getString("name") reads a column. You map each row to an object yourself, or use a RowMapper or an ORM.

A mental model

Reading a printed table with a ruler under the current row: next() moves the ruler down one line, and getString reads one cell on that line.

How it works

try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { list.add(new User(rs.getLong("id"), rs.getString("name"))); } }. The cursor starts before the first row, so next() is needed before the first read.

Read by column name (clear) or index (1-based, a bit faster): rs.getInt(1).

SQL NULL: getInt returns 0 for NULL, so check with rs.wasNull() or use getObject("age", Integer.class). Money: getBigDecimal. Time: getTimestamp("created").toInstant() or getObject("created", OffsetDateTime.class).

Spring JdbcTemplate uses a RowMapper<T> ((rs, rowNum) -> new User(...)) so you do not write the loop.

Going deeper

Loading millions of rows: set ps.setFetchSize(1000) so the driver streams instead of loading everything (PostgreSQL also needs auto-commit off for cursors). Do not collect the whole table into a list if you can process row by row.

Always close ResultSet, Statement and Connection (try-with-resources closes all in reverse order). A ResultSet is invalid after its statement or connection is closed, so map to objects before returning.

Select only the columns you need and name them in the query; SELECT * breaks column-index code when the table changes.

Under the hood

The driver fetches rows from the network in blocks (the fetch size) into a buffer; next() moves within the buffer and requests the next block when it is used up. Type conversion happens in the getXxx call from the wire format.

Common mistakes

Forgetting next() before the first read.
Treating NULL as 0 or empty without a check.
Returning a ResultSet from a method after closing the connection.
Using SELECT * with index access.

How Unravel recognises it

When you paste code, Unravel looks for these signs of resultset and mapping rows to objects. Spotting them yourself is a good habit when you read unfamiliar code:

Types: ResultSet ResultSetMetaData RowMapper
Method calls: getString() getInt() getLong() getTimestamp() wasNull() getBigDecimal() getObject() getDate() getBoolean() getDouble() getColumnCount()

Part of a learning path

JDBC and everyday libraries · previous: JDBC transactions and batching · next: Guava utilities

Related concepts

JDBC and prepared statementsPersistence JPA entitiesPersistence RecordsJava language

Frequently asked questions

What is ResultSet and mapping rows to objects?

What are the most common mistakes with ResultSet and mapping rows to objects?

Forgetting next() before the first read. Treating NULL as 0 or empty without a check. Returning a ResultSet from a method after closing the connection. Using SELECT * with index access.

How can I see ResultSet and mapping rows to objects in real code?

Paste your own Java into Unravel and it will point out where resultset and mapping rows to objects appears, explain how it works at the detail level you choose, and draw the flow of the code around it.