Skip to content
javahacks.dev
Hibernate

How to Do Bulk Updates and Deletes in JPA Correctly

Run bulk update and delete queries in JPA and Spring Data, clear the stale persistence context, keep versions right and delete huge tables in chunks.

7 min read
Summarize in AI
Flat illustration of an entity class mapped to a database table, with the Hibernate, Spring Boot, MariaDB, MySQL and Liquibase logos.

Looping over entities to change them is the natural JPA style, and it is slow for large sets. Updating ten thousand products with findAll() and a setPrice loop loads ten thousand objects, tracks each in the persistence context, takes a snapshot of each and issues ten thousand UPDATE statements. A single statement does the same job in one round trip. That statement is the bulk update, and it comes with rules that matter more than the syntax. This post shows how to write bulk updates and deletes in JPQL and Spring Data, what they skip, the stale data trap and how to delete a huge table safely. Related background is in dirty checking and when updates happen and Hibernate's first level cache and flush modes. For the case where you need entity behaviour for a large import instead, Hibernate StatelessSession is the alternative.

The statements

JPQL supports UPDATE and DELETE directly against an entity:

src/main/java/dev/javahacks/catalog/ProductRepository.javajava
package dev.javahacks.catalog;

import org.springframework.data.jpa.repository.*;
import org.springframework.data.repository.query.Param;

import java.time.Instant;

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("update Product p set p.price = p.price * :factor where p.category = :category")
    int adjustPrices(@Param("category") String category, @Param("factor") java.math.BigDecimal factor);

    @Modifying(clearAutomatically = true)
    @Query("delete from Product p where p.discontinuedAt < :cutoff")
    int purgeDiscontinuedBefore(@Param("cutoff") Instant cutoff);
}
java
@Transactional
public int blackFriday() {
    return products.adjustPrices("books", new BigDecimal("0.80"));
}

@Modifying is required on any Spring Data query that is not a SELECT, otherwise Spring Data tries to read a result and fails. The method returns int, the number of rows the database reports as changed, which is a free sanity check: assert it is not zero when you expect rows. Turn on SQL logging and you see exactly one statement:

Text
update product set price=(price*?) where category=?

The stale persistence context

Here is the trap. A bulk statement goes straight to the database and knows nothing about entities your transaction already loaded. Suppose the same transaction loaded a product first:

java
@Transactional
public void demo() {
    Product p = products.findById(1L).orElseThrow();      // price 100.00, now managed
    products.adjustPrices("books", new BigDecimal("0.50"));  // database now says 50.00
    System.out.println(p.getPrice());                       // still 100.00
}

The entity in memory is out of date, and worse, if you modify it afterwards and the transaction flushes, Hibernate will write its stale copy back. The attributes of @Modifying exist for this. flushAutomatically = true flushes pending entity changes before the statement runs, so the bulk statement sees them. clearAutomatically = true clears the persistence context afterwards, so the next read hits the database. Clearing detaches every managed entity, which has its own consequence: any entity you hold a reference to is now detached, and changes to it will not be saved unless you merge it. In a short service method that does the bulk update and returns, this is harmless. In a long method that loads entities, then updates, then keeps working on those entities, restructure the code so the bulk statement is the first or last step.

An alternative to clearing everything is to refresh the specific entities you care about with entityManager.refresh(p).

What a bulk statement skips

Everything that Hibernate normally does for entities is bypassed, because Hibernate never sees an entity:

FeatureRuns for bulk update or delete?
Persistence context updateNo: stale until cleared
Dirty checkingNot applicable
Cascade (CascadeType.REMOVE, orphan removal)No: the database's foreign keys decide
@PreUpdate, @PostRemove callbacksNo
Auditing (@LastModifiedDate)No: set the column yourself
Hibernate Envers historyNo
@Version incrementNo, unless you ask
Soft delete rewritingDepends on version; verify the SQL

The cascade point causes the most real failures. delete from Order o where o.status = 'CANCELLED' will fail with a foreign key violation if order lines reference those orders and the database constraint has no ON DELETE CASCADE, because JPA's cascade only works when it deletes entities one by one. Either delete the children first with their own bulk statement, or define ON DELETE CASCADE in the schema deliberately.

java
@Modifying
@Query("delete from OrderLine l where l.order.status = 'CANCELLED'")
int deleteLinesOfCancelled();

