Hibernate: how to fetch only not-logically deleted

Question

Nearly every table in our database has a FK to the Auditing table which logs created, updated and deleted status (date and username).

We mapped the auditing table to the Auditing class and use it like this:

@MappedSuperclass
public class BusinessObject extends DataObject {

    private static final long serialVersionUID = -1147811010395941150L;

    @OneToOne(fetch = FetchType.EAGER, cascade = { CascadeType.ALL })
    @JoinColumn(name = "AUD_ID")
    private AuditingObject auditing;
...

As you'd expect, nearly every entity extends from BusinessObject.

Is there an easy way of saying, for every businessObject, only receive "auditing.deleted is null".

I've tried adding a @Where and @WhereJoinTable in the businessObject but this doesn't seem to work as I expect.

Currently, i've done this to one of my queries and this works, but I'd hate to do this for all queries since we have about 150.

@NamedQuery(
    name="allCountries",
    query="SELECT c FROM Country c"
        + " LEFT JOIN FETCH c.labelDefinition "
        + " LEFT JOIN FETCH c.labelDefinition.translations "
        + " WHERE c.auditing.deleted is null"
        + " ORDER BY c.code"
)

Answer 1

IMO, the easiest way to implement a soft-delete would be to add a flag in your entities and to use:

• the @SQLDelete annotation to override the default Hibernate delete (and perform an update of the flag)
• the @Where (or @Filters?) annotation on your entities and associations to filter the deleted entities

Not sure how this can fit with your Auditing table though. Some further exploration and testing are required.

Resources

• Soft deletes using Hibernate annotations