Hibernate @SQLDelete sql not adding schema

Question

I am trying to use the @SQLDelete annotation of Hibernate to make soft deletion. It works well when the DB schema is static, i.e: passing it in the SQL.
Unfortunately, it seems the SQL is passed as is to EntityPersisters (cf EntityClass's method CustomSQL createCustomSQL(AnnotationInstance customSqlAnnotation) so I can't find a way to pass the schema name dynamically like in Native SQL queries using {h-schema}
Did anyone find a good workaround for this issue (I am using Hibernate 4.3.5)?

Edit: Unless there is a real solution, I ended up modifying the code source of org.hibernate.persister.entity.AbstractEntityPersister by replacing the schema placeholder when setting the custom SQL queries in method doLateInit.

Edit2: I have created an issue for this behaviour in Hibernate JIRA. I will create a pull request later today and I wish the Hibernate Team will accept it

Answer 1

Soft deletes using Hibernate annotations.

As linked author stated below:

I am currently working on a Seam application that has a need for soft deletes in the database. To the right you can see a snippet of my database diagram which contains a CUSTOMER and APP_USER table. This is just a straight forward one to many relationship but the important thing to note though is the “DELETED” field in each table. This is the field that will be used to track the soft delete. If the field contains a ‘1’ the record has been deleted and if it contains a ‘0’ the record hasn’t been deleted.

Before ORMs like Hibernate I would have had to track and set this flag myself using SQL. It wouldn’t be super hard to do but who wants to write a bunch of boilerplate code just to keep track of whether or not a record has been deleted. This is where Hibernate and annotations comes to the rescue.

Below are the 2 Entity classes that were generated by Hibernate using seamgen. I have omitted parts of the code for clarity.

Customer.java

//Package name...

//Imports...

@Entity
@Table(name = "CUSTOMER")
//Override the default Hibernation delete and set the deleted flag rather than deleting the record from the db.
@SQLDelete(sql="UPDATE customer SET deleted = '1' WHERE id = ?")
//Filter added to retrieve only records that have not been soft deleted.
@Where(clause="deleted <> '1'")
public class Customer implements java.io.Serializable {
    private long id;
    private Billing billing;
    private String name;
    private String address;
    private String zipCode;
    private String city;
    private String state;
    private String notes;
    private char enabled;
    private char deleted;
    private Set appUsers = new HashSet(0);

    // Constructors...

    // Getters and Setters...

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "customer")
    // Filter added to retrieve only records that have not been soft deleted.
    @Where(clause = "deleted <> '1'")
    public Set getAppUsers() {
        return this.appUsers;
    }

    public void setAppUsers(Set appUsers) {
        this.appUsers = appUsers;
    }
}

AppUser.java

//Package name...

//Imports...

@Entity
@Table(name = "APP_USER")
//Override the default Hibernation delete and set the deleted flag rather than deleting the record from the db.
@SQLDelete(sql="UPDATE app_user SET deleted = '1' WHERE id = ?")
//Filter added to retrieve only records that have not been soft deleted.
@Where(clause="deleted <> '1'")
public class AppUser implements java.io.Serializable {
    private long id;
    private Customer customer;
    private AppRole appRole;
    private char enabled;
    private String username;
    private String appPassword;
    private Date expirationDate;
    private String firstName;
    private String lastName;
    private String email;
    private String phone;
    private String fax;
    private char deleted;
    private Set persons = new HashSet(0);

    // Constructors...

    // Getters and Setters...
}

The following 2 steps is all that I had to do to implement the soft delete.

1. Added the @SQLDelete annotation which overrides the default Hibernate delete for that entity.
2. Added the @Where annotation to filter the queries and only return records that haven’t been soft deleted. Notice also that in the CUSTOMER class I added an @Where to the appUsers collection. This is needed to fetch only the appUsers for that Customer that have not been soft deleted.

Viola! Now anytime you delete those entities it will set the “DELETED” field to ‘1’ and when you query those entities it will only return records that contain a ‘0’ in the “DELETED” field.

Hard to believe but that is all there is to implementing soft deletes using Hibernate annotations.

Note:

also note that instead of using the @Where(clause="deleted ‘1’") statements you can use hibernate filter (http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#entity-hibspec-filters) to globally filter-out all ‘deleted’ entities. I found that defining 2 entity managers (‘normal’ one that filter deleted items, and one that doesn’t, for the rare cases…) is usually quite convenient.

Using EntityPersister

You can create a DeleteEventListener such as:

public class SoftDeleteEventListener extends DefaultDeleteEventListener {

/**
 * 
 */
private static final long serialVersionUID = 1L;

@Override
public void onDelete(DeleteEvent event, Set arg1) throws HibernateException {
    Object o = event.getObject();
    if (o instanceof SoftDeletable) {
        ((SoftDeletable)o).setStatusId(1);
        EntityPersister persister = event.getSession().getEntityPersister( event.getEntityName(), o);
        EntityEntry entityEntry = event.getSession().getPersistenceContext().getEntry(o);
        cascadeBeforeDelete(event.getSession(), persister, o, entityEntry, arg1);

        cascadeAfterDelete(event.getSession(), persister, o, arg1);

    } else {
        super.onDelete(event, arg1);
    }
}
}

hook it into your persistence.xml like this

<property name = "hibernate.ejb.event.delete" value = "org.something.SoftDeleteEventListener"/> 

Also, don't forget to update your cascades in your annotations.

Resource Link:

1. Hibernate: Overwrite sql-delete with inheritace
2. Custom SQL for CRUD operations
3. Custom SQL for create, update and delete

Answer 2

Use like this

@SQLDelete(sql = "UPDATE {h-schema}LEAVE  SET STATUS = 'DELETED' WHERE id = ?", check = ResultCheckStyle.COUNT)

Answer 3

I think there are 2 way First is to add:

app.datasource.schema=<schema_name> 

to your application.properties.

The second is to use the schema in annotation to your table model