JDBC transactions, batching and JdbcTemplate mapping

By default each JDBC statement commits alone.

🔎 Unravel this example Prerequisites (2)

Learn these first

JDBC and prepared statements try-with-resources

At a glance

Mental model

Sending a hundred letters: one trip to the post office with all of them in a bag (batch), instead of a hundred trips.

What happens, in order

1conn.setAutoCommit(false) starts a manual transaction; conn.commit() makes it permanent, conn.rollback() undoes itThe usual shape is try, commit, catch, rollback, finally close.
2ps.addBatch() per row and ps.executeBatch() at the end send many inserts in one round tripOn some drivers you also need a flag (reWriteBatchedInserts=true for PostgreSQL) to really get the speed-up.
3JdbcTemplate removes the boilerplate: query(sql, rowMapper, args), update, batchUpdateA RowMapper<T> turns one ResultSet row into an object.
4Spring translates SQLException into unchecked DataAccessException subclasses (DuplicateKeyException, DataIntegrityViolationException), so you catch meaning, not vendor error codes

Remember

Do not mix manual commit() with Spring-managed transactions on the same connection
Huge batches in one transaction hold locks and fill the undo log
Without batching, each executeUpdate costs a network round trip plus parse and plan work

Watch out

Forgetting rollback in the failure pathSingle giant transactions for millions of rowsAssuming batch inserts are fast without driver flagsCalling queryForObject when the row may not exist

Example (highlighted lines explain it)

try (Connection conn = dataSource.getConnection();     PreparedStatement ps = conn.prepareStatement("insert into events(id, payload) values (?, ?)")) {    conn.setAutoCommit(false);    try {        for (Event e : events) {            ps.setLong(1, e.id());            ps.setString(2, e.payload());            ps.addBatch();        }        ps.executeBatch();        conn.commit();    } catch (SQLException ex) {        conn.rollback();        throw ex;    }}

line 3 uses setAutoCommit, the core of JDBC transactions, batching and JdbcTemplate mapping
line 8 uses addBatch, the core of JDBC transactions, batching and JdbcTemplate mapping
line 10 uses executeBatch, the core of JDBC transactions, batching and JdbcTemplate mapping
line 11 uses commit, the core of JDBC transactions, batching and JdbcTemplate mapping
line 13 uses rollback, the core of JDBC transactions, batching and JdbcTemplate mapping

Read the full explanation (about 1 min)

By default each JDBC statement commits alone. For several statements that must succeed together you control the transaction yourself, and for many rows you batch them so the database is not asked one row at a time.

A mental model

Sending a hundred letters: one trip to the post office with all of them in a bag (batch), instead of a hundred trips. A transaction is a sealed envelope: all inside arrive or none.

How it works

conn.setAutoCommit(false) starts a manual transaction; conn.commit() makes it permanent, conn.rollback() undoes it. The usual shape is try, commit, catch, rollback, finally close.

ps.addBatch() per row and ps.executeBatch() at the end send many inserts in one round trip. On some drivers you also need a flag (reWriteBatchedInserts=true for PostgreSQL) to really get the speed-up.

JdbcTemplate removes the boilerplate: query(sql, rowMapper, args), update, batchUpdate. A RowMapper<T> turns one ResultSet row into an object.

Spring translates SQLException into unchecked DataAccessException subclasses (DuplicateKeyException, DataIntegrityViolationException), so you catch meaning, not vendor error codes.

Going deeper

Do not mix manual commit() with Spring-managed transactions on the same connection. Either let @Transactional own it or write plain JDBC; mixing causes half-committed work.

Huge batches in one transaction hold locks and fill the undo log. Commit every 1,000 to 10,000 rows, and make the import restartable.

queryForObject throws EmptyResultDataAccessException for no rows. Use query(...) with a list or Optional handling when absence is normal.

Under the hood

Without batching, each executeUpdate costs a network round trip plus parse and plan work. Batching lets the driver send an array of parameter sets in one protocol message (or rewrite them into a multi-row INSERT), so the cost per row falls from milliseconds to microseconds.

Common mistakes

Forgetting rollback in the failure path.
Single giant transactions for millions of rows.
Assuming batch inserts are fast without driver flags.
Calling queryForObject when the row may not exist.

How Unravel recognises it

When you paste code, Unravel looks for these signs of jdbc transactions, batching and jdbctemplate mapping. Spotting them yourself is a good habit when you read unfamiliar code:

Types: BatchPreparedStatementSetter SimpleJdbcInsert KeyHolder GeneratedKeyHolder RowMapper ResultSetExtractor BeanPropertyRowMapper SqlParameterSource MapSqlParameterSource BeanPropertySqlParameterSource DataAccessException DuplicateKeyException EmptyResultDataAccessException DataIntegrityViolationException and 1 more
Method calls: setAutoCommit() commit() rollback() addBatch() executeBatch() setSavepoint() batchUpdate()

Part of a learning path

Data stores beyond SQL · previous: SQL mappers and builders (MyBatis, jOOQ)

Related concepts

@TransactionalSpring Connection pools (HikariCP)Persistence

Frequently asked questions

What is JDBC transactions, batching and JdbcTemplate mapping?

What are the most common mistakes with JDBC transactions, batching and JdbcTemplate mapping?

Forgetting rollback in the failure path. Single giant transactions for millions of rows. Assuming batch inserts are fast without driver flags. Calling queryForObject when the row may not exist.

How can I see JDBC transactions, batching and JdbcTemplate mapping in real code?

Paste your own Java into Unravel and it will point out where jdbc transactions, batching and jdbctemplate mapping appears, explain how it works at the detail level you choose, and draw the flow of the code around it.