MySQL Hibernate 命名查询 - 加入 3 个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8399379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Named Query - join 3 tables
提问by BraginiNI
I have 3 beans: Organization, Role, User
我有 3 个 bean：组织、角色、用户
Role - Organization relation - @ManyToOne
角色 - 组织关系 - @ManyToOne
Role - User relation - @ManyToMany
角色 - 用户关系 - @ManyToMany
Organization :
组织 ：
@Entity
@Table(name = "entity_organization")
public class Organization implements Serializable {
private static final long serialVersionUID = -646783073824774092L;
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
Long id;
String name;
@OneToMany(targetEntity = Role.class, mappedBy = "organization")
List<Role> roleList;
...
Role :
角色 ：
@Entity
@Table(name = "entity_role")
public class Role implements Serializable {
private static final long serialVersionUID = -8468851370626652688L;
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
Long id;
String name;
String description;
@ManyToOne
Organization organization;
...
User :
用户：
@Entity
@Table(name = "entity_user")
public class User implements Serializable {
private static final long serialVersionUID = -4353850485035153638L;
@Id
@GeneratedValue(strategy = GenerationType.TABLE)
Long id;
@ManyToMany
@JoinTable(name = "entity_user_role",
joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
List<Role> roleList;
...
So I need to get all Organizations for specified User ( first I need to select all user roles and than select all organizations that have this roles)
所以我需要获取指定用户的所有组织（首先我需要选择所有用户角色，然后选择所有具有此角色的组织）
I have an sql statement that realizes this logic ( for e.g. I choose user with id = 1):
我有一个实现这个逻辑的 sql 语句（例如，我选择了 id = 1 的用户）：
SELECT * FROM entity_organization AS o
INNER JOIN entity_role r ON r.organization_id = o.id
INNER JOIN entity_user_role ur ON ur.role_id=r.id
WHERE ur.user_id = 1
How can I implement this, using hibernate named query mechanism? Thanks!
如何使用休眠命名查询机制实现这一点？谢谢！
回答by Kohányi Róbert
@NamedQuery
@NamedQuery
I've created the following @NamedQueryon the Organizationentity class.
我@NamedQuery在Organization实体类上创建了以下内容。
@NamedQuery(name = "query", query = "SELECT DISTINCT o " +
"FROM Organization o, User u " +
"JOIN o.roles oRole " +
"JOIN u.roles uRole " +
"WHERE oRole.id = uRole.id AND u.id = :uId")
public class Organization { ...
(I used standard JPA annotations, but my provider was Hibernate.)
（我使用了标准的 JPA 注释，但我的提供者是 Hibernate。）
Test
测试
This is the test I ran.
这是我运行的测试。
EntityManager em = ...
TypedQuery<Organization> q = em.createNamedQuery("query", Organization.class);
q.setParameter("uId", 1); // try it with 1L if Hibernate barks about it
for (Organization o : q.getResultList())
System.out.println(o.name);
Using the tables and sample data below, this outputs
使用下面的表格和示例数据，输出
A
B
Please see if it works for you.
请看看它是否适合你。
Tables
表
CREATE TABLE `organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`organization_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);
CREATE TABLE `user_has_role` (
`user_id` int(11) NOT NULL DEFAULT '0',
`role_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`,`role_id`)
);
ALTER TABLE `role` ADD CONSTRAINT `cst_organization_id`
FOREIGN KEY `fk_organiztaion_id` (`organization_id`)
REFERENCES `organization` (`id`);
(I've used a bit different from yours, but it shouldn't matter too much.)
（我使用的与你的有点不同，但应该没有太大关系。）
Sample data
样本数据
`organization`
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
+----+------+
`role`
+----+------+-------------+-----------------+
| id | name | description | organization_id |
+----+------+-------------+-----------------+
| 1 | A | a | 1 |
| 2 | B | b | 1 |
| 3 | C | c | 2 |
+----+------+-------------+-----------------+
`user`
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
`user_has_role`
+---------+---------+
| user_id | role_id |
+---------+---------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 3 | 1 |
| 3 | 3 |
+---------+---------+
回答by ManuPK
Try the HQL as below:
尝试 HQL 如下：
select ur.roleList.organization from User ur where ur.id = 1
It will give you the List<Organization>.
它会给你List<Organization>。
