Lesson 10 of 12 · 10 min · Data access with Spring Data JPA

Spring Data repositories and queries

Covers: Spring Boot 4.1 with Spring Data JPA and Hibernate 7 on Java 17 or newer. Updated 13 Nov 2026. Part of the Java Full Stack Developer roadmap.

What you will learn

  • Create a repository by declaring an interface
  • Use the CRUD methods of JpaRepository
  • Write queries by method name, with @Query (JPQL and native SQL)
  • Paginate and sort with Pageable
  • Return DTO projections and run update queries, then test with @DataJpaTest

In simple words

Almost every application needs the same four database jobs: create, read, update and delete rows. Spring Data JPA writes that code for you. You declare an interface that names the entity, and Spring builds a working implementation at startup. For custom searches you just name the method sensibly, such as findByCityAndStatus, and Spring turns the name into a query.

Words to know

  • Repository: an object that handles saving and finding entities.
  • Derived query: a query Spring builds from the name of a repository method.
  • JPQL: the Jakarta Persistence Query Language, similar to SQL but written against entities and fields.
  • Projection: a query result containing only some fields, returned as an interface or record.

A repository

public interface CustomerRepository extends JpaRepository<Customer, Long> {
}

JpaRepository<Entity, IdType> gives you, with no code:

Method What it does
save(entity) insert if new, update if it exists (merge)
saveAll(entities) save many
findById(id) Optional<Customer>
findAll(), findAll(Sort), findAll(Pageable) list, sorted or one page
existsById(id), count() checks and counts
deleteById(id), delete(entity) remove
getReferenceById(id) a lazy proxy without querying (use for setting a foreign key)
flush(), saveAndFlush() push pending changes to the database
@Service
class CustomerService {
    private final CustomerRepository repo;
    CustomerService(CustomerRepository repo) { this.repo = repo; }

    Customer register(String name, String email) {
        return repo.save(new Customer(name, email, Segment.STANDARD));
    }

    Customer get(long id) {
        return repo.findById(id).orElseThrow(() -> new CustomerNotFoundException(id));
    }
}

Spring scans for repository interfaces (with Boot, automatically) and creates the implementation (a proxy) as a bean; you inject it like any other. findById returns an Optional: handle "not found" explicitly. Also available: CrudRepository (smaller), PagingAndSortingRepository, and in Spring Data 3+ you can extend ListCrudRepository.

Derived query methods

Spring parses the method name:

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByStatus(OrderStatus status);

    List<Order> findByCustomerIdAndStatus(Long customerId, OrderStatus status);

    List<Order> findByAmountGreaterThanEqualOrderByAmountDesc(BigDecimal min);

    Optional<Order> findFirstByCustomerEmailOrderByCreatedAtDesc(String email);   // navigates customer.email

    boolean existsByCustomerId(Long customerId);

    long countByStatus(OrderStatus status);

    List<Order> findByCreatedAtBetween(Instant from, Instant to);

    List<Customer> findByNameContainingIgnoreCase(String fragment);

    void deleteByStatus(OrderStatus status);
}

The grammar: find...By (also read, get, query, count, exists, delete) + property names joined with And / Or + operators (GreaterThan, LessThan, Between, Like, Containing, StartingWith, In, IsNull, IgnoreCase, True) + OrderBy...Asc/Desc, First/Top limits. A bad property name fails at startup, which is good: the error appears early. Names get long quickly; when a method name becomes unreadable, switch to @Query.

@Query: JPQL and native SQL

JPQL queries entities and fields (not tables and columns):

@Query("select o from Order o where o.customer.email = :email and o.amount > :min")
List<Order> findBigOrdersOf(@Param("email") String email, @Param("min") BigDecimal min);

@Query("select o.status, count(o), sum(o.amount) from Order o group by o.status")
List<Object[]> totalsByStatus();

Use named parameters (:email with @Param, or the -parameters compiler flag that Boot enables) and never concatenate user input into the query string (SQL injection; parameters are safe). Native SQL when you need database-specific features:

@Query(value = "select * from orders where created_at >= now() - interval '7 days'", nativeQuery = true)
List<Order> findRecent();

Native queries tie you to one database and bypass some entity features; prefer JPQL, and for complex reporting consider SQL through JdbcClient/JdbcTemplate, jOOQ or database views.

Pagination and sorting

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

Slice<Order> findByCustomerId(Long id, Pageable pageable);     // no total count query: cheaper
Pageable page = PageRequest.of(0, 20, Sort.by("createdAt").descending());
Page<Order> result = orderRepository.findByStatus(OrderStatus.PAID, page);
result.getContent();          // the rows
result.getTotalElements();    // total matching rows (an extra count query)
result.getTotalPages();

In a controller, accept Pageable directly: ?page=0&size=20&sort=amount,desc. Limit the maximum page size (spring.data.web.pageable.max-page-size, default 2000) to protect the database, and return a DTO page, not the entity page. Slice skips the count query and is better for "load more" scrolling. For deep paging over large tables prefer keyset pagination (where id < :lastId order by id desc limit 20).

Projections and DTOs

