hibernate 休眠连接获取不给出所有子记录

因为我将HQL查询与JOIN FETCH一起使用。当我使用带有父ID的GROUP BY时，我不会获得单个子记录。如果我没有使用GROUP BY，那么我将获得所有的儿童记录。但如果我不使用GROUP BY，我会得到重复的家长记录。你能帮我找一下GROUP BY的所有儿童档案吗？

编辑1
不带分组依据的查询

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

您可能希望了解什么是FETCH JOIN：它是一个SQL JOIN(没有FETCH)，但JPA将忽略其查询中的结果。然而，所有这些FETCH连接都将创建一个笛卡尔积，这意味着itinerary是复制的。如果您使用distinct和group by，您将截断该乘积的结果，而不包括所有子记录。
因此，我建议您使用第一个查询，并通过将它们放入LinkedHashSet中来过滤重复项，这样您就可以保持查询返回它们的顺序。当然，这需要正确实现equals()和hashCode()，我想您已经知道了。
示例：

List<Itinerary> queryResult = ...;

Set<Itinerary> uniqueItineraries = new LinkedHashSet<>();
for( Itinerary itinerary : queryResult ) {
  uniqueItineraries.add(itinerary);
}

List<Itinerary> filteredList = new ArrayList<>(uniqueItineraries);

在任何情况下，这个查询似乎一次加载了大量数据，所以您可能需要重新考虑您的选择，例如，为什么不加载没有孩子的行程，并在必要时分别获取孩子。我假设这些数据中的大多数无论如何都不会显示在更大的列表中。

hibernate 休眠连接获取不给出所有子记录

1条答案

相关问题

热门标签

最新问答