Hibernate

Soft Delete in Hibernate 7: @SoftDelete vs @SQLDelete + @SQLRestriction

Hibernate 7’s native @SoftDelete (DELETED, ACTIVE and TIMESTAMP strategies) compared with the hand-rolled @SQLDelete + @SQLRestriction recipe, run against PostgreSQL 17 and H2: the generated SQL, restoring rows, the unique-index traps (partial indexes, NULLS NOT DISTINCT), associations and join tables, native queries that bypass the filter, and what Spring Data JPA does with it.

Ankur Oct 28, 2026 28 min read

Every application eventually gets the request “we deleted a customer by mistake, can you get them back?” The usual answer is a soft delete: instead of removing the row you mark it, and every query learns to ignore marked rows. For years that meant three hand-written pieces in every entity: an @SQLDelete that turns DELETE into an UPDATE, an @SQLRestriction (formerly @Where) that appends a predicate to reads, and a boolean column you had to keep in sync yourself. Hibernate now has a native annotation, @SoftDelete, that does all three from one line. It is thinly documented, and the interesting questions are not about the happy path: what happens to a unique email address after the user is deleted, what happens to the join table, whether a native query still sees the row, and what Spring Data does with it. This article answers those by running everything against a real PostgreSQL 17 and H2 and quoting the SQL Hibernate actually generated.

Versions tested. Hibernate ORM 7.4.14.Final (the newest 7.x GA on Maven Central on 2026-10-28; 7.4.0.Final was published 2026-05-26; 8.0.0.Beta4 is also listed and is not GA), Spring Boot 4.1.1 with Spring Data JPA 4.1.1 (Boot manages 7.4.5.Final; the module overrides it), PostgreSQL 17.11 via Testcontainers 2.0.5, H2 2.4.240, JDK 25 (Temurin 25.0.5+8). Everything is in the soft-delete module of the hibernate-demo repository; ./scripts/run-all.sh regenerates every transcript quoted below.

Correction to a common assumption: a timestamp strategy does exist. SoftDeleteType in the shipped jar has three values, ACTIVE, DELETED and TIMESTAMP. I inspected the jars with javap: 6.6.0.Final has only ACTIVE and DELETED; 7.0.0.Final and 7.4.14.Final have TIMESTAMP as well (other versions not checked).

A soft delete is an UPDATE plus a WHERE you must never forget

The smallest correct mental model has two halves. Writes: removing an entity must not run DELETE; it must flip a marker on the row. Reads: every query that touches the table must exclude marked rows, whether it is a find(), an HQL query, a join, or a collection that Hibernate loads for you. Everything in this article is a consequence of those two halves, plus one fact that is easy to forget: the row is still in the table. Unique indexes, foreign keys, native SQL and your DBA’s reporting tool all still see it.

The figure says what to expect: the ORM side is handled for you, the database side is not. The rest of the article is the list of places where those two sides meet.

Going deeper: the vocabulary

Official user guide: soft delete (type, column, conversion, entity, collection).
SoftDelete Javadoc.
Migrating from older versions: Hibernate 5 to 7 migration cheat sheet.

The smallest thing that works: one annotation

Put @SoftDelete on the entity. There is no deleted field in the class; Hibernate adds the column and owns it. Full file: ArticleDeleted.java.

@Entity
@Table(name = "article_deleted")
@SoftDelete
public class ArticleDeleted {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;

    protected ArticleDeleted() {}
    public ArticleDeleted(String title) { this.title = title; }

