java - hibernate 中的条件连接获取

Question

我在 Restful Spring MVC 项目中使用 Spring 5.1.8。我对某些实体使用了软删除和启用标志。例如，考虑以下 2 个实体:

@Entity
@Table(name = "SECURITY_USER_REALM_ROLE", schema = "BARBANETUSER")
public class SecurityUserRealmRoleEntity {
    private int id;
    private int userId;
    private int realmId;
    private int roleId;

    private UserPersonEntity user;
    private SecurityRealmEntity realm;
    private SecurityRoleEntity role;

    @Id
    @Column(name = "ID_PK")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SecurityUserRealmRole_Sequence")
    @SequenceGenerator(name = "SecurityUserRealmRole_Sequence", sequenceName = "SECURITY_USER_REALM_ROLE_SEQ", allocationSize = 1)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "USER_ID_FK")
    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    @Basic
    @Column(name = "REALM_ID_FK")
    public int getRealmId() {
        return realmId;
    }

    public void setRealmId(int realmId) {
        this.realmId = realmId;
    }

    @Basic
    @Column(name = "ROLE_ID_FK")
    public int getRoleId() {
        return roleId;
    }

    public void setRoleId(int roleId) {
        this.roleId = roleId;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "USER_ID_FK", referencedColumnName = "ID_PK", insertable = false, updatable = false)
    public UserPersonEntity getUser() {
        return user;
    }

    public void setUser(UserPersonEntity user) {
        this.user = user;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "REALM_ID_FK", referencedColumnName = "ID_PK", insertable = false, updatable = false)
    public SecurityRealmEntity getRealm() {
        return realm;
    }

    public void setRealm(SecurityRealmEntity realm) {
        this.realm = realm;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ROLE_ID_FK", referencedColumnName = "ID_PK", insertable = false, updatable = false)
    public SecurityRoleEntity getRole() {
        return role;
    }

    public void setRole(SecurityRoleEntity role) {
        this.role = role;
    }
}

和

@Entity
@Table(name = "SECURITY_ROLE", schema = "BARBANETUSER")
public class SecurityRoleEntity {
    private int id;
    private RoleTypeEnum type;
    private boolean manageView;
    private String title;
    private String slug;
    private Integer sortOrder;
    private boolean enabled;
    private boolean deleted;

    private Set<SecurityPermissionEntity> permissions;

    @Id
    @Column(name = "ID_PK")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SecurityRole_Sequence")
    @SequenceGenerator(name = "SecurityRole_Sequence", sequenceName = "SECURITY_ROLE_SEQ", allocationSize = 1)
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "TYPE_ID_FK")
    @Convert(converter = RoleTypeConverter.class)
    public RoleTypeEnum getType() {
        return type;
    }

    public void setType(RoleTypeEnum type) {
        this.type = type;
    }

    @Basic
    @Column(name = "MANAGE_VIEW")
    public boolean isManageView() {
        return manageView;
    }

    public void setManageView(boolean manageView) {
        this.manageView = manageView;
    }

    @Basic
    @Column(name = "TITLE")
    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Basic
    @Column(name = "SLUG")
    public String getSlug() {
        return slug;
    }

    public void setSlug(String slug) {
        this.slug = slug;
    }

    @Basic
    @Column(name = "SORT_ORDER")
    public Integer getSortOrder() {
        return sortOrder;
    }

    public void setSortOrder(Integer sortOrder) {
        this.sortOrder = sortOrder;
    }

    @Basic
    @Column(name = "ENABLED")
    public boolean isEnabled() {
        return enabled;
    }

    public void setEnabled(boolean enabled) {
        this.enabled = enabled;
    }

    @Basic
    @Column(name = "DELETED")
    public boolean isDeleted() {
        return deleted;
    }

    public void setDeleted(boolean deleted) {
        this.deleted = deleted;
    }

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
    @JoinTable(name = "SECURITY_ROLE_PERMISSION", schema = "BARBANETUSER", joinColumns = @JoinColumn(name = "ROLE_ID_FK", referencedColumnName = "ID_PK", nullable = false),
            inverseJoinColumns = @JoinColumn(name = "PERMISSION_ID_FK", referencedColumnName = "ID_PK", nullable = false))
    public Set<SecurityPermissionEntity> getPermissions() {
        return permissions;
    }

    public void setPermissions(Set<SecurityPermissionEntity> permissions) {
        this.permissions = permissions;
    }
}

如您所见，我的关系是惰性的，因此我需要使用“Join Fetch”查询从数据库中读取两个实体。另一方面，我不能在“Join Fetch”查询中使用“ON 子句”。例如，考虑以下 HQL 查询:

SELECT roleRealm FROM SecurityUserRealmRoleEntity roleRealm LEFT JOIN FETCH roleRealm.role role LEFT JOIN FETCH role.permissions

现在，假设我想考虑软删除和启用标志。

问:如何更新此查询以只读启用且未删除的角色？

我已经尝试过不使用fetch的Left Join，它不会获取角色实体。

我已经在 SecurityRoleEntity 类上面尝试过 @Where(clause = "deleted = false") 但不起作用。

我已经在 SecurityUserRealmRoleEntity 类中的 @JoinColumn 子句之后尝试了 @Where(clause = "deleted = false") ，但不起作用。

我已经在过去的两种情况下尝试了 @Filter(name = "deleteCondition", condition = "DELETED = false") 但不起作用。

Answer 1

我不知道有纯粹的 Hibernate 解决方案。我在我的 Blog post 中谈到了这一点.

但是有一个FluentJPA您可以考虑的解决方案:

FluentQuery query = FluentJPA.SQL((SecurityUserRealmRoleEntity roleRealm,
                                   SecurityRoleEntity role,
                                   JoinTable<SecurityRoleEntity, SecurityPermissionEntity>
                                                                 rolesToPermissions,
                                   SecurityPermissionEntity permission) -> {
    SELECT(roleRealm, permission.getId());
    FROM(roleRealm).JOIN(role)
            .ON(roleRealm.getRole() == role)
            .JOIN(rolesToPermissions)
            .ON(rolesToPermissions.join(role, SecurityRoleEntity::getPermissions))
            .JOIN(permission)
            .ON(rolesToPermissions.inverseJoin(permission,
                                            SecurityRoleEntity::getPermissions));

    WHERE(role.isEnabled() && !role.isDeleted());
});

这会产生以下 SQL:(为了清楚起见，我特意将 enabled 和 deleted 条件放在 WHERE 中)

SELECT t0.*, t3.ID_PK 
FROM BARBANETUSER.SECURITY_USER_REALM_ROLE t0  INNER JOIN BARBANETUSER.SECURITY_ROLE t1
  ON (t0.ROLE_ID_FK = t1.ID_PK)  INNER JOIN BARBANETUSER.SECURITY_ROLE_PERMISSION t2
  ON (t2.ROLE_ID_FK = t1.ID_PK)  INNER JOIN BARBANETUSER.SECURITY_PERMISSION t3
  ON (t2.PERMISSION_ID_FK = t3.ID_PK) 
WHERE (t1.ENABLED AND NOT(t1.DELETED))

您可以阅读 FluentJPA 中的 ManyToMany 概念 here .