@Modifying
@Query("delete from PurchaseOrder o where o.status = 'CANCELLED'")
int deleteCancelled();

Run them in that order, in one transaction.

Versioned entities and audit columns

An entity with @Version relies on the version increasing on every change, so optimistic locking detects conflicts. A bulk update does not bump it, which means a concurrent user could save over your bulk change without a conflict. HQL has a keyword for this:

java
@Modifying
@Query("update versioned Product p set p.price = :price where p.category = :category")
int setPrice(@Param("category") String category, @Param("price") BigDecimal price);

update versioned makes Hibernate add version = version + 1 to the statement. For audit columns, write them yourself in the SET clause: set p.price = :price, p.updatedAt = :now. Pass the time as a parameter rather than calling the database's function, so tests can control it.

Derived deleteBy methods are not bulk

A frequent mistake is assuming Spring Data's derived delete is a bulk delete:

java
long deleteByCategory(String category);     // looks like one statement

It is not. It runs a SELECT for the matching entities and then deletes each one individually, so a thousand matches means a thousand DELETE statements, which is what makes cascades and callbacks work. That is correct when you need them and slow when you do not. For large sets prefer an explicit @Modifying @Query("delete ..."), and when you do use the derived form, know that it loads every entity.

Deleting a huge table in chunks

One DELETE that removes forty million rows holds locks for the whole statement, writes forty million rows to the transaction log, can blow the undo or WAL space and, if it fails at the end, rolls everything back. Delete in bounded chunks instead, each in its own short transaction. JPQL has no LIMIT on a delete, so use a native query that selects keys in a subquery:

java
@Modifying
@Query(value = """
        delete from audit_event
        where id in (select id from audit_event where created_at < :cutoff order by id limit :batch)
        """, nativeQuery = true)
int deleteChunk(@Param("cutoff") Instant cutoff, @Param("batch") int batch);
src/main/java/dev/javahacks/audit/AuditPurger.javajava
@Service
public class AuditPurger {
    private final AuditEventRepository events;
    private final TransactionTemplate tx;

    public AuditPurger(AuditEventRepository events, PlatformTransactionManager tm) {
        this.events = events;
        this.tx = new TransactionTemplate(tm);
    }

    public long purgeOlderThan(Instant cutoff) {
        long total = 0;
        int deleted;
        do {
            deleted = tx.execute(status -> events.deleteChunk(cutoff, 5_000));
            total += deleted;
        } while (deleted > 0);
        return total;
    }
}

Each loop iteration commits, so locks release between chunks, the log can be recycled and an interruption loses only the current chunk. The chunk size is a trade-off: smaller chunks hold locks for less time but cost more round trips; a few thousand rows is a sensible starting point, and you can pause between chunks to leave room for other traffic. On PostgreSQL, deleted rows become dead tuples until vacuum runs, so a very large purge is followed by a vacuum, and a table that is purged by age is often better partitioned so you can drop an old partition instead of deleting rows at all.

Testing

Because bulk statements bypass the persistence context, assert against the database, not the entity you hold:

java
@Test
@Transactional
void adjustPricesChangesTheDatabaseAndClearsStaleState() {
    Long id = products.save(new Product("Java Book", "books", new BigDecimal("100.00"))).getId();

    int updated = products.adjustPrices("books", new BigDecimal("0.80"));

    assertThat(updated).isEqualTo(1);
    assertThat(products.findById(id).orElseThrow().getPrice()).isEqualByComparingTo("80.00");
}

The test relies on clearAutomatically to force the second findById to read the new value. Remove that attribute and the assertion fails with 100.00, which is a good way to see the stale context trap yourself.

When this does not apply

If each row needs logic, a lifecycle callback or an event, loop over entities in batches or use the application code path, because a bulk statement skips those. If the update depends on values from another system, you cannot express it as one statement. And for a soft delete setup where delete must become an update of a flag, check that your Hibernate version rewrites the bulk delete; the approach is described in soft deletes with Hibernate. The primary sources are the Jakarta Persistence specification on bulk update and delete and the Spring Data JPA reference on modifying queries.

Taggedhow to
Share

Keep reading

Get new posts by email

We send one email when a new post is published. No spam.

Unsubscribe in one click. No spam, ever.