    public Long getId() { return id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
}

Persist two articles, remove one, then ask for it. The transcript (output/02-ddl-and-sql.txt, H2 half; the PostgreSQL half differs only in the identity insert syntax) shows the SQL that came out:

== SoftDeleteType.DELETED (default)
-- insert
   sql> insert into article_deleted (title,deleted,id) values (?,false,default)
-- remove(first)
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
   sql> update article_deleted set deleted=true where id=? and deleted=false
raw rows : [1 | first | true, 2 | second | false]
-- find(first) after delete -> null
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
-- HQL: select count(a) from ArticleDeleted a
   sql> select count(ad1_0.id) from article_deleted ad1_0 where ad1_0.deleted=false

Verbatim from output/02-ddl-and-sql.txt.

Three things are visible. The insert writes deleted=false as a literal. remove() first loads the entity (with the predicate) and then runs update ... set deleted=true where id=? and deleted=false. And find() of the removed row returns null because the predicate hides it; the raw row (1 | first | true) proves it is still in the table. The extra and deleted=false in the update is not decoration: when another session has already deleted the row, the update touches zero rows and Hibernate throws instead of pretending it worked.

== Deleting a row somebody else already soft-deleted
-- s1.flush()
   sql> update article_deleted set deleted=true where id=? and deleted=false
flush failed: OptimisticLockException -> StaleStateException: Unexpected row count (expected row count 1 but was 0) [update article_deleted set deleted=true where id=? and deleted=false]

Verbatim from output/02-ddl-and-sql.txt.

The test is A02_LifecycleSqlTest.java.

Going deeper: what the column looks like on each database

The DDL for all four mapping styles is in output/02-ddl-and-sql.txt. The boolean strategies emit deleted boolean not null; the timestamp strategy emits a nullable timestamp(6) with time zone on both H2 and PostgreSQL. A @SoftDelete annotation can also sit on a field (a collection, see the associations section), on a @MappedSuperclass (verified: output/07-bulk-and-sqldelete.txt) and, per its @Target, on a package; I did not test the package-level form.

Companion: A07_BulkAndSqlDeleteTest.java covers the mapped superclass and the converter.

Three strategies: DELETED, ACTIVE and TIMESTAMP

The strategy attribute chooses what the column means. Reading the shipped jar by reflection (output/01-api-shape.txt) gives the enum values and the annotation defaults:

== org.hibernate.annotations.SoftDeleteType
ACTIVE     default column name: active
DELETED    default column name: deleted
TIMESTAMP  default column name: deleted

Verbatim from output/01-api-shape.txt.

And the attributes of the annotation itself, from the same file:

== org.hibernate.annotations.SoftDelete attributes and defaults
comment      default = 
options      default = 
strategy     default = DELETED
converter    default = org.hibernate.annotations.SoftDelete$UnspecifiedConversion
columnName   default =

Verbatim from output/01-api-shape.txt.

Strategy	Column (default name)	Alive	Deleted	Entity
`DELETED` (default)	`deleted boolean not null`	`false`	`true`	ArticleDeleted.java
`ACTIVE`	`active boolean not null`	`true`	`false`	ArticleActive.java
`TIMESTAMP`	`deleted` by default, nullable timestamp (I used `deleted_at`)	`NULL`	a timestamp	ArticleStamped.java

The generated SQL for the other two strategies, from output/02-ddl-and-sql.txt:

== SoftDeleteType.ACTIVE
-- remove(x)
   sql> select aa1_0.id,aa1_0.title from article_active aa1_0 where aa1_0.active=true and aa1_0.id=?
   sql> update article_active set active=false where id=? and active=true
raw rows : [1 | x | false]

Verbatim from output/02-ddl-and-sql.txt.

== SoftDeleteType.TIMESTAMP
before delete: [1 | y | false]
-- insert
   sql> insert into article_stamped (title,deleted_at,id) values (?,null,default)
-- remove(y)
   sql> select as1_0.id,as1_0.title from article_stamped as1_0 where as1_0.deleted_at is null and as1_0.id=?
   sql> update article_stamped set deleted_at=localtimestamp where id=? and deleted_at is null

Verbatim from output/02-ddl-and-sql.txt.

The timestamp comes from the database clock. The update is set deleted_at=localtimestamp, not a value bound from the JVM. That is convenient (no clock skew between application nodes) and it means you cannot inject a fixed clock in a test or record who deleted the row; for that you still need auditing. The timestamp strategy also gives you the column that tells you when, which the boolean strategies cannot.

If the legacy schema stores the flag as 'Y'/'N', the converter attribute takes an AttributeConverter<Boolean, ?>. With Hibernate’s YesNoConverter and columnName = "is_gone" the generated column is char(1) not null check (is_gone in ('N','Y')) (transcript).

Going deeper: which one to pick

Pick TIMESTAMP when anyone will ever ask “when was this deleted?” or when you need to purge rows older than N days; pick DELETED for the simplest possible column. ACTIVE exists for schemas that already use an active flag. The strategy changes how unique constraints behave (next sections), which is the real deciding factor.

Tests: A01_ApiShapeTest.java, A02_LifecycleSqlTest.java.

What Hibernate filters, and what it cannot

Hibernate adds the predicate to everything it generates: find(), HQL and criteria queries, joins, and the collections it loads. It does not rewrite SQL you wrote. A native query sees every row, and a native query mapped to the entity class returns the deleted entity as a perfectly normal object. That is the one escape hatch for reading deleted rows, because there is no switch like @Filter to turn the predicate off.

The left side is shown in every transcript in this article. The right side, from output/03-restore-and-native.txt:

== Boolean strategy: the deleted row is still in the table
raw rows        : [1 | kept | true, 2 | alive | false]
-- HQL count
   sql> select count(ad1_0.id) from article_deleted ad1_0 where ad1_0.deleted=false
-- native count
   sql> select count(*) from article_deleted

== A native query mapped to the entity returns the deleted row
native select * ... id=1 -> 1 entity, title=kept

Verbatim from output/03-restore-and-native.txt.

The count through HQL is 1 and through native SQL is 2, from the same table. In a real application that difference shows up as a report that disagrees with the screen, or a nightly job that processes “deleted” customers because its author used native SQL for speed. Write a database view (create view live_article as select ... where not deleted) for the people and tools that do not go through Hibernate.

Going deeper: why there is no switch, and what to do instead

With @Filter you can enable and disable a condition per session. @SoftDelete and @SQLRestriction are static: they are part of the entity mapping and I found no API to disable them (I looked at the annotation’s attributes, shown above; I did not read the implementation). If an admin screen needs to browse deleted rows, either use native SQL as shown, or map a second, read-only entity onto the same table without @SoftDelete (@Immutable).

Interceptors and what bulk updates skip (related: some Hibernate hooks do not fire for bulk statements).
The first-level cache, which matters in the restore section next.

Restoring a row is a native UPDATE

“Undelete” sounds like it should be a method. It is not. The flag is not a mapped attribute, so HQL cannot name it; the only way back is a native statement. Here is the HQL attempt and the native fix, from output/03-restore-and-native.txt:

== HQL cannot restore: the flag is not an attribute
IllegalArgumentException -> PathElementException: Could not resolve attribute 'deleted' of 'com.ankurm.softdelete.ArticleDeleted'

== Native UPDATE restores it
rows updated: 1
-- find after restore
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?

Verbatim from output/03-restore-and-native.txt.

Two more details. First, the persistence context: a session that already loaded the entity keeps returning it after somebody else soft-deleted the row, until the session is cleared.

== Stale persistence context: a soft delete done behind the session's back
same session, row deleted by someone else: find -> still the entity (first-level cache)
after session.clear():                     find -> null

Verbatim from output/03-restore-and-native.txt.

Second, the timestamp strategy restores by setting the column back to NULL (update article_stamped set deleted_at = null where id = ?), and the transcript confirms the entity is findable again:

== Timestamp strategy: restore sets the column back to NULL
after delete  : [1 | true]
after restore : [1 | false]

Verbatim from output/03-restore-and-native.txt.

The hand-rolled recipe cannot even do this in HQL. With @SQLRestriction("deleted = false") the column is a mapped attribute, so HQL accepts update ArticleManual a set a.deleted = false, but Hibernate appends the restriction to the update too: ... where am1_0.id=? and (am1_0.deleted = false). It updated 0 rows in the transcript. Restoring is native SQL in both styles.

Going deeper: restoring a graph, not a row

Soft-deleting an entity with @SoftDelete also soft-deletes the rows of its owned collection tables (shown in the associations section), so restoring only the entity row leaves its many-to-many links hidden. Restore the join rows in the same transaction. A purge job has the opposite problem: it must delete children before parents.

Test: A03_RestoreAndNativeTest.java.
Optimistic vs pessimistic locking: the and deleted=false guard behaves like a version check for concurrent deletes.

Unique constraints: the trap that is not in the documentation

A user registers with [email protected], is soft-deleted, and registers again. Your table has a unique index on email. The old row is still there. Result, on PostgreSQL 17 (H2 gives the same failure, with its own message; see the file):

== 1. boolean strategy, unique (email)
registered and soft-deleted [email protected]; row is still there: [1 | [email protected] | true]
registering it again: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_email"

Verbatim from output/04-unique-constraints.txt.

The common fix is to widen the index to (email, deleted). It works exactly once: the second time the same email is deleted you have two rows with (bob, true).

== 2. boolean strategy, unique (email, deleted)
register, delete, register again:
   second registration OK, rows: [1 | [email protected] | true, 2 | [email protected] | false]
   deleting the second registration: FAILED ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_email_deleted"
   rows now: [1 | [email protected] | true, 2 | [email protected] | false]

Verbatim from output/04-unique-constraints.txt.

The fix that holds is an index that only covers live rows. PostgreSQL has partial indexes; Hibernate cannot express them in annotations, so create it in a migration (Flyway/Liquibase) or by hand. Register, delete, register, delete, register works, and a fourth live registration is still rejected:

== 3. partial unique index: create unique index ... (email) where deleted = false
index created
register/delete x2, then register a third time: OK, rows: [1 | [email protected] | true, 2 | [email protected] | true, 3 | [email protected] | false]
a fourth LIVE registration: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_account_live_email"

Verbatim from output/04-unique-constraints.txt.

With the timestamp strategy the natural idea is unique (email, deleted_at). It fails in the opposite direction, because NULL is never equal to NULL in a unique index: two live rows with the same email are accepted.

== 4. timestamp strategy, unique (email, deleted_at) -- NULLs are distinct
two LIVE rows with the same email were accepted (columns: id | email | deleted_at is null): [1 | [email protected] | true, 2 | [email protected] | true]

Verbatim from output/04-unique-constraints.txt.

On PostgreSQL 15 and later, NULLS NOT DISTINCT repairs it, and deleted rows stay distinct because their timestamps differ:

== 5. timestamp strategy, unique nulls not distinct (email, deleted_at)
second LIVE registration: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "ux_stamped_nnd"
delete / re-register / delete / re-register: OK (columns: id | email | deleted_at is null), rows: [1 | [email protected] | false, 3 | [email protected] | false, 4 | [email protected] | true]

Verbatim from output/04-unique-constraints.txt.

Index	Strategy	Re-register after delete	Delete twice	Two live duplicates blocked
`unique(email)`	any	fails	n/a	yes
`unique(email, deleted)`	boolean	works	fails on 2nd delete	yes
`unique(email, deleted_at)`	timestamp	works	works	no (NULLs distinct)
`unique(email) where deleted = false`	boolean	works	works	yes (PostgreSQL only; H2 rejects the syntax)
`unique nulls not distinct (email, deleted_at)`	timestamp	works	works	yes (PostgreSQL 15+; H2 rejects the syntax)

H2 cannot show you the fix. H2 2.4.240 rejected both where deleted = false and nulls not distinct with a syntax error (section 3, 5 and 6 of the H2 half of the transcript). A test suite on H2 will pass with the wrong index and fail in production. SQL Server has filtered indexes (create unique index ... where deleted = 0) and Oracle needs a function-based index; I did not run either, so check your own database.

Going deeper: choosing the index

Partial index on the live-row predicate is the best fit: smaller, and it documents intent. The same predicate makes a good partial index for lookups (where deleted = false) because most queries only want live rows. Whichever you pick, put it in a versioned migration, not in hibernate.hbm2ddl.auto.

PostgreSQL: partial indexes; CREATE INDEX (NULLS NOT DISTINCT).
Test: A04_UniqueConstraintTest.java; the full transcript is output/04-unique-constraints.txt.

Associations: what happens to children, parents and join tables

Soft-deleting a parent does not cascade unless you ask it to, and nothing in the database stops a child from pointing at a parent you consider deleted. Model: Author has many Book, all with @SoftDelete, no cascade. Delete the author and load a book:

== A. soft-delete the parent of a @OneToMany (no cascade)
-- remove(author)
   sql> select a1_0.id,a1_0.name from author a1_0 where a1_0.deleted=false and a1_0.id=?
   sql> update author set deleted=true where id=? and deleted=false
books: [1 | The Dispossessed | 1, 2 | Earthsea | 1]  authors: [1 | Le Guin | true]
-- load a book, touch its (soft-deleted) author
   sql> select b1_0.id,a1_0.id,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id and a1_0.deleted=false where b1_0.deleted=false and b1_0.id=?
book.getAuthor() == null ? true
-- HQL: select b.title from Book b join b.author a   (inner join)
   sql> select b1_0.title from book b1_0 join author a1_0 on a1_0.id=b1_0.author_id and a1_0.deleted=false where b1_0.deleted=false
rows: []
-- HQL: select b.title from Book b left join b.author a
   sql> select b1_0.title from book b1_0 where b1_0.deleted=false
rows: 2

Verbatim from output/05-associations.txt.

The book survives and book.getAuthor() is null: the to-one is loaded with left join author ... and a1_0.deleted=false, so the deleted author is simply absent, without an exception. An inner join in HQL drops the book entirely (rows: []), and a left join that selects nothing from the author is pruned from the SQL altogether. The same book therefore looks authorless, missing, or present depending on how you ask. Worse, the database is happy to link new rows to the deleted parent, because the physical row satisfies the foreign key:

== B. a NEW book can still point at the soft-deleted author
-- persist(new Book(.., getReference(Author, deletedId)))
   sql> insert into book (author_id,title,deleted,id) values (?,?,false,default)
books: [1 | The Dispossessed | 1, 2 | Earthsea | 1, 3 | Orphan | 1]

Verbatim from output/05-associations.txt.

Collections work the way you would hope: a soft-deleted child disappears from the parent’s collection (where b1_0.author_id=? and b1_0.deleted=false). Cascades fire soft deletes too: with CascadeType.ALL and orphanRemoval, removing an item from the list issues update item set deleted=true, and removing the shelf soft-deletes every live item before the shelf itself.

== F. CascadeType.ALL + orphanRemoval on a soft-deleted parent
-- remove an item from shelf.items (orphanRemoval)
   sql> select s1_0.id,s1_0.label from shelf s1_0 where s1_0.deleted=false and s1_0.id=?
   sql> select i1_0.shelf_id,i1_0.id,i1_0.name from item i1_0 where i1_0.shelf_id=? and i1_0.deleted=false
   sql> update item set deleted=true where id=? and deleted=false
-- remove(shelf)
   sql> select s1_0.id,s1_0.label from shelf s1_0 where s1_0.deleted=false and s1_0.id=?
   sql> select i1_0.shelf_id,i1_0.id,i1_0.name from item i1_0 where i1_0.shelf_id=? and i1_0.deleted=false
   sql> update item set deleted=true where id=? and deleted=false
   sql> update shelf set deleted=true where id=? and deleted=false
items: [1 | i1 | true, 2 | i2 | true]  shelves: [1 | true]

Verbatim from output/05-associations.txt.

Many-to-many: the join table is soft-deleted too

For a plain @ManyToMany between two @SoftDelete entities, Hibernate gives the join table a deleted column as well, filters on both the tag and the link row when loading, and soft-deletes the link rows when the owning entity is removed. Restoring only the book row therefore brings back a book with no tags:

== D. plain @ManyToMany to a soft-deleted Tag
DDL: [create table book_tag (deleted boolean not null, Book_id bigint not null, tags_id bigint not null, primary key (Book_id, tags_id));]
join table rows (book_id | tags_id | deleted): [6 | 1 | false, 6 | 2 | false]
-- book.getTags().size()
   sql> select b1_0.id,a1_0.id,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id and a1_0.deleted=false where b1_0.deleted=false and b1_0.id=?
   sql> select t1_0.Book_id,t1_1.id,t1_1.label from book_tag t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.Book_id=? and t1_1.deleted=false and t1_0.deleted=false
tags seen through the collection: 1
-- remove(book): what happens to its join-table rows?
   sql> select b1_0.id,a1_0.id,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id and a1_0.deleted=false where b1_0.deleted=false and b1_0.id=?
   sql> update book_tag set deleted=true where Book_id=? and deleted=false
   sql> update book set deleted=true where id=? and deleted=false
join table rows (book_id | tags_id | deleted): [6 | 1 | true, 6 | 2 | true]
-- restore ONLY the book row (update book set deleted = false), then book.getTags().size()
   sql> select b1_0.id,a1_0.id,a1_0.name,b1_0.title from book b1_0 left join author a1_0 on a1_0.id=b1_0.author_id and a1_0.deleted=false where b1_0.deleted=false and b1_0.id=?
   sql> select t1_0.Book_id,t1_1.id,t1_1.label from book_tag t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.Book_id=? and t1_1.deleted=false and t1_0.deleted=false
tags seen: 0   (the book_tag rows are still deleted = true)
after also restoring book_tag: tags seen = 1

Verbatim from output/05-associations.txt.

You can also put @SoftDelete on the collection field alone, so that removing a tag from reader.subscriptions soft-deletes the link row while the Reader and Tag entities stay normal. It has a sharp edge: the join table’s primary key does not include the flag, so subscribing to the same tag again fails.

== E. @SoftDelete on the COLLECTION (join-table rows)
DDL: [create table reader_subscription (deleted boolean not null, Reader_id bigint not null, subscriptions_id bigint not null, primary key (Reader_id, subscriptions_id));, comment on column reader_subscription.deleted is 'Soft-delete indicator';, alter table if exists reader_subscription add constraint FK8mnw2gfmlf0orqbofu5tgdq7p foreign key (subscriptions_id) references tag;, alter table if exists reader_subscription add constraint FKcmo9696q8cos9fjs9hgewj9f2 foreign key (Reader_id) references reader;]
join rows before: [1 | 3 | false]
-- reader.getSubscriptions().clear()
   sql> select r1_0.id,r1_0.name from reader r1_0 where r1_0.id=?
   sql> select s1_0.Reader_id,s1_1.id,s1_1.label from reader_subscription s1_0 join tag s1_1 on s1_1.id=s1_0.subscriptions_id where s1_0.Reader_id=? and s1_1.deleted=false and s1_0.deleted=false
   sql> update reader_subscription set deleted=true where Reader_id=? and deleted=false
join rows after : [1 | 3 | true]
-- subscribe to the same tag again
   sql> select r1_0.id,r1_0.name from reader r1_0 where r1_0.id=?
   sql> select t1_0.id,t1_0.label from tag t1_0 where t1_0.deleted=false and t1_0.id=?
   sql> select s1_0.Reader_id,s1_1.id,s1_1.label from reader_subscription s1_0 join tag s1_1 on s1_1.id=s1_0.subscriptions_id where s1_0.Reader_id=? and s1_1.deleted=false and s1_0.deleted=false
   sql> insert into reader_subscription (Reader_id,subscriptions_id,deleted) values (?,?,false)
result: ConstraintViolationException -> PSQLException: SQLState 23505, ERROR: duplicate key value violates unique constraint "reader_subscription_pkey"
join rows still: [1 | 3 | true]

Verbatim from output/05-associations.txt.

Lazy to-one towards a soft-deleted entity is rejected. @ManyToOne(fetch = LAZY) pointing at a @SoftDelete entity fails at startup, which is why Book.author above is eager. Presumably this is because a lazy proxy cannot know whether the row it stands for is hidden; the message itself only says the combination is unsupported. Adding @NotFound(action = IGNORE) was accepted at startup in my test (transcript); I did not investigate what fetching it then does, so do not assume it gives you laziness.

LazyChild -> UnsupportedMappingException: To-one attribute (com.ankurm.lazytest.LazyEntities$LazyChild.author) cannot be mapped as LAZY as its associated entity is defined with @SoftDelete

Verbatim from output/06-lazy-to-one.txt.

Finally, a purge job that does a real DELETE of a soft-deleted parent hits the foreign key from its soft-deleted (or live!) children:

== G. purging a soft-deleted parent that still has children
physical DELETE of the soft-deleted author: ConstraintViolationException -> PSQLException: SQLState 23503, ERROR: update or delete on table "author" violates foreign key constraint "fkklnrv3weler2ftkweewlky958" on table "book"

Verbatim from output/05-associations.txt.

Going deeper: association checklist

Decide per relationship whether deleting the parent should cascade; the default is that it does not, and children point at a hidden parent.
Use a foreign-key-aware purge order (children first), and purge only rows that are soft-deleted and old.
Remember that @ManyToMany is usually better modelled as two @OneToMany with an explicit link entity, which also lets you put your own columns on the relationship: @ManyToMany is almost always wrong.
Test: A05_AssociationsTest.java; transcripts output/05-associations.txt, output/06-lazy-to-one.txt.

@SoftDelete versus @SQLDelete + @SQLRestriction

Both do the same job for a single em.remove(); the SQL is almost identical. The differences are in the corners, and the largest one is the bulk delete. ArticleManual.java is the hand-rolled version, ArticleDeleted.java the native one. The same HQL delete from X against each (output/07-bulk-and-sqldelete.txt):

== 1. HQL bulk delete: delete from ArticleDeleted / delete from ArticleManual
-- delete from ArticleDeleted
   sql> update article_deleted ad1_0 set deleted=true where ad1_0.deleted=false
article_deleted rows: [1 | a | true, 2 | b | true]
-- delete from ArticleManual
   sql> delete from article_manual am1_0 where (am1_0.deleted = false)
article_manual rows : []

Verbatim from output/07-bulk-and-sqldelete.txt.

@SQLDelete does not apply to HQL bulk deletes. delete from ArticleManual ran a real DELETE and the rows are gone for good; delete from ArticleDeleted became an UPDATE. If your codebase uses the hand-rolled recipe, every deleteAllInBatch() and every @Modifying @Query("delete ...") is a hard delete.

The second difference is concurrency. The hand-written update has no and deleted = false, so deleting a row that someone else already deleted succeeds silently, where the native annotation raises the stale-state exception shown earlier:

== 2. Two sessions delete the same row (second one is stale)
-- @SQLDelete entity, s1.flush()
   sql> update article_manual set deleted = true where id = ?
flush succeeded silently (the hand-written UPDATE has no 'and deleted = false' guard)
(the @SoftDelete entity fails the same scenario with StaleStateException: see 02-ddl-and-sql.txt)

Verbatim from output/07-bulk-and-sqldelete.txt.

