Soft deletes using Hibernate annotations.

blog-exampleI 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
//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;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
//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.



Comments

  1. gilad bar orion May 4th

    Comment Arrow

    Hi
    Great post! wasn’t aware of ‘@sqlDelete’, thanks!

    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.


  2. Brian May 4th

    Comment Arrow

    Glad the post helped you. Never thought about using 2 Entity Managers. Will keep that in mind. Thanks for the tip.


  3. Anitha May 25th

    Comment Arrow

    Hi,
    I have a same scenario. I want use soft delete in my seam application and i’m new to hibernate and seam. After reading your post i got to know that using hibernate annotations one can provide soft delete functionality.

    I understood how to use the annotations in java files. But should i be making any changes in any of the *.xhtml files? I’m not able to proceed. Please help me by explaining in detail.


  4. Brian May 25th

    Comment Arrow

    Anitha – That is all you have to do. Now when you delete one of the entities Hibernate will set the flag rather than deleting the item from the database.

    You should never try and implement this logic in the view or controller. It should always be in the model.


  5. Anitha May 27th

    Comment Arrow

    Thanks Brian,

    Its working as expected.. Thanks a lot


  6. Brian May 27th

    Comment Arrow

    Glad to hear it is working Anitha.


  7. John Logan July 23rd

    Comment Arrow

    @Where unfortunately does not work with entityManager.find()


  8. Brian July 29th

    Comment Arrow

    John – Not sure on that one. I thought JPA supported the @Where but I will double check. Thanks for the heads up.


  9. Shane August 13th

    Comment Arrow

    If you are using a version property that value will be added to the params so your annotation will look something like this:

    @SQLDelete(sql=”UPDATE table_name SET hidden = ‘1′, updated = ? WHERE id = ?”)

    Unfortunately the params are in the wrong order i.e. the id is first and the version is second…do you know any way to fix this?


  10. Brian August 16th

    Comment Arrow

    Shane – I may be understanding correctly what you asking but:

    The application must not alter the version number set up by Hibernate in any way. To artificially increase the version number, check in Hibernate Entity Manager’s reference documentation LockModeType.OPTIMISTIC_FORCE_INCREMENT or LockModeType.PESSIMISTIC_FORCE_INCREMENT.


  11. Shane August 16th

    Comment Arrow

    Brian – there are 2 params being passed into the prepared statement – in my case a Date (I am using a date for version) and a Long (the primary key of the record).

    If you use sql=”UPDATE table_name SET hidden = ‘1′ WHERE id = ?” there is a param index error thrown. So you have to use both of the params – they are just out of order to be of much help. The Date is second so it must be in or after the WHERE clause. Here is what I ended up with.

    sql=”UPDATE client SET hidden = 1, updated = now() where id = ? and 0 ?”

    It works fine but I was hoping there would be either a fix in Hibernate or another way of reordering the params.


  12. Shane August 16th

    Comment Arrow

    the not equals sign was zapped out of my last post.
    sql=”UPDATE client SET hidden = 1, updated = now() where id = ? and 0 != ?” to replace the operator with java


  13. Shane August 16th

    Comment Arrow

    See:http://opensource.atlassian.com/projects/hibernate/browse/HHH-4083


  14. Brian August 17th

    Comment Arrow

    Shane – I always use database triggers for the DATE_CREATED and DATE_UPDATED fields in the database. I have it is as a standard for all of my tables. Then I don’t have to worry about it Hibernate or in my code.


  15. Hari September 28th

    Comment Arrow

    Will this feature avail the multiple table update? say the parent and the join/link tables which uses manttomany relationship.

    Do update on how to proceed with the new concept.


  16. Brian September 30th

    Comment Arrow

    Hari – Thanks for stopping by. I believe it should work fine if you set up the soft deletes on your relationships. If you want the children to be removed with the parent then turn on cascading.


  17. Andres February 23rd

    Comment Arrow

    Brian, thanks a lot for your post. It is very useful, indead.
    I’m using mapping files instead of annotations. Do you know if it still possible to achieve this behavior?
    Thanks!


  18. Brian February 24th

    Comment Arrow

    Andres – It has been a while since I used mapping files but I believe you should be able to do the following.

    The “where” is an attribute of the class tag and “sql-delete” as a child element of class.

    Try this.

    1
    2
    3
    <class where="deleted <> '1'" ...>
      <sql-delete>UPDATE app_user SET deleted = '1' WHERE id = ?</sql-delete>
    </class>

  19. Andres February 24th

    Comment Arrow

    Brian, thanks a lot for your reply!

    This worked like a charm for me.

    BTW I also used the where attribute in some set tags to achieve what I needed.

    Best,


  20. Brian February 24th

    Comment Arrow

    Glad to hear it worked for you Andres. Thanks for letting me know.


  21. Rupali March 23rd

    Comment Arrow

    Hi Brain,

    Its a nice post. In my application I have soft delete and hard delete feature. Can I use same hibernate mapping to hard delete the records?


  22. Mohammad March 23rd

    Comment Arrow

    Hi Brian!

    Do @SQLDelete & @Where work on superclass? For example, I have a super class containing the ‘deleted’ field which all of my models extend this super class. I do have more than 200 models and I do not want to annotate all of my models with @SQLDelete and @Where.

    Is there any way? Or What can you suggest?

    Many Thanks!


  23. Brian March 23rd

    Comment Arrow

    @Rupali,

    I don’t think you can use both on the same table using mappings. If you need to be able to do both soft and hard on the same table you will probably have to handle it in your code. So for a soft delete you would have to set the flag yourself and a hard delete hibernate can take care of and just delete the record. You can still use the @Where though to filter the soft deletes.


  24. Brian March 23rd

    Comment Arrow

    @Mohammad – I have never tried that but if the mapping is in the parent class anything that extends it should get its behavior. Look at the”Table Per Concrete Class” mapping scheme. I think that will work for you.


  25. Selman August 5th

    Comment Arrow

    The same problem with Shane. sql=”UPDATE table_name SET hidden = ‘1′ WHERE id = ?” throws “invalid param index”.


  26. Brian August 5th

    Comment Arrow

    Selman – I am assuming you have both ‘hidden’ and ‘id’ fields in your domain class file?


  27. John B. November 15th

    Comment Arrow

    Hi,

    Great post! Thanks for the information.

    I was wondering if there is anyway to use the @SQLDelete in a generic way so that I could use it in a mapped superclass?

    I have a base entity that I want to use for all my entities, and I need the soft delete for each as well. I was hoping there was a way to setup the query so that it updates the correct table.

    Thanks,
    John


  28. John B. November 15th

    Comment Arrow

    Sorry, I guess my question is the exact same as Mohammad’s


  29. James November 19th

    Comment Arrow

    Thank you, Brian. You’ve saved my life. I was banging my head against the wall to make soft-delete work. Thanks!!!!


  30. Brian November 19th

    Comment Arrow

    You are welcome James. Glad it helped.


  31. Ustun November 23rd

    Comment Arrow

    Thanks, I got it working after setting the delete field to 0 manually. Do you know of a way to set the newly added `delete’ field to 0? Simply putting private char delete = 0 or ‘0′ did not put in the new fields. (I used the following: update media set deleted=0 where deleted is null; ) do you know of a simpler way?


  32. Flemming Behrend December 30th

    Comment Arrow

    Hi

    Another way to implement soft delete is with an interceptor.

    I tried this method first but got into trouble when having catalog prefix before my table name.

    What I did instead was to implement a SoftDeleteInterceptor that intercepted the prepared stmt and modified it to an update stmt.

    This will of course make it impossible to delete anything, but if that is the preferred behaviour it will work.

    public class SoftDeleteInterceptor extends EmptyInterceptor {

    private static final long serialVersionUID = 1L;

    @Override
    public String onPrepareStatement(String sql) {
    System.err.println(sql);
    if (sql.startsWith(“delete from”)) {
    String softDeleteSql = sql.replace(“delete from”, “update”).replace(“where”, “set c_rcd_del = ‘Y’ where”);
    return super.onPrepareStatement(softDeleteSql);
    }
    return super.onPrepareStatement(sql);
    }

    }

    To activate the interceptor add the following to persistence.xml


  33. Flemming Behrend December 30th

    Comment Arrow

    property name=”hibernate.ejb.interceptor” value=”dk.topdanmark.td.core.persistence.SoftDeleteInterceptor”


  34. Brian January 9th

    Comment Arrow

    Thanks for sharing Flemming.


  35. Brian January 9th

    Comment Arrow

    Ustun – You should be able to set the field to 0 in the Constructor.


  36. Neo August 24th

    Comment Arrow

    Hi,Brian:
    My case is, I have relationship table such as enterprise_id,user_id,status ,there is no unique primary key ‘id’ exists in the table. Hence in my Entity I use annotation likes
    @SQLDelete (sql=”update entepriseuser set status=’1′ where enterprise_id=? and user_id=?”);
    Is that correct? what is the correct parameter order?


  37. Brian October 7th

    Comment Arrow

    Neo – Couldn’t you just add an id field that Hibernate handles to your join table? It has been a while so I can’t remember off hand if the code you posted would work.


Add Yours

  • Author Avatar

    YOU


Comment Arrow



About Author

Brian

Brian Abston is a Java Developer in Oklahoma who also loves technology.