Spring JDBC and Database Access - Complete Notes

A complete guide to JDBC foundations, DataSource and HikariCP, JdbcTemplate, named parameters, row mapping, batching, generated keys, exception translation, transactions, PostgreSQL, security, testing, performance, production operations, and choosing JDBC instead of an ORM.

00. The Spring JDBC mental model

Spring JDBC keeps SQL visible while removing repetitive JDBC plumbing. The application still chooses the SQL, parameters, and object mapping. Spring obtains and releases resources, executes the statement, participates in transactions, and translates low-level failures.

A useful request-to-database path is:

1. A controller or message consumer invokes an application service.
2. The service establishes the business and transaction boundary.
3. A repository expresses one database operation with SQL and parameters.
4. A Spring JDBC template obtains the transaction-bound or pooled connection.
5. The PostgreSQL JDBC driver sends the protocol messages to PostgreSQL.
6. PostgreSQL plans and executes the statement.
7. A mapper converts each result row into an application value.
8. Spring closes JDBC resources and returns the connection to the pool.

01. JDBC foundations

JDBC is the standard Java API for connecting to tabular data sources, executing SQL, binding parameters, reading results, controlling transactions, and inspecting database metadata.

Raw JDBC usually repeats this workflow for every operation:

String sql = "select id, email from customer where id = ?";

try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, customerId);

    try (ResultSet result = statement.executeQuery()) {
        if (!result.next()) {
            return Optional.empty();
        }
        return Optional.of(
            new Customer(result.getLong("id"), result.getString("email"))
        );
    }
} catch (SQLException failure) {
    throw translate(failure);
}

The code is valid, but the business-specific parts are only the SQL, parameter, expected cardinality, and mapping. The rest is infrastructure that must be correct every time.

02. Problems Spring JDBC solves

Spring uses the template and callback pattern. The template controls the stable workflow, while application callbacks supply only the parts that vary.

• Obtains a connection from a configured DataSource.
• Reuses the connection already bound to the current Spring transaction.
• Creates, executes, and closes statements.
• Iterates and closes result sets.
• Binds ordinary Java values to prepared statements.
• Converts result rows through RowMapper and related callbacks.
• Translates checked SQLException failures into Spring's exception hierarchy.
• Supports updates, generated keys, batches, stored procedures, and streaming callbacks.

03. Dependencies and Boot auto-configuration

A Spring Boot JDBC application normally uses the JDBC starter and a database-specific driver.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

The starter brings Spring JDBC and HikariCP under Spring Boot's dependency management. With a suitable driver and configuration, Boot can create a pooled DataSource, JdbcTemplate, NamedParameterJdbcTemplate, and JDBC transaction manager. Defining an application DataSource bean makes Boot's default back off.

04. DataSource and connection ownership

javax.sql.DataSource is a connection factory. In a production Boot application, its implementation is usually a connection pool rather than a factory that creates a new physical database connection for every call.

A physical connection includes a network socket, authentication, PostgreSQL backend process, and session state. Creating one per query is expensive. A pool maintains a bounded set and lends a logical connection handle to a caller.

1. getConnection() borrows an available pool entry.
2. JDBC work uses the underlying physical session.
3. close() on the proxy returns it to the pool.
4. The pool resets important connection state before later reuse.

Keep the DataSource as a singleton infrastructure bean. Do not cache a Connection in a repository field. Connections are mutable session resources and may be bound to a particular thread and transaction.

How a transaction gets the same connection

A Spring JDBC transaction manager obtains a connection, disables auto-commit as needed, and binds a holder to the current execution thread. JdbcTemplate uses Spring's data-source utilities, so repository calls inside that transaction obtain the same connection. At completion, the manager commits or rolls back, resets state, unbinds, and releases the connection.

Code that directly calls dataSource.getConnection() can bypass this coordination. Prefer the template or DataSourceUtils in infrastructure code that must participate in Spring-managed transactions.

05. HikariCP connection pooling

HikariCP is Spring Boot's preferred JDBC pool when it is available. Its purpose is bounded, efficient connection reuse, not making the database capable of unlimited concurrency.

spring:
  datasource:
    url: jdbc:postgresql://db.internal:5432/orders
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    hikari:
      pool-name: orders-pool
      maximum-pool-size: 12
      connection-timeout: 2000
      max-lifetime: 1740000
      keepalive-time: 120000
  jdbc:
    template:
      query-timeout: 5

These are examples, not universal values. The pool size must be load-tested. Twelve application instances with a maximum of 20 can request 240 database connections, before migrations, administration, background jobs, and other applications are counted.

