JavaSpringDataJPA+SpringProjections使用@Query(native和JPQL)为相关实体返回null
我试图实现的是,在Spring Data Jpa中使用JpaRepository接口使用3种不同的方法编写相同的查询:
- 命名方法策略李>
- @使用JPQL进行查询李>
- @查询本机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;
我正在使用ProjectLombok,我不会复制类上面的所有注释。 这就是实验。我创建了一个方法,该方法应该在给定的时间间隔内返回特定医生的所有就诊
以下是我写的方法:
List<VisitView> findByDoctorIdAndStatusAndDateFromGreaterThanEqualAndDateToLessThanEqual
(long doctorId, VisitStatus visitStatus, LocalDateTime dateFrom, LocalDateTime dateTo);
如您所见,我已经使用Spring投影实现了VisitView界面
这是:
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();
}
使用这种方法,一切都很好。我可以通过两种方式从doctor实体类中获取doctor firstName和lastName->;使用getter和内置的另一个Doctor接口从实体访问字段。在这里,您可以看到使用投影接口的两个JSON:
[
{
"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和JPQL以及原生SQL实现相同的结果。因此,我打印了从这个方法生成的SQL,并尝试将其与@Query注释一起使用。在这里您可以看到:
@Query+native-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);
这两个查询都返回带有getter的JSON或带有空值的Doctor接口,格式为VisitView:
[
{
"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
}
]
我已经尝试了很多版本的Hibernate,因为我读了很多关于不同版本中出现的bug的文章。尝试按字母顺序对所选字段进行分组,因为我在这里的另一个问题中找到了此提示。尝试按建议使用@Join Column注释,但也没有帮助
现在我开始发疯了,因为我不明白为什么它不起作用。 谁能帮帮我吗
Hibernate core verion->;5.4.14.决赛
休眠orm搜索验证->;5.11.5.决赛
编辑: 上述问题已解决,但。。 关于这个话题,我还有一个问题
实体访问与医疗服务有@manytomy关系。现在我想拉这个列表,所以我设计了另一个界面:
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列表。以下是来自邮递员的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"
}
]
它就像在工作台上一样工作,因为我使用的是MySQL
我可以使用命名方法策略和@Query注释(原生SQL和JPQL)来获得相同的JSON响应吗
# 1 楼答案
您正在使用
d.firstName as firstName
和d.lastName as lastName
。这意味着您希望在接口的firstName
和lastName
字段中投影值在@Query中使用
d.firstName as doctorFirstName, d.lastName as doctorLastName
获取值