95577

[code]

    @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);
        }
    }
    
[/code]

生成されるSQL:

[code]

    select
        l1_0.id,
        l1_0.name,
        r2_0.user_id,
        r2_1.id,
        r2_1.name
    from
        login_user l1_0
    left
        join ( /* Specificationが生成するJOIN句 */
            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 ( /* @EntityGraphが生成するJOIN句 */
            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'
    
[/code]

希望するSQL:

[code]

    select
        l1_0.id,
        l1_0.name,
        r2_0.user_id,
        r2_1.id,
        r2_1.name
    from
        login_user l1_0
    left
        join ( /* @EntityGraphが生成するJOIN句 */
            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'
    
[/code]

上記のように重複しているJOIN句を一つにまとめることは可能でしょうか?

[code]

    root.join("roleList", JoinType.LFET).get("name"), role);
    
[/code]

本来であれば検索条件を指定するのでJoinType.INNERが正しいですが、LEFTに合わせればもしかしたら最適化してくれるかな?と期待した次第です。


CC BY-SA 4.0
2023-07-07T03:17:34.427
95511
2023-07-12T15:02:55.040
2023-07-07T07:56:54.107
58916
58916
question
java spring-boot spring java-ee jpa
Spring data JPAで@EntityGraphとSpecificationがそれぞれ別々のJOIN句を生成するのを一つに（共用）したい


やり方が見つからなかったのでSQLを変更して対処しました。  
具体的にはSpecificationの方をexists句に変更しました。

[code]

    select
        l1_0.id,
        l1_0.name,
        r2_0.user_id,
        r2_1.id,
        r2_1.name
    from
        login_user l1_0
    left
        join ( /* @EntityGraphが生成するJOIN句 */
            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
        exists (
            select
                1
            from
                login_user l2_0
                join (
                    user_role r2_0
                    join roles r2_1 on r2_1.id = r2_0.role_id
                )
                on l2_0.id = r2_0.user_id
            where
                l1_0.id = l2_0.id
                and
                r2_1.name in (
                    'ROLE_ADMIN'
                )
        )
    
[/code]


CC BY-SA 4.0
2023-07-12T15:02:55.040
95577
2023-07-12T15:02:55.040
58916
95511
answer