SQL Grammar Exception in Spring Boot H2

I’v got a problem in my Spring Boot with H2 project. I got a get method for listing elements from the SQL table and the SQL command working. In the H2 database I can execute and see the results but I can’t get the values from Postman. My GET post went wrong. My SQL codes are also in here.

I also uploaded my project to github. If you want to see all classes Here is my GitHub project link

UrunEntity class

@Entity
@Table(name = "urunler")
public class UrunEntity{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "urun_id")
    private int urunId;

    @Column(name = "hayvan_kupe_no")
    private int hayvanKupeNo;

    @Column(name = "hayvan_adi")
    private String hayvanAdi;

    @Column(name = "dogum_sekli")
    private String dogumSekli;

    @Column(name = "hayvan_resmi")
    private String hayvanResmi;

    @Column(name = "hayvan_cinsiyet")
    private String hayvanCinsiyet;

    @Column(name = "hayvan_irki")
    private String hayvanIrki;

    @Column(name = "hayvan_anneAdi")
    private String hayvanAnneAdi;

    @Column(name = "dogum_tarihi")
    private String dogumTarihi;

    @Column(name = "dogum_agirligi")
    private Double dogumAgirligi;

    @Column(name = "tohuma_hazir")
    private Boolean tohumaHazir;

    @Column(name = "sut_miktari")
    private Double sutMiktari;

    @Column(name = "sut_tarihi")
    private String sutTarihi;

    @Column(name = "urun_tutar")
    private Double urunTutar;

    @Column(name = "user_id")
    private Integer userId;
//getters and setters after that

Here is my UrunRepository

@Query(value="SELECT DOGUM_AGIRLIGI, DOGUM_SEKLI, DOGUM_TARIHI, HAYVAN_ADI, HAYVAN_ANNE_ADI, HAYVAN_CINSIYET, HAYVAN_IRKI, URUN_TUTAR FROM URUNLER d " +
            "INNER JOIN kullanicilar k on d.user_id = k.user_id " +
            "WHERE k.user_id=:userId AND HAYVAN_ADI IS NOT NULL",
            nativeQuery=true)
    List<UrunEntity> findHayvanAll(@Param("userId") String userId);

Here is the error code at backend

> 2021-01-12 17:58:39.357  WARN 19652 --- [nio-6161-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42122, SQLState: 42S22
2021-01-12 17:58:39.357 ERROR 19652 --- [nio-6161-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column "urun_id" not found [42122-200]
2021-01-12 17:58:39.374 ERROR 19652 --- [nio-6161-exec-5] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/invoiceControl] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT DOGUM_AGIRLIGI, DOGUM_SEKLI, DOGUM_TARIHI, HAYVAN_ADI, HAYVAN_ANNE_ADI, HAYVAN_CINSIYET, HAYVAN_IRKI, URUN_TUTAR FROM URUNLER d INNER JOIN kullanicilar k on d.user_id = k.user_id WHERE k.user_id=? AND HAYVAN_ADI IS NOT NULL]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "urun_id" not found [42122-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:453) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.get(DbException.java:205) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.message.DbException.get(DbException.java:181) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3169) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3268) ~[h2-1.4.200.jar:1.4.200]
    at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:352) ~[h2-1.4.200.jar:1.4.200]

Here is the error from Postman:

    "timestamp": "2021-01-12T14:30:50.458+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "could not execute query; SQL [SELECT SUT_MIKTARI, SUT_TARIHI, URUN_TUTAR FROM URUNLER d INNER JOIN kullanicilar k on d.user_id = k.user_id WHERE k.user_id=? AND SUT_MIKTARI IS NOT NULL]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query",
    "trace": "org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT SUT_MIKTARI, SUT_TARIHI, URUN_TUTAR FROM URUNLER d INNER JOIN kullanicilar k on d.user_id = k.user_id WHERE k.user_id=? AND SUT_MIKTARI IS NOT NULL]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute queryrntat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)rntat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)rntat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)rntat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)rntat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)rntat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)rntat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)rntat org.springframework.data.jpa.repository.support

KullanicilarEntity class

@Entity
@Table(name = "kullanicilar")
public class KullaniciEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "user_id")
    private long userId;

    @Column(name = "email")
    private String email;

    @Column(name = "kullanici_sifre")
    private String kullaniciSifre;

    @Column(name = "kullanici_adi")
    private String kullaniciAdi;

    @Column(name = "kullanici_soyadi")
    private String kullaniciSoyadi;

    @Column(name = "telefon_no")
    private String telefonNo;

    @Column(name = "enabled")
    private boolean enabled;

    @Column(name = "username")
    private String username;

My Resource(endpoints) interface

 @GetMapping(path = "/getSut")
    public ResponseEntity<List<UrunEntity>> getSut(@RequestParam("userId") String userId) {
        List<UrunEntity> urunEntities = ccAppService.findSutAll(userId);
        return new ResponseEntity(urunEntities, HttpStatus.OK);
    }

Answer

I create a new model class named HayvanDto and I created all of the UrunEntity entites inside of this.

public class HayvanDto {

private Double dogumAgirligi;

private String dogumSekli;

private String dogumTarihi;

private String hayvanAdi;

private String hayvanAnneAdi;

private String hayvanCinsiyet;

private String hayvanIrki;

private String hayvanResmi;

private Boolean tohumaHazir;

private Double urunTutar;

After that, in my implementation class, I wrote this code.

@Override
public List<HayvanDto> findHayvanAll(Integer userId) {
    List<UrunEntity> urunEntities = urunRepository.findHayvanAll(userId);
    List<HayvanDto> hayvanDtos = new ArrayList<>();
          for (UrunEntity urunEntity : urunEntities) {
              HayvanDto hayvanDto = new HayvanDto();
              hayvanDto.setHayvanAdi(urunEntity.getHayvanAdi());
              hayvanDto.setHayvanIrki(urunEntity.getHayvanIrki());
              hayvanDto.setHayvanResmi(urunEntity.getHayvanResmi());
              hayvanDto.setHayvanCinsiyet(urunEntity.getHayvanCinsiyet());
              hayvanDto.setHayvanAnneAdi(urunEntity.getHayvanAnneAdi());
              hayvanDto.setDogumAgirligi(urunEntity.getDogumAgirligi());
              hayvanDto.setDogumSekli(urunEntity.getDogumSekli());
              hayvanDto.setUrunTutar(urunEntity.getUrunTutar());
              hayvanDto.setTohumaHazir(urunEntity.getTohumaHazir());

              hayvanDtos.add(hayvanDto);
          }
    return hayvanDtos;

}

Then it worked. Thanks for the answers.

Advertisement

Answer