Repositories & CRUD Operations

🌿 Spring BootLesson 6Intermediate

Spring Data JPA repositories eliminate the need to write SQL for common operations. By extending JpaRepository, you get dozens of CRUD methods for free — plus the ability to derive queries from method names.

1 JpaRepository Interface

Java — UserRepository.java

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Optional;
import java.util.List;

public interface UserRepository extends JpaRepository<User, Long> {

    // --- Derived Query Methods (Spring generates SQL automatically) ---
    Optional<User> findByEmail(String email);
    boolean existsByEmail(String email);
    List<User> findByRoleOrderByCreatedAtDesc(Role role);
    List<User> findByNameContainingIgnoreCase(String name);
    long countByActiveTrue();

    // --- JPQL Query ---
    @Query("SELECT u FROM User u WHERE u.active = true AND u.role = :role")
    List<User> findActiveByRole(@Param("role") Role role);

    // --- Native SQL Query ---
    @Query(value = "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '7 days',
           nativeQuery = true)
    List<User> findNewUsersThisWeek();

    // --- Modifying Query ---
    @Modifying
    @Transactional
    @Query("UPDATE User u SET u.active = false WHERE u.id = :id")
    int deactivateUser(@Param("id") Long id);
}

2 Pagination & Sorting

Java — Pageable & Sorting

// Repository — just extend JpaRepository, Pageable support is free
Page<User> findByRole(Role role, Pageable pageable);

// Service — build the Pageable
public Page<UserDto> getUsers(Role role, int page, int size, String sortBy) {
    Pageable pageable = PageRequest.of(
        page, size,
        Sort.by(Sort.Direction.DESC, sortBy)
    );
    return userRepository.findByRole(role, pageable)
                         .map(userMapper::toDto);
}

// Controller
@GetMapping
public ResponseEntity<Page<UserDto>> getUsers(
        @RequestParam(defaultValue = "USER") Role role,
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "20") int size,
        @RequestParam(defaultValue = "createdAt") String sortBy) {
    return ResponseEntity.ok(userService.getUsers(role, page, size, sortBy));
}
// GET /api/v1/users?role=ADMIN&page=0&size=10&sortBy=name

3 Code Challenge

Challenge: Create a ProductRepository with: findByCategory, findByPriceBetween, findByNameContaining, a JPQL query for low-stock products (quantity below a threshold), and a native query for the top 5 most expensive products per category.