Mapping Rows to Objects
Every ResultSet your DAO executes gives you a cursor over raw column values — strings, integers, timestamps. Your application does not think in rows; it thinks in domain objects like User, Order, or Product. The translation layer between those two worlds is called row mapping, and getting it right is more nuanced than it looks at first glance.
The Problem with Inline Mapping
The most obvious approach is to convert columns to fields directly inside the DAO method that executes the query:
public List<User> findAll() throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT id, email, full_name, created_at FROM users";
try (Connection conn = DataSourceFactory.get().getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
User u = new User();
u.setId(rs.getLong("id"));
u.setEmail(rs.getString("email"));
u.setFullName(rs.getString("full_name"));
u.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
users.add(u);
}
}
return users;
}
This works for a single method. The moment your DAO has five methods that all return User objects — findById, findByEmail, findByRole, etc. — you have the same six-line mapping block copy-pasted everywhere. One column rename in the schema and you hunt down every copy.
Extracting a Row Mapper
The fix is to extract the mapping logic into a private method (or a dedicated class). A private method is the simplest form:
// Inside UserDao
private User mapRow(ResultSet rs) throws SQLException {
User u = new User();
u.setId(rs.getLong("id"));
u.setEmail(rs.getString("email"));
u.setFullName(rs.getString("full_name"));
u.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
return u;
}
public List<User> findAll() throws SQLException {
List<User> users = new ArrayList<>();
String sql = "SELECT id, email, full_name, created_at FROM users";
try (Connection conn = DataSourceFactory.get().getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
users.add(mapRow(rs));
}
}
return users;
}
public Optional<User> findById(long id) throws SQLException {
String sql = "SELECT id, email, full_name, created_at FROM users WHERE id = ?";
try (Connection conn = DataSourceFactory.get().getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
}
}
}
Single-row vs. multi-row results: For queries expected to return at most one row, use Optional<T> as the return type. It forces callers to handle the not-found case explicitly instead of returning null and hoping.
Handling Nullable Columns
JDBC has a subtle trap with numeric types: rs.getInt("discount") returns 0 if the column is NULL, not an exception. Your code silently treats a missing discount as zero. The correct check is:
Integer discount = rs.getInt("discount");
if (rs.wasNull()) {
discount = null;
}
// or, more concisely:
Integer discount = rs.getObject("discount", Integer.class);
rs.getObject(column, Class) is available since Java 7 and returns null when the column is NULL, making null-handling explicit and clean. Use it for any nullable numeric or boolean column.
Never use getInt / getLong / getDouble for nullable columns. These primitive-returning methods silently return zero on NULL. Always use getObject(col, BoxedType.class) or check wasNull() immediately after.
Mapping Dates and Timestamps
Java 8 introduced the java.time API, which is far superior to java.sql.Date and java.sql.Timestamp. Modern JDBC drivers (MySQL Connector/J 8+, PostgreSQL JDBC 42.x) support getObject with java.time types directly:
// Preferred — no legacy sql.Timestamp involved
LocalDateTime createdAt = rs.getObject("created_at", LocalDateTime.class);
LocalDate birthDate = rs.getObject("birth_date", LocalDate.class);
Instant updatedAt = rs.getObject("updated_at", Instant.class);
If your driver does not yet support this (older versions), fall back to getTimestamp and convert:
Timestamp ts = rs.getTimestamp("created_at");
LocalDateTime createdAt = ts != null ? ts.toLocalDateTime() : null;
Building a Generic RowMapper Interface
When a project has many DAOs, duplicating the mapping pattern across them becomes tedious. A lightweight functional interface mirrors what Spring's RowMapper does — and it works with lambdas:
@FunctionalInterface
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
A shared JdbcTemplate-style helper then accepts the mapper:
public class JdbcHelper {
private final DataSource dataSource;
public JdbcHelper(DataSource dataSource) {
this.dataSource = dataSource;
}
public <T> List<T> query(String sql, RowMapper<T> mapper, Object... params)
throws SQLException {
List<T> results = new ArrayList<>();
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
try (ResultSet rs = ps.executeQuery()) {
int row = 0;
while (rs.next()) {
results.add(mapper.mapRow(rs, ++row));
}
}
}
return results;
}
}
Usage at the DAO level becomes a single, readable expression:
// Mapper defined once as a lambda or method reference
private static final RowMapper<User> USER_MAPPER = (rs, n) -> {
User u = new User();
u.setId(rs.getLong("id"));
u.setEmail(rs.getString("email"));
u.setFullName(rs.getString("full_name"));
u.setCreatedAt(rs.getObject("created_at", LocalDateTime.class));
return u;
};
// Used across all query methods
public List<User> findAll() throws SQLException {
return jdbc.query("SELECT id, email, full_name, created_at FROM users", USER_MAPPER);
}
public List<User> findByRole(String role) throws SQLException {
return jdbc.query(
"SELECT id, email, full_name, created_at FROM users WHERE role = ?",
USER_MAPPER, role
);
}
Declare the mapper as a static constant. It has no mutable state, so a single instance shared across all method calls is safe, cheap, and avoids repeated lambda allocation on hot paths.
Mapping Joined Queries (One-to-Many)
Pulling two related tables in one query is more efficient than N+1 calls. The mapping logic must detect which parent row the current result-set row belongs to and append child objects accordingly:
public List<Order> findOrdersWithItems(long customerId) throws SQLException {
String sql =
"SELECT o.id AS order_id, o.total, " +
" i.id AS item_id, i.product_name, i.quantity " +
"FROM orders o " +
"JOIN order_items i ON i.order_id = o.id " +
"WHERE o.customer_id = ? " +
"ORDER BY o.id";
Map<Long, Order> orderMap = new LinkedHashMap<>();
try (Connection conn = DataSourceFactory.get().getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setLong(1, customerId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
long orderId = rs.getLong("order_id");
Order order = orderMap.computeIfAbsent(orderId, id -> {
Order o = new Order();
o.setId(id);
try { o.setTotal(rs.getBigDecimal("total")); }
catch (SQLException e) { throw new RuntimeException(e); }
o.setItems(new ArrayList<>());
return o;
});
OrderItem item = new OrderItem();
item.setId(rs.getLong("item_id"));
item.setProductName(rs.getString("product_name"));
item.setQuantity(rs.getInt("quantity"));
order.getItems().add(item);
}
}
}
return new ArrayList<>(orderMap.values());
}
LinkedHashMap preserves insertion order so orders are returned in the same sequence the DB sent them. computeIfAbsent creates the parent Order only on its first appearance, then every subsequent row just appends an OrderItem.
Column Aliases and SELECT *
Always select columns explicitly — never use SELECT * in production DAOs. Explicit column lists mean:
- Adding a column to the schema never silently changes your result set or breaks your mapper.
- Column aliases (
o.id AS order_id) eliminate ambiguity in joins.
- You only transfer the columns you actually use, reducing network traffic.
Summary
Row mapping is the bridge between JDBC's tabular world and your domain model. Start with a private mapRow method to consolidate mapping logic. Use getObject(col, Type.class) for nullable and date columns. For larger codebases, promote the pattern to a RowMapper functional interface so each DAO defines a single constant mapper reused across all its query methods. For joined queries, accumulate parent objects in a LinkedHashMap and append children as you iterate. These techniques keep your DAOs concise, correct, and maintainable as schemas evolve.