Repository abstraction — JpaRepository, derived queries, @Query

public interface ProjectRepository extends JpaRepository<Project, Long> {
    // Inherit ~20 method built-in
}

@Service
public class ProjectService {
    private final ProjectRepository repo;

    public Project create(Project p) {
        return repo.save(p);                        // INSERT
    }

    public Optional<Project> findById(Long id) {
        return repo.findById(id);                   // SELECT
    }

    public List<Project> findAll() {
        return repo.findAll();                      // SELECT (no LIMIT — careful)
    }

    public Page<Project> page(Pageable page) {
        return repo.findAll(page);                  // SELECT with LIMIT/OFFSET
    }

    public boolean exists(Long id) {
        return repo.existsById(id);                 // SELECT 1
    }

    public void delete(Long id) {
        repo.deleteById(id);                        // DELETE
    }

    public long count() {
        return repo.count();                        // SELECT COUNT(*)
    }
}

public interface ProjectRepository extends JpaRepository<Project, Long> {

    // Single field
    Optional<Project> findByName(String name);
    List<Project> findByStatus(ProjectStatus status);
    long countByStatus(ProjectStatus status);
    boolean existsByName(String name);

    // Multiple field
    List<Project> findByStatusAndCreatedAtAfter(ProjectStatus status, Instant since);
    Optional<Project> findByNameOrSlug(String name, String slug);

    // Comparison
    List<Project> findByCreatedAtBefore(Instant before);
    List<Project> findByCreatedAtBetween(Instant start, Instant end);
    List<Project> findByPriorityGreaterThanEqual(int min);

    // String matching
    List<Project> findByNameContaining(String fragment);              // LIKE %?%
    List<Project> findByNameStartingWith(String prefix);              // LIKE ?%
    List<Project> findByNameEndingWith(String suffix);                // LIKE %?
    List<Project> findByNameContainingIgnoreCase(String fragment);

    // Collection
    List<Project> findByStatusIn(Collection<ProjectStatus> statuses);
    List<Project> findByIdIn(List<Long> ids);

    // Null check
    List<Project> findByDescriptionIsNull();
    List<Project> findByDescriptionIsNotNull();

    // Sort + Limit
    List<Project> findTop10ByOrderByCreatedAtDesc();
    List<Project> findFirst5ByStatusOrderByName(ProjectStatus status);

    // Distinct
    List<Project> findDistinctByStatus(ProjectStatus status);

    // Modify (DELETE, UPDATE) — require @Modifying + @Transactional
    @Modifying
    @Transactional
    int deleteByStatus(ProjectStatus status);
}

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.orm.jdbc.bind: TRACE

[verb] [Distinct]? [Top|First N]? [subject] By [property] [keyword] ... [OrderBy ...]

public interface ProjectRepository extends JpaRepository<Project, Long> {

    // JPQL
    @Query("SELECT p FROM Project p WHERE p.status = :status AND p.createdAt > :since")
    List<Project> findRecent(@Param("status") ProjectStatus status,
                              @Param("since") Instant since);

    // Native SQL
    @Query(value = "SELECT * FROM projects WHERE status = :status",
           nativeQuery = true)
    List<Project> findByStatusNative(@Param("status") String status);

    // Aggregate
    @Query("SELECT p.status, COUNT(p) FROM Project p GROUP BY p.status")
    List<Object[]> countByStatus();

    // With JOIN
    @Query("SELECT p FROM Project p JOIN p.tasks t WHERE t.priority = :priority")
    List<Project> findWithHighPriorityTasks(@Param("priority") TaskPriority priority);

    // SpEL
    @Query("SELECT p FROM Project p WHERE p.createdAt > ?#{#since} AND p.id < ?1")
    List<Project> findRecentBefore(Long beforeId, @Param("since") Instant since);
}

// Positional — ?1, ?2, ?3
@Query("SELECT p FROM Project p WHERE p.status = ?1 AND p.name = ?2")
List<Project> find1(ProjectStatus status, String name);

// Named — :name (recommended)
@Query("SELECT p FROM Project p WHERE p.status = :status AND p.name = :name")
List<Project> find2(@Param("status") ProjectStatus status,
                     @Param("name") String name);

@Query(value = """
    SELECT p.* FROM projects p
    WHERE p.created_at > :since
    AND p.status = :status
    AND EXISTS (SELECT 1 FROM tasks t WHERE t.project_id = p.id AND t.priority = 'HIGH')
    ORDER BY p.created_at DESC
    LIMIT 10
    """,
    nativeQuery = true)
List<Project> findComplexNative(@Param("since") Instant since,
                                  @Param("status") String status);

public interface ProjectRepository extends JpaRepository<Project, Long> {

    @Modifying
    @Transactional
    @Query("UPDATE Project p SET p.status = :newStatus WHERE p.status = :oldStatus")
    int bulkUpdateStatus(@Param("oldStatus") ProjectStatus oldStatus,
                          @Param("newStatus") ProjectStatus newStatus);

    @Modifying
    @Transactional
    @Query("DELETE FROM Project p WHERE p.archivedAt < :cutoff")
    int deleteOldArchived(@Param("cutoff") Instant cutoff);
}

@Transactional
public void example() {
    Project p = repo.findById(42L).orElseThrow();
    System.out.println(p.getStatus());      // ACTIVE

    repo.bulkUpdateStatus(ACTIVE, ARCHIVED);

    System.out.println(p.getStatus());      // VAN ACTIVE — entity stale
    repo.flush();
    em.refresh(p);                           // force reload
    System.out.println(p.getStatus());      // ARCHIVED
}

public interface ProjectSummary {
    Long getId();
    String getName();
    ProjectStatus getStatus();
}

