SQLGrammarException with native Query in spring boot

Question

I tried to run a GettMapping with Postman. But it's not working and I am getting the error:

Status 500 error. SQLGrammarException: could not extract ResultSet

   @GetMapping("/clients/month/{month}")
    public Meter getAllMeterByMonth(@PathVariable (value = "month") String month) {
        return meterRepository.findByMonth(month);
    }

Repository:

   public interface MeterRepository extends JpaRepository<Meter, Long> {

    Meter findByClientId(Long clientId);

    @Query(value = "select * from meter where month = :month", nativeQuery = true)
    Meter findByMonth(@Param("month")String month);

}

Client Entity:

@Entity
@Table(name = "clients")

    public class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @NotNull
    @Size(max = 100)
    private String name;

Meter entity:

@Entity
@Table(name = "meters")

    public class Meter{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotNull
    @Column(name="year")
    private int year;

    @NotNull
    @Column(name="month")
    private String month;

    @NotNull
    private int value;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "client_id", nullable = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIdentityInfo(generator=ObjectIdGenerators.PropertyGenerator.class, property="id")
    @JsonIdentityReference(alwaysAsId=true)
    @JsonProperty("client_id")
    private Client client;

Do you have any ideas about my issue ?

Answer 1

You are facing this error just because of a simple typo. Replace meter with meters in the query mentioned in MeterRepository.java.

Something like this:

package com.stackoverflow;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface MeterRepository extends JpaRepository<Meter, Long> {
    @Query(value = "select * from meters where month = :month", nativeQuery = true)
    Meter findByMonth(@Param("month")String month);
}