Pool sizing principles

• A larger pool can increase database contention and context switching.
• Start small, measure connection wait time, database CPU, locks, and query latency.
• Count every replica, worker process, read pool, and deployment overlap.
• Keep application request concurrency bounded relative to downstream capacity.
• A saturated pool is often a symptom of slow SQL or long transactions.
• Do not use pool growth to hide leaked connections or missing query timeouts.

Session state and pool safety

PostgreSQL settings, temporary tables, advisory locks, and session variables may outlive one statement. A pool resets standard JDBC state, but cannot understand every custom session change. Prefer transaction-local settings such as SET LOCAL, release advisory locks, and do not assume the next borrow uses a fresh server session.

06. PostgreSQL configuration and credentials

Boot reads ordinary data-source settings under spring.datasource and Hikari-specific settings under spring.datasource.hikari.

• The URL commonly has the form jdbc:postgresql://host:5432/database.
• The pgJDBC driver is discovered through Java's service-provider mechanism.
• Boot can infer the driver class from the JDBC URL in normal cases.
• Credentials should come from a secret manager or protected environment injection.
• TLS identity verification is required across untrusted networks.
• Use a least-privilege role specific to the application and environment.

spring.datasource.url=jdbc:postgresql://db.example.net:5432/orders?sslmode=verify-full
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}

Multiple data sources

Multiple databases require explicit bean names, qualifiers, templates, and transaction managers. A transaction managed for one data source does not automatically make work on another atomic. Distributed atomicity needs a suitable distributed transaction system, or more commonly an application pattern such as an outbox with idempotent consumers.

07. Schema ownership and migrations

The database schema is a versioned production contract. Use a migration tool such as Flyway or Liquibase rather than relying on repository code to create tables at runtime.

create table customer (
    id bigint generated always as identity primary key,
    email text not null,
    display_name text not null,
    status text not null,
    version integer not null default 0,
    created_at timestamptz not null default current_timestamp,
    constraint customer_email_unique unique (email),
    constraint customer_status_check
        check (status in ('ACTIVE', 'SUSPENDED'))
);

create index customer_created_at_id_idx
    on customer (created_at desc, id desc);

• Let constraints enforce invariants even if Java validates them too.
• Use expand-and-contract migrations for zero-downtime compatibility.
• Test application version N against the schema states seen during rollout.
• Run migrations once per deployment under controlled credentials.
• Give the runtime role only the DML and sequence privileges it needs.

08. JdbcTemplate fundamentals

JdbcTemplate is the central classic Spring JDBC class. Once configured, it is thread-safe and should be shared. Repository methods provide SQL, bind values, and map results.

@Repository
final class CustomerRepository {
    private final JdbcTemplate jdbc;

    CustomerRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    List<CustomerSummary> findActive(int limit) {
        return jdbc.query("""
            select id, email, display_name
            from customer
            where status = ?
            order by id
            limit ?
            """,
            customerSummaryMapper(),
            "ACTIVE",
            limit
        );
    }

    private static RowMapper<CustomerSummary> customerSummaryMapper() {
        return (rs, rowNumber) -> new CustomerSummary(
            rs.getLong("id"),
            rs.getString("email"),
            rs.getString("display_name")
        );
    }
}

The repository owns persistence language. The service owns business orchestration. Avoid a generic repository with methods like runSql(String), because it spreads SQL and persistence semantics throughout the application.

The newer JdbcClient facade

Current Spring Framework versions also provide JdbcClient, a fluent facade that delegates to JdbcTemplate and NamedParameterJdbcTemplate. It can make ordinary query and update code more readable. The classic templates remain important because they expose the complete callback, batch, and lower-level feature set. Choose one consistent repository style rather than wrapping every Spring JDBC API in another generic abstraction.

Common operations

09. Query cardinality and null handling

Choose an API whose contract matches the number of rows the query is allowed to return.

Optional<Customer> findById(long id) {
    List<Customer> rows = jdbc.query("""
        select id, email, display_name, status, version, created_at
        from customer
        where id = ?
        """, CUSTOMER_MAPPER, id);

    return DataAccessUtils.optionalResult(rows);
}

queryForObject expects exactly one row. Zero rows result in an EmptyResultDataAccessException, and multiple rows result in an incorrect-result-size exception. That is useful when absence means a data invariant was violated. For a normal optional lookup, express optionality intentionally.

