Hibernate Tips: Permanently remove records when using soft delete


Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.


Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please leave a comment below.

Question:

I found this question on StackOverflow and thought it’s a great fit for the Hibernate Tips series:

I want to implement soft deletion, but still be able to delete permanently. Is there any way to ignore a declared @SQLDelete() annotation?

Solution:

You can use the @SQLDelete annotation to specify a custom SQL statement that Hibernate executes when you delete an entity. You can use that to implement a soft delete feature, which only changes the state of a record and excludes it from your query results. You can see the required mapping annotations in the following code snippet. I explained the approach in more details in How to implement a soft delete with Hibernate.

@Entity
@SQLDelete(sql = "UPDATE book SET status = 'DELETED' WHERE id = ? AND version = ?", check = ResultCheckStyle.COUNT)
@Where(clause = "status <> 'DELETED'")
public class Book { ... }

When you call the remove method on your EntityManager, Hibernate will execute the SQL statement defined in the @SQLDelete operation.

You can’t deactivate the @SQLDelete annotation. So, if you want to remove the record from the database permanently, you can’t use the remove method of your EntityManager. You need to execute a SQL DELETE statement using a JPQL, Criteria or native query.

Here is an example of a JPQL DELETE statement:

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

// do something ...

// add this if you fetched the Book entity in this session
em.flush();
em.clear();

Query query = em.createQuery("DELETE Book b WHERE id = :id");
query.setParameter("id", 1L);
query.executeUpdate();

em.getTransaction().commit();
em.close();

In case you fetched the Book entity you want to remove within your current Hibernate Session, you need to call the flush and clear methods on your EntityManager before you execute the DELETE statement. This ensures that all pending changes are written to the database before you remove the record.

Learn more:

If you want to dive deeper, you should also take a look at the following articles:

Hibernate Tips Book

Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!