Spring Data JDBC: Simplified Database Access Without JPA

If you've spent years wrestling with JPA's persistence context, lazy loading exceptions, and mysterious N+1 queries, you're not alone. I've debugged countless production incidents where a seemingly innocent findById triggered 50 SQL queries because of uninitialized proxies. The problem isn't ORMs in general—it's that JPA tries to be too clever. Spring Data JDBC takes a different path: it treats the database as a database, not an object store. It doesn't manage a first-level cache, doesn't proxy your entities, and doesn't auto-flush changes. You write a repository interface, define your aggregate root, and get back clean SQL execution. No magic. No surprises. This approach is perfect for microservices where each service owns its data and you want minimal framework overhead. In this article, I'll walk you through setting up Spring Data JDBC, show you how to model aggregates, and share hard-earned lessons from production deployments. By the end, you'll know exactly when to reach for Spring Data JDBC and when to stick with JPA.

Getting Started with Spring Data JDBC

Let's cut the fluff. To start with Spring Data JDBC, add the dependency to your pom.xml:

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

Yes, that's it. No EntityManager, no PersistenceContext, no XML configuration. Spring Boot will auto-configure a DataSource and a JdbcTemplate for you. You'll also need a database driver, like mysql-connector-j or postgresql.

Now define a simple aggregate root. In Spring Data JDBC, an aggregate root is the only entity that has a repository. Everything else is part of that aggregate and is stored in the same table or as embedded columns.

```java import org.springframework.data.annotation.Id; import org.springframework.data.relational.core.mapping.Table;

@Table("payments") public class Payment { @Id private Long id; private String transactionId; private BigDecimal amount; private LocalDateTime createdAt;

// Constructors, getters, setters } ```

Then create a repository:

```java import org.springframework.data.repository.CrudRepository;

public interface PaymentRepository extends CrudRepository<Payment, Long> { } ```

That's it. You now have save, findById, findAll, delete methods. No @Query needed for basic operations. The framework generates SQL based on the table name and column names derived from the class name and field names.

But here's the catch: Spring Data JDBC is opinionated about aggregates. It expects that within an aggregate, all references are part of the same table or are embedded. If you try to model a one-to-many relationship as a separate table, you'll need to use @MappedCollection and ensure the child table has a foreign key back to the root. This is not JPA; you can't have lazy loading across aggregates.

import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Table("payments")
public class Payment {
    @Id
    private Long id;
    private String transactionId;
    private BigDecimal amount;
    private LocalDateTime createdAt;

    public Payment() {}

    public Payment(String transactionId, BigDecimal amount) {
        this.transactionId = transactionId;
        this.amount = amount;
        this.createdAt = LocalDateTime.now();
    }

    // getters and setters omitted for brevity
}

Aggregate Design: The Heart of Spring Data JDBC

In Spring Data JDBC, the aggregate is king. An aggregate is a cluster of domain objects that can be treated as a single unit. The aggregate root is the only entry point; all external references should go through it. This is not just a theoretical DDD concept—it directly maps to how the framework persists data.

Consider a payment with line items. In JPA, you'd have a Payment entity with a @OneToMany to LineItem. In Spring Data JDBC, you have two choices:

1. Embedded: If line items are few and always loaded with the payment, store them as JSON in a column or as separate columns. But that's not relational.
2. Separate table with foreign key: Use @MappedCollection to indicate that LineItem is part of the Payment aggregate.

Here's the proper way:

```java @Table("payments") public class Payment { @Id private Long id; private String transactionId; private BigDecimal amount; private LocalDateTime createdAt;

@MappedCollection(idColumn = "payment_id") private List<LineItem> lineItems = new ArrayList<>();

// constructors, getters, setters }

public class LineItem { @Id private Long id; private String productCode; private int quantity; private BigDecimal price; } ```

When you save a Payment, Spring Data JDBC will insert into the payments table and also insert/update/delete rows in the line_items table based on the lineItems list. The idColumn attribute tells the framework that line_items has a column payment_id referencing the payment's ID.

Crucial: The LineItem does not have its own repository. It is only accessible through Payment. If you need to query line items independently, you've designed your aggregate wrong.

