기록만이살길

Question 1

H2 프로젝트를 사용하는 Spring Boot에 문제가 있습니다. SQL 테이블의 요소를 나열하고 SQL 명령이 작동하는 get 메소드가 있습니다. H2 데이터베이스에서 실행하고 결과를 볼 수 있지만 Postman에서 값을 가져올 수 없습니다. 내 GET 게시물이 잘못되었습니다. 내 SQL 코드도 여기에 있습니다. 또한 프로젝트를 github에 업로드했습니다. 모든 클래스를 보려면 여기에 내 GitHub 프로젝트 링크가 있습니다.

다음은 Postman의 오류입니다.

다음은 내 코드의 오류입니다. 내 칼럼을 찾을 수 없습니다.

get 메서드에 대한 내 끝점

UrunEntity 클래스



    @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

여기 내 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);

다음은 백엔드의 오류 코드입니다.

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

KullanicilarEntity 클래스

@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;

내 리소스 (엔드 포인트) 인터페이스

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

Question 2

HayvanDto라는 새 모델 클래스를 만들고이 안에 모든 UrunEntity 엔터티를 만들었습니다.

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;

그 후 구현 클래스에서이 코드를 작성했습니다.

@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;

}

그런 다음 작동했습니다. 답변 해주셔서 감사합니다.

기록만이살길

Spring Boot H2의 SQL Grammer 예외

Spring Boot H2의 SQL Grammer 예외

1. 질문(문제점):

2. 해결방안:

'카테고리 없음'의 다른글

티스토리툴바