Spring Data JPA & Hibernate: Entities, Repositories & Queries

@Entity
@Table(name = "appointments")
public class Appointment {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;

    @Column(nullable = false)
    private LocalDateTime dateTime;

    @Enumerated(EnumType.STRING)   // store "SCHEDULED", not 0
    @Column(nullable = false, length = 20)
    private AppointmentStatus status;

    @Column(columnDefinition = "TEXT")
    private String notes;

    @Column(nullable = false)
    private AppointmentType type;

    @CreationTimestamp
    @Column(updatable = false)
    private Instant createdAt;

    @UpdateTimestamp
    private Instant updatedAt;

    // Relationships defined below
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "clinic_id", nullable = false)
    private Clinic clinic;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "patient_id", nullable = false)
    private Patient patient;

    // Getters/setters or use Lombok @Getter @Setter
}

public enum AppointmentStatus {
    SCHEDULED, CONFIRMED, COMPLETED, CANCELLED, NO_SHOW
}

@Entity
@Table(name = "clinics")
public class Clinic {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private String id;

    @Column(nullable = false, unique = true)
    private String name;

    @OneToMany(mappedBy = "clinic", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<Appointment> appointments = new ArrayList<>();

    // Add/remove helpers maintain bidirectional consistency
    public void addAppointment(Appointment appt) {
        appointments.add(appt);
        appt.setClinic(this);
    }

    public void removeAppointment(Appointment appt) {
        appointments.remove(appt);
        appt.setClinic(null);
    }
}

@Entity
public class Patient {

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(
        name = "patient_clinics",
        joinColumns        = @JoinColumn(name = "patient_id"),
        inverseJoinColumns = @JoinColumn(name = "clinic_id")
    )
    private Set<Clinic> clinics = new HashSet<>();
}

// WRONG — loading 100 appointments will also load 100 patients immediately
@ManyToOne(fetch = FetchType.EAGER)  // avoid for @ManyToOne if not needed
private Patient patient;

// RIGHT — load patient only when accessed
@ManyToOne(fetch = FetchType.LAZY)
private Patient patient;

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, String> {

    // Derived query — Spring generates SQL from the method name
    List<Appointment> findByClinicIdAndStatus(String clinicId, AppointmentStatus status);

    // Date range
    List<Appointment> findByClinicIdAndDateTimeBetween(
        String clinicId, LocalDateTime from, LocalDateTime to);

    // Exists check
    boolean existsByPatientIdAndDateTimeAndStatusNot(
        String patientId, LocalDateTime dateTime, AppointmentStatus status);

    // Paging
    Page<Appointment> findByClinicId(String clinicId, Pageable pageable);

    // Count
    long countByClinicIdAndStatus(String clinicId, AppointmentStatus status);
}

@Repository
public interface AppointmentRepository extends JpaRepository<Appointment, String> {

    // Fetch appointments with eagerly loaded clinic and patient to avoid N+1
    @Query("""
        SELECT a FROM Appointment a
        JOIN FETCH a.clinic c
        JOIN FETCH a.patient p
        WHERE a.clinic.id = :clinicId
          AND a.dateTime >= :from
          AND a.dateTime < :to
        ORDER BY a.dateTime ASC
        """)
    List<Appointment> findByClinicAndDateRange(
        @Param("clinicId") String clinicId,
        @Param("from")     LocalDateTime from,
        @Param("to")       LocalDateTime to
    );

    // Projection — only fetch needed columns
    @Query("SELECT new com.clinic.portal.appointment.dto.AppointmentSummary(a.id, a.dateTime, a.status) FROM Appointment a WHERE a.clinic.id = :clinicId")
    List<AppointmentSummary> findSummariesByClinic(@Param("clinicId") String clinicId);

    // Native SQL for DB-specific features
    @Query(value = """
        SELECT DATE_TRUNC('hour', date_time) as hour,
               COUNT(*) as count
        FROM appointments
        WHERE clinic_id = :clinicId
          AND date_time >= NOW() - INTERVAL '7 days'
        GROUP BY 1
        ORDER BY 1
        """, nativeQuery = true)
    List<Object[]> getHourlyVolume(@Param("clinicId") String clinicId);

