intermediate~3h

Soft Deletes — @SQLDelete, @SQLRestriction/@Where

Hibernate's dedicated soft-delete annotations move the pattern from something every developer must remember per query into something the ORM enforces automatically, everywhere, by default.

Learning objectives

Implement soft delete using @SQLDelete and @SQLRestriction/@Where together.
Explain why using only one of the two annotations leaves a real gap in the pattern.
Build a deliberate escape hatch for viewing soft-deleted rows when genuinely needed.

An earlier chapter mentioned soft delete as a general pattern — this chapter covers Hibernate's SPECIFIC annotations for it, which automate the pattern at the ORM layer rather than requiring you to remember a filter in every single query by hand.

📖 Story

Imagine a library that, instead of shredding a withdrawn book, moves it to a locked back room — still technically "in the system," recoverable, just invisible to anyone browsing the regular shelves. Here's the hand-rolled (risky) version:

customerRepository.delete(customer);   // gone. Permanently. No recovery.

Here's Hibernate's dedicated soft-delete mechanism:

@Entity
@SQLDelete(sql = "UPDATE customers SET deleted_at = now() WHERE id = ?")
@SQLRestriction("deleted_at IS NULL")
public class Customer {
    @Id @GeneratedValue
    private Long id;
    private Instant deletedAt;
}

Now entityManager.remove(customer) looks and behaves EXACTLY like a real delete from the application code's perspective — but the row is preserved, and every normal query automatically excludes it.

@SQLDelete — replaces the actual DELETE Hibernate would generate with a custom UPDATE. @SQLRestriction (formerly @Where in Hibernate 5) — automatically appends a filter excluding soft-deleted rows to EVERY query Hibernate generates for that entity.

Let's see exactly what happens with this chapter's Customer entity, step by step.

remove() now performs a soft delete, transparently

entityManager.remove(customer);
// Because of @SQLDelete, Hibernate runs:
//   UPDATE customers SET deleted_at = now() WHERE id = ?
// ...instead of a real DELETE. The row still physically exists.

Every OTHER query automatically excludes it, with zero extra code

List<Customer> all = customerRepository.findAll();
// Thanks to @SQLRestriction("deleted_at IS NULL"), the soft-deleted
// customer from above is NEVER in this list — no manual filter needed,
// anywhere, ever, in any derived query or JPQL you write.

The escape hatch — when you DO need to see soft-deleted rows

@Query(value = "SELECT * FROM customers WHERE id = :id", nativeQuery = true)
Optional<Customer> findByIdIncludingDeleted(@Param("id") Long id);
// A native query BYPASSES @SQLRestriction entirely — this is exactly
// how an admin "recover deleted item" feature would look up a soft-deleted row.

@SQLDelete works by Hibernate substituting your custom SQL string in place of the generated DELETE at flush time — the entity still transitions through the exact same "removed" lifecycle state from Chapter 5; only the actual SQL executed differs. @SQLRestriction works by Hibernate appending your specified condition directly into the WHERE clause of every generated SQL statement for that entity.

This chapter's exact Customer setup — a "trash/recycle bin" feature in almost any real application relies on precisely this pattern.
An e-commerce order history commonly soft-deletes cancelled orders rather than hard-deleting them, preserving them for accounting purposes.

Use @SQLDelete and @SQLRestriction together — this chapter's Customer example shows why using only one leaves a real gap.
Add a partial index on the not-deleted subset for any large, frequently-queried soft-deleted table.
Build a deliberate escape hatch, like this chapter's findByIdIncludingDeleted native query, for cases genuinely needing to see soft-deleted rows.

⚠️ Why this keeps happening

@SQLRestriction's automatic, invisible filtering is exactly what makes soft delete safe by default — but that same invisibility can confuse a developer who genuinely needs to see soft-deleted rows and doesn't realize a filter is silently active.

Forgetting @SQLRestriction and relying only on @SQLDelete — remove() correctly soft-deletes, but every other query still needs a manual filter.
Not indexing the deletion-flag column, letting the automatic filter degrade to a full scan on a large table.
Being surprised a native query bypasses @SQLRestriction entirely — exactly this chapter's escape-hatch example; native queries need their own explicit exclusion.

A partial index on the not-deleted subset (CREATE INDEX ... WHERE deleted_at IS NULL) keeps this chapter's automatically-injected filter cheap regardless of how many soft-deleted rows accumulate.

Soft-deleted data is still physically present — for genuinely sensitive data with a hard "right to erasure" requirement, soft delete alone may not be sufficient compliance.

Track the ratio of soft-deleted to active rows per table over time — a table where soft-deleted rows vastly outnumber active ones is worth reviewing for an archival policy.

Document, per entity using soft delete, the retention policy and the specific, intended escape hatches for viewing soft-deleted data.

Build this chapter's exact Customer entity with @SQLDelete and @SQLRestriction, call remove(), and confirm (via direct database inspection) the row still physically exists with deleted_at set.
Confirm findAll() no longer returns the soft-deleted customer.
Write this chapter's findByIdIncludingDeleted native query and confirm it CAN still see the soft-deleted row.

✓ Quick recap

@SQLDelete redirects what remove() actually executes — an UPDATE instead of a real DELETE, exactly this chapter's Customer example.
@SQLRestriction automatically excludes soft-deleted rows from every Hibernate-generated query — with no per-query filter needed.
Native queries bypass this automatic filter and need their own explicit exclusion.

Want a visual for this concept?

Generate a diagram tailored to “Soft Deletes — @SQLDelete, @SQLRestriction/@Where” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Bulk Updates & Batch Processing →← Back to all Spring Data JPA & Hibernate Mastery chapters