public interface ProjectRepository extends JpaRepository<Project, Long> {
    List<ProjectSummary> findByStatus(ProjectStatus status);
}

public record ProjectSummary(Long id, String name, ProjectStatus status, long taskCount) {}

@Query("""
    SELECT new com.olhub.dto.ProjectSummary(
        p.id, p.name, p.status, COUNT(t.id)
    )
    FROM Project p LEFT JOIN p.tasks t
    WHERE p.status = :status
    GROUP BY p.id, p.name, p.status
    """)
List<ProjectSummary> findSummaryByStatus(@Param("status") ProjectStatus status);

public class ProjectSummaryClass {
    private final Long id;
    private final String name;
    public ProjectSummaryClass(Long id, String name) {
        this.id = id;
        this.name = name;
    }
    // getters
}

public interface ProjectRepository extends JpaRepository<Project, Long> {
    List<ProjectSummaryClass> findByStatus(ProjectStatus status);
}

public interface ProjectVerbose {
    Long getId();
    String getName();

    @Value("#{target.name + ' (' + target.status + ')'}")
    String getDisplayName();
}

public interface ProjectRepository extends JpaRepository<Project, Long> {

    // Default page method
    Page<Project> findAll(Pageable pageable);

    // Custom với filter
    Page<Project> findByStatus(ProjectStatus status, Pageable pageable);

    // With @Query
    @Query("SELECT p FROM Project p WHERE p.createdAt > :since")
    Page<Project> findRecent(@Param("since") Instant since, Pageable pageable);
}

@Service
public class ProjectService {
    public Page<ProjectDto> list(ProjectStatus status, Pageable pageable) {
        Page<Project> page = repo.findByStatus(status, pageable);
        return page.map(ProjectDto::from);
    }
}

@RestController
public class ProjectController {
    @GetMapping
    public Page<ProjectDto> list(
        @RequestParam(required = false) ProjectStatus status,
        Pageable pageable                           // Spring auto-bind từ ?page=&size=&sort=
    ) {
        return service.list(status, pageable);
    }
}

SELECT p.* FROM projects p WHERE p.status = 'ACTIVE'
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

-- Spring Data also runs:
SELECT COUNT(*) FROM projects p WHERE p.status = 'ACTIVE';

Slice<Project> findByStatus(ProjectStatus status, Pageable pageable);

Window<Project> findFirst20By(ScrollPosition position);

public interface ProjectRepository extends JpaRepository<Project, Long>, JpaSpecificationExecutor<Project> {
}

@Service
public class ProjectService {
    public Page<Project> search(ProjectFilter filter, Pageable page) {
        Specification<Project> spec = Specification.where(null);

        if (filter.status() != null) {
            spec = spec.and((root, query, cb) ->
                cb.equal(root.get("status"), filter.status()));
        }

        if (filter.createdAfter() != null) {
            spec = spec.and((root, query, cb) ->
                cb.greaterThan(root.get("createdAt"), filter.createdAfter()));
        }

        if (filter.nameContains() != null) {
            spec = spec.and((root, query, cb) ->
                cb.like(cb.lower(root.get("name")), "%" + filter.nameContains().toLowerCase() + "%"));
        }

        return repo.findAll(spec, page);
    }
}

@Configuration
@EnableJpaAuditing
public class JpaConfig {
}

@MappedSuperclass
@EntityListeners(AuditingEntityListener.class)
public abstract class AuditableEntity {

    @CreatedDate
    @Column(name = "created_at", nullable = false, updatable = false)
    private Instant createdAt;

    @LastModifiedDate
    @Column(name = "updated_at", nullable = false)
    private Instant updatedAt;

    @CreatedBy
    @Column(name = "created_by", updatable = false, length = 50)
    private String createdBy;

    @LastModifiedBy
    @Column(name = "updated_by", length = 50)
    private String updatedBy;
}

@Entity
public class Project extends AuditableEntity {
    @Id @GeneratedValue Long id;
    String name;
    // createdAt, updatedAt inherit
}

@Component
public class SecurityAuditor implements AuditorAware<String> {
    public Optional<String> getCurrentAuditor() {
        return Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication())
            .map(Authentication::getName);
    }
}

spring:
  jpa:
    properties:
      hibernate:
        session:
          events:
            log:
              LOG_QUERIES_SLOWER_THAN_MS: 100

WARN o.h.SQL_SLOW: SlowQuery: 250 milliseconds. SQL: SELECT ... FROM orders WHERE ...

spring.jpa.properties.hibernate.generate_statistics: true
management.metrics.enable.hibernate: true

// VULNERABLE — string concat
@Query(value = "SELECT * FROM orders WHERE status = '" + status + "'", nativeQuery = true)

// SAFE — Spring binds parameter
@Query(value = "SELECT * FROM orders WHERE status = :status", nativeQuery = true)
List<Order> findByStatus(@Param("status") String status);

@Entity
@SQLRestriction("deleted_at IS NULL")        // Hibernate 6.3+, query auto filter
@SQLDelete(sql = "UPDATE orders SET deleted_at = NOW() WHERE id = ?")
public class Order extends Auditable {
    Instant deletedAt;
}

List<Project> findByCustmer(String c);    // typo "Custmer"

@Query("SELECT * FROM projects WHERE id = ?1")    // SQL — fail JPQL

@Modifying
@Query("UPDATE Project p SET p.status = ?1")
int updateAll(ProjectStatus s);     // throw TransactionRequiredException

List<Project> all = repo.findAll();    // OOM

Project p = repo.findById(42L).get();
repo.bulkUpdate(...);                  // p stale, persistence context mismatch

List<ProjectSummary> findByStatus(...);    // load full entity, map