    // Update query — modifies data directly without loading entities
    @Modifying
    @Transactional
    @Query("UPDATE Appointment a SET a.status = :status WHERE a.id = :id")
    int updateStatus(@Param("id") String id, @Param("status") AppointmentStatus status);
}

// Interface-based projection — Spring generates a proxy
public interface AppointmentSummary {
    String getId();
    LocalDateTime getDateTime();
    AppointmentStatus getStatus();

    @Value("#{target.patient.firstName + ' ' + target.patient.lastName}")
    String getPatientName();
}

// DTO projection (faster — no proxy overhead)
public record AppointmentSummary(String id, LocalDateTime dateTime, AppointmentStatus status) {}

// In the repository
List<AppointmentSummary> findByClinicId(String clinicId);

@Service
@Transactional(readOnly = true)   // default: all methods read-only
public class AppointmentService {

    @Transactional                // override: this method writes
    public AppointmentResponse create(AppointmentRequest request, String userId) {
        Clinic clinic = clinicRepository.findById(request.clinicId())
            .orElseThrow(() -> new EntityNotFoundException("Clinic not found: " + request.clinicId()));

        Patient patient = patientRepository.findById(request.patientId())
            .orElseThrow(() -> new EntityNotFoundException("Patient not found"));

        // Check for scheduling conflict
        boolean conflict = appointmentRepository.existsByPatientIdAndDateTimeAndStatusNot(
            request.patientId(), request.dateTime(), AppointmentStatus.CANCELLED);

        if (conflict) {
            throw new AppointmentConflictException("Patient already has an appointment at this time");
        }

        Appointment appt = mapper.toEntity(request, clinic, patient);
        Appointment saved = appointmentRepository.save(appt);

        eventPublisher.publishEvent(new AppointmentCreatedEvent(saved));

        return mapper.toResponse(saved);
    }

    @Transactional
    public void cancel(String id, String reason) {
        Appointment appt = appointmentRepository.findById(id)
            .orElseThrow(() -> new EntityNotFoundException("Appointment not found: " + id));

        if (appt.getStatus() == AppointmentStatus.COMPLETED) {
            throw new IllegalStateException("Cannot cancel a completed appointment");
        }

        appt.setStatus(AppointmentStatus.CANCELLED);
        appt.setNotes(reason);
        // No explicit save() needed — Hibernate's dirty checking persists changes
    }
}

// This triggers N+1 queries:
// 1 query to load 100 appointments
// 100 queries to load each appointment's clinic (lazy)
List<Appointment> appts = repository.findAll();
appts.forEach(a -> System.out.println(a.getClinic().getName())); // LAZY load here

@Query("SELECT a FROM Appointment a JOIN FETCH a.clinic JOIN FETCH a.patient WHERE a.clinic.id = :id")
List<Appointment> findWithDetails(@Param("id") String clinicId);

@EntityGraph(attributePaths = {"clinic", "patient"})
List<Appointment> findByClinicId(String clinicId);

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

-- src/main/resources/db/migration/V1__create_clinics.sql
CREATE TABLE clinics (
    id         VARCHAR(36)  PRIMARY KEY,
    name       VARCHAR(200) NOT NULL UNIQUE,
    state      VARCHAR(2)   NOT NULL,
    active     BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- src/main/resources/db/migration/V2__create_appointments.sql
CREATE TABLE appointments (
    id         VARCHAR(36)  PRIMARY KEY,
    clinic_id  VARCHAR(36)  NOT NULL REFERENCES clinics(id),
    patient_id VARCHAR(36)  NOT NULL REFERENCES patients(id),
    date_time  TIMESTAMPTZ  NOT NULL,
    status     VARCHAR(20)  NOT NULL DEFAULT 'SCHEDULED',
    notes      TEXT,
    type       VARCHAR(50)  NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_appointments_clinic_date ON appointments(clinic_id, date_time);
CREATE INDEX idx_appointments_patient     ON appointments(patient_id);
CREATE INDEX idx_appointments_status      ON appointments(status);