Spring Data JPA原生查询指定字段映射POJO报错解决方案咨询

这个问题的核心原因很明确：你用的BillingProduct是标注了@Entity的JPA实体类，Hibernate默认期望查询结果能填充该实体的所有映射字段。当你的原生查询没有返回aggregator_name（对应实体里的aggregatorName）时，Hibernate找不到对应的列值，就会抛出你看到的Column 'aggregator_name' not found异常。

下面给你三种可行的解决方案，按推荐程度排序：

1. 使用专用DTO（数据传输对象）映射查询结果 #

这是最规范的做法：创建一个只包含你需要查询字段的DTO类，让查询直接返回这个DTO的列表，避免用全量实体类来承载部分数据。

步骤： #

首先定义DTO类，包含需要的字段和对应的构造函数（构造函数参数顺序要和查询的字段顺序完全一致）：

public class BillingProductStopInfoDTO implements Serializable {
    private static final long serialVersionUID = 1L;
    
    private int id;
    private String accountId;
    private int operatorId;
    private String stopAggregator;
    private boolean isFreeMsgOnUnsubscribe;
    private boolean unsubscribeCallToSubEngineRequired;
    private String unsubscribeByAccountNotifyUrl;
    private boolean isUnsubscribeByAccountForwardingEnabled;
    private String productType;
    private String operatorBillingType;
    private String aggregatorUsername;
    private String aggregatorPassword;

    // 构造函数参数顺序必须和查询语句的字段顺序匹配
    public BillingProductStopInfoDTO(int id, String accountId, int operatorId, String stopAggregator,
                                     boolean isFreeMsgOnUnsubscribe, boolean unsubscribeCallToSubEngineRequired,
                                     String unsubscribeByAccountNotifyUrl, boolean isUnsubscribeByAccountForwardingEnabled,
                                     String productType, String operatorBillingType, String aggregatorUsername,
                                     String aggregatorPassword) {
        this.id = id;
        this.accountId = accountId;
        this.operatorId = operatorId;
        this.stopAggregator = stopAggregator;
        this.isFreeMsgOnUnsubscribe = isFreeMsgOnUnsubscribe;
        this.unsubscribeCallToSubEngineRequired = unsubscribeCallToSubEngineRequired;
        this.unsubscribeByAccountNotifyUrl = unsubscribeByAccountNotifyUrl;
        this.isUnsubscribeByAccountForwardingEnabled = isUnsubscribeByAccountForwardingEnabled;
        this.productType = productType;
        this.operatorBillingType = operatorBillingType;
        this.aggregatorUsername = aggregatorUsername;
        this.aggregatorPassword = aggregatorPassword;
    }

    // 按需添加getter方法
    public int getId() { return id; }
    // ... 其他getter
}

然后修改Repository方法，返回这个DTO的列表：

@Repository 
public interface BillingProductsRepo extends JpaRepository<BillingProduct, Integer> { 
    @Query(value = "select id, account_id, operator_id,stop_aggregator," + 
                   "is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url," + 
                   "is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, " + 
                   "aggregator_password from billing_products where id=:id", 
           nativeQuery = true) 
    List<BillingProductStopInfoDTO> loadStopInfo(@Param("id") String productId); 
}

2. 使用Spring Data Projections（投影接口） #

如果你觉得写DTO的构造函数太繁琐，可以用Spring Data提供的投影功能，定义一个接口来指定需要的字段：

步骤： #

先定义投影接口，方法名要和查询字段的别名（或者JPA的驼峰转下划线规则）匹配：

public interface BillingProductStopInfoProjection {
    int getId();
    String getAccountId(); // 对应查询里的account_id（JPA自动处理驼峰转下划线）
    int getOperatorId();
    String getStopAggregator();
    boolean isFreeMsgOnUnsubscribe();
    boolean isUnsubscribeCallToSubEngineRequired();
    String getUnsubscribeByAccountNotifyUrl();
    boolean isUnsubscribeByAccountForwardingEnabled();
    String getProductType();
    String getOperatorBillingType();
    String getAggregatorUsername();
    String getAggregatorPassword();
}

修改Repository方法，返回这个投影接口的列表，注意给查询字段加别名（如果需要严格匹配的话）：

@Repository 
public interface BillingProductsRepo extends JpaRepository<BillingProduct, Integer> { 
    @Query(value = "select id, account_id as accountId, operator_id as operatorId,stop_aggregator as stopAggregator," + 
                   "is_free_msg_on_unsubscribe as freeMsgOnUnsubscribe,unsubscribe_call_to_sub_engine_required as unsubscribeCallToSubEngineRequired," + 
                   "unsubscribe_by_account_notify_url as unsubscribeByAccountNotifyUrl," + 
                   "is_unsubscribe_by_account_forwarding_enabled as unsubscribeByAccountForwardingEnabled," + 
                   "product_type as productType,operator_billing_type as operatorBillingType, aggregator_username as aggregatorUsername, " + 
                   "aggregator_password as aggregatorPassword from billing_products where id=:id", 
           nativeQuery = true) 
    List<BillingProductStopInfoProjection> loadStopInfo(@Param("id") String productId); 
}

这种方式不需要写实体类的构造函数，非常简洁，适合简单的查询场景。

3. 临时方案：给缺失字段添加默认值 #

如果你只是想快速解决问题，不想改动太多代码，可以在原生查询中给缺失的字段（比如aggregator_name）指定一个默认值（比如NULL或者空字符串），让Hibernate能找到对应的列：

修改查询语句：

@Query(value = "select id, account_id, operator_id,stop_aggregator," + 
               "is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url," + 
               "is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, " + 
               "aggregator_password, NULL as aggregator_name " + // 新增这一行，给缺失字段加默认值
               "from billing_products where id=:id", 
       nativeQuery = true) 
List<BillingProduct> loadStopInfo(@Param("id") String productId); 

这种方法能快速解决异常，但不推荐长期使用：因为实体类设计是对应全表的，用它来承载部分数据会导致代码语义不清，而且如果后续实体新增字段，你又要修改查询语句添加新的默认值。

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