Long managerId = rs.getObject("manager_id", Long.class);
Instant deletedAt = rs.getObject("deleted_at", OffsetDateTime.class) == null
    ? null
    : rs.getObject("deleted_at", OffsetDateTime.class).toInstant();

10. RowMapper and result extraction

A RowMapper<T> maps the current row to one object. Spring advances the result set and calls it once per row.

• Read columns by stable alias instead of fragile numeric position.
• List columns explicitly instead of using select *.
• Keep mapper code deterministic and free of database calls.
• Use immutable records or values when they match the result shape.
• Create purpose-specific projections instead of partially populated domain objects.

private static final RowMapper<OrderView> ORDER_VIEW_MAPPER = (rs, rowNum) ->
    new OrderView(
        rs.getLong("order_id"),
        rs.getString("customer_email"),
        rs.getBigDecimal("total_amount"),
        rs.getObject("placed_at", OffsetDateTime.class).toInstant()
    );

One-to-many results

A join can return one order row per item. A RowMapper<Order> would then produce duplicate orders. Use a ResultSetExtractor to aggregate ordered rows, query parent and children separately in a bounded way, or return a flat reporting projection.

return jdbc.query(sql, rs -> {
    Map<Long, MutableOrder> grouped = new LinkedHashMap<>();
    while (rs.next()) {
        long orderId = rs.getLong("order_id");
        MutableOrder order = grouped.computeIfAbsent(orderId, ignored ->
            mapOrder(rs)
        );
        order.addItem(mapItem(rs));
    }
    return grouped.values().stream().map(MutableOrder::freeze).toList();
});

The SQL should order by the grouping key when the extractor depends on contiguous rows. Bound the result set because aggregation retains objects in memory.

Automatic property mapping

BeanPropertyRowMapper and DataClassRowMapper reduce mapping code for simple projections. Explicit mappers are safer when conversions, required columns, domain types, or schema evolution matter. Reflection convenience should not hide an unclear data contract.

11. NamedParameterJdbcTemplate

NamedParameterJdbcTemplate replaces positional question marks with descriptive parameter names and delegates to a wrapped JdbcTemplate.

List<CustomerSummary> search(Set<String> statuses, Instant createdAfter) {
    String sql = """
        select id, email, display_name
        from customer
        where status in (:statuses)
          and created_at >= :createdAfter
        order by created_at, id
        """;

    var parameters = new MapSqlParameterSource()
        .addValue("statuses", statuses)
        .addValue("createdAfter", Timestamp.from(createdAfter));

    return namedJdbc.query(sql, parameters, CUSTOMER_SUMMARY_MAPPER);
}

Named parameters reduce swapped-parameter errors and support expanding a collection for an IN predicate. An empty collection may produce invalid SQL or unwanted semantics, so define the empty case in Java. Very large lists should use another design, such as a temporary table, array parameter, staged data, or a joined relation.

12. Updates, row counts, and generated keys

An update's affected-row count is part of its correctness contract. Check it when the operation expects exactly one row.

boolean rename(long id, int expectedVersion, String newName) {
    int changed = jdbc.update("""
        update customer
        set display_name = ?, version = version + 1
        where id = ? and version = ?
        """, newName, id, expectedVersion);

    if (changed > 1) {
        throw new IncorrectUpdateSemanticsDataAccessException(
            "Customer identity was not unique"
        );
    }
    return changed == 1;
}

Zero rows can mean not found, stale version, or a predicate that no longer matches. Decide whether the application needs to distinguish those outcomes. A second query can distinguish them but introduces another concurrency observation, so design the API carefully.

Generated keys

long insert(CustomerDraft draft) {
    var keys = new GeneratedKeyHolder();

    jdbc.update(connection -> {
        PreparedStatement statement = connection.prepareStatement("""
            insert into customer (email, display_name, status)
            values (?, ?, ?)
            """, new String[] {"id"});
        statement.setString(1, draft.email());
        statement.setString(2, draft.displayName());
        statement.setString(3, "ACTIVE");
        return statement;
    }, keys);

    Number id = Objects.requireNonNull(keys.getKey(), "Database returned no id");
    return id.longValue();
}

PostgreSQL also supports INSERT ... RETURNING, which can return one or several generated or defaulted columns and map them like a query:

Customer insert(CustomerDraft draft) {
    return jdbc.queryForObject("""
        insert into customer (email, display_name, status)
        values (?, ?, 'ACTIVE')
        returning id, email, display_name, status, version, created_at
        """, CUSTOMER_MAPPER, draft.email(), draft.displayName());
}

