Mapping ResultSets to Records

Walk a ResultSet safely, avoid the NULL-becomes-zero trap, and turn each row into a record with one reusable mapper.

Syntax

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

while (rs.next()) { rows.add(mapper.map(rs)); }

A ResultSet is a cursor that starts before the first row. Each call to next() moves it forward one row and returns false when the rows run out, so the loop is always while (rs.next()). Read the current row with typed getters and turn it into a record straight away. Nothing outside the repository should see a ResultSet: it stops working once its statement or connection is closed.

Reading columns

Getters take a column label or a 1-based index. Labels survive someone reordering the SELECT list, so prefer them. List the columns you need instead of SELECT *, so adding a column to the table cannot change what the mapper sees. The label is the alias when there is one: SELECT created_at AS created is read with "created".

The NULL trap

getInt, getLong, getDouble and getBoolean return primitives, so SQL NULL comes back as 0 or false, indistinguishable from a real zero. For a nullable number use rs.getObject("discount", Integer.class), which returns null, or call rs.wasNull() straight after the getter. getString and getBigDecimal already return null.

Types that fit

Column type	Read with
`bigint`	`getLong`
`numeric`, `decimal`	`getBigDecimal`
`varchar`, `text`	`getString`
`date`	`getObject(col, LocalDate.class)`
`timestamp with time zone`	`getObject(col, OffsetDateTime.class)`
`uuid` (PostgreSQL)	`getObject(col, UUID.class)`

A row mapper

Write each mapping once, as a small functional interface whose method is T map(ResultSet rs) throws SQLException, plus a helper that runs a query and applies the mapper to every row. java.util.function.Function does not fit because its apply cannot throw the checked SQLException. Records make good targets: the canonical constructor is the one place every column is assigned, and a compact constructor can reject a row that breaks an invariant before it reaches the rest of the application.

For a lookup by id, return Optional: map the first row if there is one, otherwise return Optional.empty(). Keep that record separate from the JSON your API returns, so renaming a column does not silently change a public field name.

Example

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.UUID;

record Customer(UUID id, String email, Integer loyaltyPoints, OffsetDateTime createdAt) {
    static Customer from(ResultSet rs) throws SQLException {
        return new Customer(
            rs.getObject("id", UUID.class),
            rs.getString("email"),
            rs.getObject("loyalty_points", Integer.class),   // NULL stays null
            rs.getObject("created_at", OffsetDateTime.class));
    }
}

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

final class Jdbc {
    static <T> List<T> list(Connection conn, String sql, RowMapper<T> mapper, Object... params)
            throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            try (ResultSet rs = ps.executeQuery()) {
                List<T> rows = new ArrayList<>();
                while (rs.next()) {
                    rows.add(mapper.map(rs));
                }
                return rows;
            }
        }
    }

    static <T> Optional<T> one(Connection conn, String sql, RowMapper<T> mapper, Object... params)
            throws SQLException {
        List<T> rows = list(conn, sql, mapper, params);
        if (rows.size() > 1) {
            throw new IllegalStateException("expected at most one row, got " + rows.size());
        }
        return rows.isEmpty() ? Optional.empty() : Optional.of(rows.getFirst());
    }
}

// Usage:
// Optional<Customer> c = Jdbc.one(conn,
//     "SELECT id, email, loyalty_points, created_at FROM customers WHERE id = ?",
//     Customer::from, customerId);

When to use it

Turn the rows of a product search into records that the API layer converts to JSON.
Return Optional from findById so a GET handler can answer 404 without catching exceptions.
Keep a nullable discount column null in the response instead of reporting a misleading 0.

More examples

Testing a mapper without a database

The fake mimics how drivers behave: getInt returns 0 for NULL, and only wasNull tells a real zero (the cable) from a missing value (the gift card). It checks the mapper's logic quickly, but whether the SQL and column names are right still needs a test against a real database.

import java.lang.reflect.Proxy;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

record Product(long id, String name, BigDecimal price, Integer stock) {}

Product map(ResultSet rs) throws SQLException {
    long id = rs.getLong("id");
    String name = rs.getString("name");
    BigDecimal price = rs.getBigDecimal("price");
    int stock = rs.getInt("stock");                  // 0 when the column is NULL
    Integer stockOrNull = rs.wasNull() ? null : stock;
    return new Product(id, name, price, stockOrNull);
}

