updating multiple rows using JPA

Question

I want to update all fields of a table that has value of colum NAME as 'PCNAME'. The table name which i want to update is XYZ.I want to update only some fields and not keep some unchanged.

This will affect many rows and not a single row as there will be many rows with NAME='PCNAME' How can i do it using JPA.I have entity class associated with this table.

score 22 · Accepted Answer · answered Nov 01 '10 at 14:06

You can either do it the object oriented way or using an update query.

Object oriented:

public void setNameOfAllEntities(String newname){
    List<MyEntity> items =
        entityManager.createQuery("from MyEntity", MyEntity.class)
            .getResultList();
    for(MyEntity entity : items){
        entity.setName(newname);
    }
}

With Update Query (untested):

public void setNameOfAllEntities(final String newname){

    final int changes =
        entityManager.createQuery("update MyEntity set name = :name")
            .setParameter("name", newname)
            .executeUpdate();

    System.out.println(changes + " rows changed");

}

Obviously, the second version performs better.

score 18 · Answer 2 · edited May 23 '17 at 12:09

18

seanizer's answer is correct (+1) and a bulk update would be indeed nice for this use case. But you must take some precautions with bulk update operations. To paraphrase the JPA specification:

bulk updates bypass optimistic locking checks (so you must manually increment the version column and/or manually validate the version column if desired)
the persistence context is not synced with the result of bulk operations (so bulk operations should be performed in a separate transaction or at the very beginning of a transaction, before loading the state of any entity that might be affected).

My suggestion would thus be to at least increment the version column to avoid concurrency problem with other threads:

UPDATE XYZ xyz
SET xyz.name = :newname, xyz.version = xyz.version + 1

And to perform it in a separate transaction or before loading any XYZ as previously explained.

References

JPA 1.0 specification
- Section 4.10 "Bulk Update and Delete Operations"

edited May 23 '17 at 12:09

Community

1
1

answered Nov 01 '10 at 21:13

Pascal Thivent

562,542
136
1,062
1,124

Hi Pascal, do this answer and your comments aboves hold for JPA 2.0 ? my application needs to do some optimistic Locking checks. thanks in advance. – arthur Dec 02 '12 at 18:10
Hi @arthur, I just ran a bulk update on GlassFish 4.1/EclipseLink 2.5.2/JPA 2.1 and found that a version field (i.e. @Version) is used automatically in the bulk update, e.g. "UPDATE ... SET version = (version + ?), fieldToBeUpdate = ..." and "?" is bound to 1. I wonder why it is done that way, maybe it is foreseen that under certain circumstances more than one version counter increase can happen. But the danger wrt persistence context synchronization of course remains. – Hein Blöd Apr 02 '15 at 11:37

score 0 · Answer 3 · answered Dec 16 '19 at 15:15

Since Java Persistence 2.1 you can use CriteriaUpdate to do bulk updates using the Criteria API.

CriteriaUpdate<Entity> criteriaUpdate = builder.createCriteriaUpdate(Entity.class)
     .set(root.get("field"), value)
     .where(predicates);
int updated = entityManager.createQuery(criteriaUpdate).executeUpdate();

Keep in mind:

Criteria API bulk update operations map directly to database update operations, bypassing any optimistic locking checks. Portable applications using bulk update operations must manually update the value of the version column, if desired, and/or manually validate the value of the version column. The persistence context is not synchronized with the result of the bulk update.

updating multiple rows using JPA

3 Answers3

References

Linked