Loading the whole entity when you need three columns wastes memory and queries. Return a projection:

public record OrderSummary(Long id, BigDecimal amount, String customerName) {}

@Query("select new com.shop.orders.OrderSummary(o.id, o.amount, o.customer.name) from Order o where o.status = :status")
List<OrderSummary> summaries(@Param("status") OrderStatus status);

or an interface projection (Spring generates a proxy) or a class-based one for derived queries:

interface OrderView { Long getId(); BigDecimal getAmount(); }
List<OrderView> findByStatus(OrderStatus status);

Projections avoid loading lazy associations and entity tracking, a simple fix for many performance problems (and a clean way to produce DTOs).

Modifying queries

Bulk updates and deletes run directly in the database:

@Modifying
@Transactional
@Query("update Order o set o.status = :status where o.createdAt < :before and o.status = 'PENDING'")
int expirePending(@Param("status") OrderStatus status, @Param("before") Instant before);

They bypass the persistence context, so entities already loaded in the session become stale; use @Modifying(clearAutomatically = true) when you read them again in the same transaction. Derived deleteBy... loads entities one by one; a @Query delete is far faster for bulk removal but skips cascades and lifecycle callbacks.

Specifications and dynamic queries

For optional search filters (name, status, date range, all optional), method names explode. Use Specifications (JpaSpecificationExecutor and the criteria API), Querydsl, or Query by Example; or build a small @Query with (:status is null or o.status = :status) conditions for simple cases.

Testing repositories

@DataJpaTest starts only the JPA layer with an embedded database (rolled back after each test):

@DataJpaTest
class OrderRepositoryTests {

    @Autowired OrderRepository orders;
    @Autowired CustomerRepository customers;

    @Test
    void findsPaidOrdersOfCustomer() {
        Customer asha = customers.save(new Customer("Asha", "asha@example.com", Segment.STANDARD));
        orders.save(new Order(asha, new BigDecimal("1200"), OrderStatus.PAID));
        orders.save(new Order(asha, new BigDecimal("800"), OrderStatus.PENDING));

        List<Order> paid = orders.findByCustomerIdAndStatus(asha.getId(), OrderStatus.PAID);

        assertThat(paid).hasSize(1);
    }
}

(In Boot 4, import @DataJpaTest from org.springframework.boot.data.jpa.test.autoconfigure.) An embedded H2 database can behave differently from PostgreSQL (functions, case sensitivity, locking), so for important queries test against the real database with Testcontainers and @AutoConfigureTestDatabase(replace = NONE). Turn on SQL logging in tests to see what runs.

Common mistakes

  • Returning List<Entity> for everything and loading too much (use projections).
  • Unbounded findAll() on a big table (use pagination).
  • String-concatenated queries instead of parameters.
  • Overlong derived method names instead of @Query.
  • Bulk update queries that leave stale entities in the session.
  • Testing only on H2 while production runs PostgreSQL.
  • Calling save inside a loop for thousands of rows without batching.

Try it yourself

  1. Create CustomerRepository and OrderRepository, and test save, findById and findAll with @DataJpaTest.
  2. Add findByStatus, findByCustomerEmail and existsByCustomerId and see the generated SQL.
  3. Add an endpoint with Pageable returning a DTO page and request page 1, size 5, sorted by amount.
  4. Write a JPQL query that returns an OrderSummary record directly.
  5. Write a bulk-update query and show the stale-entity problem, then fix it with clearAutomatically.

Check yourself

How does Spring Data create a repository implementation?

At startup it detects the repository interface and generates a proxy implementation, providing CRUD methods and turning derived method names and @Query annotations into queries.

What is the difference between Page and Slice?

Page also runs a count query to know the total elements and pages; Slice only knows whether there is a next slice, so it is cheaper.

Why use projections?

To select only the fields you need instead of loading full entities, which saves memory and queries and avoids lazy loading problems.

Why must bulk @Modifying queries be used carefully?

They run directly in the database and bypass the persistence context, so already loaded entities can become stale, and they skip cascades and callbacks.

Interview questions

What is the difference between CrudRepository, JpaRepository and PagingAndSortingRepository?

CrudRepository provides basic CRUD, PagingAndSortingRepository adds paging and sorting, and JpaRepository adds JPA-specific operations such as flush, batch deletes and getReferenceById on top of those.

How would you implement search with several optional filters?

With Specifications (criteria API), Querydsl or a carefully written JPQL query using optional conditions, combined with pagination; avoid building SQL by string concatenation.

What does save do for an entity that already has an id?

It merges it, loading the current state if needed and issuing an update when it differs; for a new entity (null id, or no version) it persists it as an insert.

Summary

  • Extend JpaRepository for free CRUD; use derived method names for simple queries and @Query for the rest.
  • Paginate with Pageable, prefer Slice for scrolling, and cap page sizes.
  • Use projections and DTOs for read models; be careful with bulk modifying queries.
  • Test with @DataJpaTest, and use a real database (Testcontainers) for important queries.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Spring Boot, REST and JPA for Java Full Stack Developer and scores every answer, with what to fix.