JPA原生SQL查询报错：Column 'report_id'不存在的问题求助

问题代码 #

@Query(value = "select re.id,re.external_ref_no ,re.tr_date ,re.event_start_time ,re.event_end_time ,re.status ,\n" +
        "re.event_data_link ,re.created_at ,re.created_by ,re.last_updated_at ,re.last_updated_by  \n" +
        "from  report_master rm join report_event re on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id " +
        "   where re.report_id =?1 and re.external_ref_no =?2 and re.tr_date =?3 and re.service_name=?4", nativeQuery = true)
ReportEventModel findEventByExternalRefNoAndTrDate1(Long reportId, String externalRefNo, String trDate
        , String serviceName);

执行日志及异常信息 #

2022-09-12 14:26:45.029 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([name4_5_] : [VARCHAR]) - [HOURLY-EARLY-PAYIN1]
2022-09-12 14:26:45.033 DEBUG (qtp952590825-41) trkId=123123132123 [org.hibernate.SQL]
select
re.id,
re.external_ref_no ,
re.tr_date ,
re.event_start_time ,
re.event_end_time ,
re.status ,
re.event_data_link ,
re.created_at ,
re.created_by ,
re.last_updated_at ,
re.last_updated_by
from
report_master rm
join
report_event re
on rm.id=re.report_id
join
event_report_mapping_ erm
on re.id =erm.event_id
where
re.report_id =?
and re.external_ref_no =?
and re.tr_date =?
and re.service_name=?
2022-09-12 14:26:45.037 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] 绑定参数[1]为[BIGINT] - [61]
2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] 绑定参数[2]为[VARCHAR] - [8a34c]
2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] 绑定参数[3]为[VARCHAR] - [2021-12-24]
2022-09-12 14:26:45.039 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicBinder] 绑定参数[4]为[VARCHAR] - [BOGS]
2022-09-12 14:26:45.042 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([id] : [BIGINT]) - [50]
2022-09-12 14:26:45.047 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([created_at] : [TIMESTAMP]) - [2022-09-12 14:25:58.0]
2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([created_by] : [VARCHAR]) - [Manish]
2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([event_data_link] : [VARCHAR]) - [HOURLY-EARLY-PAYIN1/2021-12-24/8a34c]
2022-09-12 14:26:45.048 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([external_ref_no] : [VARCHAR]) - [8a34c]
2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([last_updated_at] : [VARCHAR]) - [2022-09-12 14:25:58]
2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([last_updated_by] : [VARCHAR]) - [Manish]
2022-09-12 14:26:45.050 TRACE (qtp952590825-41) trkId=123123132123 [o.h.type.descriptor.sql.BasicExtractor] 提取值([event_end_time] : [TIMESTAMP]) - [null]
2022-09-12 14:26:45.056 WARN (qtp952590825-41) trkId=123123132123 [o.h.engine.jdbc.spi.SqlExceptionHelper] SQL错误: 0, SQLState: S0022
2022-09-12 14:26:45.056 ERROR (qtp952590825-41) trkId=123123132123 [o.h.engine.jdbc.spi.SqlExceptionHelper] 列'report_id'不存在。
2022-09-12 14:26:45.078 ERROR (qtp952590825-41) trkId=123123132123 [c.u.b.server.advisor.ExceptionAdvisor] 发生异常。

org.springframework.dao.InvalidDataAccessResourceUsageException: 无法执行查询; SQL [select re.id,re.external_ref_no ,re.tr_date ,re.event_start_time ,re.event_end_time ,re.status ,
re.event_data_link ,re.created_at ,re.created_by ,re.last_updated_at ,re.last_updated_by
from  report_master rm join report_event re on rm.id=re.report_id join event_report_mapping_ erm on re.id =erm.event_id    where re.report_id =? and re.external_ref_no =? and re.tr_date =? and re.service_name=?]; 嵌套异常为org.hibernate.exception.SQLGrammarException: 无法执行查询
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)

排查及解决方案 #

• 核对数据库表结构：确认report_event表确实存在report_id列，检查列名的拼写、大小写是否与SQL中的写法一致（部分数据库区分大小写，若列名实际为ReportId而SQL写report_id会报错）。
• 修正表名笔误：SQL中event_report_mapping_多了一个下划线，应改为实际表名（比如event_report_mapping），无效表名可能导致关联查询解析异常，间接引发列不存在的误报。
• 简化SQL分步排查：先去掉多余的JOIN语句，仅查询report_event表：

  select re.id from report_event re where re.report_id =?1
  

  若该语句仍报错，说明report_event表确实缺少report_id列；若正常执行，再逐步添加JOIN部分排查问题根源。
• 检查实体类映射：虽然使用原生SQL，但ReportEventModel的字段与数据库列的映射错误也可能引发解析异常，可核对实体类的@Column注解配置是否与表列匹配。

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