RETURNING avoids a follow-up select and captures server defaults from the same statement. It is PostgreSQL-specific, which is acceptable when the system intentionally targets PostgreSQL.

13. Batch operations

Batching sends repeated DML work with fewer client-server round trips. It does not automatically make a large operation atomic and it does not remove database work.

int[] insertAll(List<CustomerDraft> drafts) {
    String sql = """
        insert into customer (email, display_name, status)
        values (:email, :displayName, 'ACTIVE')
        """;

    SqlParameterSource[] batch = drafts.stream()
        .map(draft -> new MapSqlParameterSource()
            .addValue("email", draft.email())
            .addValue("displayName", draft.displayName()))
        .toArray(SqlParameterSource[]::new);

    return namedJdbc.batchUpdate(sql, batch);
}

• Choose a bounded chunk size through measurement.
• Wrap related batches in one intentional transaction if all-or-nothing is required.
• Inspect returned update counts when individual outcomes matter.
• Specify SQL types for ambiguous null batch values if driver inference is unreliable.
• Do not include SELECT statements in a JDBC update batch.
• For very large PostgreSQL ingestion, benchmark COPY against batches.

pgJDBC batch rewrite

The pgJDBC reWriteBatchedInserts=true connection property can rewrite compatible batches into multi-value inserts and reduce round trips. Treat it as a measured driver-level optimization. Verify generated-key behavior, statement compatibility, failure reporting, and memory use for the driver version managed by the application.

14. Exception translation

Spring translates checked SQLException failures into the unchecked DataAccessException hierarchy, which expresses failure categories independently of a particular database API.

Current Spring Framework versions use JDBC 4 exception subclasses with SQL-state fallback by default. Translation allows repository code to avoid driver-specific catch blocks. It does not mean every database error can be handled generically.

15. Transaction boundaries

A transaction boundary should surround one business operation that must preserve a database invariant. It normally belongs on a public application-service method, not around each repository statement.

@Service
final class TransferService {
    private final AccountRepository accounts;
    private final OutboxRepository outbox;

    TransferService(AccountRepository accounts, OutboxRepository outbox) {
        this.accounts = accounts;
        this.outbox = outbox;
    }

    @Transactional
    public void transfer(long fromId, long toId, BigDecimal amount) {
        int debited = accounts.debitIfEnough(fromId, amount);
        if (debited != 1) {
            throw new InsufficientFundsException(fromId);
        }

        accounts.credit(toId, amount);
        outbox.append(new TransferCompleted(fromId, toId, amount));
    }
}

Spring's default transaction propagation is REQUIRED. The method joins an existing transaction or creates one. By default, unchecked exceptions and Error trigger rollback, while checked exceptions do not. Configure rollback rules when the business exception model requires different behavior.

Proxy behavior

Declarative transactions usually run through a Spring AOP proxy. A call from another bean enters the proxy and receives transaction advice. Self-invocation, where one method calls another method on this, bypasses the proxy, so an annotation on the inner method does not create new transactional behavior in default proxy mode.

Keep transactions short

• Do validation that needs no database access before opening the transaction.
• Do not hold a database transaction while waiting for a user.
• Avoid remote HTTP calls inside a transaction.
• Use database statement and lock timeouts.
• Access rows in a consistent order to reduce deadlocks.
• Move reliable external side effects through an outbox or equivalent pattern.

A transaction occupies a connection and may retain locks, row versions, and database resources. Long transactions reduce pool capacity and can interfere with PostgreSQL vacuum progress.

Programmatic boundaries

TransactionTemplate is useful when code needs an explicit small scope, sequential transactions, or a commit before a later non-transactional action. Keep the boundary readable and do not mix manual Connection.commit() with Spring-managed transactions.

16. Query safety and SQL injection

Bind every untrusted value through a prepared-statement parameter. Parameters represent data, while the SQL string defines executable structure.

// Unsafe: attacker input becomes SQL syntax.
String unsafe = "select * from customer where email = '" + email + "'";

// Safe: the driver binds email as a value.
return jdbc.query(
    "select id, email from customer where email = ?",
    CUSTOMER_MAPPER,
    email
);

private static final Map<String, String> SORT_COLUMNS = Map.of(
    "created", "created_at",
    "email", "email"
);

String column = Optional.ofNullable(SORT_COLUMNS.get(requestedSort))
    .orElseThrow(InvalidSortException::new);
String direction = ascending ? "asc" : "desc";

