What I'm trying to achieve is write the same query using 3 different approaches in Spring Data Jpa using JpaRepository interface:
Here you can see how I've created Visit Entity with all relations which I'm trying to select.
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;
I'm using Project Lombok to I wont copy all the annotations above the class. So here is the experiment. I created method which should return all visits for specific doctor in given time interval.
Here is the method that I wrote:
List<VisitView> findByDoctorIdAndStatusAndDateFromGreaterThanEqualAndDateToLessThanEqual
(long doctorId, VisitStatus visitStatus, LocalDateTime dateFrom, LocalDateTime dateTo);
As you can see I already implemented VisitView interface using Spring Projections.
Here it is:
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();
}
And using this method everything works just fine. I can get doctor firstName and lastName from Doctor Entity Class in both ways -> using getters and built in another Doctor interface to access fields from the Entity. Here you can see both JSON's using projected interface:
[
{
"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
}
]
Now I want to achieve the same result using @Query with JPQL and native SQL. So I printed out the generated SQL from this method i tried to use it with @Query annotation. Here you can see it:
@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);
Both these queries return the JSONs with getters or Doctor interface with null values form 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
}
]
What I've already done is tried many versions of Hibernate, because I read a lot about bugs which have appeared in different versions. Tried to group selected fields in alphabetically way, as i found this tip in another question here. Tried to use @Join Column annotation as it's suggested to do so but it did not help either.
And now I'm getting crazy because I can not figure out why it's not working. Can somebody please help me?
Hibernate core verion -> 5.4.14.Final
Hibernate orm-search verion -> 5.11.5.Final
EDIT: Issue above is solved but.. I have another question regarding this topic.
Entity Visit is in @ManyToMany relation with MedicalServices. Now i want to pull this list, so i projected another interface:
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();
}
}
This interface return only ONE object with List of MedicalServices using Named Method Strategy. Here is JSON from Postman:
[
{
"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"
}
]
But i still can not get it right with the Native SQL and @Query annotation. I know that i can use solution from this question to get it, you can see it commented out in above VisitInfoWithPatientAndMedServices interface and its working but it's returning not 1 Visit Object with List of medical services, but 2 same object, each one with one medical services. It looks like this:
{
"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"
}
]
It's working just like in the Workbench, because I'm using MySQL.
Can i do anything about it to get the same JSON response using Named Method Strategy and @Query annotation (native SQL and JPQL) ??
You are using d.firstName as firstName and d.lastName as lastName this means you want to project the values in firstName and lastName field in interface.
Use d.firstName as doctorFirstName, d.lastName as doctorLastName in @Query to get the value.
@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);