My entities are user and roles. There is many-to-many relation between them.Fetch of role is lazy in user.
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "users_roles",
joinColumns = @JoinColumn(name = "user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id"))
@BatchSize(size = 3)
private Set<Role> roles = new HashSet<>();
and i want to get user and rules data in just one query. so that i try to get this data with JPQL. My user repository is
@Query(value = "select u from User u left join u.roles where u.username=:username")
Optional<User> findByUserNameWithRoles(@Param("username") String username);
I log my query with datasource-proxy and i have two queries but i did left join for one query. Why dont it do with one query?
my logs are
Name:Db Connection, Connection:4, Time:1, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["select user0_.id as id1_1_, user0_.created_by as created_2_1_, user0_.created_date as created_3_1_, user0_.last_modified_by as last_mod4_1_, user0_.last_modified_date as last_mod5_1_, user0_.email as email6_1_, user0_.first_name as first_na7_1_, user0_.password as password8_1_, user0_.status as status9_1_, user0_.surname as surname10_1_, user0_.user_uuid as user_uu11_1_, user0_.username as usernam12_1_ from users user0_ left outer join users_roles roles1_ on user0_.id=roles1_.user_id left outer join roles role2_ on roles1_.role_id=role2_.id where user0_.username=?"]
Params:[(yusuf)]
-
Name:Db Connection, Connection:4, Time:1, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["select roles0_.user_id as user_id1_2_1_, roles0_.role_id as role_id2_2_1_, role1_.id as id1_0_0_, role1_.role_name as role_nam2_0_0_ from users_roles roles0_ inner join roles role1_ on roles0_.role_id=role1_.id where roles0_.user_id=?"]
Params:[(1)]
