spring-boot - 尽管获取加入，但无法解决 @ManyToOne n+1 呼叫

Question

我遇到了一个问题，我无法解决特定的延迟提取，导致 n+1 调用。尽管对我需要的资源使用了 join fetch。

这是我的实体：

@Entity
@Table(schema = "dbo", name = "Authorization")
public class Authorization {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private Integer id;

    .............

    @Column(name = "Auditor")
    private String auditor;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "auditor", referencedColumnName = "UserId", insertable = false, updatable = false)
    private User auditorUser;
    
    @Column(name = "Analyst")
    private Integer analyst;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "Analyst", referencedColumnName = "Id", insertable = false, updatable = false)
    private User analystUser;
    
    @OneToMany(mappedBy = "authorization", cascade = CascadeType.ALL)
    private Set<Approver> approvers;

    .............
}

@Entity
@Table(name = "[User]")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private int id;

    @NaturalId
    @Column(name = "UserId")
    private String userId;
}


@Entity
@Table(name = "Approver")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    private int id;

    @Column(name = "ApproverId")
    private String approverId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ApproverId", referencedColumnName = "UserId", insertable = false, updatable = false)
    private User approverUser;
}

我正在尝试获取所有授权并加入获取它的关联审计用户、分析用户和批准者。这是我对 queryDSL 所做的事情：

QUser auditorUser = new QUser("auditorUser");
QUser analystUser = new QUser("analystUser");
QUser approverUser = new QUser("approverUser");

List<Authorization> auths = repo.findAll(
    new JPAQuery<Authorization>()
        .from(authorization)
        .leftJoin(authorization.auditorUser, auditorUser).fetchJoin()
        .leftJoin(authorization.analystUser, analystUser).fetchJoin()
        .leftJoin(authorization.approvers, approver).fetchJoin()
        .leftJoin(workAuthorizationApproverEntity.approverUser, approverUser).fetchJoin()
);

这会产生：

select authorization
from Authorization authorization
left join fetch authorization.auditorUser as auditorUser
left join fetch authorization.analystUser as analystUser
left join fetch workAuthorizationEntity.approvers as workAuthorizationApproverEntity
left join fetch authorization.approvers as approver
left join fetch approver.approverUser as approverUser

but it also then generates for every authorization one of these:

select user0_.Id as Id1_338_0_, user0_.UserId as UserId5_338_0_ from [User] user0_ where user0_.UserId=? 

从用户 id 中，我可以识别出这是来自正在加入 User 表中 NaturalId 的 auditUser 。然而，在同一个班级中，分析师加入主键 id 并且工作正常。

发生了什么事，我能做些什么来防止这种情况发生？

标签： spring-bootspring-data-jpaquerydsl