"org.hibernate.exception.SQLGrammarException:" while using Native Query to retrieve "specific column Data" by Id using SpringDataJPA

I recently started working with Spring data jpa.

It would be highly appreciable if some one could help me to fix the below issue. Not sure where I am going wrong.

I am trying to write a Native Query using SpringDataJpa in Spring Boot application.
Purpose of the query is to retrieve "firstName" (first_name in DB) corresponding to its "ID". While executing the code I am getting the below error in. However, the SQL query is working fine while using in the DB (MySQL DB).

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

Here, I am passing in Id and want to retrieve firstName based on this Id.

Below are the code snippets which am using.

ENTITY

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

}

REPOSITORY

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

CALLING METHOD

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

Below is the stack

******* 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

>Solution :

Your query will return a string (first_name) but you’re trying to map it to an "EmployeeEntity" as shown in your query

Optional<EmployeeEntity> response = employeeRepository.findFirstNameById(1L);

Instead make it an Optional String

Optional<String> response = employeeRepository.findFirstNameById(1L);