java - SQL Grammer Exception in Spring Boot H2

Question

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

Here is the error from Postman

Here is the error from my code. It can't find my column.

My EndPoints for get methods

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 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 query
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at 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 1

Seems like you want to materialize an entity from a native query, but the native query is not selecting the urun_id column which is required to materialize the entity. Try using HQL queries instead of native queries.