Spring Boot中基于子表的@ManyToMany关联分页实现问题

问题根源分析 #

1. 分页逻辑错误：原查询以ForecastModel为主体，Pageable会作用在Forecast实例上（而非关联的Item），因此只会返回符合条件的单个Forecast，并加载其所有关联Item，无法实现Item的分页。
2. 异常原因：countProjection = "f.item"指定的是集合属性，Hibernate不支持直接对集合路径进行计数投影，因此抛出UnsupportedOperationException。

分步解决 #

1. 定位目标Forecast #

先通过条件查询找到符合要求的单个Forecast：

@Repository
public interface ForecastRepository extends JpaRepository<ForecastModel, UUID> {
    @Query("SELECT f FROM ForecastModel f WHERE f.countryId.id = :countryId AND f.status = '1' AND f.chainId.id = (SELECT DISTINCT am.chain.id FROM AccountModel am WHERE am.vendorId = :vendorId and (am.accountId = :accountId OR am.vendorAccountId = :accountId OR am.beesAccountId = :accountId))")
    Optional<ForecastModel> findTargetForecast(@Param("vendorId") UUID vendorId, @Param("countryId") UUID countryId, @Param("accountId") String accountId);
}

2. 分页查询该Forecast下的Item #

由于ItemModel未映射反向关联，需通过中间表forecast_item关联Forecast和Item，同时实现Item的分页与过滤：

@Repository
public interface ItemRepository extends JpaRepository<ItemModel, UUID> {
    @Query(value = """
            SELECT i FROM ItemModel i
            JOIN forecast_item fi ON fi.item_id = i.id
            JOIN FETCH i.clientTypeId ct
            JOIN FETCH i.itemProductId ip
            JOIN FETCH i.itemRegionId ir
            WHERE fi.forecast_id = :forecastId
            AND i.month = :#{#params.period}
            AND (CAST(:#{#params.search} as text) IS NULL OR UPPER(ip.codeSku) LIKE %:#{#params.search}%
                OR UPPER(ip.description) LIKE %:#{#params.search}%
                OR UPPER(CAST(ip.ean as text)) LIKE %:#{#params.search}%)
            AND (CAST(:#{#params.type} as text) IS NULL OR ct.name = :#{#params.type})
            AND (CAST(:#{#params.region} as text) IS NULL OR ir.nameTarget = :#{#params.region})
            """,
            countQuery = """
            SELECT COUNT(DISTINCT i.id) FROM ItemModel i
            JOIN forecast_item fi ON fi.item_id = i.id
            JOIN i.clientTypeId ct
            JOIN i.itemProductId ip
            JOIN i.itemRegionId ir
            WHERE fi.forecast_id = :forecastId
            AND i.month = :#{#params.period}
            AND (CAST(:#{#params.search} as text) IS NULL OR UPPER(ip.codeSku) LIKE %:#{#params.search}%
                OR UPPER(ip.description) LIKE %:#{#params.search}%
                OR UPPER(CAST(ip.ean as text)) LIKE %:#{#params.search}%)
            AND (CAST(:#{#params.type} as text) IS NULL OR ct.name = :#{#params.type})
            AND (CAST(:#{#params.region} as text) IS NULL OR ir.nameTarget = :#{#params.region})
            """)
    Page<ItemModel> findItemsByForecastId(@Param("forecastId") UUID forecastId, @Param("params") GetForecastListRequestDTO params, Pageable pageable);
}

3. 业务层封装结果 #

将Forecast信息与分页后的Item封装成DTO返回：

// 定义返回DTO
public class ForecastWithItemsDTO {
    private ForecastModel forecast;
    private Page<ItemModel> items;

    public ForecastWithItemsDTO(ForecastModel forecast, Page<ItemModel> items) {
        this.forecast = forecast;
        this.items = items;
    }

    // Getters & Setters
}

// 业务方法
@Service
public class ForecastService {
    private final ForecastRepository forecastRepository;
    private final ItemRepository itemRepository;

    public ForecastService(ForecastRepository forecastRepository, ItemRepository itemRepository) {
        this.forecastRepository = forecastRepository;
        this.itemRepository = itemRepository;
    }

    public ForecastWithItemsDTO getForecastWithItems(UUID vendorId, UUID countryId, String accountId, GetForecastListRequestDTO params, Pageable pageable) {
        ForecastModel forecast = forecastRepository.findTargetForecast(vendorId, countryId, accountId)
                .orElseThrow(() -> new RuntimeException("目标Forecast不存在"));
        Page<ItemModel> items = itemRepository.findItemsByForecastId(forecast.getId(), params, pageable);
        return new ForecastWithItemsDTO(forecast, items);
    }
}

关键说明 #

• 以ItemModel为查询主体，Pageable会直接作用在Item上，实现Item的分页。
• 计数查询使用COUNT(DISTINCT i.id)避免因关联导致的重复计数。
• 利用中间表forecast_item关联Forecast与Item，绕过ItemModel缺失反向关联的问题。

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