Spring MVC医院系统：实现按患者数排序的Doctor分页查询

我正在开发一个医院MVC网站，用数据库存储患者、医生数据。需要获取按患者数量排序的医生列表，之前在Java内存里用Comparator排序：

Page<Doctor> pageDoctor = doctorRepository.findAll(pageable);
List<Doctor> doctorList = pageDoctor.getContent();
doctorList.sort(Comparator.comparing(o -> patientRepository.findAllByDoctor(o).size()));

但现在需要直接从数据库查询并返回排序后的Page<Doctor>对象，刚接触SQL，不知道怎么写查询。以下是实体和仓库代码：

实体类和仓库代码 #

Doctor.java #

@Entity
@Table(name = "doctors")
public class Doctor {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    @SequenceGenerator(name="doctors_generator", sequenceName = "doctors_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "doctors_generator")
    private Long id;
    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "doctors_type_id")
    private DoctorsType doctorsType;

    public Doctor(User user, DoctorsType doctorsType) {
        this.user = user;
        this.doctorsType = doctorsType;
    }

    public Doctor() {
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public DoctorsType getDoctorsType() {
        return doctorsType;
    }

    public void setDoctorsType(DoctorsType doctorsType) {
        this.doctorsType = doctorsType;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

Patient.java #

@Entity
@Table(name = "patients")
public class Patient {
    @Id
    @Column(name = "id", nullable = false, unique = true)
    @SequenceGenerator(name="patients_generator", sequenceName = "patients_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "patients_generator")
    private Long id;

    @OneToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "doctor_id")
    private Doctor doctor;
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "treatment_id")
    private Treatment treatment;

    public Patient(User user, Doctor doctor, Treatment treatment) {
        this.user = user;
        this.doctor = doctor;
        this.treatment = treatment;
    }

    public Patient() {
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public Doctor getDoctor() {
        return doctor;
    }

    public void setDoctor(Doctor doctor) {
        this.doctor = doctor;
    }

    public Treatment getTreatment() {
        return treatment;
    }

    public void setTreatment(Treatment treatment) {
        this.treatment = treatment;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }
}

PatientRepository.java #

@Repository
public interface PatientRepository extends JpaRepository<Patient, Long> {
    Patient findPatientByUser(User user);

    List<Patient> findAllByDoctor(Doctor doctor);
    Patient findPatientById(long id);
    Page<Patient> findAllByOrderByIdAsc(Pageable pageable);
    List<Patient> findAllByOrderByIdAsc();
}

DoctorRepository.java #

@Repository
public interface DoctorRepository extends JpaRepository<Doctor, Long> {
    Doctor findDoctorById(long id);
    Doctor findDoctorByUser(User user);

    @Query(
            //sql query there
    )
    Page<Doctor> findAllByPatientCountAsc(Pageable pageable);
    Page<Doctor> findAll(Pageable pageable);

    List<Doctor> findAllByOrderByIdAsc();
    List<Doctor> findAllByDoctorsTypeNot(DoctorsType doctorsType);
    List<Doctor> findAllByDoctorsType(DoctorsType doctorsType);
}

方法1：JPQL实现分页+排序 #

直接在DoctorRepository的findAllByPatientCountAsc方法中编写JPQL，通过左连接统计患者数并排序：

@Repository
public interface DoctorRepository extends JpaRepository<Doctor, Long> {
    // 其他方法...

    @Query("SELECT d, COUNT(p.id) AS patientCount " +
           "FROM Doctor d " +
           "LEFT JOIN Patient p ON d.id = p.doctor.id " +
           "GROUP BY d.id " +
           "ORDER BY patientCount ASC")
    Page<Object[]> findAllByPatientCountAsc(Pageable pageable);
}

结果处理 #

该查询返回Object[]数组（第一个元素是Doctor，第二个是患者数量），可在Service层转换为Page<Doctor>：

public Page<Doctor> getDoctorsSortedByPatientCount(Pageable pageable) {
    Page<Object[]> resultPage = doctorRepository.findAllByPatientCountAsc(pageable);
    List<Doctor> doctorList = resultPage.stream()
            .map(objArr -> (Doctor) objArr[0])
            .collect(Collectors.toList());
    return new PageImpl<>(doctorList, pageable, resultPage.getTotalElements());
}

方法2：原生SQL查询（适配复杂场景） #

如果JPQL满足需求，也可以用原生SQL，注意要单独指定count查询用于分页统计：

@Repository
public interface DoctorRepository extends JpaRepository<Doctor, Long> {
    // 其他方法...

    @Query(value = "SELECT d.*, COUNT(p.id) AS patient_count " +
                   "FROM doctors d " +
                   "LEFT JOIN patients p ON d.id = p.doctor_id " +
                   "GROUP BY d.id " +
                   "ORDER BY patient_count ASC",
           countQuery = "SELECT COUNT(DISTINCT d.id) FROM doctors d LEFT JOIN patients p ON d.id = p.doctor_id",
           nativeQuery = true)
    Page<Doctor> findAllByPatientCountAsc(Pageable pageable);
}

方法3：优化实体关联（更简洁） #

在Doctor实体中添加患者集合的关联，让JPA自动处理统计逻辑：

@Entity
@Table(name = "doctors")
public class Doctor {
    // 原有字段...

    @OneToMany(mappedBy = "doctor", fetch = FetchType.LAZY)
    private List<Patient> patients = new ArrayList<>();

    // 添加getter
    public List<Patient> getPatients() {
        return patients;
    }
}

然后JPQL查询可简化为：

@Repository
public interface DoctorRepository extends JpaRepository<Doctor, Long> {
    // 其他方法...

    @Query("SELECT d FROM Doctor d ORDER BY SIZE(d.patients) ASC")
    Page<Doctor> findAllByPatientCountAsc(Pageable pageable);
}

为什么不建议内存排序？ #

你之前的内存排序存在两个核心问题：

1. 性能损耗大：每个医生单独查询患者列表，会触发N+1次SQL查询
2. 分页逻辑错误：先分页再排序，仅对当前页的医生排序，无法实现全局排序后分页

内容的提问来源于stack exchange，提问作者smoothieee