LEFT JOIN FETCH not solving n+1 issue

Question

I would like to ask about solution for N+1 issue. I have Account table and Account_role table with 1:M relationship. I try the join fetch method by using LEFT JOIN FETCH in @Query but does not work.

Account class:

@Entity(name = "account")
@Table(name = "account")
public class AccountBean implements Serializable {
    @Id
    @Column("username")
    private String username

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "username", referencedColumnName = "username", insertable = false, updatable = false)
    private Set<AccountRoleBean> roles = new HashSet<>();

    // getters setters
}

Account Role class:

@Entity(name = "account_role")
@Table(name = "account_role")
public class AccountRoleBean implements Serializable {

    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    // getters setters
}

Account Repository class

public interface AccountRepo extends JpaRepository<AccountBean, String> {
    @Query("FROM account a LEFT JOIN FETCH account_role role ON a.username = role.username WHERE a.username = :username")
    AccountBean findAccountWithRoles(String username);
}

Output

Hibernate: 
    select
        accountbea0_.username as username1_0_0_,
        accountrol1_.id as id1_1_1_,
        accountbea0_.is_active as is_activ2_0_0_,
        accountbea0_.last_login_date as last_log3_0_0_,
        accountbea0_.pw as pw4_0_0_,
        accountrol1_.username as username3_1_1_,
        accountrol1_.role_name as role_nam2_1_1_ 
    from
        account accountbea0_ 
    left outer join
        account_role accountrol1_ 
            on (
                accountbea0_.username=accountrol1_.username
            ) 
    where
        accountbea0_.username=?
Hibernate: 
    select
        roles0_.username as username3_1_0_,
        roles0_.id as id1_1_0_,
        roles0_.id as id1_1_1_,
        roles0_.username as username3_1_1_,
        roles0_.role_name as role_nam2_1_1_ 
    from
        account_role roles0_ 
    where
        roles0_.username=?

Answer 1

A better solution is to use name entity graphs. Entity graphs define what fields to fetch eagerly and you can reuse the same graph for multiple queries, even the queries automatically inferred by Spring Data from the repository method names. You can defined an named entity graph on top of your AccountBean entity where you would specify @NamedAttributeNode("roles") since you want it eagerly fetched. Then you'd do something like

accountRepo.findByName(myName, EntityGraph.EntityGraphType.LOAD, "theNameOfYourCustomEntityGraph");

Answer 2

Using JPQL, you don't need to specify the joining table and columns for join because you've already done that in your entities.

Your query should be like this:

@Query("FROM account a LEFT JOIN FETCH a.roles r WHERE a.username = :username")