N+1 双向一对一映射的休眠问题

Question

我正在使用带有休眠的JPARepository来表示我的数据库。我有几个实体，我的两个实体具有双向一对一映射。每当我对双向关系的子类(或父类以获取子类(运行查询时，我都面临着 N+1 休眠问题。

我的父实体：

@Entity
@Table(name = "ORGANIZATION")
public class Organization {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
@Id
private Long id;
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "organization_source_id")
private OrganizationSource source;
//other columns then getters and setters
}

儿童类

@Entity
@Table(name = "ORGANIZATION_SOURCE")
public class OrganizationSource {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
@Id
private Long id;
@OneToOne(mappedBy="source", fetch = FetchType.LAZY)
@Cascade(value=org.hibernate.annotations.CascadeType.SAVE_UPDATE)
private Organization organization;
//other columns then getters and setters
}

然后，我有了我的 DAO 类来创建查询。我使用了@Query注释

public interface OrganizationSourceDao extends PagingAndSortingRepository<OrganizationSource, Long> {

@Query("SELECT source FROM OrganizationSource source RIGHT JOIN source.organization"
+ "WHERE source.name like %:name% "
//   order by part of the query
)
Page<OrganizationSource> findByNameContaining(
@Param("name") String name,
@PageableDefault(size = 10) Pageable pageable);
}

在 DAO 中执行查询会在日志中显示以下内容

Hibernate: select organizati0_.id as id1_6_, organizati0_.city as city2_6_, organizati0_.code as code3_6_, organizati0_.name as name4_6_, organizati0_.source_system_id as source_s5_6_, organizati0_.state as state6_6_, organizati0_.street as street7_6_, organizati0_.uuid as uuid8_6_, organizati0_.zip as zip9_6_ from organization_source organizati0_ right outer join organization organizati1_ on organizati0_.id=organizati1_.organization_source_id where (organizati0_.name like ?) and (organizati0_.state like ?) and (organizati0_.city like ?) order by case ? when 'ASC' then case ? when 'name' then organizati0_.name when 'state' then organizati0_.state when 'city' then organizati0_.city when 'zip' then organizati0_.zip when 'street' then organizati0_.street end end ASC, case ? when 'DESC' then case ? when 'name' then organizati0_.name when 'state' then organizati0_.state when 'city' then organizati0_.city when 'zip' then organizati0_.zip when 'street' then organizati0_.street end end DESC offset 0 rows fetch next ? rows only
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?
Hibernate: select organizati0_.id as id1_4_1_, organizati0_.mfa_method_id as mfa_meth4_4_1_, organizati0_.organization_source_id as organiza5_4_1_, organizati0_.status_id as status_i6_4_1_, organizati0_.uuid as uuid2_4_1_, organizati0_.version as version3_4_1_, organizati1_.id as id1_6_0_, organizati1_.city as city2_6_0_, organizati1_.code as code3_6_0_, organizati1_.name as name4_6_0_, organizati1_.source_system_id as source_s5_6_0_, organizati1_.state as state6_6_0_, organizati1_.street as street7_6_0_, organizati1_.uuid as uuid8_6_0_, organizati1_.zip as zip9_6_0_ from organization organizati0_ left outer join organization_source organizati1_ on organizati0_.organization_source_id=organizati1_.id where organizati0_.organization_source_id=?

我已经查看了使用注释@Fetch(FetchMode.JOIN)以及所有快速解决Google上可用解决方案的人 - 似乎无法解决此问题。

任何帮助将不胜感激。

Answer 1

我知道一个技巧。将组织映射从一对一更改为多对一。这对我来说一直都很好。这次没有 n+1 查询。

组织来源

@Entity
@Table(name = "ORGANIZATION_SOURCE")
public class **OrganizationSource** {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
@Id
private Long id;
@JsonBackReference
@Cascade(value=org.hibernate.annotations.CascadeType.SAVE_UPDATE)
@ManyToOne(fetch = FetchType.LAZY)
private Organization organization;
private String name;
}

和组织

@Data
@Entity
@Table(name = "ORGANIZATION")
public class Organization {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", nullable = false)
@Id
private Long id;
@JsonManagedReference
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "organization_source_id")
private OrganizationSource source;
}