@JoinFetch 遇上 @ElementCollection 時，會 join 太多次

[jackjieru]

使用的專案：azalea-tl-sc

Entity： TlMainPolicyEntity

@Entity
@Table(name = "TL_MAIN_POLICY", uniqueConstraints = {
    @UniqueConstraint(name = "TL_MAIN_POLICY_UK1", columnNames = { "POLICY_NO", })
})
@Getter
@Setter
@Comment("旅責大保單")
public class TlMainPolicyEntity extends AbstractEntity implements TlDnEntity {
  .......omit

  @ElementCollection(fetch = FetchType.LAZY)
  @CollectionTable(name = "TL_MAIN_POLICY_DISCOUNT", joinColumns = @JoinColumn(name = "P_ID"))
  @MapKeyColumn(name = "RATIO_CODE", length = 10)
  @Column(name = "DISCOUNT", nullable = false, precision = 10, scale = 8)
  Map<String, BigDecimal> discounts = new HashMap<>();
}

Criteria: TlMainPolicyCriteria

@Getter
@Setter
@Builder
@JoinFetch(paths = "discounts")
public class TlMainPolicyCriteria implements TlDnQuery {
  ........ omit
}

方法：Page<T> QueryBySpecExecutor.findBySpec(@Nullable Object spec, @NonNull Pageable pageable)

產生的 SQL join 了 14 次：
（DB 是 SQL Server）

 select
            distinct tlmainpoli0_.id as id1_2_,
            tlmainpoli0_.created_by as created_2_2_,
            tlmainpoli0_.created_date as created_3_2_,
            tlmainpoli0_.last_modified_by as last_mod4_2_,
            tlmainpoli0_.last_modified_date as last_mod5_2_,
            tlmainpoli0_.optlock as optlock6_2_,
            tlmainpoli0_.active as active7_2_,
            tlmainpoli0_.applicant_dn as applican8_2_,
            tlmainpoli0_.applicant_idno as applican9_2_,
            tlmainpoli0_.applicant_name as applica10_2_,
            tlmainpoli0_.broker_dn as broker_11_2_,
            tlmainpoli0_.broker_name as broker_12_2_,
            tlmainpoli0_.business_source as busines13_2_,
            tlmainpoli0_.eff_date as eff_dat14_2_,
            tlmainpoli0_.exp_date as exp_dat15_2_,
            tlmainpoli0_.policy_no as policy_16_2_,
            discounts14_.p_id as p_id1_3_13__,
            discounts14_.discount as discount2_3_13__,
            discounts14_.ratio_code as ratio_co3_13__,
            discounts13_.p_id as p_id1_3_12__,
            discounts13_.discount as discount2_3_12__,
            discounts13_.ratio_code as ratio_co3_12__,
            discounts12_.p_id as p_id1_3_11__,
            discounts12_.discount as discount2_3_11__,
            discounts12_.ratio_code as ratio_co3_11__,
            discounts11_.p_id as p_id1_3_10__,
            discounts11_.discount as discount2_3_10__,
            discounts11_.ratio_code as ratio_co3_10__,
            discounts10_.p_id as p_id1_3_9__,
            discounts10_.discount as discount2_3_9__,
            discounts10_.ratio_code as ratio_co3_9__,
            discounts9_.p_id as p_id1_3_8__,
            discounts9_.discount as discount2_3_8__,
            discounts9_.ratio_code as ratio_co3_8__,
            discounts8_.p_id as p_id1_3_7__,
            discounts8_.discount as discount2_3_7__,
            discounts8_.ratio_code as ratio_co3_7__,
            discounts7_.p_id as p_id1_3_6__,
            discounts7_.discount as discount2_3_6__,
            discounts7_.ratio_code as ratio_co3_6__,
            discounts6_.p_id as p_id1_3_5__,
            discounts6_.discount as discount2_3_5__,
            discounts6_.ratio_code as ratio_co3_5__,
            discounts5_.p_id as p_id1_3_4__,
            discounts5_.discount as discount2_3_4__,
            discounts5_.ratio_code as ratio_co3_4__,
            discounts4_.p_id as p_id1_3_3__,
            discounts4_.discount as discount2_3_3__,
            discounts4_.ratio_code as ratio_co3_3__,
            discounts3_.p_id as p_id1_3_2__,
            discounts3_.discount as discount2_3_2__,
            discounts3_.ratio_code as ratio_co3_2__,
            discounts2_.p_id as p_id1_3_1__,
            discounts2_.discount as discount2_3_1__,
            discounts2_.ratio_code as ratio_co3_1__,
            discounts1_.p_id as p_id1_3_0__,
            discounts1_.discount as discount2_3_0__,
            discounts1_.ratio_code as ratio_co3_0__ 
        from
            tl_main_policy tlmainpoli0_ 
        left outer join
            tl_main_policy_discount discounts1_ 
                on tlmainpoli0_.id=discounts1_.p_id 
        left outer join
            tl_main_policy_discount discounts2_ 
                on tlmainpoli0_.id=discounts2_.p_id 
        left outer join
            tl_main_policy_discount discounts3_ 
                on tlmainpoli0_.id=discounts3_.p_id 
        left outer join
            tl_main_policy_discount discounts4_ 
                on tlmainpoli0_.id=discounts4_.p_id 
        left outer join
            tl_main_policy_discount discounts5_ 
                on tlmainpoli0_.id=discounts5_.p_id 
        left outer join
            tl_main_policy_discount discounts6_ 
                on tlmainpoli0_.id=discounts6_.p_id 
        left outer join
            tl_main_policy_discount discounts7_ 
                on tlmainpoli0_.id=discounts7_.p_id 
        left outer join
            tl_main_policy_discount discounts8_ 
                on tlmainpoli0_.id=discounts8_.p_id 
        left outer join
            tl_main_policy_discount discounts9_ 
                on tlmainpoli0_.id=discounts9_.p_id 
        left outer join
            tl_main_policy_discount discounts10_ 
                on tlmainpoli0_.id=discounts10_.p_id 
        left outer join
            tl_main_policy_discount discounts11_ 
                on tlmainpoli0_.id=discounts11_.p_id 
        left outer join
            tl_main_policy_discount discounts12_ 
                on tlmainpoli0_.id=discounts12_.p_id 
        left outer join
            tl_main_policy_discount discounts13_ 
                on tlmainpoli0_.id=discounts13_.p_id 
        left outer join
            tl_main_policy_discount discounts14_ 
                on tlmainpoli0_.id=discounts14_.p_id 
        order by
            tlmainpoli0_.id desc

