Spring框架Native SQL查询报错：ResultSet未找到group_index列

我在Spring框架的Java Repository中编写了如下Native SQL查询：

@Query(value = "SELECT cr.reviewer_name, cr.message, cr.rating, cr.source, cr.show_status, cr.created_date, " +
    "cr.group_index, string_agg(cr.type, ',') " +
    "FROM customer_review cr " +
    "WHERE cr.type LIKE 'NEWCAR%' AND cr.object_code = :objectCode AND cr.is_deleted NOT IN (true) " +
    "GROUP BY cr.reviewer_name, cr.message, cr.rating, cr.source, cr.show_status, cr.created_date, cr.group_index " +
    "LIMIT :limit OFFSET :offset", nativeQuery = true)
List<Object[]> findByObjectCode(@Param("objectCode") String objectCode, @Param("limit") Integer limit, @Param("offset") Integer offset);

我已确认查询中的表名与数据库表完全一致，但调用时始终返回错误：

The column name group_index was not found in this ResultSet.

我尝试重写查询，手动在PostgreSQL中执行可正常运行，但在Repository接口中调用仍报错。以下是我的表结构：

解决方案 #

1. 给列添加别名（解决下划线命名兼容问题） #

Spring Data JPA处理Native SQL结果集时，会自动将下划线命名的列转为驼峰式（比如group_index被转为groupIndex），导致找不到原列名。给查询中的cr.group_index显式添加别名即可：

@Query(value = "SELECT cr.reviewer_name, cr.message, cr.rating, cr.source, cr.show_status, cr.created_date, " +
    "cr.group_index AS group_index, string_agg(cr.type, ',') AS types " +
    "FROM customer_review cr " +
    "WHERE cr.type LIKE 'NEWCAR%' AND cr.object_code = :objectCode AND cr.is_deleted NOT IN (true) " +
    "GROUP BY cr.reviewer_name, cr.message, cr.rating, cr.source, cr.show_status, cr.created_date, cr.group_index " +
    "LIMIT :limit OFFSET :offset", nativeQuery = true)
List<Object[]> findByObjectCode(@Param("objectCode") String objectCode, @Param("limit") Integer limit, @Param("offset") Integer offset);

2. 检查实体类字段映射（若用实体类接收结果） #

如果后续打算用实体类替换Object[]，需确保实体类中对应字段的@Column注解明确指定列名：

@Column(name = "group_index")
private Integer groupIndex;

3. 全局关闭下划线转驼峰（可选） #

若不想逐个添加别名，可在配置文件中关闭Spring的下划线自动转驼峰功能（注意会影响全局字段映射）：

• application.properties

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

• application.yml

spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

内容的提问来源于stack exchange，提问作者Banta Solagratia