Spring Boot JPA原生查询选取指定列报ResultSet列不存在错误如何解决

你当前直接返回Customer实体类的写法不符合JPA的实体映射规则：JPA会自动从查询结果集中匹配实体所有@Column注解映射的字段，你的SQL仅查询了4个指定字段，缺失current_access_token、consent_accepted等实体映射字段，因此触发The column name current_access_token was not found in this ResultSet.报错。

以下是3种常用的实现方案，按推荐优先级排序：

• 方案1：接口投影（实现最简单，适合只读查询场景）
  定义仅包含所需字段getter的接口，JPA会自动生成代理类封装查询结果：

  // 定义投影接口
  public interface CustomerSimpleInfo {
      Long getId();
      String getPhoneNumber();
      String getFirstname();
      String getLastname();
  }
  

  修改Repository方法的返回值类型即可：

  @Query(value = "SELECT c.id, c.phone_number, c.firstname, c.lastname FROM tbl_customers c JOIN tbl_subscriptions s ON c.id = s.customer_id WHERE s.role = 'member' AND s.deleted_at IS NULL", nativeQuery = true) 
  List<CustomerSimpleInfo> findMemberByRole(String role);
  

  使用时直接调用接口对应get方法即可获取字段值。

• 方案2：DTO类投影（适合需要对结果做二次处理的场景）
  先定义包含所需字段的DTO类，提供对应参数的全参构造方法：

  @Data
  @AllArgsConstructor
  public class CustomerDTO {
      private Long id;
      private String phoneNumber;
      private String firstname;
      private String lastname;
  }
  

  在Customer实体类上添加原生查询和结果集映射注解：

  @NamedNativeQuery(
      name = "Customer.findMemberByRole",
      query = "SELECT c.id, c.phone_number, c.firstname, c.lastname FROM tbl_customers c JOIN tbl_subscriptions s ON c.id = s.customer_id WHERE s.role = 'member' AND s.deleted_at IS NULL",
      resultSetMapping = "CustomerDtoMapping"
  )
  @SqlResultSetMapping(
      name = "CustomerDtoMapping",
      classes = @ConstructorResult(
          targetClass = CustomerDTO.class,
          columns = {
              @ColumnResult(name = "id", type = Long.class),
              @ColumnResult(name = "phone_number", type = String.class),
              @ColumnResult(name = "firstname", type = String.class),
              @ColumnResult(name = "lastname", type = String.class)
          }
      )
  )
  @Entity
  @Table(name = "tbl_customers")
  public class Customer implements Serializable {
      // 原有实体代码保持不变
  }
  

  Repository直接声明对应返回值的方法即可：

  List<CustomerDTO> findMemberByRole(String role);
  

• 方案3：强制返回Customer实体（仅推荐临时调试使用）
  若坚持要返回Customer实体，可以在原生SQL中为缺失的映射字段手动补null值并指定和实体注解一致的别名，避免列匹配失败：

  @Query(value = "SELECT c.id, c.phone_number, c.firstname, c.lastname, NULL AS current_access_token, NULL AS consent_accepted FROM tbl_customers c JOIN tbl_subscriptions s ON c.id = s.customer_id WHERE s.role = 'member' AND s.deleted_at IS NULL", nativeQuery = true) 
  List<Customer> findMemberByRole(String role);
  

  注意：该方案返回的实体未查询的字段均为null，后续操作容易触发空指针异常，不建议在生产环境使用。

内容的提问来源于stack exchange，提问作者Hikaru Shindo