	`@SoftDelete`	`@SQLDelete` + `@SQLRestriction`
Column	managed by Hibernate, not an attribute	ordinary mapped attribute
`em.remove()`	update … and deleted=false	your SQL, no guard
HQL bulk delete	UPDATE	real DELETE
Strategies / converter	boolean, inverted boolean, timestamp, converter	whatever you write
HQL can restore	no (column is not an attribute)	no (restriction rewrites the update)
Arbitrary predicate (tenant, status)	no	yes, `@SQLRestriction` takes any SQL
Join-table rows	soft-deleted with the owner	you write `@SQLDelete` per collection

Going deeper: when the old recipe is still right

Keep @SQLRestriction when the condition is not a delete flag at all (tenant id, status != 'ARCHIVED'), or when you need a column the annotation does not model, such as deleted_by set in the same statement. @SoftDelete has no attribute for extra columns (see the attribute list above); a hand-written @SQLDelete can set them.

Test: A07_BulkAndSqlDeleteTest.java.

Spring Data JPA: what deleteById, findById and count do now

Spring Data sits on the entity manager, so it inherits the behaviour wholesale. The repository (ArticleRepository.java) is an ordinary JpaRepository:

    /** Derived delete: Spring Data loads the matching entities, then calls em.remove on each. Needs a transaction (plain Spring Data behaviour, not soft-delete specific). */
    @Transactional
    long deleteByTitle(String title);

