JOIN FETCH on@ManyToMany关系生成N+1个查询

Question

在我的应用程序中，我有以下包含@ManyToMany关系的@Entity。

@Entity(name="CommonStaff")
@Table(name="staff")
@Getter @Setter @FieldNameConstants
@NoArgsConstructor
public class Staff implements Serializable {
...
@ManyToMany(cascade={ CascadeType.PERSIST, CascadeType.MERGE }, fetch=FetchType.LAZY)
@JoinTable(name="staff_language",
joinColumns={ @JoinColumn(name="username", referencedColumnName="username") },
inverseJoinColumns={ @JoinColumn(name="language_code", referencedColumnName="code") })
private Set<Language> languages = new HashSet<>();
...
}
@Entity(name="CommonLanguage")
@Table(name="language")
@Getter @Setter
@NoArgsConstructor
public class Language implements Serializable {
@Id
@Column(name="id")
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
@NaturalId
private String code;
private String name;
@Column(name="short_name")
private String shortName;
private String description;
@Column(name="order_id")
private Integer orderId;
@Override
public int hashCode() {
return Objects.hashCode(this.getCode());
}
@Override
public boolean equals(Object other) {
if (this == other)
return true;
if (!(other instanceof Language))
return false;
Language that = (Language) other;
return Objects.equals(that.getCode(), this.getCode());
}
}

在@Repository中，我创建了以下方法来急切地获取languages。

@Query(value="SELECT S"
+ "    FROM CommonStaff S"
+ "    JOIN FETCH S.languages"
+ "    WHERE S.userId = :userId")
Staff find(String userId);

我在@Controller中创建了以下方法来测试查询。

Staff staff = staffRepo.find(userId);
if (staff != null) {
System.out.println(staff.getName());
staff.getLanguages().forEach(language -> System.out.println(language.getName()));
}

我在控制台中看到的内容如下。

2020-04-18 18:41:02,394 DEBUG [http-nio-9000-exec-2] org.hibernate.SQL   : 
/* SELECT
S    
FROM
CommonStaff S    
JOIN
FETCH S.languages    
WHERE
S.userId = :userId */ select
staff0_.id as id1_24_0_,
language2_.id as id1_9_1_,
staff0_.email as email2_24_0_,
staff0_.name as name3_24_0_,
staff0_.username as username4_24_0_,
staff0_.is_active as is_activ5_24_0_,
staff0_.address as address6_24_0_,
staff0_.biometric_id as biometri7_24_0_,
staff0_.card_number as card_num8_24_0_,
language2_.code as code2_9_1_,
language2_.description as descript3_9_1_,
language2_.name as name4_9_1_,
language2_.order_id as order_id5_9_1_,
language2_.short_name as short_na6_9_1_,
languages1_.username as username1_25_0__,
languages1_.language_code as language2_25_0__ 
from
staff staff0_ 
inner join
staff_language languages1_ 
on staff0_.username=languages1_.username 
inner join
language language2_ 
on languages1_.language_code=language2_.code 
where
staff0_.username=?
2020-04-18 18:41:02,395 TRACE [http-nio-9000-exec-2] org.hibernate.type.descriptor.sql.BasicBinder   : binding parameter [1] as [VARCHAR] - [90000010]
2020-04-18 18:41:02,411 DEBUG [http-nio-9000-exec-2] org.hibernate.SQL   : 
/* load com.ft.common.db.customer.domain.Language */ select
language0_.id as id1_9_0_,
language0_.code as code2_9_0_,
language0_.description as descript3_9_0_,
language0_.name as name4_9_0_,
language0_.order_id as order_id5_9_0_,
language0_.short_name as short_na6_9_0_ 
from
language language0_ 
where
language0_.code=?
2020-04-18 18:41:02,411 TRACE [http-nio-9000-exec-2] org.hibernate.type.descriptor.sql.BasicBinder   : binding parameter [1] as [VARCHAR] - [LAN_ENG]
2020-04-18 18:41:02,420 DEBUG [http-nio-9000-exec-2] org.hibernate.SQL   : 
/* load com.ft.common.db.customer.domain.Language */ select
language0_.id as id1_9_0_,
language0_.code as code2_9_0_,
language0_.description as descript3_9_0_,
language0_.name as name4_9_0_,
language0_.order_id as order_id5_9_0_,
language0_.short_name as short_na6_9_0_ 
from
language language0_ 
where
language0_.code=?
2020-04-18 18:41:02,420 TRACE [http-nio-9000-exec-2] org.hibernate.type.descriptor.sql.BasicBinder   : binding parameter [1] as [VARCHAR] - [LAN_MAL]
Edgar Rey Tann
English
Malay

根据我的理解，JOIN FETCH或LEFT JOIN FETCH应该可以帮助我摆脱最后两个查询，但它们都不起作用。在我的研究过程中，我找不到任何可行的解决方案。如果你能给我指个方向，我将不胜感激。

Answer 1

当实体已经在一级缓存或二级缓存中时，就会发生这种情况。在调用find()之前尝试使用entityManager.clear()