Spring Boot & Spring Cloud

Spring Data JPA with Spring Boot: Repositories, Queries, Transactions and Migrations

Intermediate7 min readUpdated

You will be able to persist and query data with Spring Data JPA in a Spring Boot 3 service: define entities and repositories, write derived, JPQL and native queries, page and project results, place transactions correctly, avoid the N+1 and open-in-view traps, and manage the schema with Flyway instead of ddl-auto.

What you will learn

  • Configure a DataSource, JPA and Hibernate through Spring Boot properties and know which defaults to change
  • Write repository interfaces with derived queries, @Query and @Modifying methods
  • Page, sort and project results without loading whole entity graphs
  • Place @Transactional at the right layer and explain what read-only transactions change
  • Detect and fix N+1 selects with fetch joins and @EntityGraph
  • Manage schema changes with Flyway migrations rather than hibernate.ddl-auto

Spring Data JPA removes most of the code that data access used to require: an interface declaration replaces a DAO class, method names replace many queries, and Spring Boot wires the DataSource, Hibernate and transaction manager from a handful of properties. The same convenience makes it easy to ship a service that issues hundreds of queries per request without anyone noticing until traffic arrives. This page covers the mechanics and, just as importantly, the places where the defaults need to be changed. It targets Spring Boot 3 with Spring Data JPA 3 and Hibernate 6, on Jakarta Persistence 3.1.

What Boot configures for you

Adding spring-boot-starter-data-jpa and a JDBC driver brings a connection pool (HikariCP), an EntityManagerFactory backed by Hibernate, a JpaTransactionManager, and repository scanning from the application's package. The minimal configuration is a URL and credentials:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/shop
    username: shop
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10        # match it to the database's connection budget
  jpa:
    open-in-view: false            # see below
    properties:
      hibernate:
        default_batch_fetch_size: 50
  flyway:
    enabled: true

Two defaults deserve a decision on day one. spring.jpa.open-in-view is true by default and keeps the EntityManager open for the entire web request so that views and JSON serialisers can trigger lazy loads. It is convenient, and it is why Boot logs a warning at startup: it hides query explosions and holds a database connection while the response is written. Turn it off and fetch what you need explicitly. spring.jpa.hibernate.ddl-auto defaults to create-drop for embedded databases and none otherwise; leave it at none and let migrations own the schema. spring.jpa.show-sql writes SQL to standard output without the logging framework; prefer logging.level.org.hibernate.SQL=DEBUG (and org.hibernate.orm.jdbc.bind=TRACE in Hibernate 6 to see bound parameters).

Entities and identifiers

An entity is a class mapped to a table. The example below shows the choices that matter more than the annotations themselves:

@Entity
@Table(name = "orders")
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "orders_seq")
    @SequenceGenerator(name = "orders_seq", sequenceName = "orders_seq", allocationSize = 50)
    private Long id;

    @Version
    private long version;                     // optimistic locking

    @Enumerated(EnumType.STRING)              // never ORDINAL: reordering the enum corrupts data
    private OrderStatus status;

    @ManyToOne(fetch = FetchType.LAZY)        // to-one defaults to EAGER; override it
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderLine> lines = new ArrayList<>();

    private Instant createdAt;

    protected Order() {}                      // JPA needs a no-args constructor; keep it non-public

    // business methods, getters; no setter for id or version
}

