The goal is to count collection elements without initializing collection. I made it work in some way, but it's not the soulution that I'd say I want.
Back reference is needed because each Price gets some data from Article while being indexed by Hibernate Search.
Join column gives me an option to have only two tables, avoiding 3rd linking table.
@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;
}
This setup gives me 2 tables where Price table has article_id and there is no 3rd table for linking purposes. But while loading Article and issuing prices.size(), the whole collection is fetched. This is not good because many other entities are loaded too: Provider from Price and User many times because Provider also has its own 2 properties of User to track creator and updater.
When there is no @JoinColumn, 3rd linking table is created and then it starts to use COUNT() instead of loading the whole collection.
My only question is:
How can I keep only 2 tables and use .size() without loading the whole collection?
SQL example when not having @JoinColumn:
select count(prices_id) from article_prices where article_id =?
SQL example when having @JoinColumn (11 tables joined):
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 = ?
CodePudding user response：
Not sure about your objectives, however sometime following trick could help:
public class Article {
...
Set<Price> prices;
@Formula("select count(*) from price where article_id=:id")
private int priceCount;
public int getPriceCount() {
if (Hibernate.isInitialized(prices)) {
return prices == null ? 0 : prices.size();
}
return priceCount;
}
}