Production tip: Be explicit about cascade operations. Spring Data JDBC will delete all children and re-insert them when you update the aggregate. This is fine for small collections but can be a performance disaster for large ones. I once saw a system that stored hundreds of line items per payment; the delete-insert pattern caused excessive database load. We had to switch to a manual approach using JdbcTemplate for those cases.

@Table("payments")
public class Payment {
    @Id
    private Long id;
    private String transactionId;
    private BigDecimal amount;
    private LocalDateTime createdAt;

    @MappedCollection(idColumn = "payment_id")
    private List<LineItem> lineItems = new ArrayList<>();

    public Payment(String transactionId, BigDecimal amount) {
        this.transactionId = transactionId;
        this.amount = amount;
        this.createdAt = LocalDateTime.now();
    }

    public void addLineItem(String productCode, int quantity, BigDecimal price) {
        this.lineItems.add(new LineItem(productCode, quantity, price));
    }

    // getters
}

class LineItem {
    @Id
    private Long id;
    private String productCode;
    private int quantity;
    private BigDecimal price;

    public LineItem(String productCode, int quantity, BigDecimal price) {
        this.productCode = productCode;
        this.quantity = quantity;
        this.price = price;
    }
}

Custom Queries with @Query

While CrudRepository provides basic CRUD, real applications need custom queries. Spring Data JDBC supports @Query annotation on repository methods, just like Spring Data JPA. But there's a critical difference: you must use plain SQL, not JPQL.

```java public interface PaymentRepository extends CrudRepository<Payment, Long> {

@Query("SELECT * FROM payments WHERE transaction_id = :transactionId") Optional<Payment> findByTransactionId(@Param("transactionId") String transactionId);

@Query("SELECT * FROM payments WHERE amount > :minAmount") List<Payment> findLargePayments(@Param("minAmount") BigDecimal minAmount); } ```

Spring Data JDBC also supports @Modifying for update/delete operations:

``java @Modifying @Query("UPDATE payments SET amount = :amount WHERE id = :id") int updateAmount(@Param("id") Long id, @Param("amount") BigDecimal amount); ``

Notice that @Modifying returns int (number of affected rows). This is straightforward and predictable.

What the docs don't tell you: When you use @Query, the framework does NOT automatically handle aggregate persistence. If your query returns a Payment with line items, you must ensure the SQL joins the necessary tables and that the result set contains all columns required to construct the aggregate. Spring Data JDBC uses a ResultSetExtractor to map rows to entities, and it expects the columns to match the field names (or @Column mappings). If your query omits columns, those fields will be null.

Also, @Query methods bypass the aggregate root's lifecycle. This means no optimistic locking checks are performed. If you need versioning, you must include the version column in your SQL and handle it manually.

Production tip: Always test custom queries with a database that has realistic data. I've seen queries that worked in dev with 10 rows but caused full table scans in production with millions.

public interface PaymentRepository extends CrudRepository<Payment, Long> {

    @Query("SELECT * FROM payments WHERE transaction_id = :transactionId")
    Optional<Payment> findByTransactionId(@Param("transactionId") String transactionId);

    @Modifying
    @Query("UPDATE payments SET amount = :amount WHERE id = :id")
    int updateAmount(@Param("id") Long id, @Param("amount") BigDecimal amount);
}

Transactions and Concurrency Control

Spring Data JDBC works with Spring's transaction management seamlessly. Use @Transactional on service methods to ensure atomicity. However, because there's no persistence context, you won't encounter issues like detached entities or auto-flush surprises.

Optimistic locking is supported via @Version:

```java @Table("payments") public class Payment { @Id private Long id; private String transactionId; private BigDecimal amount; private LocalDateTime createdAt;

@Version private Integer version; } ```

When you save an entity with a version, Spring Data JDBC includes the version in the UPDATE statement's WHERE clause. If the version in the database differs, an OptimisticLockingFailureException is thrown. This is straightforward and doesn't require a persistence context.

But here's the gotcha: The version check only applies to the aggregate root. If you modify a child entity (like a line item), the version of the parent is not automatically incremented. This means concurrent modifications to different line items of the same payment could overwrite each other. To protect against that, you must either:

• Include the parent's version column in the child table and check it manually.
• Or redesign your aggregate so that line items are not independent (e.g., store them as JSON).