String sql = """
    select id, email, display_name
    from customer
    order by %s %s, id %s
    limit ?
    """.formatted(column, direction, direction);

Prepared statements prevent value injection, but authorization remains separate. A safe query can still disclose another tenant's data if it omits the tenant predicate. Carry trusted tenant identity from authentication and include it in every relevant read and write.

17. Resource management and streaming

Templates close ordinary JDBC statements and result sets, and release connections correctly. Results that escape as a stream or callback-driven cursor have a longer resource lifetime.

• Consume a JDBC-backed Stream in try-with-resources.
• Keep the transaction and connection open until streaming is complete.
• Set an appropriate fetch size and test driver behavior.
• Do not return a lazy stream through a web layer without explicit lifecycle control.
• Prefer bounded pagination for ordinary APIs.
• Cancel or time out abandoned work so the connection returns to the pool.

@Transactional(readOnly = true)
public void exportActiveCustomers(Consumer<CustomerSummary> sink) {
    try (Stream<CustomerSummary> rows = jdbc.queryForStream(
        """
        select id, email, display_name
        from customer
        where status = 'ACTIVE'
        order by id
        """,
        CUSTOMER_SUMMARY_MAPPER
    )) {
        rows.forEach(sink);
    }
}

18. PostgreSQL-specific integration

JDBC covers common relational types, while PostgreSQL offers useful types and SQL features that sometimes require deliberate mapping.

namedJdbc.update("""
    update customer
    set preferences = cast(:preferences as jsonb)
    where id = :id
    """,
    new MapSqlParameterSource()
        .addValue("preferences", objectMapper.writeValueAsString(preferences))
        .addValue("id", id)
);

Avoid storing data as JSON only to escape schema design. Use jsonb when document-like attributes genuinely vary, and add expression or GIN indexes for measured access paths.

Atomic upsert

insert into idempotency_record (scope, request_key, request_hash, status)
values (:scope, :key, :hash, 'STARTED')
on conflict (scope, request_key) do nothing

Let a unique constraint arbitrate concurrency. A preceding "does it exist?" query has a race because another transaction can insert between the check and write.

Large values

JDBC supports binary and character streams, but storing large user files directly in frequently accessed tables can inflate backups, replication, and query I/O. Object storage plus database metadata is often a better architecture. When the database is appropriate, stream with explicit size limits and do not materialize unbounded content.

19. Performance engineering

Database performance comes primarily from correct data modeling, selective SQL, useful indexes, bounded work, short transactions, and measured capacity. A template call is rarely the main cost.

Query shape

• Select only needed columns.
• Filter and aggregate in PostgreSQL when it reduces transferred data correctly.
• Use deterministic ordering for pages.
• Avoid one query per row by fetching the required relation in a set-oriented query.
• Use EXPLAIN (ANALYZE, BUFFERS) in a safe representative environment.
• Keep table statistics current and inspect actual versus estimated row counts.

Pagination

Offset pagination is convenient but deep offsets still make PostgreSQL find and discard earlier rows. Keyset pagination continues after the ordered key and is usually more stable for large, changing datasets.

select id, email, created_at
from customer
where (created_at, id) < (:cursorCreatedAt, :cursorId)
order by created_at desc, id desc
limit :limit

The supporting index should follow the filtering and ordering access path. Encode and validate the cursor as an opaque API token. Request one extra row to determine whether a next cursor exists.

Timeout hierarchy

• The incoming request deadline is the outer budget.
• Connection acquisition timeout must leave time for query execution and response work.
• Statement timeout stops unexpectedly expensive SQL.
• Lock timeout limits waiting for contested locks.
• Transaction timeout bounds the complete transactional unit.

A JDBC timeout may rely on driver cancellation and is not a guarantee of instantaneous server-side termination. PostgreSQL statement_timeout provides a server-enforced safeguard. Set it per role, transaction, or connection with clear ownership and verify interaction with pooling.

Prepared statement behavior

pgJDBC can switch repeated prepared statements to server-prepared execution based on its prepareThreshold. This may reduce parse work and enable binary transfer, but generic versus custom planning can affect data-skewed queries. Do not tune it without measurements from representative parameters and driver documentation.

20. Production operations and observability

Operate the pool, driver, SQL, and PostgreSQL as one dependency chain. Observe saturation and latency before users experience timeouts.

• Measure active, idle, maximum, pending, and timed-out pool acquisitions.
• Measure query latency by operation name, not raw SQL containing values.
• Track errors by translated category and PostgreSQL SQL state.
• Watch database connections, CPU, I/O, locks, deadlocks, and long transactions.
• Use slow-query tooling and pg_stat_statements where operational policy permits.
• Correlate a request trace with repository operation and database session metadata.
• Alert on trends and exhausted budgets, not only process health.

