最近在学习spring data jpa ,发现spring data jpa虽然也支持自定义sql的本地查询,但是对超级长的sql支持的不是很好,特别是自己写的含有from 重命名的sql,虽然sql在数据库可以执行,但编译的时候却不通过,比如以下这条长sql:
select f.str_FlightNumber as flightNumber,f.str_ExecDate as execDate,f.i_SecurityCheckStatus as securityCheckStatus,f.i_BoardingStatus as boardingStatus,
count(distinct p.i_Id) as departureNumber,//统计离港人数
(select count(1) from t_passengerinfo where i_BoardingStatus = 2 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as checkedNumber,//本次航班已安检人数
(select count(1) from t_passengerinfo where i_CheckStatus = 1 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as boardingNumber//本次航班已登机人数
from t_flightplan f, t_passengerinfo p
where f.str_FlightNumber = p.str_Flight and f.str_ExecDate = p.str_Date and f.str_BelongAirportThreeCode = p.str_Group
group by f.i_Id
这是查询航班表,乘客表,统计本次航班已登机的乘客数,统计本次航班已安检的乘客数,统计本次航班离港人数,然后按航班id分组
这条sql在数据库里执行是没有问题的,但是被spring data jpa 解析的时候就各种毛病,反正就是编译失败,我查找了N篇博客都没找到问题所在,难道是SQL太长了它看不懂???
然后我在数据库创建了一个视图:
create view v_flightdynamic as
select f.str_FlightNumber as flightNumber,f.str_ExecDate as execDate,f.i_SecurityCheckStatus as securityCheckStatus,f.i_BoardingStatus as boardingStatus,
count(distinct p.i_Id) as departureNumber,
(select count(1) from t_passengerinfo where i_BoardingStatus = 2 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as checkedNumber,
(select count(1) from t_passengerinfo where i_CheckStatus = 1 and str_FlightNumber = str_Flight and str_ExecDate = str_Date and str_BelongAirportThreeCode = str_Group) as boardingNumber
from t_flightplan f, t_passengerinfo p
where f.str_FlightNumber = p.str_Flight and f.str_ExecDate = p.str_Date and f.str_BelongAirportThreeCode = p.str_Group
group by f.i_Id
在我原本的sql位置:
@Query(value = "select * from v_flightdynamic",nativeQuery = true)
Page<Object[]> getFlightDynamics(Pageable pageable);
emm 完美通过 美滋滋。