将SQL查询翻译为JPQL-@ManyTo许多加入

Question

我试图在@query（“…”）中翻译SQL本机查询用法，native=true）Spring数据Jpa注释，用于同一注释中的JPQL查询用法。SQL查询：

select d.id as doctorId, d.firstName as doctorFirstName, d.lastName as doctorLastName, d.title as doctorTitle, 
d.email as doctorEmail, v.id as id, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status,
md.id as medicalServicesId, md.service as medicalServicesService, md.price as medicalServicesPrice 
from Visit v 
left outer join Doctor d on v.doctor_id=d.id 
left outer join visit_medical_services vms on v.id=vms.medical_services_id 
left outer join MedicalService md on vms.visit_id=md.id 
where d.id= :doctorId and v.status= :status and v.dateFrom>= :dateFrom and v.dateTo<= :dateTo

我使用的是Spring投影接口，这就是为什么列的别名是这样命名的。

现在我想用JPQL得到完全相同的结果——项目列表。我尝试的是这样的：

    @Query("select d.id as doctorId, d.firstName as doctorFirstName, d.lastName as doctorLastName, d.title as doctorTitle, d.email as doctorEmail, " +
            "v.id as id, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status, md.id as medicalServicesId, md.service as medicalServicesService, md.price as medicalServicesPrice \n" +
            "from Visit v \n" +
            "left outer join Doctor d on v.doctor.id=d.id \n" +
            "left outer join v.medicalServices vms on vms.id=v.id \n" +
            "left outer join MedicalService md on md.id=vms.id \n" +
            "where d.id= :doctorId and v.status= :status and v.dateFrom>= :dateFrom and v.dateTo<= :dateTo")
    List<VisitInfoWithPatientAndMedServices3joins> getAllVisitInfoWithPatientAndMedicalServicesJpqlQuery
    (@Param("doctorId") Long doctorId, @Param("status") VisitStatus status, @Param("dateFrom") LocalDateTime dateFrom, @Param("dateTo") LocalDateTime dateTo, Pageable pageable);

这里是翻译SQL从JPQL。

select doctor1_.id as col_0_0_, doctor1_.firstName as col_1_0_, doctor1_.lastName as col_2_0_, doctor1_.title as col_3_0_, doctor1_.email as col_4_0_, visit0_.id as col_5_0_, visit0_.dateFrom as col_6_0_, visit0_.dateTo as col_7_0_, visit0_.status as col_8_0_, medicalser4_.id as col_9_0_, medicalser4_.service as col_10_0_, medicalser4_.price as col_11_0_ 
from Visit visit0_ 
left outer join  
(visit_medical_services medicalser2_ left outer join MedicalService medicalser3_ on medicalser2_.visit_id=medicalser3_.id) on visit0_.id=medicalser2_.medical_services_id 
and (medicalser3_.id=visit0_.id) 
left outer join Doctor doctor1_ on (visit0_.doctor_id=doctor1_.id) 
left outer join MedicalService medicalser4_ on (medicalser4_.id=medicalser3_.id) where doctor1_.id=? and visit0_.status=? and visit0_.dateFrom>=? and visit0_.dateTo<=? limit ?

这里的问题是，它返回@ManyToMany relations（visit_medical_services）的列，但只返回第一个对象。以下是邮递员的Json回复：

[
    {
        "id": 1,
        "status": "PAID",
        "dateFrom": "2019-04-10T08:00:00",
        "dateTo": "2019-04-10T08:30:00",
        "medicalServicesId": 1,
        "medicalServicesService": "Visit",
        "medicalServicesPrice": 100.0,
        "doctorLastName": "James",
        "doctorFirstName": "Alex",
        "doctorEmail": "[e-mail]",
        "doctorId": 1,
        "doctorTitle": "dr n. md."
    },
    {
        "id": 2,
        "status": "PAID",
        "dateFrom": "2019-04-10T09:00:00",
        "dateTo": "2019-04-10T09:30:00",
        "medicalServicesId": null,
        "medicalServicesService": null,
        "medicalServicesPrice": null,
        "doctorLastName": "James",
        "doctorFirstName": "Alex",
        "doctorEmail": "[e-mail]",
        "doctorId": 1,
        "doctorTitle": "dr n. md."
    }
]

我尝试在Workbench中使用JPQL使用翻译后的SQL查询，因为我使用的是MySQL数据库，结果是一样的——第一个对象是正确的，其余的在mapped@manytomy colums中有空值。下面是我的实体类，如果它能让这个问题变得简单一点的话：

public class Visit {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    Long id;
    LocalDateTime dateFrom;
    LocalDateTime dateTo;
    @Enumerated(EnumType.STRING)
    VisitStatus status;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "doctor_id", referencedColumnName = "id", nullable = true)
    @JsonManagedReference
    Doctor doctor;
    @ManyToOne(fetch = FetchType.LAZY)
    @JsonManagedReference
    @JoinColumn(name = "patient_id", referencedColumnName = "id", nullable = true)
    Patient patient;
    @ManyToMany
    @JsonManagedReference
    @JoinTable(
            name = "visit_diseases",
            joinColumns = @JoinColumn(
                    name = "disease_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(
                    name = "visit_id", referencedColumnName = "id"))
    List<Disease> diseases;
    @ManyToMany
    @JsonManagedReference
    @JoinTable(
            name = "visit_medical_services",
            joinColumns = @JoinColumn(
                    name = "medical_services_id"),
            inverseJoinColumns = @JoinColumn(
                    name = "visit_id"))
    Set<MedicalService> medicalServices;
    String mainSymptoms;
    String treatment;
    String allergy;
    String addiction;
    String comment;
}

和医疗服务：

public class MedicalService {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    Long id;
    String service;
    Float price;

    @ManyToMany(mappedBy = "medicalServices", fetch = FetchType.EAGER)
    private Set<Visit> visits;

    public MedicalService(Long id, String service, float price) {
        this.id = id;
        this.service = service;
        this.price = price;
    }
}

有人可以看一下，并解释给我什么是不正确的工作在这里？我想要实现的是让JPQL生成相同的SQL查询。甚至有可能吗？请帮帮我。。。

Answer 1

您的查询中有一些错误。请使用以下方法

@Query("select d.id as doctorId, d.firstName as doctorFirstName, d.lastName as doctorLastName, d.title as doctorTitle, d.email as doctorEmail, " +
        "v.id as id, v.dateFrom as dateFrom, v.dateTo as dateTo, v.status as status, md.id as medicalServicesId, md.service as medicalServicesService, md.price as medicalServicesPrice \n" +
        "from Visit v \n" +
        "left outer join v.doctor d \n" +
        "left outer join v.medicalServices md \n"
        "where d.id= :doctorId and v.status= :status and v.dateFrom>= :dateFrom and v.dateTo<= :dateTo")
List<VisitInfoWithPatientAndMedServices3joins> getAllVisitInfoWithPatientAndMedicalServicesJpqlQuery
(@Param("doctorId") Long doctorId, @Param("status") VisitStatus status, @Param("dateFrom") LocalDateTime dateFrom, @Param("dateTo") LocalDateTime dateTo, Pageable pageable);