0

I am developing an application with spring boot. While using JPA, i am getting error that error in SQL syntax. 

public interface ReservoirRepository extends CrudRepository<Reservoir, Integer> {

    @Query(value = "From reservoir where patientID = ?1", nativeQuery = true)
    public List<Reservoir> findByPatientId(Integer patientId);
}

Here is the MySQL table and data

enter image description here

The error description is like this 

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 reservoir where patientID = 1' at line 1

Please help me i am not getting what's wrong here. Thanks :)

code veda
  • 87
  • 1
  • 6

1 Answers1

1

Write the full sql command, as its native query. Like @Query(value = "SELECT * FROM Reservoir res where res.patientID = ?1", nativeQuery = true)

Madhav Kumar Jha
  • 353
  • 1
  • 2
  • 13
  • Thanks @Madhav, it worked. If you don't mind i want to ask one question. I want only level value from table so i wrote like this public interface ReservoirRepository extends CrudRepository { @Query(value = "Select res.reservoirId, res.level From reservoir res where res.patientID = ?1 and res.type = ?2", nativeQuery = true) public List findByPatientIdAndType(Integer patientId, Integer type); } but i am getting exception as by stating that "java.sql.SQLException: Column 'patientID' not found." It will be great if you help me to get this. Thanks – code veda Dec 14 '19 at 14:38
  • AND In the above query execution, i got full reservoir object including patient object even though i have added annotation @ManyToOne(fetch = FetchType.LAZY) @JsonIgnore @JoinColumn(name = "patientID") private Patient patient; in Reservoir class. So why it's not working ? – code veda Dec 14 '19 at 14:38
  • As far as your requirement to read specific column rather than full row, its not possible the way you are doing, as result and java object mapping will not work. I don't have specific answer for that , just read this one : https://stackoverflow.com/questions/22007341/spring-jpa-selecting-specific-columns – Madhav Kumar Jha Dec 14 '19 at 15:36
  • For second one as well i don't have any specific answer especially as i can't see full declaration and configuration. For that as well, if you have followed any article/blog then recheck your implementation to check if anything missing. Or explore articles on spring data jpa lazy loading – Madhav Kumar Jha Dec 14 '19 at 15:38