如何在仅需count()时避免Hibernate加载带反向引用的集合

背景需求 #

需要统计集合元素但不初始化集合；由于Hibernate Search索引时每个Price需从Article获取数据，必须保留反向引用；使用@JoinColumn可避免生成第三张关联表，仅保留Article和Price两张表，但当前调用article.getPrices().size()时会加载整个Price集合，还会连带加载Price关联的Provider、多次加载User（Provider也有created/updated两个User属性）。若不使用@JoinColumn，会生成第三张关联表，此时调用size()会执行COUNT查询而非加载集合，不符合表结构要求。

当前实体配置 #

@Entity @Indexed
class Article {
    
    String name;

    @JsonManagedReference   
    @OneToMany 
    @LazyCollection(org.hibernate.annotations.LazyCollectionOption.EXTRA)
    @JoinColumn(name = "article_id")
    @Field(bridge = @FieldBridge(impl = PriceBridge.class))
    Set<Price> prices;

    private User created;
    private User updated;
}

@Entity @Indexed
class Price {

    @Reference @JsonBackReference
    @ManyToOne(fetch=FetchType.LAZY)
    @Field(bridge = @FieldBridge(impl = PriceArticleBridge.class))
    private Article article;

    @ManyToOne private Provider provider;

    @ManyToOne private CustomCurrency currency;

    private BigDecimal amount;

    private User created;
    private User updated;
}

问题现象对比 #

• 无@JoinColumn时的SQL（仅COUNT查询）：

select count(prices_id) from article_prices where article_id =?

• 有@JoinColumn时的SQL（关联11张表，加载全量数据）：

