java - Spring JPA Entity Graph 和 Self Reference 发生 N+1 查询
问题描述
我尝试使用实体图来避免 N+1 查询,它按预期工作。
自引用实体不同,虽然这些代码可以得到正确的列表,但发生了 N+1 查询。
我的问题是如何使用自引用实体消除 N+1 查询?
提前致谢。
日志和代码如下
Hibernate:
select
sysperm0_.pval as pval1_0_0_,
children1_.pval as pval1_0_1_,
sysperm2_.pval as pval1_0_2_,
sysperm0_.parent as parent4_0_0_,
sysperm0_.created as created2_0_0_,
sysperm0_.leaf as leaf3_0_0_,
sysperm0_.pname as pname5_0_0_,
sysperm0_.ptype as ptype6_0_0_,
sysperm0_.updated as updated7_0_0_,
children1_.parent as parent4_0_1_,
children1_.created as created2_0_1_,
children1_.leaf as leaf3_0_1_,
children1_.pname as pname5_0_1_,
children1_.ptype as ptype6_0_1_,
children1_.updated as updated7_0_1_,
children1_.parent as parent4_0_0__,
children1_.pval as pval1_0_0__,
sysperm2_.parent as parent4_0_2_,
sysperm2_.created as created2_0_2_,
sysperm2_.leaf as leaf3_0_2_,
sysperm2_.pname as pname5_0_2_,
sysperm2_.ptype as ptype6_0_2_,
sysperm2_.updated as updated7_0_2_
from
sys_perm sysperm0_
left outer join
sys_perm children1_
on sysperm0_.pval=children1_.parent
left outer join
sys_perm sysperm2_
on children1_.parent=sysperm2_.pval
where
sysperm0_.pval=?
Hibernate:
select
children0_.parent as parent4_0_0_,
children0_.pval as pval1_0_0_,
children0_.pval as pval1_0_1_,
children0_.parent as parent4_0_1_,
children0_.created as created2_0_1_,
children0_.leaf as leaf3_0_1_,
children0_.pname as pname5_0_1_,
children0_.ptype as ptype6_0_1_,
children0_.updated as updated7_0_1_
from
sys_perm children0_
where
children0_.parent=?
.......XN
492373 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
5197227 nanoseconds spent preparing 8 JDBC statements;
18997333 nanoseconds spent executing 8 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
实体
@Table(name="sys_perm")
@Entity
@NamedEntityGraph(
name = "test",
attributeNodes = {
@NamedAttributeNode(value="children",subgraph="sub_perm"),
},
subgraphs = {
@NamedSubgraph(
name = "sub_perm",
attributeNodes = {
@NamedAttributeNode("_parent")
}
)
}
)
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class SysPerm implements Serializable {
@Id
private String pval;
private String parent;
private String pname;
private Integer ptype;
private Boolean leaf;
@CreationTimestamp
private Date created;
@UpdateTimestamp
private Date updated;
@ManyToOne(fetch=FetchType.LAZY, cascade={CascadeType.ALL})
@JoinColumn(name = "parent", referencedColumnName = "pval", insertable=false, updatable=false)
@JsonIgnore
private SysPerm _parent;
@OneToMany(mappedBy="_parent", fetch=FetchType.EAGER, cascade={CascadeType.ALL})
private List<SysPerm> children = new ArrayList<>();
}
存储库
public interface SysPermRepository extends JpaRepository<SysPerm, Long>{
@EntityGraph(value = "test", type = EntityGraphType.FETCH)
List<SysPerm> findByPval(String pval);
}
架构
CREATE TABLE `sys_perm` (
`pval` varchar(50) NOT NULL ,
`parent` varchar(25) DEFAULT NULL ,
`pname` varchar(50) DEFAULT NULL ,
`ptype` int(3) DEFAULT NULL ,
`leaf` tinyint(1) DEFAULT NULL ,
`created` timestamp NULL DEFAULT NULL ,
`updated` timestamp NULL DEFAULT NULL ,
PRIMARY KEY (`pval`),
UNIQUE KEY `pval` (`pval`),
KEY `FKaiy87e3krvn4suwleaooces17` (`parent`),
CONSTRAINT `FKaiy87e3krvn4suwleaooces17` FOREIGN KEY (`parent`) REFERENCES `sys_perm` (`pval`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
解决方案
该问题将通过更改EntityGraphType.FETCH
为EntityGraphType.LOAD
public interface SysPermRepository extends JpaRepository<SysPerm, Long>{
@EntityGraph(value = "test", type = EntityGraphType.LOAD)
List<SysPerm> findByPval(String pval);
}
我的问题就这样解决了,你可以试试。
推荐阅读
- javascript - 未找到无服务器命令“离线”
- node.js - 如何在我的 nginx 服务器上读取数据包,我正在使用数据包发送方发送数据包
- javascript - 沿现有要素扩展 LineString/MultiLineString
- rust - 优化格式!如果不使用其结果,则为 nop
- java - iText 7:此 pdf 文档可能无法正确显示 Firefox
- node.js - Node.js 服务器未正确提供文件
- c# - Entity Framework Core 首次查询与后续查询性能
- r - Shiny:增加 MathJax 的字体大小
- templates - Shopware 自定义选项卡在选择变体后导致错误
- powerquery - 如果 logic = true,则使用前一行的值,否则使用另一列