Spring JdbcTemplate: bound values and visible database constraints

This lesson uses the downloadable source kit: Java 21, Spring Boot 4.0.8 and its managed Spring Framework 7 dependencies. The version is pinned for repeatable builds.

Bind values, keep SQL fixed

JdbcReceiptLedger uses placeholders for receipt identifier and amount. The SQL statement structure is fixed. Parameter binding does not sanitize an arbitrary table name or ORDER BY expression supplied as text; allowlist any structural choices separately.

The database schema requires a positive amount and a unique identifier. The tests insert a valid row and reject a duplicate, confirming a database invariant rather than relying only on a controller constraint. Prepared JDBC values use the same underlying principle.

A helper does not choose a transaction for you

The class uses TransactionTemplate for multi-step changes. Calling two ordinary update methods without that boundary can commit the first before the second fails. The helper’s exception translation is useful to callers, but it is not atomicity.

The fixture uses DriverManagerDataSource and isolated H2 databases. A production service normally needs a bounded pool, migration management and tests against the chosen database. H2 behavior cannot stand in for every lock and isolation rule in PostgreSQL or MySQL.

Checked source

package in.aitrove.learning;
import java.util.UUID;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;
public class JdbcReceiptLedger {
    private final JdbcTemplate jdbc;
    private final TransactionTemplate transactions;
    public JdbcReceiptLedger() {
        DriverManagerDataSource database = new DriverManagerDataSource(
            "jdbc:h2:mem:ledger_" + UUID.randomUUID() + ";DB_CLOSE_DELAY=-1", "sa", "");
        jdbc = new JdbcTemplate(database);
        transactions = new TransactionTemplate(new DataSourceTransactionManager(database));
        jdbc.execute("create table receipt (id bigint primary key, amount_minor int check(amount_minor > 0))");
    }
    public void add(long id, int amount) { jdbc.update("insert into receipt(id,amount_minor) values (?,?)", id, amount); }
    public int count() { return jdbc.queryForObject("select count(*) from receipt", Integer.class); }
    public void batchThatFails() {
        transactions.executeWithoutResult(status -> { add(1, 40); add(2, -7); });
    }
    public void rollbackOnly() {
        transactions.executeWithoutResult(status -> { add(3, 60); status.setRollbackOnly(); });
    }
    public static void main(String[] args) {
        JdbcReceiptLedger ledger = new JdbcReceiptLedger();
        try { ledger.batchThatFails(); } catch (org.springframework.dao.DataIntegrityViolationException rejected) {
            System.out.println("constraint rejected");
        }
        System.out.println("after failure=" + ledger.count());
        ledger.rollbackOnly(); System.out.println("after rollback=" + ledger.count());
        ledger.add(4, 80); System.out.println("after commit=" + ledger.count());
    }
}

Test the boundary

Run mvn test in the source-kit directory. ReceiptBoundaryTest.boundInsertCommitsAndDuplicateIsRejected checks the behavior described here. Java excerpts belong to the named source-kit classes; they are not independent source files unless the complete class is shown.

Costs and boundaries

DriverManagerDataSource opens connections rather than pooling them. The count query scans behavior determined by the database plan; the fixture does not claim constant-time counting or production connection throughput.

Common Mistakes

• Bind values instead of concatenating input into SQL.
• Place related writes inside an explicit transaction.
• Do not infer production database isolation from H2 alone.

Read next

Transactions, Java JDBC PreparedStatement: values stay outside SQL grammar, Java connection pools: borrowed handles, rollback and admission timeout.

Extend this boundary

Continue with Spring JPA fetch plans: measure N+1 queries before changing mappings, Spring JPA optimistic locking: reject a stale stock update.