如何在Spring Data JPA中合并@EntityGraph与Specification的重复JOIN？

我希望在Spring Data JPA中结合@EntityGraph和Specification查询时，生成单个JOIN子句而非多个独立的JOIN。

实体类与Repository代码 #

@Entity
public class LoginUser {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(nullable = false)
    private String name;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "user_role",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private List<Role> roleList;
}

@Entity
public class Role {
    @Id
    private Integer id;

    @Column(nullable = false)
    private String name;
}

public interface LoginUserRepository extends JpaRepository<LoginUser, String>,
        JpaSpecificationExecutor<LoginUser> {

    @EntityGraph(attributePaths = {"roleList"})
    public Page<LoginUser> findAll(Specification<LoginUser> spec, Pageable pageable);
}

public class LoginUserSpecification {
   static public Specification<LoginUser> equalsRole(String role) {
        return (root, query, builder) -> builder.equal(
                    root.join("roleList", JoinType.LEFT).get("name"), role);
    }
}

public class LoginUserDetailsService {
    public Page<LoginUser> getAccounts(Pageable pageable, UserSearchForm form) {
        return loginUserRepository.findAll(
                Specification.where(LoginUserSpecification.equalsRole(form.getRole())),
                pageable);
    }
}

当前生成的SQL #

select
    l1_0.id,
    l1_0.name,
    r2_0.user_id,
    r2_1.id,
    r2_1.name
from
    login_user l1_0
left
    join ( /* JOIN clause generated by Specification */
        user_role r1_0
        join roles r1_1 on r1_1.id = r1_0.role_id
    )
    on l1_0.id = r1_0.user_id
left
    join ( /* JOIN clause generated by @EntityGraph */
        user_role r2_0
        join roles r2_1 on r2_1.id = r2_0.role_id
    )
    on l1_0.id = r2_0.user_id
where
    r1_1.name = 'ROLE_ADMIN'

期望的SQL #

select
    l1_0.id,
    l1_0.name,
    r2_0.user_id,
    r2_1.id,
    r2_1.name
from
    login_user l1_0
left
    join ( /* JOIN clause generated by @EntityGraph */
        user_role r2_0
        join roles r2_1 on r2_1.id = r2_0.role_id
    )
    on l1_0.id = r2_0.user_id
where
    r2_1.name = 'ROLE_ADMIN'

问题核心 #

是否可以将上述重复的JOIN子句合并为单个？

注：代码中存在笔误JoinType.LFET应为JoinType.LEFT；理想情况下查询条件使用JoinType.INNER更合适，但我选择LEFT JOIN是希望优化查询。

解决方案 #

方案1：用Specification的Fetch Join替代@EntityGraph

直接去掉@EntityGraph注解，在Specification中通过fetch方法加载关联并复用该关联添加查询条件，这样只会生成一次JOIN：

修改后的Repository：

public interface LoginUserRepository extends JpaRepository<LoginUser, String>,
        JpaSpecificationExecutor<LoginUser> {

    public Page<LoginUser> findAll(Specification<LoginUser> spec, Pageable pageable);
}

修改后的Specification：

public class LoginUserSpecification {
    static public Specification<LoginUser> equalsRole(String role) {
        return (root, query, builder) -> {
            // 通过fetch加载roleList关联
            Fetch<LoginUser, Role> roleFetch = root.fetch("roleList", JoinType.LEFT);
            // 将Fetch转换为Join以用于条件判断
            Join<LoginUser, Role> roleJoin = (Join<LoginUser, Role>) roleFetch;
            return builder.equal(roleJoin.get("name"), role);
        };
    }
}

方案2：保留@EntityGraph并复用已存在的关联

如果必须保留@EntityGraph，可以在Specification中查找已创建的关联，避免重复生成JOIN（注意：该方式依赖JPA实现，兼容性需测试）：

public class LoginUserSpecification {
   static public Specification<LoginUser> equalsRole(String role) {
        return (root, query, builder) -> {
            // 查找root中已存在的roleList关联
            Join<LoginUser, Role> roleJoin = null;
            for (Join<?, ?> join : root.getJoins()) {
                if ("roleList".equals(join.getAttribute().getName())) {
                    roleJoin = (Join<LoginUser, Role>) join;
                    break;
                }
            }
            // 不存在则创建新关联
            if (roleJoin == null) {
                roleJoin = root.join("roleList", JoinType.LEFT);
            }
            return builder.equal(roleJoin.get("name"), role);
        };
    }
}

另外补充：如果你的查询条件是过滤指定角色的用户，使用INNER JOIN的性能会比LEFT JOIN更优——因为LEFT JOIN后通过WHERE条件过滤掉无匹配角色的记录，最终效果和INNER JOIN一致，但INNER JOIN的查询逻辑更简洁高效。

内容的提问来源于stack exchange，提问作者Elen