java - Spring Boot native 查询抛出错误，但在mysql中有效

Question

这个问题已经有答案了:

奥 git _a (20 个回答)

已关闭 3 年前。

我的实际 MySQL 查询是:

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;

我喜欢在 Spring Boot JPA 中执行相同的查询。我的 repo 代码是:

@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);

我正在获取

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

请告诉我如何解决这个问题？

提前感谢您的回答，我已经尝试了很长时间了。

按照评论中的要求添加模型详细信息:

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;

}

这是我得到的错误:

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

Answer 1

非常感谢您的帮助。

特别感谢@Lemmy。它有帮助。

我像这样更改了代码。

@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)

现在可以了。