springboot-jdbc

Spring Boot JDBC Patterns

JdbcTemplate Setup

@Configuration
public class DatabaseConfig {
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public NamedParameterJdbcTemplate namedJdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
}

RowMapper Patterns

Inline RowMapper (for simple cases)

private static final RowMapper<Todo> TODO_MAPPER = (rs, rowNum) -> {
    Todo todo = new Todo();
    todo.setId(rs.getLong("id"));
    todo.setTitle(rs.getString("title"));
    todo.setCompleted(rs.getBoolean("completed"));
    todo.setCreatedAt(rs.getTimestamp("created_at").toInstant());
    return todo;
};

BeanPropertyRowMapper (for matching column/field names)

private final RowMapper<Todo> todoMapper = new BeanPropertyRowMapper<>(Todo.class);

Handling Nulls

private static final RowMapper<Todo> TODO_MAPPER = (rs, rowNum) -> {
    Todo todo = new Todo();
    todo.setId(rs.getLong("id"));

    // Handle nullable columns
    Timestamp completedAt = rs.getTimestamp("completed_at");
    todo.setCompletedAt(completedAt != null ? completedAt.toInstant() : null);

    // For primitive wrappers
    todo.setPriority(rs.getObject("priority", Integer.class));

    return todo;
};

Query Patterns

Parameterized Queries (ALWAYS use these)

// Single parameter
Todo todo = jdbc.queryForObject(
    "SELECT * FROM todos WHERE id = ?",
    TODO_MAPPER,
    id
);

// Multiple parameters
List<Todo> todos = jdbc.query(
    "SELECT * FROM todos WHERE completed = ? AND created_at > ?",
    TODO_MAPPER,
    completed,
    since
);

Named Parameters (for complex queries)

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("status", status)
    .addValue("userId", userId)
    .addValue("limit", limit);

List<Todo> todos = namedJdbc.query(
    "SELECT * FROM todos WHERE status = :status AND user_id = :userId LIMIT :limit",
    params,
    TODO_MAPPER
);

NEVER Do This (SQL Injection Risk)

// WRONG - SQL INJECTION VULNERABILITY
String query = "SELECT * FROM todos WHERE title = '" + title + "'";
jdbc.query(query, TODO_MAPPER);

// WRONG - Still vulnerable
String query = "SELECT * FROM todos WHERE id = " + id;
jdbc.query(query, TODO_MAPPER);

Insert with Generated Keys

public Todo save(Todo todo) {
    KeyHolder keyHolder = new GeneratedKeyHolder();

    jdbc.update(connection -> {
        PreparedStatement ps = connection.prepareStatement(
            "INSERT INTO todos (title, completed, created_at) VALUES (?, ?, ?)",
            Statement.RETURN_GENERATED_KEYS
        );
        ps.setString(1, todo.getTitle());
        ps.setBoolean(2, todo.isCompleted());
        ps.setTimestamp(3, Timestamp.from(todo.getCreatedAt()));
        return ps;
    }, keyHolder);

    todo.setId(Objects.requireNonNull(keyHolder.getKey()).longValue());
    return todo;
}

Batch Operations

public void saveAll(List<Todo> todos) {
    jdbc.batchUpdate(
        "INSERT INTO todos (title, completed, created_at) VALUES (?, ?, ?)",
        new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Todo todo = todos.get(i);
                ps.setString(1, todo.getTitle());
                ps.setBoolean(2, todo.isCompleted());
                ps.setTimestamp(3, Timestamp.from(todo.getCreatedAt()));
            }

            @Override
            public int getBatchSize() {
                return todos.size();
            }
        }
    );
}

Connection Pool Configuration (HikariCP)

application.yml

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST:localhost}:${DB_PORT:5432}/${DB_NAME:tododb}
    username: ${DB_USER:postgres}
    password: ${DB_PASSWORD:postgres}
    hikari:
      maximum-pool-size: ${HIKARI_MAX_POOL:5}
      minimum-idle: ${HIKARI_MIN_IDLE:2}
      idle-timeout: 300000
      connection-timeout: 20000
      max-lifetime: 1200000
      pool-name: TodoHikariPool

Connection Pool Sizing Rule

CRITICAL: maxPoolSize × expected_pod_count < max_connections

Scenario	Formula	Example
Single instance	maxPoolSize = max_connections - 5	100 - 5 = 95
Multiple pods	maxPoolSize = max_connections / (pod_count + 1)	100 / (5 + 1) = 16
With headroom	maxPoolSize = (max_connections × 0.8) / pod_count	(100 × 0.8) / 5 = 16

Example for 5 K8s pods with PostgreSQL max_connections=100:

spring:
  datasource:
    hikari:
      maximum-pool-size: 16  # 100 / (5 + 1) = 16

Verify Pool Sizing in Tests

@Test
void connectionPoolSizing_shouldNotExceedMaxConnections() {
    int maxPoolSize = hikariDataSource.getMaximumPoolSize();
    int expectedPods = 5;
    int maxConnections = 100;

    assertThat(maxPoolSize * expectedPods)
        .as("Total connections across all pods")
        .isLessThan(maxConnections);
}

Transaction Management

@Service
@RequiredArgsConstructor
public class OrderService {
    private final OrderRepository orderRepository;
    private final InventoryRepository inventoryRepository;

    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        // Both operations in same transaction
        Order order = orderRepository.save(toOrder(request));
        inventoryRepository.decrementStock(request.productId(), request.quantity());
        return order;
    }

    @Transactional(readOnly = true)
    public List<Order> findAll() {
        return orderRepository.findAll();
    }

    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void auditLog(String action) {
        // Commits independently of outer transaction
        auditRepository.log(action);
    }
}

Error Handling

public Optional<Todo> findById(Long id) {
    try {
        Todo todo = jdbc.queryForObject(
            "SELECT * FROM todos WHERE id = ?",
            TODO_MAPPER,
            id
        );
        return Optional.ofNullable(todo);
    } catch (EmptyResultDataAccessException e) {
        return Optional.empty();
    }
}

public void delete(Long id) {
    int rowsAffected = jdbc.update("DELETE FROM todos WHERE id = ?", id);
    if (rowsAffected == 0) {
        throw new TodoNotFoundException(id);
    }
}

Common Exceptions and Handling

Exception	Cause	Handling
EmptyResultDataAccessException	queryForObject returned 0 rows	Return Optional.empty() or throw custom exception
IncorrectResultSizeDataAccessException	Expected 1 row, got more	Fix query with LIMIT or more specific WHERE
DataIntegrityViolationException	FK/unique constraint	Wrap in domain exception
DataAccessResourceFailureException	Connection failed	Retry or circuit breaker
BadSqlGrammarException	SQL syntax error	Fix the query