Soft deletes using Hibernate annotations.
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.
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.
- Added the @SQLDelete annotation which overrides the default Hibernate delete for that entity.
- 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
About Author
gilad bar orion May 4th
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.
Brian May 4th
Glad the post helped you. Never thought about using 2 Entity Managers. Will keep that in mind. Thanks for the tip.
Anitha May 25th
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.
Brian May 25th
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.
Anitha May 27th
Thanks Brian,
Its working as expected.. Thanks a lot
Brian May 27th
Glad to hear it is working Anitha.
John Logan July 23rd
@Where unfortunately does not work with entityManager.find()
Brian July 29th
John – Not sure on that one. I thought JPA supported the @Where but I will double check. Thanks for the heads up.
Shane August 13th
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?
Brian August 16th
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.
Shane August 16th
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.
Shane August 16th
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
Shane August 16th
See:http://opensource.atlassian.com/projects/hibernate/browse/HHH-4083
Brian August 17th
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.
Add Yours
YOU