Bulk update using EntityManager / Hibernate

Question

I am using Hibernate and have (as an example) an Employee entity with 10000 entries. Let's assume 1000 of them have a salary of 4000 which I want to increase to 5000. I have difficulties to find an effective way to do this.

Doing it in code by getting all employees and updating the relevant entries seems ineffective, slow and also seems to cause "Batch update returned unexpected row count from update" errors in some situations.

So it seems to be better to use a native query as suggested here, but then you need to update the Hibernate cache to see the changes on the next request:

Employee e = em.find(Employee.class, <id ???>);
em.flush();
em.detach(e);
em.createNativeQuery("UPDATE employee SET salery = 5000 where salery = 4000") 
    .executeUpdate();

However, what I do not understand is how to detach the entities before issuing the native update query. The second argument to the em.find call seems to be the id of a single entity? But the Emloyees are used all over the whole application so how should I know which ones to detach? Is there a way to detach all (e.g. something like em.detachAll(Employee.class)) or am I supposed to iterate over all 1000 enployees in a loop and detach them individually?

I am also open for other solutions solving my initial problem.

SternK · Answer 1 · 2021-07-03T15:23:56.563

Doing it in code by getting all employees and updating the relevant entries seems ineffective, slow ...

As it's stated in the hibernate documentation:

Both the Hibernate native Query Language and JPQL (Java Persistence Query Language) provide support for bulk UPDATE and DELETE.

So, you can write the query like this:

int updatedEntities = entityManager.createQuery(
    "update Employee e " +
    "set e.salery = :newSalery " +
    "where e.salery = :oldSalery" )
.setParameter("oldSalery", 4000)
.setParameter("newSalery", 5000)
.executeUpdate();

Please also note that as it's stated in the Section 4.10 of the JPA 2.0 Specification care should be taken as to when an UPDATE or DELETE statement is executed:

Caution should be used when executing bulk update or delete operations because they may result in inconsistencies between the database and the entities in the active persistence context. In general, bulk update and delete operations should only be performed within a transaction in a new persistence context or before fetching or accessing entities whose state might be affected by such operations.

Bulk update using EntityManager / Hibernate

1 Answers1