updating multiple rows using JPA

问题:

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.

回答1:

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.

回答2:

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"

回答3:

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.