Spring JPA多表原生查询报错cliententi0_.name列不存在解决方案

错误根本原因 #

1. ClientEntity绑定了payments表，但你在实体类中定义了name、username等不属于payments表的字段并加了@Column注解，Hibernate操作该实体时会默认去payments表查询这些不存在的字段，触发报错。
2. Repository的查询方法错误添加了@Modifying注解，该注解仅适用于增删改操作，查询方法使用会导致结果解析逻辑异常。
3. 原生SQL的返回列别名和你定义的Client投影接口的getter方法不匹配，Hibernate无法将查询结果映射到投影接口实例。

具体修复步骤 #

1. 修正Repository配置与SQL语句 #

首先移除查询方法上多余的@Modifying、@Transactional注解，同时调整SQL列别名和投影接口的getter完全匹配：

@Repository
public interface ClientRepository extends JpaRepository<ClientEntity, Long> {
    @Query(value = "SELECT pp.id, " +
               "pp.created_dt AS created_dt, " +
               "au.username AS username, " +
               "ss.name AS name, " +
               "pp.amount AS amount, " +
               "REPLACE(pp.status, 'SUCCESS', 'Success') AS status, " +
               "pp.account AS account, " +
               "pp.external_id AS external_id, " +
               "COALESCE(pp.external_status, 'DN') AS external_status " +
               "FROM payments AS pp " +
               "INNER JOIN user AS au ON au.id = pp.creator_id " +
               "INNER JOIN services AS ss ON ss.id = pp.service_id " +
               "WHERE pp.created_dt >= '2021-09-28' " +
               "AND ss.name = 'Faberlic' AND pp.status = 'SUCCESS'", nativeQuery = true)
    List<Client> getAllByRegDate();
}

注：你之前SQL里的日期格式转换、时间字段如果需要保留，要对应在Client接口和DTO里增加对应的getter和属性，否则不需要查询冗余字段

2. 修正ClientEntity的字段配置 #

删除ClientEntity中不属于payments表的字段，避免Hibernate查询实体时报错：

@Getter
@NoArgsConstructor
@AllArgsConstructor
@Immutable
@Entity
@Table(name = "payments", schema = "public")
public class ClientEntity {
    @Id
    private Long id;
    @Column(name = "created_dt")
    private LocalDate created_dt;
    @Column(name = "amount")
    private int amount;
    @Column(name = "status")
    private String status;
    @Column(name = "account")
    private String account;
    @Column(name = "external_id")
    private String external_id;
    @Column(name = "external_status")
    private String external_status;
    // 移除了不存在的username、name字段的@Column配置
}

如果你不需要用到JpaRepository自带的findById、save等针对ClientEntity的CRUD方法，也可以直接把Repository的父类改成JpaRepository<Object, Long>，避免实体类校验的问题。

3. 验证映射逻辑 #

调整后调用getAllByRegDate获取Client列表，直接传入ClientDto的构造方法转换即可，后续导出CSV时直接读取ClientDto的属性拼接输出即可。

可选替代方案 #

如果觉得接口投影匹配麻烦，可以直接用List<Object[]>接收原生SQL的查询结果，然后手动遍历数组封装到ClientDto中，不需要依赖Hibernate的投影映射逻辑，兼容性更高。

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