Hibernate中join fetch结合group by丢失子记录的问题求助

使用包含join fetch的HQL查询时遇到矛盾情况：

• 按父实体ID执行group by分组后，无法获取任何子记录；
• 不使用group by时，能拿到所有子记录，但父实体出现重复。

需要找到在使用group by的同时，仍能完整获取所有子记录的解决方案。

编辑1：查询代码 #

无Group by的查询 #

select distinct itinerary from Itinerary itinerary 
   join fetch itinerary.customer customer 
   left join fetch customer.rewardProgram 
   left join fetch customer.customerContactDetails 
   left join fetch customer.customerPreferredAirlines 
   left join fetch itinerary.couponHistory 
   left join fetch itinerary.rewardUsageData 
   left join fetch itinerary.itineraryCancellationDetails 
   left join fetch itinerary.insuranceDetails 
   left join fetch itinerary.customerTravelRequest  
   left join fetch itinerary.flightSegments flightSegments 
   left join fetch itinerary.employeeEmployerDetails 
   left join fetch itinerary.customerGSTClaimDetails 
   left join fetch itinerary.itineraryExtension 
   left join fetch itinerary.itineraryGSTDetails 
   left join fetch itinerary.itnerariesTravlersAssosciation assosciation 
   left join fetch assosciation.traveler
   where (itinerary.status IN ('BOOK','CONFIRM')) 
     AND (itinerary.id,flightSegments.arrivalTime) IN 
       (select flightSegments.itinerary,flightSegments.arrivalTime 
          from flightSegments where flightSegments.arrivalTime >= ?2) 
     AND customer.id = ?1 AND itinerary.companyId = :companyId 
  ORDER BY itinerary.bookingDate DESC

带Group By的查询 #

select distinct itinerary from Itinerary itinerary 
   join fetch itinerary.customer customer 
   left join fetch customer.rewardProgram 
   left join fetch customer.customerContactDetails 
   left join fetch customer.customerPreferredAirlines 
   left join fetch itinerary.couponHistory 
   left join fetch itinerary.rewardUsageData 
   left join fetch itinerary.itineraryCancellationDetails 
   left join fetch itinerary.insuranceDetails 
   left join fetch itinerary.customerTravelRequest  
   left join fetch itinerary.flightSegments flightSegments 
   left join fetch itinerary.employeeEmployerDetails 
   left join fetch itinerary.customerGSTClaimDetails 
   left join fetch itinerary.itineraryExtension 
   left join fetch itinerary.itineraryGSTDetails 
   left join fetch itinerary.itnerariesTravlersAssosciation assosciation 
   left join fetch assosciation.traveler 
   where (itinerary.status IN ('BOOK','CONFIRM')) 
     AND (itinerary.id,flightSegments.arrivalTime) IN 
       (select flightSegments.itinerary,flightSegments.arrivalTime 
          from flightSegments where flightSegments.arrivalTime >= ?2) 
     AND  customer.id = ?1 AND itinerary.companyId = :companyId 
   group by flightSegments.itinerary.id 
   ORDER BY itinerary.bookingDate DESC

核心原因 #

join fetch的作用是立即加载关联的子实体，但group by会对查询结果做聚合操作，导致Hibernate无法将聚合后的结果正确映射到包含子实体的父实体对象中，最终丢失子记录。而不加group by时，每条子实体记录都会对应生成一条父实体行，因此出现重复。

可行解决方法 #

1. 用DISTINCT_ROOT_ENTITY替代GROUP BY（推荐）

你已经在无group by的查询中用了select distinct，但仍有重复父实体，是因为Hibernate默认的去重逻辑无法处理关联集合带来的重复行。可以通过Hibernate的结果转换器强制对父实体去重：

Query<Itinerary> query = session.createQuery(hql, Itinerary.class);
query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List<Itinerary> result = query.list();

这种方式既能保留所有子记录，又能彻底去掉重复的父实体，完全满足需求。

2. 先过滤主实体，再延迟加载子实体

如果不需要立即加载所有子实体，可以去掉查询中的join fetch，先通过子查询过滤出符合条件的Itinerary，之后按需加载子实体：

select distinct itinerary from Itinerary itinerary 
   join itinerary.customer customer 
   where (itinerary.status IN ('BOOK','CONFIRM')) 
     AND EXISTS (
         select 1 from FlightSegments fs 
         where fs.itinerary.id = itinerary.id 
         and fs.arrivalTime >= ?2
     )
     AND customer.id = ?1 AND itinerary.companyId = :companyId 
ORDER BY itinerary.bookingDate DESC

后续访问子实体时，Hibernate会自动触发懒加载（需确保会话未关闭）。如果要优化懒加载性能，可以给实体的集合字段加上@BatchSize注解。

3. 调整子查询逻辑，再关联fetch

把FlightSegments的过滤逻辑改成子查询筛选Itinerary ID，再关联fetch所有子实体，最后结合去重：

select distinct itinerary from Itinerary itinerary 
   join fetch itinerary.customer customer 
   left join fetch customer.rewardProgram 
   -- 保留其他所有join fetch语句
   left join fetch itinerary.flightSegments flightSegments 
   where (itinerary.status IN ('BOOK','CONFIRM')) 
     AND itinerary.id IN (
         select fs.itinerary.id from FlightSegments fs 
         where fs.arrivalTime >= ?2
     )
     AND customer.id = ?1 AND itinerary.companyId = :companyId 
ORDER BY itinerary.bookingDate DESC

同样搭配DISTINCT_ROOT_ENTITY去重，既能保证FlightSegments符合条件，又能完整加载所有子记录，同时避免父实体重复。

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