Map<String, Object> row(Object... pairs) {
    Map<String, Object> m = new HashMap<>();          // HashMap allows null values
    for (int i = 0; i < pairs.length; i += 2) m.put((String) pairs[i], pairs[i + 1]);
    return m;
}

// Just enough of ResultSet for the mapper, backed by in-memory rows.
ResultSet fake(List<Map<String, Object>> rows) {
    int[] cursor = {-1};
    Object[] last = {null};
    return (ResultSet) Proxy.newProxyInstance(
        ResultSet.class.getClassLoader(),
        new Class<?>[] {ResultSet.class},
        (proxy, method, args) -> switch (method.getName()) {
            case "next" -> ++cursor[0] < rows.size();
            case "wasNull" -> last[0] == null;
            case "getLong", "getInt", "getString", "getBigDecimal" -> {
                Object v = rows.get(cursor[0]).get((String) args[0]);
                last[0] = v;
                if (v == null && method.getName().equals("getLong")) yield 0L;
                if (v == null && method.getName().equals("getInt")) yield 0;
                yield v;
            }
            default -> throw new UnsupportedOperationException(method.getName());
        });
}

void main() throws SQLException {
    ResultSet rs = fake(List.of(
        row("id", 1L, "name", "Desk lamp", "price", new BigDecimal("24.90"), "stock", 12),
        row("id", 2L, "name", "USB cable", "price", new BigDecimal("3.50"), "stock", 0),
        row("id", 3L, "name", "Gift card", "price", new BigDecimal("25.00"), "stock", null)));
    while (rs.next()) {
        IO.println(map(rs));
    }
}

// Output:
// Product[id=1, name=Desk lamp, price=24.90, stock=12]
// Product[id=2, name=USB cable, price=3.50, stock=0]
// Product[id=3, name=Gift card, price=25.00, stock=null]

Mapping a join into nested records

When two joined tables both have an id column, reading by the label "id" returns the first matching column, which may not be the one you meant. Alias every ambiguous column in the SQL and map by alias.

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;

record CustomerRef(long id, String name) {}
record OrderSummary(long id, BigDecimal total, CustomerRef customer) {}

final class OrderMappers {
    static final String SQL = """
        SELECT o.id AS order_id, o.total, c.id AS customer_id, c.name AS customer_name
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        WHERE o.status = ?
        ORDER BY o.id
        """;

    // Aliases make every label unique, so "id" cannot mean two different columns.
    static OrderSummary summary(ResultSet rs) throws SQLException {
        return new OrderSummary(
            rs.getLong("order_id"),
            rs.getBigDecimal("total"),
            new CustomerRef(rs.getLong("customer_id"), rs.getString("customer_name")));
    }
}

The same mapping with Spring's JdbcClient

JdbcClient (Spring Framework 6.1 and later) handles connections, statements and closing; you still write the SQL and, where you need control, the row mapping. The explicit lambda is the same mapper as plain JDBC, which is why learning the ResultSet rules first pays off.

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.stereotype.Repository;

record Product(long id, String name, BigDecimal price, Integer stock) {}

@Repository
class ProductQueries {
    private final JdbcClient jdbc;

    ProductQueries(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    Optional<Product> findById(long id) {
        return jdbc.sql("SELECT id, name, price, stock FROM products WHERE id = ?")
                .param(id)
                .query(Product.class)        // columns matched to record components by name
                .optional();
    }

    List<Product> inStock() {
        return jdbc.sql("SELECT id, name, price, stock FROM products WHERE stock > 0 ORDER BY name")
                .query((rs, rowNum) -> new Product(
                    rs.getLong("id"), rs.getString("name"),
                    rs.getBigDecimal("price"), rs.getObject("stock", Integer.class)))
                .list();
    }
}

Discussion

Be the first to comment on this lesson.

Reading columns

The NULL trap

Types that fit

A row mapper

Example

When to use it

More examples

Testing a mapper without a database

Mapping a join into nested records

The same mapping with Spring's JdbcClient

Related

Discussion