Spring Data JDBC - Quick Reference

Full Reference: See advanced.md for custom row mappers, ID generation, auditing, event listeners, and Testcontainers integration.

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: spring-data-jdbc for comprehensive documentation.

Why Spring Data JDBC over JPA?

Spring Data JDBC Spring Data JPA

No lazy loading Lazy loading

No dirty checking Auto dirty checking

No session/cache First-level cache

Explicit SQL Generated SQL

Fast startup Slower startup

Dependencies

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

Entity Mapping

@Table("users") public class User { @Id private Long id; private String username; private String email;

@Column("created_at")
private LocalDateTime createdAt;

}

Aggregate Design

@Table("orders") public class Order { @Id private Long id; private Long customerId; // Reference by ID, not entity private OrderStatus status;

@MappedCollection(idColumn = "order_id")
private Set<OrderItem> items = new HashSet<>();

public void addItem(Long productId, int quantity, BigDecimal price) {
    items.add(new OrderItem(productId, quantity, price));
}

public BigDecimal getTotal() {
    return items.stream()
        .map(OrderItem::getSubtotal)
        .reduce(BigDecimal.ZERO, BigDecimal::add);
}

}

// Aggregate member (no @Id - lifecycle managed by root) public class OrderItem { private Long productId; private int quantity; private BigDecimal unitPrice;

public BigDecimal getSubtotal() {
    return unitPrice.multiply(BigDecimal.valueOf(quantity));
}

}

Repository Pattern

public interface OrderRepository extends CrudRepository<Order, Long> {

List<Order> findByCustomerId(Long customerId);
List<Order> findByStatus(OrderStatus status);

@Query("SELECT * FROM orders WHERE status = :status ORDER BY created_at DESC LIMIT :limit")
List<Order> findRecentByStatus(OrderStatus status, int limit);

@Modifying
@Query("UPDATE orders SET status = :newStatus WHERE status = :oldStatus AND created_at < :before")
int updateOldOrders(OrderStatus oldStatus, OrderStatus newStatus, LocalDateTime before);

boolean existsByCustomerIdAndStatus(Long customerId, OrderStatus status);

}

Schema

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL, status VARCHAR(50) NOT NULL );

CREATE TABLE order_item ( order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE, product_id BIGINT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10, 2) NOT NULL );

When NOT to Use This Skill

• Need lazy loading - Use spring-data-jpa for complex entity graphs

• Reactive applications - Use spring-r2dbc for non-blocking access

• Complex ORM features - Use JPA for second-level cache, dirty checking

Anti-Patterns

Anti-Pattern Problem Solution

JPA-style entity graphs Not supported Design proper aggregates

Embedding other aggregates Coupling Reference by ID only

Large aggregates Performance issues Keep aggregates small

Missing CASCADE on FK Orphan records Add ON DELETE CASCADE

Quick Troubleshooting

Problem Diagnostic Fix

Entity not saved Check @Id generation Configure ID callback or auto-increment

Children not persisted Check @MappedCollection Add idColumn properly

Column not mapped Check naming Use @Column for custom names

Transaction not working Check @Transactional Ensure Spring proxy

Best Practices

Do Don't

Design proper aggregates Use JPA-style entity graphs

Reference other aggregates by ID Embed other aggregate roots

Keep aggregates small Create huge aggregate graphs

Use immutable value objects Mutate embedded objects directly

Production Checklist

• Aggregate boundaries defined

• Schema matches entity mapping

• Indexes on query columns

• Foreign keys with CASCADE

• Transaction boundaries clear

• Connection pool configured

Reference Documentation

• Spring Data JDBC Reference