Health checks

Liveness should answer whether the process must restart. It should not fail only because the database is briefly unavailable. Readiness may reflect whether the instance can serve database-dependent traffic, but frequent probes must not create a connection storm or make a dependency outage worse.

Graceful shutdown

Stop accepting new work, allow bounded in-flight requests to finish, stop workers, and close the application context so HikariCP closes physical connections. Deployment grace periods must exceed the intended request drain window but remain bounded.

Connection timeout diagnostic order

1. Check pool pending, active, idle, and maximum counts.
2. Find long requests, transactions, streams, and leaked handles.
3. Inspect slow queries, blocked sessions, deadlocks, and database resource pressure.
4. Check network, TLS, authentication, and database connection limits.
5. Compare total connection budgets across application replicas.
6. Change pool size only after identifying the actual bottleneck.

21. Testing database code

Test at several levels. Pure unit tests cover mapping and service decisions quickly. Integration tests against PostgreSQL verify the SQL, schema, driver, constraints, transactions, and type behavior that mocks cannot prove.

Unit tests

• Keep complex SQL result calculations in testable domain functions when possible.
• Test service rollback behavior through integration tests, not only mock verification.
• Avoid mocking fluent JDBC internals or ResultSet extensively.
• If a mapper has meaningful conversion logic, test it with a small supported fixture.

@JdbcTest slice

Spring Boot's @JdbcTest loads a focused JDBC test context and is transactional by default. Depending on test configuration, Boot may replace the application data source with an embedded database. That is fast, but an embedded database does not prove PostgreSQL SQL or type behavior.

PostgreSQL integration with Testcontainers

@JdbcTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Testcontainers
class CustomerRepositoryTest {
    @Container
    @ServiceConnection
    static PostgreSQLContainer postgres =
        new PostgreSQLContainer("postgres:18");

    @Autowired JdbcTemplate jdbc;

    @Test
    void insertReturnsDatabaseGeneratedValues() {
        CustomerRepository repository = new CustomerRepository(jdbc);

        Customer saved = repository.insert(
            new CustomerDraft("reader@example.com", "Reader")
        );

        assertThat(saved.id()).isPositive();
        assertThat(saved.status()).isEqualTo("ACTIVE");
        assertThat(saved.createdAt()).isNotNull();
    }
}

Use the PostgreSQL major version deployed in production, apply real migrations, and let Boot's service-connection support provide dynamic connection details when available. Otherwise, @DynamicPropertySource can publish the container URL, username, and password. @ServiceConnection requires Spring Boot's spring-boot-testcontainers test module. The test also needs the current Testcontainers JUnit and PostgreSQL modules plus the PostgreSQL JDBC driver, preferably under the Boot-managed dependency set.

Isolation strategies

• Transactional rollback is fast for tests running on the test thread.
• Work committed by another thread or server request is not rolled back by the test transaction.
• Truncate owned tables between tests when commit behavior must be tested.
• Use unique business keys to allow safe parallel execution.
• Reset sequences only when exact identifiers are part of the intended contract.
• Never point automated integration tests at a shared production-like database.

High-value database test cases

1. Zero, one, and multiple-row cardinality.
2. SQL NULL mapping for every nullable type.
3. Unique, foreign-key, check, and not-null constraint translation.
4. Generated defaults and PostgreSQL RETURNING.
5. Rollback after a later statement fails.
6. Optimistic update conflict under concurrent versions.
7. Batch partial failure and retry semantics.
8. Time zone, decimal precision, UUID, JSONB, and array mappings.
9. Pagination stability with duplicate sort values.
10. Migration compatibility with the application build.

22. When JDBC is preferable to an ORM

Choose the abstraction that makes the important behavior clearest. JDBC favors explicit SQL and result shapes. An ORM favors entity state, relationships, and persistence-context behavior.

Both approaches can coexist. A system might use JPA for an aggregate write model and Spring JDBC for reporting projections. Keep transaction ownership and data-source use coherent, and do not hide one abstraction behind misleading repository contracts.

23. Production repository design

A good repository exposes domain-relevant persistence operations and keeps SQL close to its mapping and cardinality rules.

@Repository
final class AccountRepository {
    private static final String FIND_FOR_UPDATE = """
        select id, balance, version
        from account
        where id = :id
        for update
        """;

