Getting table aliases correctly in Hibernate Interceptor

Question

I need to add soft delete feature for all entities that my application manages. In order to accomplish that I created a base entity class(MappedSuperclass) with deleted field as below:

@MappedSuperclass
public abstract class BaseVersionableEntity {

    @Id
    @Basic(optional = false)
    @NotNull
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID", nullable = false)
    protected Long id;

    @Version
    @Column(name = "VERSION", nullable = false)
    protected Long version;

    @Basic
    @Column(name = "DELETED")
    protected boolean deleted;

    public boolean isDeleted() {
        return deleted;
    }

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

    public Long getId() {
        return id;
    }

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

    public Long getVersion() {
        return version;
    }

    public void setVersion(Long version) {
        this.version = version;
    }

    // equals/hashCode

}

By using hibernate's interceptor mechanism I configured a soft delete interceptor in order to catch all queries that hibernate executes:

public class SoftSelectInterceptor extends EmptyInterceptor{

    private static final long serialVersionUID = 1L;

    @Override
    public String onPrepareStatement(String sql) {

        if(sql.startsWith("select")){
            sql = sql.replace("where ","where deleted=0 and ");
        }

        return super.onPrepareStatement(sql);
    }
}

I added below interceptor config to persistence.xml file

<property name="hibernate.ejb.interceptor" value="com.my.organization.SoftSelectInterceptor"/> 

Note that there is a SoftDeleteInterceptor as well. Works with the same way.

When I execute this code hibernate catches select statement calls in onPeraparedStatement method but it adds a table alias in front of the table fields. That is if table name is reportTemplate, hibernate give this table reportTemplate0 alias. So my replace statement needs to be like below:

sql = sql.replace("where ","where reportTemplate0.deleted=0 and ");

I can parse the sql string and try to infer correct table aliases. However in complex sql statements (when same entity used multiple times) it would be hard to infer.

I wonder if there is a better way to do this.

Answer 1

I see three options:

1. Use Hibernate Filters. Major disadvantage: You have to attach the filter for every entity.

   @Entity
   // The FilterDef can be declared at package level as well:
   @FilterDef(name="alive", defaultCondition="deleted = 0")
   // Turn on the filter for our entity:
   @Filter(name="alive")
   public class MyEntity { ... }
   

2. Use a SQL parser (there are enough out there), modify the parsed result and recreate the SQL. Major disadvantage: There are enough not so trivial statements (including subselects) to modify. You have to be sure to add the restriction only to affected tables.

3. Use a service architecture with a base service, where you always use the same methods for loading/finding entities, which builds the correct query. Major disadvantage: You can't use named queries.

Answer 2

You could map the deletable entities to database views which select from the original tables with the filter condition (deleted = 0).

EDIT

Unfortunately, it's impossible to do it the way you are trying to.

1) There is no way to exactly predict what the aliases will look like, especially with complex queries with multiple joins and subqueries. Even if you analyze the source code, it is not part of the public API and as such is subject to change.

However, if you decide to take that endeavor (analyze the Hibernate source for query parsing and SQL generation), keep in mind that you will probably spend your entire summer doing only that since it is a complex and huge code base and a few very important feature requests (like left joins for non-associated entities) are waiting for years for it to be refactored.

2) The queries (and subqueries) without the where clause will not exclude deleted rows (since your replacement will find no matches):

select c from MyEntity;

3) Less likely for where combination of letters but still possible, where might be part of an identifier or a text constant and your replacement would make the query invalid. Suppose someone adds two fields to a Trip entity: fromWhere and toWhere. Maybe not a good naming example, but you get the point.

You could solve it by replacing only occurrences of the whole where word, but then imagine a query like:

select ... from ... where aColumn = 'A constant with the where word in it'