conditional join fetch in hibernate

Question

I'm using Spring 5.1.8 in Restful Spring MVC project. I have used soft delete and enable flag for some entity. For example consider the following 2 entities:

@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;
    }
}

and

@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;
    }
}

As you can see, my relations are LAZY and therefore I need to use "Join Fetch" query to read both entities from database. On the other hand I can't use "ON clause" in "Join Fetch" query. For example consider the following HQL query:

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

Now, assume I want to consider soft delete and enable flag.

Q: How can I update this query to read only enabled and not deleted roles?

I already tried Left Join without fetch which do not fetch role entity.

I already tried @Where(clause = "deleted = false") above SecurityRoleEntity class which do not work.

I already tried @Where(clause = "deleted = false") after @JoinColumn clause in SecurityUserRealmRoleEntity class which do not work.

I already tried @Filter(name = "deleteCondition", condition = "DELETED = false") in past two situations which do not work.

Answer 1

I'm not aware of a pure Hibernate solution. I talk about that in my Blog post.

But there is a FluentJPA solution that you may consider:

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());
});

This produces the following SQL: (I deliberately put enabled and deleted condition to WHERE for clarity)

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))

You may read about ManyToMany concept in FluentJPA here.