Spring Data JPA @Query nativeQuery org.hibernate.exception.SQLGrammarException: could not execute query 錯誤

Spring Data JPA在Repository方法使用@Query(nativeQuery = true)以原生SQL(native query)查詢出現org.hibernate.exception.SQLGrammarException: could not execute query錯誤。

範例環境：

• Spring Boot 2.3.2
• Spring Data JPA

Entity類Employee。

Employee

package com.abc.demo.entity;

import javax.persistence.Entity;
import javax.persistence.Id;
import java.io.Serializable;

@Entity
public class Employee implements Serializable {
    private static final Long serialVersionUID = 1L;

    @Id
    private long id;

    private String name;

    private int age;
    
    // getters and setters

}

EmployeeRepository中findByName()使用@Query(nativeQuery = true)以原生SQL
select id, name from EMPLOYEE where name = :name 查詢。

EmployeeRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
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;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "select id, name from EMPLOYEE where name = :name",
            nativeQuery = true)
    List<Employee> findByName(@Param("name") String name);

}

則執行

EmployeeRepository.findByName()出現錯誤。錯誤原因為Spring Data JPA以原生SQL查詢的欄位必須符合entity的全部欄位，而上面的SQL少了age欄位所以出現錯誤。把SQL改成
select id, name, age from EMPLOYEE where name = :name 或
select * from EMPLOYEE where name = :name 查詢全部欄位即可解決。

或如果只要查詢部分欄位，則多筆結果改回傳List<Map<String, Object>。

EmployeeRepository

package com.abc.demo.repository;

import com.abc.demo.entity.Employee;
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.Map;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {

    @Query(value = "select id, name from EMPLOYEE where name = :name",
            nativeQuery = true)
    List<Map<String, Object>> findByName(@Param("name") String name);

}

List代表每一列(row)結果，Map<String, Object>的key為欄位(column)，value為該欄的值。

把查詢結果的欄位與值全部印出。

List<Map<String, Object>> rowList = employeeRepository.findByName(name);
for (Map<String, Object> row : rowList) {
    for (Map.Entry<String, Object> entry : row.entrySet()) {
        String column = entry.getKey();
        Object value = entry.getValue();
        System.out.println(column + "=" + value);
    }
}

// lambda
List<Map<String, Object>> rowList = employeeRepository.findByName(name);
rowList.forEach(row -> row.forEach((column, value) -> {
    System.out.println(column + "=" + value);
}));

• Spring Data JPA org.springframework.orm.jpa.JpaSystemException: identifier of an instance of Entity was altered from x to y錯誤
• Spring JDBC SQLException Invalid column name錯誤
• Spring Data JPA @Query nativeQuery ORA-03001: unimplemented feature 錯誤
• Spring Data JPA org.hibernate.AnnotationException: No identifier specified for entity 錯誤原因
• Spring Data JPA SQLSyntaxErrorException: ORA-00904: 無效的 ID invalid identifier