5.3 Repositories & Query Methods

← Back to Chapter 5: Spring Data JPA, Hibernate & Database

Spring Data JPA eliminates repetitive database access code through dynamic proxy implementation of repository interfaces.

---

Spring Data Repository Hierarchy

        ┌────────────────────────────────┐
        │       Repository<T, ID>        │ (Marker interface)
        └───────────────┬────────────────┘
                        │
        ┌───────────────▼────────────────┐
        │     CrudRepository<T, ID>      │ (save, findById, delete)
        └───────────────┬────────────────┘
                        │
        ┌───────────────▼────────────────┐
        │  ListCrudRepository / Paging   │ (findAll with Sort/Pageable)
        └───────────────┬────────────────┘
                        │
        ┌───────────────▼────────────────┐
        │      JpaRepository<T, ID>      │ (flush, saveAndFlush, batch)
        └────────────────────────────────┘

Repository

Now we need a way to communicate with the database.

Create:

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

That's enough to get many common operations.

Common JPA Repository Methods

findById()
findAll()
save()
deleteById()
count()
existsById()

What is JpaRepository?

JpaRepository provides ready-made database operations.

Hierarchy conceptually:

Repository
    ↓
CrudRepository
    ↓
PagingAndSortingRepository
    ↓
JpaRepository

JpaRepository provides CRUD + JPA-specific capabilities.

---

2. Derived Query Method Keywords

Spring Data JPA parses method names automatically into SQL queries:

public interface UserRepository extends JpaRepository<User, Long> {

    // SELECT * FROM users WHERE email = ?
    Optional<User> findByEmail(String email);

    // SELECT * FROM users WHERE status = ? AND created_at > ?
    List<User> findByStatusAndCreatedAtAfter(UserStatus status, Instant since);

    // SELECT * FROM users WHERE active = true ORDER BY name ASC
    List<User> findByActiveTrueOrderByNameAsc();

    // SELECT COUNT(*) FROM users WHERE role = ?
    long countByRole(Role role);

    // Check existence without loading entire entity
    boolean existsByEmail(String email);
}

---

3. Custom @Query (JPQL vs Native SQL)

When method name derivation becomes unwieldy, write explicit queries:

public interface OrderRepository extends JpaRepository<Order, UUID> {

    // JPQL (Queries entities and attributes, database agnostic)
    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.totalAmount >= :minTotal")
    List<Order> findHighValueOrders(
        @Param("status") OrderStatus status, 
        @Param("minTotal") BigDecimal minTotal
    );

    // Native SQL (Direct engine query, required for DB-specific syntax/functions)
    @Query(
        value = "SELECT * FROM orders WHERE metadata ->> 'channel' = :channel",
        nativeQuery = true
    )
    List<Order> findByJsonChannel(@Param("channel") String channel);

    // Modifying Queries (UPDATE/DELETE)
    @Modifying
    @Query("UPDATE Order o SET o.status = :newStatus WHERE o.id = :id")
    int updateOrderStatus(@Param("id") UUID id, @Param("newStatus") OrderStatus newStatus);
}

---

4. Pagination & Sorting

@GetMapping
public Page<UserDTO> getUsers(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "20") int size
) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("lastName").ascending().and(Sort.by("firstName")));
    return userRepository.findAll(pageable).map(userMapper::toDTO);
}

• Page<T>: Executes count query to calculate total pages.
• Slice<T>: Ideal for infinite scroll; fetches limit + 1 rows to check if a next page exists without triggering an expensive COUNT(*) query!