JdbcTemplate
The classic Spring JDBC workhorse: still the right tool for batch updates, callbacks and the many codebases that already use it.
List<T> rows = jdbcTemplate.query(sql, rowMapper, arg1, arg2);
T one = jdbcTemplate.queryForObject(sql, Type.class, arg1); // throws if no row
int changed = jdbcTemplate.update(sql, arg1, arg2);JdbcTemplate has been part of Spring since the first release. It opens and closes connections, binds parameters, translates SQLException into DataAccessException and joins the current transaction. Spring Boot auto-configures one, plus a NamedParameterJdbcTemplate, alongside JdbcClient. You will meet it in almost every existing Spring codebase, and JdbcClient is built on top of it.
Reading rows
List<Room> rooms = jdbcTemplate.query(
"SELECT id, name, capacity FROM room WHERE capacity >= ?",
new DataClassRowMapper<>(Room.class),
minCapacity);The varargs at the end are the values for each ?, in order. DataClassRowMapper maps columns onto a record's constructor, and a lambda (rs, rowNum) -> ... works for anything custom. queryForObject(sql, Long.class, args) reads a single value.
The classic trap: queryForObject throws EmptyResultDataAccessException when no row matches. Teams wrap it in try/catch to emulate an optional lookup, which is noisy and easy to get wrong. Use query(...) and take the first element, or switch that lookup to JdbcClient.optional().
Writing and batching
update(sql, args...) runs an INSERT, UPDATE or DELETE and returns the number of rows affected. Check that number: an update that hits zero rows usually means the record was deleted or the id was wrong, and silently returning 200 hides it.
Batching is where JdbcTemplate still beats the fluent API. batchUpdate(sql, items, batchSize, setter) sends rows to the database in groups, one round trip per group instead of one per row. Importing 10,000 rows one statement at a time spends most of its time on network latency. With PostgreSQL, add reWriteBatchedInserts=true to the JDBC URL so the driver merges a batch into multi-row inserts.
Settings and the named variant
The auto-configured template reads spring.jdbc.template.*: query-timeout stops a runaway query, fetch-size controls how many rows come back per round trip and max-rows caps the result size. These apply to every query through that template, so set them deliberately.
Positional ? markers get hard to follow past four or five values. NamedParameterJdbcTemplate accepts :name markers and a map or SqlParameterSource, and it also expands a collection into IN (:ids). For new code, JdbcClient gives you both styles in one API; keep JdbcTemplate for batches, row callbacks and code that already uses it.
Example
import java.sql.Timestamp;
import java.time.Instant;
import java.util.List;
import java.util.Optional;
import org.springframework.jdbc.core.DataClassRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
record Room(long id, String name, int capacity) {}
record PriceChange(long roomId, long pricePence, Instant validFrom) {}
public class RoomRepository {
private static final RowMapper<Room> ROOM = new DataClassRowMapper<>(Room.class);
private final JdbcTemplate jdbcTemplate;
public RoomRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public List<Room> withCapacityAtLeast(int minCapacity) {
return jdbcTemplate.query(
"SELECT id, name, capacity FROM room WHERE capacity >= ? ORDER BY name",
ROOM, minCapacity);
}
public Optional<Room> findById(long id) {
return jdbcTemplate.query("SELECT id, name, capacity FROM room WHERE id = ?", ROOM, id)
.stream()
.findFirst();
}
public boolean rename(long id, String name) {
return jdbcTemplate.update("UPDATE room SET name = ? WHERE id = ?", name, id) == 1;
}
public void importPrices(List<PriceChange> changes) {
jdbcTemplate.batchUpdate(
"INSERT INTO room_price (room_id, price_pence, valid_from) VALUES (?, ?, ?)",
changes,
500,
(ps, change) -> {
ps.setLong(1, change.roomId());
ps.setLong(2, change.pricePence());
ps.setTimestamp(3, Timestamp.from(change.validFrom()));
});
}
}
When to use it
- Import a supplier's nightly price file of 50,000 rows with batchUpdate in groups of 500.
- Maintain an older Spring service whose data access layer is built entirely on JdbcTemplate and RowMappers.
- Look up a page of rooms by a list of ids with NamedParameterJdbcTemplate and an IN clause.
More examples
IN lists with NamedParameterJdbcTemplate
Plain JDBC cannot bind a list to a single ?; the named template expands :ids into one marker per element. The empty check matters: IN () is a syntax error on most databases. Very large lists also produce huge statements, so split them or use a temporary table past a few thousand ids.
import java.util.Collection;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.DataClassRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
record RoomName(long id, String name) {}
@Repository
public class RoomLookup {
private final NamedParameterJdbcTemplate named;
public RoomLookup(NamedParameterJdbcTemplate named) {
this.named = named;
}
public List<RoomName> namesFor(Collection<Long> ids) {
if (ids.isEmpty()) {
return List.of();
}
return named.query(
"SELECT id, name FROM room WHERE id IN (:ids)",
Map.of("ids", ids),
new DataClassRowMapper<>(RoomName.class));
}
}
Template-wide limits
A query timeout means a bad plan fails fast instead of holding a pooled connection for minutes. max-rows is a safety net against a missing WHERE clause, not a substitute for pagination. Because these are global, a report that genuinely needs longer should override them per statement, for example with JdbcClient's withQueryTimeout(seconds), rather than raising the limit for every query.
# Applies to the auto-configured JdbcTemplate (and JdbcClient built on it)
spring.jdbc.template.query-timeout=5s
spring.jdbc.template.fetch-size=200
spring.jdbc.template.max-rows=10000
# Lets the PostgreSQL driver turn batches into multi-row inserts
spring.datasource.url=jdbc:postgresql://localhost:5432/bookings?reWriteBatchedInserts=true
Streaming rows with a RowCallbackHandler
A RowCallbackHandler sees one row at a time and builds the result as it goes. Memory only stays flat if the driver also reads in chunks: PostgreSQL loads the entire result set unless a fetch size is set (here through spring.jdbc.template.fetch-size) and the query runs inside a transaction, hence readOnly = true. For a sum this simple, GROUP BY in SQL is better still; use the callback when the per-row logic cannot be expressed in the query.
import java.util.HashMap;
import java.util.Map;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class OccupancyReport {
private final JdbcTemplate jdbcTemplate;
public OccupancyReport(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Transactional(readOnly = true)
public Map<String, Integer> nightsPerRoom(int year) {
Map<String, Integer> nights = new HashMap<>();
// Uses spring.jdbc.template.fetch-size; without one PostgreSQL loads the whole result first.
jdbcTemplate.query(
"SELECT r.name, b.nights FROM booking b JOIN room r ON r.id = b.room_id "
+ "WHERE extract(year FROM b.starts_at) = ?",
rs -> {
nights.merge(rs.getString("name"), rs.getInt("nights"), Integer::sum);
},
year);
return nights;
}
}
Discussion