0

My actual MySQL Query is:

select * from (select * from consultations where doctor_id='9188389277441234567890' and is_deleted = 0 ORDER BY updated_at desc) as c GROUP BY patient_id, doctor_id, diagnosis_id;

I like to execute the same query in spring boot JPA. My repo code is:

@Query(value = "select c from (select cd from consultations cd where cd.doctor_id=?1 and cd.is_deleted = 0 ORDER BY cd.updated_at desc) as c GROUP BY c.patient_id, c.doctor_id, c.diagnosis_id", nativeQuery = true)
public Page<Consultations> findForDoctor(@Param("username") String username, Pageable pageable);

I am getting

  "message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

Please let me know how can I solve this?

Advance thanks to answers, I am trying this so long.

Adding model details, as asked in the comment:

import java.io.Serializable;
import java.time.LocalDateTime;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Entity
@Table(name = "consultations")
@Data
public class Consultations implements Serializable {

    private static final long serialVersionUID = 6576324953564602096L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "doctor_id")
    private String doctorId;

    @Column(name = "patient_id")
    private String patientId;

    @Column(name = "diagnosis_id")
    private Long diagnosisId;

    @Column(name = "updated_at")
    private LocalDateTime updatedAt;

    @Column(name = "is_deleted")
    private Boolean isDeleted = false;

}

This is the error I got:

2020-04-22 14:30:00,977 DEBUG org.hibernate.SQL : select count(* from (select *) from consultations where doctor_id=? and is_deleted = 0
Hibernate: select count(* from (select *) from consultations where doctor_id=? and is_deleted = 0
2020-04-22 14:30:01,321 DEBUG org.hibernate.engine.jdbc.spi.SqlExceptionHelper : could not extract ResultSet [n/a]
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from (select *) from consultations where doctor_id='9188389277441234567890' a' at line 1
Kayal
  • 21
  • 6
  • Do you extend interface `JpaRepository<>`? – Seldo97 Apr 22 '20 at 08:03
  • Yes. public interface ConsultationsRepo extends JpaRepository {} – Kayal Apr 22 '20 at 08:13
  • Ok, so can you edit post and add Consultations model(Entity) code? I think I can resolve your problem. – Seldo97 Apr 22 '20 at 08:20
  • Edited in the question as you said @Seldo97 – Kayal Apr 22 '20 at 08:27
  • So you can just fold up your custom method without custom query. Add import jaca.util.*; for list (if you want list collection) and try this code: `List getAllByDoctorIdAndIsDeletedOrderByUpdatedAtDesc(String doctorId, boolean isDeleted);` But I think you should correct your structure. You need empty constructor in your model and setters / getters. And I don't see any relation with `doctorId`. – Seldo97 Apr 22 '20 at 08:33
  • Does it work when retrieving the result as a list instead of `Page`? – Amir M Apr 22 '20 at 08:34
  • Yes, you will get list of `Consultations`. But with `Page` it should work too. – Seldo97 Apr 22 '20 at 08:37
  • 1
    Does this answer your question? [Spring Data and Native Query with pagination](https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination) – Lemmy Apr 22 '20 at 08:54
  • @Lemmy I think, his mainly problem is bad structure in model for hibernate. – Seldo97 Apr 22 '20 at 09:00
  • Can you look at the qn now? i added some detailed errors. But the same query I copy and paste in mysql , works – Kayal Apr 22 '20 at 09:03

1 Answers1

0

thanks much for your help.

Special Thanks to @Lemmy. It helps.

I changed my code like this.

@Query(value = "select * from (select * from consultations where doctor_id=?1 and is_deleted = 0 ORDER BY updated_at desc) as c GROUP BY patient_id, doctor_id, diagnosis_id", 
            countQuery = "select count(*) from (select * from consultations where doctor_id=?1 and is_deleted = 0 ORDER BY updated_at desc) as c GROUP BY patient_id, doctor_id, diagnosis_id",
            nativeQuery = true)

Now it works.

Kayal
  • 21
  • 6