使用@query（native and JPQL）的Spring Data Jpa+Spring Projections为相关实体返回null

Question

我试图实现的是在Spring Data Jpa中使用JpaRepository接口使用3种不同的方法编写相同的查询：

1. 命名方法策略。
2. @使用jpql查询。
3. @查询本机SQL.

在这里，您可以看到我如何创建访问实体与所有关系，我试图选择。

public class Visit {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    long visitId;
    LocalDateTime dateFrom;
    LocalDateTime dateTo;
    @Enumerated(EnumType.STRING)
    VisitStatus status;
    @ManyToOne(fetch = FetchType.EAGER)
    @JsonManagedReference
    Doctor doctor;
    @ManyToOne
    @JsonManagedReference
    Patient patient;
    @ManyToMany
    @JsonManagedReference
    List<Disease> diseases;
    @ManyToMany
    @JsonManagedReference
    List<MedicalService> medicalServices;
    String mainSymptoms;
    String treatment;
    String allergy;
    String addiction;
    String comment;

我使用Project Lombok来避免复制类上面的所有注释。这就是实验。我创建了一种方法，它应该在给定的时间间隔内返回特定医生的所有访问。

List<VisitView> findByDoctorIdAndStatusAndDateFromGreaterThanEqualAndDateToLessThanEqual
            (long doctorId, VisitStatus visitStatus, LocalDateTime dateFrom, LocalDateTime dateTo);

public interface VisitView {
    long getDoctorId();
//    Doctor getDoctor();
//    interface Doctor {
//        String getFirstName();
//        String getLastName();
//    }
    String getDoctorFirstName();
    String getDoctorLastName();
    Long getPatientId();
    long getVisitId();
    LocalDateTime getDateFrom();
    LocalDateTime getDateTo();
    VisitStatus getStatus();
}

[
    {
        "status": "PAID",
        "visitId": 395,
        "dateTo": "2019-04-10T08:30:00",
        "dateFrom": "2019-04-10T08:00:00",
        "doctorId": 401,
        "patientId": 394,
        "doctorFirstName": "Aleksander",
        "doctorLastName": "Ziółko"
    }
]

[
    {
        "status": "PAID",
        "visitId": 395,
        "dateTo": "2019-04-10T08:30:00",
        "doctor": {
            "firstName": "Aleksander",
            "lastName": "Ziółko"
        },
        "dateFrom": "2019-04-10T08:00:00",
        "doctorId": 401,
        "patientId": 394
    }
]

@Query+原生SQL:

@Query(value = "SELECT d.id as doctorId, d.firstName as firstName, d.lastName as lastName, p.id as patientId, v.id as visitId, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status \n" +
            "FROM visit v \n" +
            "LEFT OUTER JOIN doctor d on v.doctor_id=d.id \n" +
            "LEFT OUTER JOIN users ud on d.id=ud.id \n" +
            "LEFT OUTER JOIN patient p on v.patient_id=p.id \n" +
            "LEFT OUTER JOIN users up on p.id=up.id \n" +
            "where d.id= :doctorId and v.status= :status and v.dateFrom>= :dateFrom and v.dateTo<= :dateTo ", nativeQuery = true)
    List<VisitView> searchForDoctorsVisitByStatusAndTimeIntervalNativeQuery(
            @Param("doctorId") long doctorId, @Param("status") String status, @Param("dateFrom") LocalDateTime dateFrom, @Param("dateTo") LocalDateTime dateTo);

@Query+JPQL:

@Query("SELECT d.id as doctorId, d.firstName as firstName, d.lastName as lastName, p.id as patientId, v.visitId as visitId, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status \n" +
            "FROM Visit v \n" +
            "LEFT OUTER JOIN Doctor d ON v.doctor.id=d.id \n" +
            "LEFT OUTER JOIN Patient p ON v.patient.id=p.id \n" +
            "WHERE d.id= :doctorId AND v.status= :status AND v.dateFrom>= :dateFrom AND v.dateTo<= :dateTo")
    List<VisitView> searchForDoctorsVisitByStatusAndTimeIntervalJqplQuery(
            @Param("doctorId") long doctorId, @Param("status") VisitStatus status, @Param("dateFrom") LocalDateTime dateFrom, @Param("dateTo") LocalDateTime dateTo);

这两个查询都在VisitView中返回带有getter的JSONs或带有null值的Doctor接口：

[
    {
        "status": "PAID",
        "visitId": 395,
        "dateTo": "2019-04-10T08:30:00",
        "dateFrom": "2019-04-10T08:00:00",
        "doctorId": 401,
        "patientId": 394,
        "doctorFirstName": null,
        "doctorLastName": null
    }
]

[
    {
        "status": "PAID",
        "visitId": 395,
        "dateTo": "2019-04-10T08:30:00",
        "doctor": null,
        "dateFrom": "2019-04-10T08:00:00",
        "doctorId": 401,
        "patientId": 394
    }
]

实体访问与MedicalServices有@manytomany关系。现在我想拉这个列表，所以我投射了另一个界面：

public interface VisitInfoWithPatientAndMedServices {
    LocalDateTime getDateFrom();
    LocalDateTime getDateTo();
    VisitStatus getStatus();
//  long getMedicalServicesId();
//  String getMedicalServicesService();
//  float getMedicalServicesPrice();
    List<MedicalService> getMedicalServices();
    interface MedicalService {
        String getId();
        String getService();
        float getPrice();
    }
}

此接口使用命名方法策略只返回一个带有MedicalServices列表的对象。以下是Postman的JSON：

[
    {
        "status": "PAID",
        "medicalServices": [
            {
                "id": "3",
                "service": "Something",
                "price": 250.0
            },
            {
                "id": "4",
                "service": "USG",
                "price": 400.0
            }
        ],
        "dateTo": "2019-04-10T08:30:00",
        "dateFrom": "2019-04-10T08:00:00"
    }
]

但是我仍然不能正确地使用本机SQL和@Query注释。我知道我可以从这个问题中使用解决方案来得到它，你可以看到它在上面的VisitInfoWithPatientAndMedServices接口中被注释掉，它的工作，但它返回的不是一个带有医疗服务列表的访问对象，而是两个相同的对象，每个对象带有一个医疗服务。它看起来是这样的：