    private final NamedParameterJdbcTemplate jdbc;

    AccountRepository(NamedParameterJdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    Optional<AccountBalance> findForUpdate(long id) {
        List<AccountBalance> rows = jdbc.query(
            FIND_FOR_UPDATE,
            Map.of("id", id),
            (rs, rowNum) -> new AccountBalance(
                rs.getLong("id"),
                rs.getBigDecimal("balance"),
                rs.getInt("version")
            )
        );
        return DataAccessUtils.optionalResult(rows);
    }

    int debitIfEnough(long id, BigDecimal amount) {
        return jdbc.update("""
            update account
            set balance = balance - :amount,
                version = version + 1
            where id = :id and balance >= :amount
            """,
            Map.of("id", id, "amount", amount)
        );
    }
}

Prefer atomic SQL when it expresses the invariant

The conditional debit combines check and update in one statement, so concurrent transactions cannot both pass a stale Java-side balance check. Database constraints should still protect invariants such as non-negative balance where the business rule is universal.

Organizing SQL

• Keep short SQL next to the repository operation.
• Give long statements descriptive constants or dedicated resource files.
• Format and review SQL as first-class code.
• Version SQL changes with application and migration changes.
• Use stable operation names for metrics.
• Do not create a homemade string-concatenation query language.

24. Edge cases to understand

DDL and transactions

PostgreSQL supports transactional DDL for many operations, but migration tools, locks, concurrent index operations, and external effects have special behavior. Keep schema migration ownership separate from request-time repository transactions.

Cancellation and ambiguous outcomes

If a network connection fails after a write was sent, the client may not know whether PostgreSQL committed it. Use idempotency keys, unique constraints, and an operation-status lookup for retryable commands. A transport error does not always mean "nothing happened."

Time zones

Define what every timestamp means. Store instants for events, use timestamptz, and convert at presentation boundaries. A calendar date or local appointment time is a different domain type and should not be forced into an instant.

Numeric precision

Use BigDecimal with deliberate scale and rounding for decimal business values. PostgreSQL numeric can exceed Java decimal expectations, so validate ranges and preserve the database constraint.

Lost updates

A read-modify-write sequence can overwrite a concurrent change. Use a version predicate, a row lock for a short transaction, or an atomic update expression. Choose based on contention, conflict handling, and the invariant.

Read-only transactions

@Transactional(readOnly = true) communicates intent and may allow optimizations, but do not treat it as the sole authorization control. Enforce write restrictions with architecture, database roles, routing, and tests where required.

Dynamic predicates

Build optional filters from a controlled set of SQL fragments and keep values bound. If filter combinations become complex, use a deliberate query builder or specification layer that still preserves parameterization and inspectable SQL.

25. Common mistakes and fixes

26. Security checklist

• Use prepared parameters for every untrusted value.
• Allowlist dynamic identifiers, operators, and directions.
• Authorize at the service boundary and include tenant scope in SQL.
• Use separate least-privilege runtime and migration roles.
• Require verified TLS across untrusted networks.
• Keep credentials out of source control, logs, and diagnostics.
• Rotate secrets and use short-lived credentials when the platform supports them.
• Limit returned columns and redact sensitive values.
• Enforce critical invariants with constraints.
• Bound query cost, page size, batch size, and timeout.
• Patch the JDK, Spring, HikariCP, and pgJDBC through supported release lines.
• Audit privileged operations with identities and safe metadata.

27. Interview questions and answers

What problems does JdbcTemplate solve?

It controls repetitive JDBC resource acquisition, statement execution, result-set iteration, cleanup, transaction participation, and exception translation. Application code retains control of SQL, binding, cardinality, and result mapping.

Is JdbcTemplate thread-safe?

A fully configured JdbcTemplate is thread-safe and normally shared as a singleton. It does not store one mutable connection per caller. It obtains the appropriate connection for each operation, including a transaction-bound connection.

DataSource versus DriverManager?

Both can obtain JDBC connections, but DataSource is the preferred application abstraction. It supports container configuration, pooling, and indirection without embedding driver connection logic in repository code.

What happens when a pooled connection is closed?

Application code closes a proxy handle. The pool resets important state and makes the underlying physical connection available for reuse. Failing to close the handle leaks pool capacity.

JdbcTemplate versus NamedParameterJdbcTemplate?

JdbcTemplate uses positional JDBC placeholders. NamedParameterJdbcTemplate rewrites descriptive named placeholders into positional placeholders and is clearer for many parameters or collections.

What does RowMapper do?

It maps the current result-set row into one object. Spring moves the cursor and calls the mapper for every row. One-to-many aggregation usually needs a ResultSetExtractor or a different query shape.

How does Spring translate SQLExceptions?

Spring's SQLExceptionTranslator examines JDBC exception subclasses, SQL state, and configured vendor details to produce an unchecked DataAccessException subtype. This gives callers useful categories without depending directly on a driver.

Where should @Transactional be placed?

Usually on a public application-service method that represents one business operation. Putting it on every repository method creates statement-sized transactions and cannot protect a multi-step invariant.

Why can self-invocation break @Transactional?

In default proxy mode, transaction advice runs when a call enters through the proxy. A method calling another annotated method on the same instance does not cross that proxy.

How do prepared statements prevent SQL injection?

The SQL structure and parameter values travel through separate APIs. The database interprets the value as data, not executable SQL syntax. Identifiers cannot be parameters and must come from trusted allowlists.

How do you size HikariCP?

Start with a small bounded pool, account for every application replica and database consumer, then load-test while observing acquisition wait, query latency, database CPU, I/O, and locks. More connections can reduce performance by increasing database contention.

How do you retrieve generated keys?

Use a KeyHolder with a prepared-statement creator for JDBC-generated keys, or use PostgreSQL INSERT ... RETURNING and map the returned row. Verify exactly which columns and cardinality the operation guarantees.

What is the benefit of batching?

Batching reduces round trips for repeated DML. It does not remove server work, guarantee atomicity, or justify unbounded input. Use bounded chunks and an explicit transaction policy.

Why test with PostgreSQL instead of only an embedded database?

SQL dialect, type mapping, constraints, query planning, isolation, locking, generated values, and error states differ. A real PostgreSQL integration test proves the production-relevant contract.

When is Spring JDBC better than JPA?

It is often better when explicit SQL, predictable query shape, reporting projections, bulk operations, or PostgreSQL-specific features dominate. JPA can be stronger when managed entity lifecycle and relationship persistence provide real value.

Why is a huge connection pool dangerous?

It permits more concurrent database work, which can increase CPU scheduling, memory use, lock contention, and latency. It can also exceed PostgreSQL's total connection budget during scaling or rolling deployments.

How should deadlocks be handled?

Keep transactions short, access resources in consistent order, inspect the conflicting SQL, and fix the design where possible. A bounded retry of the complete idempotent transaction can handle unavoidable transient deadlock victims.

What is the N+1 query problem in JDBC?

It occurs when code runs one query for a collection and then one additional query per row. JDBC does not create it automatically, but repository loops can. Replace it with a set-oriented join, batched lookup, or bounded two-query assembly.

How do transaction and connection pools interact?

A local JDBC transaction borrows and retains one connection until completion. Therefore slow or blocked transactions reduce available pool capacity even when they execute little Java code.

Why are unique constraints still needed after validation?

Two concurrent requests can both pass an application-side existence check. The unique constraint arbitrates at the database serialization point. Translate its known violation into the domain outcome.

28. Practice exercises

1. Build a customer repository with create, optional find, update, and delete row-count checks.
2. Map a joined order and items result with a ResultSetExtractor.
3. Implement allowlisted sorting and keyset pagination with duplicate timestamps.
4. Add PostgreSQL RETURNING for identity, defaults, and version.
5. Compare individual inserts, JDBC batching, rewritten batching, and PostgreSQL COPY.
6. Write a transfer service whose invariant is enforced atomically and transactionally.
7. Produce a deadlock in a test, inspect it, then enforce consistent lock order.
8. Test unique-constraint translation and decide the public domain error.
9. Use Testcontainers and real migrations for JSONB, UUID, and time-zone mapping tests.
10. Load-test different pool sizes and explain the observed throughput and latency.
11. Design safe retry behavior for an ambiguous connection failure after a write.
12. Add pool and repository-operation metrics without logging sensitive values.

29. One-screen cheat sheet

30. Official references

• Spring Framework JDBC core and exception handling
• Spring Framework JDBC batch operations
• Spring Framework JDBC access approaches
• Spring Framework declarative transactions
• Spring Boot SQL database support
• Spring Boot Testcontainers service connections
• Java SE JDBC API
• Java JDBC prepared statements
• pgJDBC connection and driver properties
• Testcontainers PostgreSQL module
• PostgreSQL RETURNING
• PostgreSQL transaction isolation
• PostgreSQL statement and lock timeouts
• HikariCP configuration reference