Spring Boot native query throws error, but works in mysql [duplicate]

Question

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

Yes. public interface ConsultationsRepo extends JpaRepository<Consultations, Long> {} — Kayal
– Kayal, Commented Apr 22, 2020 at 8:13
Ok, so can you edit post and add Consultations model(Entity) code? I think I can resolve your problem. — Seldo97
– Seldo97, Commented Apr 22, 2020 at 8:20
Does this answer your question? Spring Data and Native Query with pagination — Lemmy
– Lemmy, Commented Apr 22, 2020 at 8:54

Kayal · Accepted Answer · 2020-04-22 09:10:45Z

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.

answered Apr 22, 2020 at 9:10

Kayal

396 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Boot native query throws error, but works in mysql [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related