Spring Framework
Spring JDBC and Declarative Transactions
Intermediate7 min readUpdated
Spring's JDBC support removes the connection, statement and result-set ceremony from plain SQL, and its transaction abstraction lets one @Transactional annotation work against JDBC, JPA or JTA. This guide shows JdbcTemplate and the newer JdbcClient in practice, explains what the transaction proxy actually does to a connection, and works through propagation, isolation and rollback behaviour with the failure modes that catch teams in production.
What you will learn
- Query and update a database with JdbcTemplate and JdbcClient without manual resource handling
- Explain why Spring translates SQLException into the unchecked DataAccessException hierarchy
- Configure a transaction manager and apply @Transactional with the right propagation, isolation and rollback rules
- Predict what happens with nested REQUIRED and REQUIRES_NEW calls, including UnexpectedRollbackException
- Use TransactionTemplate and @TransactionalEventListener when annotations are not enough
Plain JDBC is verbose in a way that hides bugs: obtain a connection, prepare a statement, bind parameters, iterate the result set, close three resources in the right order in a finally, and translate a vendor-specific SQLException code into something the caller can act on. Spring's spring-jdbc module does the ceremony for you and leaves the SQL, and its transaction abstraction turns "begin, commit, roll back on failure" into a single annotation that works the same way whether the underlying resource is a JDBC connection, a JPA EntityManager or a JTA transaction. This guide targets Spring Framework 6.x; it assumes a DataSource bean already exists — a HikariCP pool in most applications.
JdbcTemplate
JdbcTemplate is the original workhorse: a thread-safe object created once around a DataSource and injected wherever SQL runs. It acquires and releases connections, executes statements, iterates result sets and translates exceptions, and it does all of that inside whatever transaction is currently bound to the thread.
@Repository
public class JdbcCustomerRepository implements CustomerRepository {
private final JdbcTemplate jdbc;
public JdbcCustomerRepository(DataSource dataSource) {
this.jdbc = new JdbcTemplate(dataSource);
}
private static final RowMapper<Customer> MAPPER = (rs, rowNum) ->
new Customer(rs.getLong("id"), rs.getString("email"), rs.getString("name"));
@Override
public Optional<Customer> findById(long id) {
List<Customer> rows = jdbc.query("select id, email, name from customer where id = ?", MAPPER, id);
return rows.stream().findFirst(); // query() returns an empty list rather than throwing
}
@Override
public int countByDomain(String domain) {
return jdbc.queryForObject("select count(*) from customer where email like ?", Integer.class, "%@" + domain);
}
@Override
public long insert(Customer c) {
var keys = new GeneratedKeyHolder();
jdbc.update(con -> {
var ps = con.prepareStatement("insert into customer (email, name) values (?, ?)", new String[] {"id"});
ps.setString(1, c.email());
ps.setString(2, c.name());
return ps;
}, keys);
return keys.getKey().longValue();
}
}
A RowMapper<T> turns one row into one object; DataClassRowMapper does it automatically for records and classes with a matching constructor, and BeanPropertyRowMapper maps columns to setters by name. queryForObject throws EmptyResultDataAccessException when no row matches, so use query and take the first element when zero rows is a normal outcome. NamedParameterJdbcTemplate wraps the same engine with :name placeholders, which matters as soon as a statement has more than three parameters.
JdbcClient: the fluent API
Spring Framework 6.1 added JdbcClient, a fluent facade over both templates that reads better and unifies positional and named parameters:
@Repository
public class OrderRepository {
private final JdbcClient db;
public OrderRepository(DataSource dataSource) {
this.db = JdbcClient.create(dataSource);
}
public List<Order> openOrdersFor(long customerId) {
return db.sql("""
select id, customer_id, total_cents, status
from orders
where customer_id = :customer and status = :status
""")
.param("customer", customerId)
.param("status", "OPEN")
.query(Order.class) // record or bean mapped by column name
.list();
}
public Optional<Order> find(long id) {
return db.sql("select id, customer_id, total_cents, status from orders where id = ?")
.param(id) // positional
.query(Order.class)
.optional();
}
public int markShipped(long id) {
return db.sql("update orders set status = 'SHIPPED' where id = :id and status = 'OPEN'")
.param("id", id)
.update(); // affected rows
}
}
query(Class) maps by column name with a SimplePropertyRowMapper, so customer_id binds to a component or property named customerId. You can still pass an explicit RowMapper, and .query().singleValue() or .listOfRows() return scalars and maps for ad-hoc work. The client is the recommended entry point for new code; JdbcTemplate remains fully supported and is what JdbcClient delegates to.
Exception translation
Every Spring data-access technology throws subclasses of the unchecked DataAccessException, never a raw SQLException. The translator inspects the SQL state and vendor error code and maps it to something meaningful and portable:
| Exception | Typical cause |
|---|---|
DuplicateKeyException | Unique constraint violated on insert |
DataIntegrityViolationException | Foreign key, not-null or check constraint failed |
EmptyResultDataAccessException | queryForObject found no row |
IncorrectResultSizeDataAccessException | Expected one row, got several |
CannotAcquireLockException | Lock timeout or deadlock |
QueryTimeoutException | Statement exceeded its timeout |
BadSqlGrammarException | SQL syntax error or missing table |
DataAccessResourceFailureException | Connection could not be obtained |
The pay-off is that a service can catch DuplicateKeyException and return "email already registered" without knowing whether the database is PostgreSQL or Oracle, and that an unrecoverable failure propagates as a runtime exception — which, as the next section shows, is exactly what triggers a rollback.
The transaction abstraction
Spring's model has three parts. A PlatformTransactionManager knows how to begin, commit and roll back for one resource type: JdbcTransactionManager (the 6.x successor to DataSourceTransactionManager, adding exception translation on commit) for JDBC, JpaTransactionManager for JPA, JtaTransactionManager for container-managed XA transactions. A TransactionDefinition describes what is wanted — propagation, isolation, timeout, read-only flag. A TransactionStatus represents the running transaction and carries the rollback-only flag.
@Configuration
@EnableTransactionManagement
public class DataConfig {
@Bean
DataSource dataSource() { /* HikariDataSource */ }
@Bean
PlatformTransactionManager transactionManager(DataSource dataSource) {
return new JdbcTransactionManager(dataSource);
}
}
@EnableTransactionManagement registers a bean post-processor that wraps every bean carrying @Transactional in an AOP proxy. Around each annotated method the proxy's TransactionInterceptor:
- Asks the transaction manager for a transaction matching the method's definition — for
REQUIRED, joining the current one if the thread already has one, otherwise starting a new one. - For JDBC, that means obtaining a connection, calling
setAutoCommit(false), and binding the connection to the current thread inTransactionSynchronizationManager.JdbcTemplate,JdbcClientand Hibernate all look there first, which is why they participate in the transaction without being told about it. - Invokes the method.
- On normal return, commits (or, if the transaction was joined, does nothing and lets the outer method commit). On a rollback-triggering exception, rolls back and rethrows. Finally, unbinds and releases the connection.
The thread binding explains a rule that trips people who mix in threads: a transaction does not follow work submitted to an executor, a CompletableFuture or a parallel stream. The other thread has no bound connection and runs in auto-commit mode.
Using @Transactional
@Service
public class TransferService {
private final AccountRepository accounts;
private final LedgerRepository ledger;
public TransferService(AccountRepository accounts, LedgerRepository ledger) {
this.accounts = accounts;
this.ledger = ledger;
}
@Transactional(timeout = 5, rollbackFor = InsufficientFundsException.class)
public void transfer(long from, long to, long cents) throws InsufficientFundsException {
Account source = accounts.lockForUpdate(from);
if (source.balanceCents() < cents) {
throw new InsufficientFundsException(from); // checked: rolls back only because of rollbackFor
}
accounts.adjust(from, -cents);
accounts.adjust(to, cents);
ledger.record(from, to, cents);
}
@Transactional(readOnly = true)
public Statement statement(long account) { … }
}
Place the annotation on the class to apply it to every public method, then override on individual methods. The attributes that matter:
Propagation decides how a call relates to an existing transaction.
| Propagation | No current transaction | Current transaction exists |
|---|---|---|
REQUIRED (default) | Start one | Join it |
REQUIRES_NEW | Start one | Suspend it, start a new independent one |
SUPPORTS | Run without | Join it |
NOT_SUPPORTED | Run without | Suspend it, run without |
MANDATORY | Throw | Join it |
NEVER | Run without | Throw |
NESTED | Start one | Create a JDBC savepoint inside it |
REQUIRES_NEW is the one to understand deeply. It gives the inner method its own connection and its own commit, which is what you want for "record the audit row even if the business operation fails". It also means the outer transaction's uncommitted changes are invisible to the inner one, and that two connections from the pool are held at once — with a small pool, nested REQUIRES_NEW calls can deadlock waiting for connections. NESTED uses a savepoint on the same connection instead, so partial rollback is possible without a second connection, but only JDBC-based managers support it.
Isolation maps directly to the JDBC levels — READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE — with DEFAULT deferring to the database. Only the outermost transaction's isolation applies; an inner REQUIRED method asking for a different level is ignored unless validateExistingTransaction is enabled, in which case it throws.
Rollback rules default to rolling back on RuntimeException and Error only. rollbackFor and noRollbackFor adjust this per exception type. rollbackForClassName accepts class names as strings for exceptions you cannot reference at compile time, but explicit classes are clearer.
readOnly is a hint passed to the driver and to Hibernate; it can improve performance and enable replica routing but does not enforce anything.
timeout in seconds is applied to statements executed within the transaction, via JdbcTemplate's query timeout, not as a hard kill of the method.
Method visibility and proxies
Because @Transactional is applied by a proxy, everything said about Spring AOP applies. Self-invocation bypasses the transaction; an object created with new has none; a final method under CGLIB cannot be advised. Since Spring Framework 6.0, protected and package-private methods are advised by class-based proxies — earlier versions silently ignored the annotation on non-public methods, which was a long-standing source of confusion. Interface-based proxies still require public interface methods.
Programmatic transactions
Annotations cover most needs, but two cases call for code. When the transactional boundary does not align with a method — a loop that must commit every thousand rows, say — use TransactionTemplate:
@Service
public class ImportService {
private final TransactionTemplate tx;
private final JdbcClient db;
public ImportService(PlatformTransactionManager manager, JdbcClient db) {
this.tx = new TransactionTemplate(manager);
this.tx.setTimeout(30);
this.db = db;
}
public void importAll(List<Row> rows) {
for (int from = 0; from < rows.size(); from += 1_000) {
List<Row> chunk = rows.subList(from, Math.min(from + 1_000, rows.size()));
tx.executeWithoutResult(status -> {
chunk.forEach(r -> db.sql("insert into staging (k, v) values (:k, :v)")
.param("k", r.key()).param("v", r.value()).update());
}); // commits per chunk
}
}
}
When something must happen after the commit — sending an email, publishing to a message broker — do not do it inside the transactional method, where it would run even if the commit later fails. Publish an application event and handle it with @TransactionalEventListener, which by default fires in the AFTER_COMMIT phase and is skipped entirely on rollback.
Common mistakes
Catching the exception inside the transactional method and returning normally. The transaction still commits, because the interceptor never saw the exception. Either rethrow, or call TransactionAspectSupport.currentTransactionStatus().setRollbackOnly() when you must swallow it.
Swallowing an inner method's exception and hitting UnexpectedRollbackException. The inner REQUIRED call marked the shared transaction rollback-only; the outer commit then fails. Decide deliberately: propagate the failure, or isolate the inner work with REQUIRES_NEW.
Annotating the repository and not the service. Three repository calls become three transactions. A crash between the second and third leaves partial data.
Assuming @Transactional follows the thread pool. Async work runs outside the transaction and outside the connection binding. Pass data, not open transactions, to other threads.
Long transactions holding connections. A transactional method that calls a remote HTTP API keeps a pooled connection and database locks for the whole round trip. Move remote calls outside the transaction or before it.
Relying on readOnly to prevent writes. It is a hint. Enforce with database permissions if it matters.
REQUIRES_NEW with a tiny pool. Each level holds a connection; with pool size 2 and two nested levels under concurrent load, threads block waiting for connections and eventually time out.
Key takeaways
JdbcTemplateandJdbcClienthandle connections, statements, result sets and exception translation; you supply SQL and a row mapping.- Spring converts
SQLExceptioninto the portable, uncheckedDataAccessExceptionhierarchy, so services can react toDuplicateKeyExceptionwithout vendor knowledge. @Transactionalis an AOP proxy around the method that begins, binds to the thread, commits or rolls back — so self-invocation, other threads andnewobjects are outside it.REQUIREDjoins,REQUIRES_NEWsuspends and uses a second connection,NESTEDuses a savepoint; the default rollback rule covers runtime exceptions only.- Put transaction boundaries on service methods that represent a unit of work, keep them short, and never make remote calls inside them.
- Use
TransactionTemplatefor chunked commits and@TransactionalEventListenerfor work that must run only after a successful commit.
Sources and further reading
Frequently asked questions
- Why did my transaction not roll back when a checked exception was thrown?
- By default Spring rolls back only on RuntimeException and Error, mirroring the EJB convention that checked exceptions are expected business outcomes. Add rollbackFor = YourCheckedException.class (or Exception.class) to @Transactional, or rethrow as an unchecked exception. Conversely, noRollbackFor keeps a transaction alive across a specific runtime exception.
- What does UnexpectedRollbackException mean?
- An inner REQUIRED method threw a runtime exception, so the transaction interceptor marked the shared transaction rollback-only. The outer method caught the exception and returned normally, so the interceptor tried to commit — and the transaction manager refused, because it had been marked for rollback. Either let the exception propagate, or run the inner work in REQUIRES_NEW if its failure should be isolated.
- Should @Transactional go on the service or the repository?
- On the service layer. A use case usually touches several repositories and must succeed or fail as a whole; annotating repositories individually creates many small transactions and loses that atomicity. Repositories can carry @Transactional(readOnly = true) as a hint, but the unit of work is a service method.
- Is readOnly = true a real optimisation?
- It is a hint, and its effect depends on the stack. With JDBC it sets Connection.setReadOnly(true), which some drivers use to route to replicas or reject writes; with Hibernate it disables dirty checking and flushing, which can noticeably cut CPU and memory on large reads. It never guarantees that writes will fail, so do not rely on it for correctness.