SEQUENCE with an allocation size lets Hibernate assign ids in batches and enables JDBC batching of inserts; IDENTITY forces a round trip per row and disables insert batching. @Version turns concurrent updates into an OptimisticLockException (surfaced as Spring's ObjectOptimisticLockingFailureException) instead of silent lost updates. Every @ManyToOne and @OneToOne should be LAZY; the specification's EAGER default for to-one associations is the single biggest source of accidental joins.

Do not implement equals/hashCode on database ids that are null until the entity is saved; either use a business key or the class-based identity Hibernate's documentation describes. Records cannot be entities (they are final and immutable) but are ideal for the DTOs and projections that leave the persistence layer.

Repositories

A repository is an interface; Spring Data generates the implementation at startup:

public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {

    // derived query: parsed from the method name at startup
    List<Order> findByCustomerIdAndStatusOrderByCreatedAtDesc(Long customerId, OrderStatus status);

    Page<Order> findByStatus(OrderStatus status, Pageable pageable);

    boolean existsByCustomerIdAndStatus(Long customerId, OrderStatus status);

    // JPQL with a fetch join to avoid N+1 on lines
    @Query("select o from Order o join fetch o.lines where o.id = :id")
    Optional<Order> findWithLines(@Param("id") Long id);

    // bulk update: bypasses the persistence context, hence clearAutomatically
    @Modifying(clearAutomatically = true)
    @Query("update Order o set o.status = :status where o.createdAt < :before and o.status = 'NEW'")
    int expireStaleOrders(@Param("status") OrderStatus status, @Param("before") Instant before);

    // native SQL when the database can do it better
    @Query(value = """
            select customer_id, count(*) as orders, sum(total_cents) as revenue_cents
            from orders where created_at >= :since
            group by customer_id order by revenue_cents desc limit :limit
            """, nativeQuery = true)
    List<CustomerRevenue> topCustomers(@Param("since") Instant since, @Param("limit") int limit);
}

JpaRepository extends ListCrudRepository and ListPagingAndSortingRepository (the List-returning variants introduced in Spring Data 3) and adds JPA specifics such as flush() and saveAllAndFlush(). If a repository should not expose deleteAll to the whole code base, extend Repository<T, ID> and declare only the methods you want.

Derived query names are validated at startup, so a typo fails fast, but long names become unreadable; past three conditions switch to @Query. JPQL queries are also validated at startup against the entity model. Native queries are not, which is one reason to keep them rare and covered by tests against the real database.

Specification (from JpaSpecificationExecutor) builds dynamic where clauses with the Criteria API for search screens with many optional filters, and Spring Data's Query by Example covers simple equality matching. For anything more elaborate, Querydsl or plain JPQL built in a custom repository fragment is clearer than nesting specifications.

Paging, sorting and projections

Pageable and Sort flow from controller to repository unchanged, and Page<T> carries total counts (an extra count query) while Slice<T> only knows whether a next page exists, which is cheaper for infinite scrolling.

Loading full entities to return three fields is wasteful. Interface projections let the query select only what is declared:

public interface OrderSummary {
    Long getId();
    OrderStatus getStatus();
    Instant getCreatedAt();
    CustomerName getCustomer();          // nested projection

    interface CustomerName { String getName(); }
}

Page<OrderSummary> findByStatus(OrderStatus status, Pageable pageable);

Class-based (DTO) projections use a record and a constructor expression, and are the most efficient because Hibernate selects exactly the constructor arguments:

public record CustomerRevenue(Long customerId, long orders, long revenueCents) {}

@Query("select new com.example.shop.report.CustomerRevenue(o.customer.id, count(o), sum(o.totalCents)) "
     + "from Order o where o.createdAt >= :since group by o.customer.id")
List<CustomerRevenue> revenueSince(@Param("since") Instant since);

Transactions and the persistence context

Spring Data's SimpleJpaRepository is annotated @Transactional(readOnly = true) at class level with read-write overrides on the mutating methods, so each repository call is transactional on its own. Business operations spanning several calls need their own boundary, and the service layer is where it belongs:

@Service
public class OrderService {

    private final OrderRepository orders;
    private final InventoryClient inventory;

    @Transactional
    public Order ship(long orderId) {
        Order order = orders.findWithLines(orderId).orElseThrow(() -> new OrderNotFoundException(orderId));
        order.markShipped();          // dirty checking flushes this at commit; no save() call needed
        inventory.release(order);     // a remote call inside a transaction: keep it short, or move it after commit
        return order;
    }

    @Transactional(readOnly = true)
    public Page<OrderSummary> search(OrderStatus status, Pageable pageable) {
        return orders.findByStatus(status, pageable);
    }
}

Inside a transaction the persistence context tracks every loaded entity; changes are detected at flush and written as UPDATE statements, so save() on an already-managed entity is redundant. readOnly = true sets Hibernate's flush mode to MANUAL, skips dirty checking snapshots and lets some drivers and routers send the query to a replica; use it on every read path.

Rules that save debugging time: @Transactional only works on public methods called through the Spring proxy, so a self-invocation inside the same class is not transactional; runtime exceptions roll back by default while checked exceptions do not (rollbackFor changes that); and a @Transactional method that catches an exception from a nested repository call still rolls back, because the inner failure marked the transaction rollback-only. The Spring Framework guide to JDBC and transactions covers propagation and isolation in depth.

The N+1 problem

Load twenty orders, then access order.getLines() on each: one query for the orders plus twenty for the lines. Spring Data and Hibernate cannot know in advance which associations the caller will touch, so the fix is to say so:

  • Fetch join in JPQL: select o from Order o join fetch o.lines where .... Fetching two collections in one query multiplies rows (a Cartesian product); Hibernate 6 removes the duplicates, but the transferred row count still grows, so fetch one collection per query.
  • @EntityGraph on a repository method: @EntityGraph(attributePaths = {"lines", "customer"}) applies to derived queries and to findById.
  • Batch fetching with hibernate.default_batch_fetch_size: lazy collections of already-loaded entities are initialised in batches with an in (...) query instead of one query each. This is the cheapest global improvement and works everywhere.
  • Projections that never load the collection at all.

Detect the problem before users do: log SQL in @DataJpaTest tests and count statements, or use Hibernate's statistics (spring.jpa.properties.hibernate.generate_statistics=true) in a staging environment and watch the query counts per request.

Schema migrations with Flyway

Boot auto-configures Flyway when flyway-core is on the classpath together with the database-specific module (flyway-database-postgresql for PostgreSQL since Flyway 10). Migrations are SQL files in src/main/resources/db/migration named by version:

-- V3__add_order_version_and_status_index.sql
alter table orders add column version bigint not null default 0;
create index orders_status_created_at_idx on orders (status, created_at desc);

Flyway runs pending migrations at startup, records each in flyway_schema_history, and refuses to start if a previously applied file has been edited (its checksum changed). That behaviour is a feature: migrations are immutable history, and a wrong one is fixed by a new migration. Keep Hibernate's ddl-auto at none in every environment that uses migrations; the two mechanisms should never both own the schema. Setting spring.jpa.hibernate.ddl-auto=validate in tests is a useful safety net: Hibernate checks that the migrated schema matches the entity mappings and fails the build if they drift apart.

Liquibase is the equivalent alternative with XML, YAML or SQL changelogs and a richer rollback model; Boot auto-configures it the same way.

Common mistakes

Trusting EAGER defaults on to-one associations. Every findById drags the customer, the customer's address and whatever hangs off it. Set fetch = FetchType.LAZY explicitly on every @ManyToOne and @OneToOne.

Using IDENTITY generation and wondering why batch inserts are slow. Switch to SEQUENCE with allocationSize and set spring.jpa.properties.hibernate.jdbc.batch_size=50 and hibernate.order_inserts=true.

Calling save() in a loop for a bulk update. Use a @Modifying JPQL update or a native statement; a thousand managed entities and a thousand UPDATEs is not a batch job.

Catching an exception inside a transaction and continuing. The transaction is already rollback-only; the commit will fail with UnexpectedRollbackException. Handle failures outside the transactional method or use REQUIRES_NEW deliberately.

Storing enums as ordinals. Inserting a new constant in the middle of the enum silently remaps every stored row. Always EnumType.STRING.

Testing against H2 in PostgreSQL mode and deploying to PostgreSQL. Type handling, sequences, on conflict and JSON differ. Use Testcontainers with the real engine for repository tests, as shown in the testing guide.

Best practices

  • Keep entities in the persistence layer and map to records at the service boundary.
  • One @Transactional boundary per use case, in the service; readOnly = true on every query path.
  • Make every association lazy, then fetch deliberately with fetch joins, entity graphs or batch fetching.
  • Prefer projections for lists and reports; load full aggregates only to modify them.
  • Own the schema with Flyway or Liquibase; validate mappings against it in CI.
  • Size the HikariCP pool from the database's capacity, not the application's thread count, and monitor pool wait times through Actuator metrics.

Key takeaways

  • Boot configures HikariCP, Hibernate and transactions from a few properties; disable open-in-view and keep ddl-auto at none outside throwaway databases.
  • Repositories are interfaces: derived queries for simple cases, @Query JPQL for the rest, native SQL only when the database offers something JPQL cannot.
  • Transactions belong on service methods; repository methods are already transactional individually, and dirty checking writes changes without explicit save() calls.
  • N+1 selects come from lazy associations touched outside a fetch plan; fix them with fetch joins, @EntityGraph, batch fetching or projections.
  • Use SEQUENCE ids, @Version for optimistic locking, EnumType.STRING, and lazy to-one associations by default.
  • Versioned Flyway migrations, validated against the entity model in tests, keep the schema and the code in step.

Sources and further reading

Frequently asked questions

Do I need @Transactional on repository calls?
Not for single calls: Spring Data's repository implementation already wraps each method in a transaction (read-only for queries). You do need @Transactional on a service method that performs several repository calls that must succeed or fail together, or that modifies an entity and relies on dirty checking to flush the change at commit.
Why does my lazy association throw LazyInitializationException?
The entity was loaded inside a transaction, the transaction ended and closed the persistence context, and later code touched an uninitialised collection or proxy. Either fetch the association in the query (join fetch or @EntityGraph), map to a DTO inside the transaction, or widen the transaction to cover the access. Enabling open-in-view hides the symptom but not the extra queries.
Is spring.jpa.hibernate.ddl-auto=update safe for production?
No. It can only add, never rename or drop safely, it guesses column types, it runs unreviewed DDL at startup with the application's privileges, and it gives you no history to roll back. Use it for throwaway local databases at most, and manage real schemas with versioned migrations from Flyway or Liquibase.
When should I use a native query instead of JPQL?
When you need something JPQL cannot express or the database does it much better: window functions, recursive common table expressions, vendor-specific JSON operators, or a hand-tuned join. Native queries lose portability and entity-aware validation at startup, so keep them few, test them against the real database engine, and map the results to a projection or DTO.