SELECT 
    prices0_.article_id AS article14_18_0_,
    prices0_.id AS id1_18_0_,
    prices0_.id AS id1_18_1_,
    prices0_.date_created AS date_cre2_18_1_,
    prices0_.date_last_update AS date_las3_18_1_,
    prices0_.created_by_id AS created12_18_1_,
    prices0_.last_update_by_id AS last_up13_18_1_,
    prices0_.amount AS amount4_18_1_,
    prices0_.article_id AS article14_18_1_,
    prices0_.availability AS availabi5_18_1_,
    prices0_.currency_id AS currenc15_18_1_,
    prices0_.description AS descript6_18_1_,
    prices0_.local_date_time_valid_from AS local_da7_18_1_,
    prices0_.local_date_time_valid_to AS local_da8_18_1_,
    prices0_.price_url AS price_ur9_18_1_,
    prices0_.provider_id AS provide16_18_1_,
    prices0_.purchase_url AS purchas10_18_1_,
    prices0_.zone_id_valid_from_to AS zone_id11_18_1_,
    user1_.id AS id1_27_2_,
    user1_.date_created AS date_cre2_27_2_,
    user1_.date_last_update AS date_las3_27_2_,
    user1_.created_by_id AS created13_27_2_,
    user1_.last_update_by_id AS last_up14_27_2_,
    user1_.display_name AS display_4_27_2_,
    user1_.failed_login_attempts AS failed_l5_27_2_,
    user1_.full_name AS full_nam6_27_2_,
    user1_.note AS note7_27_2_,
    user1_.object_status AS object_s8_27_2_,
    user1_.password AS password9_27_2_,
    user1_.phone AS phone10_27_2_,
    user1_.time_zone_id AS time_zo11_27_2_,
    user1_.username AS usernam12_27_2_,
    user2_.id AS id1_27_3_,
    user2_.date_created AS date_cre2_27_3_,
    user2_.date_last_update AS date_las3_27_3_,
    user2_.created_by_id AS created13_27_3_,
    user2_.last_update_by_id AS last_up14_27_3_,
    user2_.display_name AS display_4_27_3_,
    user2_.failed_login_attempts AS failed_l5_27_3_,
    user2_.full_name AS full_nam6_27_3_,
    user2_.note AS note7_27_3_,
    user2_.object_status AS object_s8_27_3_,
    user2_.password AS password9_27_3_,
    user2_.phone AS phone10_27_3_,
    user2_.time_zone_id AS time_zo11_27_3_,
    user2_.username AS usernam12_27_3_,
    user3_.id AS id1_27_4_,
    user3_.date_created AS date_cre2_27_4_,
    user3_.date_last_update AS date_las3_27_4_,
    user3_.created_by_id AS created13_27_4_,
    user3_.last_update_by_id AS last_up14_27_4_,
    user3_.display_name AS display_4_27_4_,
    user3_.failed_login_attempts AS failed_l5_27_4_,
    user3_.full_name AS full_nam6_27_4_,
    user3_.note AS note7_27_4_,
    user3_.object_status AS object_s8_27_4_,
    user3_.password AS password9_27_4_,
    user3_.phone AS phone10_27_4_,
    user3_.time_zone_id AS time_zo11_27_4_,
    user3_.username AS usernam12_27_4_,
    user4_.id AS id1_27_5_,
    user4_.date_created AS date_cre2_27_5_,
    user4_.date_last_update AS date_las3_27_5_,
    user4_.created_by_id AS created13_27_5_,
    user4_.last_update_by_id AS last_up14_27_5_,
    user4_.display_name AS display_4_27_5_,
    user4_.failed_login_attempts AS failed_l5_27_5_,
    user4_.full_name AS full_nam6_27_5_,
    user4_.note AS note7_27_5_,
    user4_.object_status AS object_s8_27_5_,
    user4_.password AS password9_27_5_,
    user4_.phone AS phone10_27_5_,
    user4_.time_zone_id AS time_zo11_27_5_,
    user4_.username AS usernam12_27_5_,
    customcurr5_.id AS id1_9_6_,
    customcurr5_.date_created AS date_cre2_9_6_,
    customcurr5_.date_last_update AS date_las3_9_6_,
    customcurr5_.created_by_id AS created10_9_6_,
    customcurr5_.last_update_by_id AS last_up11_9_6_,
    customcurr5_.is_default AS is_defau4_9_6_,
    customcurr5_.is_reference AS is_refer5_9_6_,
    customcurr5_.java_currency AS java_cur6_9_6_,
    customcurr5_.object_status AS object_s7_9_6_,
    customcurr5_.sign_position AS sign_pos8_9_6_,
    customcurr5_.val AS val9_9_6_,
    user6_.id AS id1_27_7_,
    user6_.date_created AS date_cre2_27_7_,
    user6_.date_last_update AS date_las3_27_7_,
    user6_.created_by_id AS created13_27_7_,
    user6_.last_update_by_id AS last_up14_27_7_,
    user6_.display_name AS display_4_27_7_,
    user6_.failed_login_attempts AS failed_l5_27_7_,
    user6_.full_name AS full_nam6_27_7_,
    user6_.note AS note7_27_7_,
    user6_.object_status AS object_s8_27_7_,
    user6_.password AS password9_27_7_,
    user6_.phone AS phone10_27_7_,
    user6_.time_zone_id AS time_zo11_27_7_,
    user6_.username AS usernam12_27_7_,
    user7_.id AS id1_27_8_,
    user7_.date_created AS date_cre2_27_8_,
    user7_.date_last_update AS date_las3_27_8_,
    user7_.created_by_id AS created13_27_8_,
    user7_.last_update_by_id AS last_up14_27_8_,
    user7_.display_name AS display_4_27_8_,
    user7_.failed_login_attempts AS failed_l5_27_8_,
    user7_.full_name AS full_nam6_27_8_,
    user7_.note AS note7_27_8_,
    user7_.object_status AS object_s8_27_8_,
    user7_.password AS password9_27_8_,
    user7_.phone AS phone10_27_8_,
    user7_.time_zone_id AS time_zo11_27_8_,
    user7_.username AS usernam12_27_8_,
    provider8_.id AS id1_20_9_,
    provider8_.date_created AS date_cre2_20_9_,
    provider8_.date_last_update AS date_las3_20_9_,
    provider8_.created_by_id AS created23_20_9_,
    provider8_.last_update_by_id AS last_up24_20_9_,
    provider8_.text_id_map AS text_id_4_20_9_,
    provider8_.address_search_aid AS address_5_20_9_,
    provider8_.alternate_country_id AS alterna25_20_9_,
    provider8_.city_id AS city_id26_20_9_,
    provider8_.lat AS lat6_20_9_,
    provider8_.lng AS lng7_20_9_,
    provider8_.street_line1 AS street_l8_20_9_,
    provider8_.street_line2 AS street_l9_20_9_,
    provider8_.zip AS zip10_20_9_,
    provider8_.company_status AS company11_20_9_,
    provider8_.compassion_level AS compass12_20_9_,
    provider8_.emails AS emails13_20_9_,
    provider8_.phones AS phones14_20_9_,
    provider8_.social_media AS social_15_20_9_,
    provider8_.urls AS urls16_20_9_,
    provider8_.description AS descrip17_20_9_,
    provider8_.importance AS importa18_20_9_,
    provider8_.name AS name19_20_9_,
    provider8_.object_status AS object_20_20_9_,
    provider8_.parent_id AS parent_27_20_9_,
    provider8_.search_aid AS search_21_20_9_,
    provider8_.short_name AS short_n22_20_9_,
    user9_.id AS id1_27_10_,
    user9_.date_created AS date_cre2_27_10_,
    user9_.date_last_update AS date_las3_27_10_,
    user9_.created_by_id AS created13_27_10_,
    user9_.last_update_by_id AS last_up14_27_10_,
    user9_.display_name AS display_4_27_10_,
    user9_.failed_login_attempts AS failed_l5_27_10_,
    user9_.full_name AS full_nam6_27_10_,
    user9_.note AS note7_27_10_,
    user9_.object_status AS object_s8_27_10_,
    user9_.password AS password9_27_10_,
    user9_.phone AS phone10_27_10_,
    user9_.time_zone_id AS time_zo11_27_10_,
    user9_.username AS usernam12_27_10_,
    user10_.id AS id1_27_11_,
    user10_.date_created AS date_cre2_27_11_,
    user10_.date_last_update AS date_las3_27_11_,
    user10_.created_by_id AS created13_27_11_,
    user10_.last_update_by_id AS last_up14_27_11_,
    user10_.display_name AS display_4_27_11_,
    user10_.failed_login_attempts AS failed_l5_27_11_,
    user10_.full_name AS full_nam6_27_11_,
    user10_.note AS note7_27_11_,
    user10_.object_status AS object_s8_27_11_,
    user10_.password AS password9_27_11_,
    user10_.phone AS phone10_27_11_,
    user10_.time_zone_id AS time_zo11_27_11_,
    user10_.username AS usernam12_27_11_
