Lesson 10 of 12 · 10 min · Data access with Spring Data JPA
Spring Data repositories and queries
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).
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
saveinside a loop for thousands of rows without batching.
Try it yourself
- Create
CustomerRepositoryandOrderRepository, and testsave,findByIdandfindAllwith@DataJpaTest. - Add
findByStatus,findByCustomerEmailandexistsByCustomerIdand see the generated SQL. - Add an endpoint with
Pageablereturning a DTO page and request page 1, size 5, sorted by amount. - Write a JPQL query that returns an
OrderSummaryrecord directly. - 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
JpaRepositoryfor free CRUD; use derived method names for simple queries and@Queryfor the rest. - Paginate with
Pageable, preferSlicefor 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
- Spring Data JPA: Query methods: the derived query grammar.
- Spring Data JPA: @Query: JPQL, native queries and modifying queries.
- Spring Data: Projections: interface and class projections.
- Access data with JPA (Spring guide): a short working tutorial.
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.