问题描述
我在用户和角色之间有很多联系.例如
I have many to many relation ship between User and Roles. For example
public class User {
@Id
private Integer id;
@ManyToMany
@JoinTable(name = "APP_USER_ROLE",
joinColumns = { @JoinColumn(name = "USER_ID") },
inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") })
private List<Role> roles = new ArrayList<Role>();
}
@Entity
public class Role {
@Id
private Integer id;
@ManyToMany(mappedBy = "roles")
private List<User> users = new ArrayList<User>();
}
我的要求是获取系统中所有具有其角色的用户.使用HQL时,我的查询是
My requirement is to fetch all users in the system with their roles. When using HQL using this my query is
select u, u.roles from User u
但是,这会触发每个具有相应角色的用户的查询.如果有100个用户,它将触发100个查询.但是在SQL中,我可以使用以下查询来实现它
however this fires a query for every user with corresponding roles. If there are 100 users, it will fire 100 queries. However in SQL I can achieve it using follwing query
select u.id,
u.name, m.roleId, r.name FROM User u left outer join UserRole m ON u.id = m.userId
inner join Roles r ON m.roleId = r.id
order by u.id;
我正在为用户进行左外部联接,因为有些用户没有关联的角色.我宁愿不使用本机SQL,因为它有其自身的缺点.有人可以帮我映射相应的HQL或JPQL查询吗?
I am doing left outer join for user as there are some user who have no associated roles. I prefer not to use native SQL as it has its own disadvantages. Could some one help me to map corresponding HQL or JPQL query?
推荐答案
尝试此查询
select u from User u left join fetch u.roles
这篇关于带有Hibernate的JPA-多对多关系,获取所有数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!