FROM price_info prices0_
LEFT OUTER JOIN veg_user user1_ ON prices0_.created_by_id=user1_.id
LEFT OUTER JOIN veg_user user2_ ON user1_.created_by_id=user2_.id
LEFT OUTER JOIN veg_user user3_ ON user1_.last_update_by_id=user3_.id
LEFT OUTER JOIN veg_user user4_ ON prices0_.last_update_by_id=user4_.id
LEFT OUTER JOIN custom_currency customcurr5_ ON prices0_.currency_id=customcurr5_.id
LEFT OUTER JOIN veg_user user6_ ON customcurr5_.created_by_id=user6_.id
LEFT OUTER JOIN veg_user user7_ ON customcurr5_.last_update_by_id=user7_.id
LEFT OUTER JOIN provider provider8_ ON prices0_.provider_id=provider8_.id
LEFT OUTER JOIN veg_user user9_ ON provider8_.created_by_id=user9_.id
LEFT OUTER JOIN veg_user user10_ ON provider8_.last_update_by_id=user10_.id
WHERE prices0_.article_id =?

解决方案 #

方案1：修复@LazyCollection(EXTRA)生效问题 #

问题核心是Price实体中@ManyToOne关联默认是EAGER加载，导致加载Price时连带加载Provider、User等关联对象，同时干扰了@LazyCollection(EXTRA)的COUNT查询逻辑。修改Price实体的关联为LAZY加载：

@Entity @Indexed
class Price {

    @Reference @JsonBackReference
    @ManyToOne(fetch=FetchType.LAZY)
    @Field(bridge = @FieldBridge(impl = PriceArticleBridge.class))
    private Article article;

    @ManyToOne(fetch = FetchType.LAZY) // 修改为LAZY
    private Provider provider;

    @ManyToOne(fetch = FetchType.LAZY) // 修改为LAZY
    private CustomCurrency currency;

    private BigDecimal amount;

    @ManyToOne(fetch = FetchType.LAZY) // 修改为LAZY
    private User created;

    @ManyToOne(fetch = FetchType.LAZY) // 修改为LAZY
    private User updated;
}

修改后，@LazyCollection(EXTRA)会正常生效，调用prices.size()时Hibernate会执行COUNT查询，不再加载整个集合及关联对象。

方案2：自定义统计查询 #

在Article的Repository中添加自定义JPQL查询，直接统计关联Price数量：

public interface ArticleRepository extends JpaRepository<Article, Long> {

    @Query("SELECT COUNT(p) FROM Price p WHERE p.article.id = :articleId")
    int countPricesByArticleId(@Param("articleId") Long articleId);
}

业务代码中直接调用articleRepository.countPricesByArticleId(article.getId())，完全绕过集合加载操作，性能最优。

方案3：使用@Formula添加冗余统计字段 #

在Article实体中添加@Formula字段，直接映射到数据库