问题背景:
在设计一个树形结构的实体中用到了多对一,一对多的映射关系,在加载其关联对象的时候,为了性能考虑,很自然的想到了懒加载。
也因此遇到了N+1的典型问题 : 通常1的这方,通过1条SQL查找得到1个对象,而JPA基于Hibernate,fetch策略默认为select(并非联表查询),由于关联的存在 ,又需要将这个对象关联的集合取出,集合数量是N,则要发出N条SQL,于是本来的1条联表查询SQL可解决的问题变成了N+1条SQL
JPA 2.1推出来的@EntityGraph、@NamedEntityGraph用来提高查询效率,很好地解决了N+1条SQL的问题。两者需要配合起来使用,缺一不可。@NamedEntityGraph配置在@Entity上面,而@EntityGraph配置在Repository的查询方法上面.通过联表查询进行解决。
解决的方法如下 :
Demo 如下:
1 . 首先在实体上面注解@NamedEntityGraph,指明name供查询方法使用,attributeNodes 指明被标注为懒加载的属性节点
@Entity
@Getter
@Setter
@Table(name = "ORG_AGENCY")
@NamedEntityGraph(name = "Agency.Graph", attributeNodes = {@NamedAttributeNode("region"), @NamedAttributeNode("parent"),@NamedAttributeNode("mofDep"),@NamedAttributeNode("unit")})
public class Agency extends BaseEntity {
@Field
@Column(nullable = false)
@ApiModelProperty(value = "编码", required = true)
private String code;
@Field
@Column(nullable = false)
@ApiModelProperty(value = "名称", required = true)
private String name;
@ApiModelProperty(value = "是否启用")
private Boolean enabled; // 是否启用
@ApiModelProperty(value = "级次")
private Integer levelNum;
@ApiModelProperty(value = "内部编码")
private String innerCode;
@ApiModelProperty(value = "是否末级")
private Boolean leafNode;
@ApiModelProperty(value = "是否允许增加下级")
private Boolean expandable;
@ApiModelProperty(value = "所属区划")
@ManyToOne(fetch = FetchType.LAZY)
private Region region;
@ManyToOne(fetch = FetchType.LAZY)
@ApiModelProperty(value = "上级")
private Agency parent;
@ApiModelProperty(value = "年度")
private String year;
@ApiModelProperty(value = "业务处室")
@ManyToOne(fetch = FetchType.LAZY)
private MOFDep mofDep;
@ApiModelProperty(value = "来源单位")
@ManyToOne(fetch = FetchType.LAZY)
private Organization unit;
}
2 . 在访问的dao的查询方法上面注解@EntityGraph,value属性值为@NamedEntityGraph的name属性值,如 AgencyRepository :
public interface AgencyRepository extends GenericRepository<Agency> {
@QueryHints(value = {@QueryHint(name = "org.hibernate.cacheable", value = "true")})
@EntityGraph(value = "Agency.Graph", type = EntityGraph.EntityGraphType.FETCH)
List<Agency> findAll(Predicate predicate, Sort sort);
}