PostgreSQL下JPA原生查询排除主键报错，求实体类调整方案

问题原因分析 #

你碰到的这个org.postgresql.util.PSQLException: The column name id was not found in this ResultSet错误，核心原因在于：你的PublisherInfoResponseEntity被标记为@Entity，且包含带@Id注解的id字段。JPA在映射原生查询结果到实体类时，默认会要求结果集中必须存在主键对应的列，但你的SQL查询里并没有选择id列，因此触发了这个异常。

解决方案1：将实体类改为DTO（推荐方案） #

既然这个类是用来接收查询结果的响应实体，而非需要持久化的数据库实体，最合理的做法是把它改成普通DTO类，移除JPA实体相关注解，再调整结果集映射规则。

步骤1：重构PublisherInfoResponseEntity类

移除@Entity、@Table、@Id注解，并补充需要接收的annual表字段，同时添加全参构造函数用于结果映射：

public class PublisherInfoResponseEntity { 
    private String publisher_name; 
    private String contact_name; 
    private String contact_phone; 
    private String contact_email; 
    private String managed_services; 
    // 补充接收annual表的字段
    private Integer customers;
    private Integer employees;
    private BigDecimal revenue;

    // 全参构造函数（用于结果集映射）
    public PublisherInfoResponseEntity(String publisher_name, String contact_name, String contact_phone, 
                                       String contact_email, String managed_services, Integer customers, 
                                       Integer employees, BigDecimal revenue) {
        this.publisher_name = publisher_name;
        this.contact_name = contact_name;
        this.contact_phone = contact_phone;
        this.contact_email = contact_email;
        this.managed_services = managed_services;
        this.customers = customers;
        this.employees = employees;
        this.revenue = revenue;
    }

    // 无参构造函数（按需添加）
    public PublisherInfoResponseEntity() {}

    // getters and setters
}

步骤2：调整@NamedNativeQuery和@SqlResultSetMapping

把原来的@EntityResult替换为@ConstructorResult，指定DTO的构造函数与查询列的映射关系，同时修复SQL语句末尾的多余逗号：

@NamedNativeQueries(value = { 
    @NamedNativeQuery( 
        name = "getPublisherInfoList", 
        query = "SELECT publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,publisher.managed_services,\n" + 
                "annual.customers,annual.employees,annual.revenue\n" + 
                "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" + 
                "ON publisher.publisher_name=annual.publisher_name", 
        resultSetMapping = "PublisherInfoResponseMappings" 
    ), 
    @NamedNativeQuery( 
        name = "getPublisherByName", 
        query = "SELECT publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,\n" + 
                "publisher.managed_services,annual.customers,annual.employees,annual.revenue\n" + 
                "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" + 
                "ON publisher.publisher_name=annual.publisher_name where publisher.publisher_name= :publisherName", 
        resultSetMapping = "PublisherInfoResponseMappings" 
    ) 
}) 
@SqlResultSetMapping(name = "PublisherInfoResponseMappings", 
    classes = { 
        @ConstructorResult(
            targetClass = PublisherInfoResponseEntity.class,
            columns = {
                @ColumnResult(name = "publisher_name", type = String.class),
                @ColumnResult(name = "contact_name", type = String.class),
                @ColumnResult(name = "contact_email", type = String.class),
                @ColumnResult(name = "contact_phone", type = String.class),
                @ColumnResult(name = "managed_services", type = String.class),
                @ColumnResult(name = "customers", type = Integer.class),
                @ColumnResult(name = "employees", type = Integer.class),
                @ColumnResult(name = "revenue", type = BigDecimal.class)
            }
        )
    }
)
// 注意：这个映射注解可以放在任意一个JPA实体类上，不需要绑定到DTO

解决方案2：保留实体类的妥协方案（不推荐） #

如果因为特殊需求必须让PublisherInfoResponseEntity作为JPA实体存在，那你必须在SQL查询中包含id列，即使你不需要它：

SELECT publisher.id, publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,publisher.managed_services,
annual.customers,annual.employees,annual.revenue
FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual
ON publisher.publisher_name=annual.publisher_name

同时在@FieldResult中添加@FieldResult(name = "id", column = "id")，让JPA能找到主键列。但这种方法会返回你不需要的id字段，仅作为特殊场景下的妥协方案。

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