我有两个实体
@Entity
@Table(name = "users")
public class User extends Model {
public String name;
@ManyToMany
public List<Role> roles;
}
和
@Entity
@Table(name = "roles")
public class Role extends Model {
public String name;
}
表格包含
Table Users | | Table Roles | |Table Users_roles |
id | Name | | id | Name | |users_id | roles_id |
1 | User 1 | | 1 | CEO | | 2 | 1 |
2 | User 2 | | 2 | Manager | | 2 | 2 |
3 | User 3 | | 3 | 1 |
我想进行以下查询(可以使用PostgreSql 9.1.12)
SELECT u.*, array_to_string(array_agg(r.name ORDER BY r.name ASC), ' / ')
FROM users u
LEFT OUTER JOIN users_roles ur ON u.id=ur.users_id
FULL JOIN roles r ON r.id=ur.roles_id
GROUP BY u.id, ur.users_id
ORDER BY array_to_string ASC
我有想要的结果
User name | roles
User 1 |
User 3 | CEO
User 2 | CEO / MANAGER
您可以帮我获取此请求的JPA语法吗?
最佳答案
无法通过JPQL查询直接做到这一点。但是您可以在User实体中只有一个方法,该方法返回角色名称的字符串,并使用其角色获取用户:
List<User> users = em.createQuery("select u from User u left join fetch u.roles")
.getResultList();
并使用Guava的Joiner在User.java中:
public String getUserNames() {
List<String> roleNames = new ArrayList<String>();
for (Role role : roles) {
roleNames.add(role.getName();
}
return Joiner.on(" / ").join(roleNames);
}