我最近开始使用Spring Data JPA。
如果有人能帮我解决下面的问题，我将不胜感激。我不确定我错在哪里。
我试图在Sping Boot 应用程序中使用SpringDataJpa编写本机查询。查询的目的是检索与其“ID”对应的“firstName”（数据库中的first_name）。在执行代码时，我得到了下面的错误。但是，SQL查询在数据库（MySQL DB）中使用时工作正常。

org.springframework.dao. InvalidDataBase ResourceUsageException：无法执行查询; SQL [select first_name from TBL_EMPLOYEES where id=？]; nested exception is org.hibernate.exception.SQLGrammarException：无法执行查询
这里我传入了Id，想根据这个Id取firstName。

下面是我使用的代码片段。

实体

package com.jpademo.demo.entity;

import lombok.*;

import javax.persistence.*;

@Data
@NoArgsConstructor
@Setter
@Getter
@ToString
@Entity
@Table(name = "TBL_EMPLOYEES")
public class EmployeeEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "email", nullable = false, length = 200)
    private String email;

    public EmployeeEntity(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

}

资料库

package com.jpademo.demo.repository;

import com.jpademo.demo.entity.EmployeeEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface EmployeeRepository extends JpaRepository <EmployeeEntity, Long>{
    //Derived Query
    Optional<EmployeeEntity> findByFirstName(String firstName);

    //Native Query
    @Query(nativeQuery=true, value="select * from TBL_EMPLOYEES where id=:id" )
    Optional<EmployeeEntity> findDataById(@Param("id") long id);

    @Query(nativeQuery=true, value="select first_name from TBL_EMPLOYEES where id=:id" )
    Optional<EmployeeEntity> findFirstNameById(@Param("id") long id);

    @Query(nativeQuery=true, value="select email from TBL_EMPLOYEES where id=:id" )
    Optional<EmployeeEntity> findEmailById(@Param("id") long id);
}

调用方法

@Test
    void nativeQuery2(){
        System.out.println("******* TEST ********");
        Optional<EmployeeEntity> response = employeeRepository.findFirstNameById(1L);

        if(response.isEmpty()) {
            System.out.println("********* No Record Found with this Id *********");
        }else{
            System.out.println(response.toString());
        }
    }

下面是堆栈

******* TEST ********
Hibernate: 
    select
        first_name 
    from
        TBL_EMPLOYEES 
    where
        id=?
2023-10-04 11:54:54.964  WARN 1732 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S0022
2023-10-04 11:54:54.964 ERROR 1732 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Column 'id' not found.

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select first_name from TBL_EMPLOYEES where id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Table Structure