    {
        "dateTo": "2019-04-10T08:30:00",
        "dateFrom": "2019-04-10T08:00:00",
        "medicalServicesId": 3,
        "medicalServicesPrice": 250.0,
        "medicalServicesService": "Something",
        "status": "PAID"
    },
    {
        "dateTo": "2019-04-10T08:30:00",
        "dateFrom": "2019-04-10T08:00:00",
        "medicalServicesId": 4,
        "medicalServicesPrice": 400.0,
        "medicalServicesService": "USG",
        "status": "PAID"
    }
]

我能对此做些什么来使用命名方法策略和@Query注释（原生SQL和JPQL）获得相同的JSON响应吗？？

Answer 1

您正在使用d.firstname作为firstname，d.lastname作为lastname，这意味着您希望在接口中的firstname和lastname字段中投射值。

在@query中使用d.firstname作为doctorFirstName，d.lastname作为doctorlastname获取值。

 @Query("SELECT d.id as doctorId, d.firstName as doctorFirstName, d.lastName as doctorLastName, p.id as patientId, v.visitId as visitId, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status \n" +
            "FROM Visit v \n" +
            "LEFT OUTER JOIN Doctor d ON v.doctor.id=d.id \n" +
            "LEFT OUTER JOIN Patient p ON v.patient.id=p.id \n" +
            "WHERE d.id= :doctorId AND v.status= :status AND v.dateFrom>= :dateFrom AND v.dateTo<= :dateTo")
    List<VisitView> searchForDoctorsVisitByStatusAndTimeIntervalJqplQuery(
            @Param("doctorId") long doctorId, @Param("status") VisitStatus status, @Param("dateFrom") LocalDateTime dateFrom, @Param("dateTo") LocalDateTime dateTo);