    /** HQL bulk delete: @SoftDelete turns it into an UPDATE. */
    @Transactional
    @Modifying
    @Query("delete from ArticleDeleted a where a.title = :title")
    int bulkDeleteByTitle(String title);

    /** Native SQL: the soft-delete predicate is NOT added. */
    @Query(value = "select count(*) from article_deleted", nativeQuery = true)
    long countIncludingDeleted();

    @Query(value = "select * from article_deleted where id = :id", nativeQuery = true)
    Optional<ArticleDeleted> findEvenIfDeleted(Long id);

    /** The only way to un-delete: the flag is not an attribute, so HQL cannot name it. */
    @Transactional
    @Modifying
    @Query(value = "update article_deleted set deleted = false where id = :id", nativeQuery = true)
    int restore(Long id);

Calling it (test A08_SpringDataTest.java, transcript output/08-spring-data.txt, H2 with Hibernate 7.4.14.Final):

-- count()
   sql> select count(*) from article_deleted ad1_0 where ad1_0.deleted=false
-- deleteById(1)
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
   sql> update article_deleted set deleted=true where id=? and deleted=false
-- findById(1)
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
isPresent = false
-- existsById(1)
   sql> select count(*) from article_deleted ad1_0 where ad1_0.id=? and ad1_0.deleted=false
existsById = false

Verbatim from output/08-spring-data.txt.

deleteById is a select followed by the soft-delete update, findById and existsById see nothing, and count() counts live rows only. Derived deletes behave the same way: deleteByTitle loads the matching live rows and soft-deletes each one.

-- deleteByTitle("x")
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.title=? and ad1_0.deleted=false
   sql> update article_deleted set deleted=true where id=? and deleted=false
returned 1

Verbatim from output/08-spring-data.txt.

Deleting something already deleted is a silent no-op (a select, no exception), which is Spring Data’s normal behaviour:

== deleting something that is already (soft) deleted
-- deleteById(1) again
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
no exception
-- delete(staleEntity) with the instance saved earlier
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
no exception

Verbatim from output/08-spring-data.txt.

The surprising one is save(). A detached instance whose row has been soft-deleted looks like an update, so Spring Data treats it as existing and merges it; the merge cannot find the row and fails with an optimistic-locking error, not a duplicate-key error and not an insert:

== save() of a detached instance whose row is soft-deleted
-- save(saved.get(0))
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
result: ObjectOptimisticLockingFailureException -> StaleObjectStateException: Row was already updated or deleted by another transaction for entity [com.ankurm.softdelete.ArticleDeleted with id '1']

Verbatim from output/08-spring-data.txt.

The bulk and native methods show the same split as before: the HQL @Query("delete ...") is rewritten to an update, while the native count sees the deleted rows (1 live, 4 total in the run below), and a native @Modifying query is the restore:

count() = 1, countIncludingDeleted() = 4

Verbatim from output/08-spring-data.txt.

-- native restore(1)
   sql> update article_deleted set deleted = false where id = ?
rows updated: 1
-- findById(1) after restore
   sql> select ad1_0.id,ad1_0.title from article_deleted ad1_0 where ad1_0.deleted=false and ad1_0.id=?
isPresent = true

Verbatim from output/08-spring-data.txt.

Two Spring Data details unrelated to soft delete but easy to blame on it. Derived delete methods and @Modifying queries need a transaction; the repository interface above carries @Transactional on them, because without it Spring throws No EntityManager with actual transaction available (I hit that while writing the test). And derived queries in Spring Data JPA 4 are now JPQL, see Derived Queries Are Now JPQL.

Going deeper: Spring Data notes

deleteAll() selects the live rows and soft-deletes each; deleteAllInBatch() is one update ... set deleted=true where deleted=false (both in the transcript).
The Spring layer was tested on H2 only; the Hibernate behaviour underneath is the same on PostgreSQL as shown in the other transcripts, but I did not run Spring Data against PostgreSQL.
A JpaSpecificationExecutor, projections and @EntityGraph were not tested.

Should you soft-delete at all?

Often the honest answer is no. Soft delete makes every unique index, every foreign key, every report and every migration aware of a flag, forever. If the real requirement is “keep an audit trail” or “undo for 30 days”, an audit/history table, Hibernate Envers, or moving the row to an archive table gives you that without poisoning the live table. If you do soft-delete: prefer @SoftDelete over the hand-rolled recipe (it handles bulk deletes and concurrent deletes correctly), choose TIMESTAMP if anyone will ever ask when, put a partial unique index in a migration and test it on the real database rather than H2, decide explicitly what a delete does to children and join rows, and give reporting tools a view.

Soft Delete in Hibernate 7: @SoftDelete vs @SQLDelete + @SQLRestriction

A soft delete is an UPDATE plus a WHERE you must never forget

The smallest thing that works: one annotation

Three strategies: DELETED, ACTIVE and TIMESTAMP

What Hibernate filters, and what it cannot

Restoring a row is a native UPDATE

Unique constraints: the trap that is not in the documentation

Associations: what happens to children, parents and join tables

Many-to-many: the join table is soft-deleted too

@SoftDelete versus @SQLDelete + @SQLRestriction

Spring Data JPA: what deleteById, findById and count do now

Should you soft-delete at all?

Further reading

Related

No Comments yet!

Leave a ReplyCancel reply