java JPA 连接多列

JPA Join on multiple columns

提问by Johanneke

I have entities User, Organization and GrantedRole. Their relation is that a GrantedRole defines the role a User has in an Organization. All three entities inherit a field 'id' (annotated with @Id) from their superclass. Organization has no references to GrantedRole or User, those relations are unidirectional.

我有实体 User、Organization 和 GrantedRole。它们的关系是 GrantedRole 定义了用户在组织中的角色。所有三个实体都从它们的超类继承了一个字段“id”（用@Id 注释）。组织没有对 GrantedRole 或 User 的引用，这些关系是单向的。

@Entity
@Table(name = "role_assignments", uniqueConstraints = @UniqueConstraint(columnNames = {
    "user_id", "organization_id" }))
public class GrantedRole extends DomainEntity implements GrantedAuthority {

    public static enum Role {
        ROLE_MODM_USER, ROLE_MODM_ORGADMIN, ROLE_MODM_ADMIN
    }

    private User user;
    private Role role;
    private Organization organization;

    public static enum Role {
        ROLE_MODM_USER, ROLE_MODM_ORGADMIN, ROLE_MODM_ADMIN
    }

    @ManyToOne
    @NotNull
    public User getUser() {
        return user;
    }

    @NotNull
    @Enumerated(EnumType.STRING)
    public Role getRole() {
        return role;
    }

    @ManyToOne
    public Organization getOrganization() {
        return organization;
    }

    // Some setters
}

@Entity
@Table(name = "modmUsers")
public class User extends DomainEntity implements UserDetails {
    // Some other fields

    private Set<GrantedRole> roles = new HashSet<GrantedRole>();
    private Set<Organization> organizations = new HashSet<Organization>();

    @OneToMany(fetch = FetchType.EAGER, mappedBy = "user")
    public Set<GrantedRole> getRoles() {
        return roles;
    }

    @ManyToMany(fetch = FetchType.EAGER)
    public Set<Organization> getOrganizations() {
        return organizations;
    }

    // Some setters
}

Now I want to use JPA Criteria to find the Organizations in which a User has the role ROLE_MODM_ORGADMIN. But the generated query joins on a single column, and that leads to repetition of the rows from GrantedRole, because User.id is not unique within GrantedRole, nor is Organization.id. The combination of both is unique.

现在我想使用 JPA Criteria 来查找用户具有角色 ROLE_MODM_ORGADMIN 的组织。但是生成的查询连接在单个列上，这会导致来自 GrantedRole 的行重复，因为 User.id 在 GrantedRole 中不是唯一的，Organization.id 也不是。两者的结合是独一无二的。

My code for finding the Organizations:

我用于查找组织的代码：

public List<Organization> findOrganizationsByOrgAdminUser(String 
    CriteriaBuilder cb = entityManager.
    CriteriaQuery<Organization> query = cb.createQuery(Organization.
    Root<User> root = query.from(User.class);
    SetJoin<User, Organization> joinOrg = root.joinSet("organizations");
    SetJoin<User, GrantedRole> joinRoles = root.joinSet("roles");

    Predicate p1 = cb.equal(root.get("id"), userId);
    Predicate p2 = cb.equal(joinRoles.get("role"), Role.ROLE_MODM_ORGADMIN);

    query.select(joinOrg);
    query.where(cb.and(p1, p2));

    return entityManager.createQuery(query).getResultList();
}

The generated query is:

生成的查询是：

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
WHERE
    users.id=? 
    and roles.role=?

The query I would want is:

我想要的查询是：

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
        AND orgs.id=roles.organization_id //how to do this???
WHERE
    users.id=? 
    and roles.role=?

回答by Johanneke

Ok, so my colleague helped me out and led me to this query:

好的，所以我的同事帮助了我并引导我进行了以下查询：

SELECT
    orgs.* 
FROM
    modmUsers users
INNER JOIN
    modmUsers_organizations u_o 
        ON users.id=u_o.modmUsers_id 
INNER JOIN
    organizations orgs
        ON u_o.organization_id=orgs.id 
INNER JOIN
    role_assignments roles 
        ON users.id=roles.user_id 
WHERE
    users.id=? 
    AND roles.role=?
    AND orgs.id=roles.organization_id

Which is a simple extension of my code:

这是我的代码的简单扩展：

public List<Organization> findOrganizationsByOrgAdminUser(String 
    CriteriaBuilder cb = entityManager.
    CriteriaQuery<Organization> query = cb.createQuery(Organization.
    Root<User> root = query.from(User.class);
    SetJoin<User, Organization> joinOrg = root.joinSet("organizations");
    SetJoin<User, GrantedRole> joinRoles = root.joinSet("roles");

    Predicate p1 = cb.equal(root.get("id"), userId);
    Predicate p2 = cb.equal(joinRoles.get("role"), Role.ROLE_MODM_ORGADMIN);
    Predicate p3 = cb.equal(joinOrg.get("id"), joinRoles.get("organization"));

    query.select(joinOrg);
    query.where(cb.and(p1, p2, p3));

    return entityManager.createQuery(query).getResultList();
}