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 ?

When yu get an exception, and you wonder why, you need to read the stack trace ofthe exception. And if you still don't understand, you need to post it. Guessing is hard. deducing from the message explaining the problem is much easier. — JB Nizet, Nov 22 '18 at 22:57
The message probably tells you that the table "meter" doesn't exist, since you mapped your entity to "meters". Why the hell are you using SQL rather than JPQL, BTW? — JB Nizet, Nov 22 '18 at 22:58
You have to check the log of your service rest to see what's the exacte error and not the stack that you have into postman. I have some doubt about your relation ManyToOne Could you add to your Client.java : @OneToMany(mappedBy = "client") private List metters. Also you do not need to use Query juste findByMonth do the job — Abder KRIMA, Nov 22 '18 at 23:42

score 1 · Accepted Answer · answered Nov 23 '18 at 00:58

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

SQLGrammarException with native Query in spring boot

1 Answers1