不只是 join 多次，連分頁查詢的 where 條件 offset 0 rows fetch next ? rows only 都沒出現
如果 criteria 拿掉 @JoinFetch ，則有正常出現

select
            tlmainpoli0_.id as id1_2_,
            tlmainpoli0_.created_by as created_2_2_,
            tlmainpoli0_.created_date as created_3_2_,
            tlmainpoli0_.last_modified_by as last_mod4_2_,
            tlmainpoli0_.last_modified_date as last_mod5_2_,
            tlmainpoli0_.optlock as optlock6_2_,
            tlmainpoli0_.active as active7_2_,
            tlmainpoli0_.applicant_dn as applican8_2_,
            tlmainpoli0_.applicant_idno as applican9_2_,
            tlmainpoli0_.applicant_name as applica10_2_,
            tlmainpoli0_.broker_dn as broker_11_2_,
            tlmainpoli0_.broker_name as broker_12_2_,
            tlmainpoli0_.business_source as busines13_2_,
            tlmainpoli0_.eff_date as eff_dat14_2_,
            tlmainpoli0_.exp_date as exp_dat15_2_,
            tlmainpoli0_.policy_no as policy_16_2_ 
        from
            tl_main_policy tlmainpoli0_ 
        order by
            tlmainpoli0_.id desc offset 0 rows fetch next ? rows only

[shihyuho]

@jackjieru 沒辦法還原耶, 請看一下測試程式是否需調整

照你留的範例, 我基本上複製過去微調了一些命名, 跑出來看起來是正常的

select distinct ...
from customer customer0_ 
left outer join phones phones1_ on customer0_.id=phones1_.cust_id 
where customer0_.name=?

[jackjieru]

我也不明白為何測試程式無法還原
我在華南的程式中 autowire SpecMapper 下斷點，查看 criteria 轉換出的 spec，確實有 14 個 JoinFetch

 var spec = specMapper.toSpec(criteria, TlMainPolicyEntity.class);

[jackjieru]

找到原因了，JoinFetch 次數 等同 Criteria class 的 field 數量。
在 CustomerFetchPhone 多加一些欄位就能發現BUG

[jackjieru]

其實這個 BUG 不只在 @ElementCollection 發生，@ManyToMany 也有同樣問題。
只是我剛好都避開 BUG。

例如 azalea-security-sc 的 RoleEntity 有 authorities 關聯

  @ManyToMany(cascade = {
      CascadeType.PERSIST,
      CascadeType.MERGE,
      CascadeType.DETACH,
      CascadeType.REFRESH
  }, fetch = FetchType.EAGER)
  @JoinTable(name = "ROLE_AUTH_MAPPING", joinColumns = {
      @JoinColumn(name = "ROLE_ID", nullable = false)
  }, inverseJoinColumns = {
      @JoinColumn(name = "AUTH_ID", nullable = false)
  })
  Set<AuthorityEntity> authorities = new HashSet<>();

我寫了兩個 criteria：
RoleCriteria 沒有 @JoinFetch

@Data
public class RoleCriteria {
  ..........

RoleJoinCriteria 有 @JoinFetch 並且 @NestedSpec RoleCriteria

@JoinFetch(paths = "authorities")
public record RoleJoinCriteria(@NestedSpec RoleCriteria roleCriteria) {
}

因為 RoleJoinCriteria 只有一個欄位
才沒有重複 join