I've seen a production incident where two concurrent requests added line items to the same payment. Because only the parent's version was checked, the second request overwrote the first's changes. The fix was to use SELECT ... FOR UPDATE on the parent row before modifying children.

For pessimistic locking, you can use @Lock annotation on repository methods, similar to JPA:

``java @Lock(LockMode.PESSIMISTIC_WRITE) Optional<Payment> findById(Long id); ``

This translates to SELECT ... FOR UPDATE. But note: this only locks the root table, not child tables. If you need to lock children, you must write a custom query.

Production tip: Use optimistic locking for most cases, but be aware of the child modification issue. For high-contention scenarios, consider using a database-level locking mechanism or moving to a different data model.

@Table("payments")
public class Payment {
    @Id
    private Long id;
    private String transactionId;
    private BigDecimal amount;
    private LocalDateTime createdAt;

    @Version
    private Integer version;

    // getters and setters
}

@Service
public class PaymentService {
    @Transactional
    public void updateAmount(Long paymentId, BigDecimal newAmount) {
        Payment payment = paymentRepository.findById(paymentId)
            .orElseThrow(() -> new IllegalArgumentException("Payment not found"));
        payment.setAmount(newAmount);
        paymentRepository.save(payment);
    }
}

What the Official Docs Won't Tell You

After years of using Spring Data JDBC in production, here are the hard truths that the official documentation glosses over:

1. No N+1 Protection Spring Data JDBC doesn't have a N+1 problem because it doesn't support lazy loading. But it can still generate many queries if you're not careful. For example, if you load a list of aggregates and then access a child collection for each, you'll get a separate query per aggregate. The solution is to use @Query with a JOIN FETCH equivalent to load everything in one query.

2. The Delete/Re-Insert Pattern As mentioned, updating an aggregate with a @MappedCollection deletes all children and re-inserts them. This is not just inefficient; it can cause issues with database triggers or foreign keys. I once had a system where a trigger on the child table fired on every delete, causing massive slowdowns. We had to switch to manual SQL.

3. Auditing Is Not Built-In Spring Data JDBC does not support @CreatedDate, @LastModifiedDate out of the box like Spring Data JPA does. You need to implement Persistable and handle auditing yourself, or use @PrePersist / @PreUpdate annotations from JPA if you're willing to add the JPA dependency. But that defeats the purpose.

4. No Entity Graphs You cannot define fetch plans. If you need different levels of detail for different use cases, you must write separate queries. This is a design choice, but it means you'll often write multiple repository methods.

5. Limited Support for Inheritance Inheritance mapping is not supported. You cannot have a table-per-class or joined inheritance. If you need polymorphism, you'll have to use a different approach (e.g., single table with a discriminator column and manual mapping).

6. @Query Methods Are Not Aggregate-Aware When you write a custom @Query, the framework doesn't apply any aggregate lifecycle logic. This means no version checks, no cascade operations, and no automatic population of audit fields. You're on your own.

These limitations are not bugs; they're trade-offs. Spring Data JDBC prioritizes simplicity and predictability over magic. If you accept that, you'll be fine. If you try to fight it, you'll end up with a mess.

When to Use Spring Data JDBC vs JPA

This is the million-dollar question. Here's my rule of thumb:

Use Spring Data JDBC when: - You want explicit control over SQL. - Your aggregates are small and well-defined. - You don't need lazy loading, caching, or complex inheritance. - You're building microservices where each service owns its data. - Performance predictability is more important than developer convenience.

Use JPA when: - You have complex object graphs with deep relationships. - You need lazy loading to avoid loading entire graphs. - You rely on a second-level cache for performance. - Your team is already proficient with JPA and its quirks. - You need advanced features like entity graphs, inheritance, or auditing.

Use plain JdbcTemplate when: - You need maximum performance and control. - Your queries are highly dynamic or involve complex joins. - You don't want any ORM overhead.

I've seen teams adopt Spring Data JDBC for a new microservice and later regret it because they needed JPA's features. Conversely, I've seen teams use JPA for a simple CRUD service and suffer from complexity. Choose based on your actual requirements, not hype.

Production tip: Start with Spring Data JDBC for new services. If you find yourself fighting it, switch to JPA or JdbcTemplate. It's easier to add complexity than to remove it.