JPA - Batch/Bulk Update - What is the better approach?

Question

I found that JPA does not support the following Update:

Update Person p set p.name = :name_1 where p.id = :id_1,
                    p.name = :name_2 where p.id = :id_2,
                    p.name = :name_3 where p.id = :id_3
                    .... 
                 // It could go on, depending on the size of the input. Could be in 100s

So I have two options:

Option 1:

Query q = em.createQuery("Update Person p set p.name = :name where p.id = :id");

For ( int x=0; PersonsList.length; x++ ) {
      // add name and id parameters
      em.executeUpdate(); 
}

Questions:

1. Is this all that's needed for Batch update? Anything else I need to add? I set hibernate.jdbc.batch_size", "20"
2. Is the optimistic lock enabled here by default? (I do not have @Version in my entity though)
3. What do I need to do in order to enforce Optimistic Locking, if not @Version?

Option 2:

Construct one single query using either Select Case syntax or with Criteria API

Questions:

1. Does the batching still happen here? (In a single big query)
2. Is this better than the 1st approach in terms of performance?
3. Whats the recommended approach out of these two options? Any other better approach?

Answer 1

In your question title, you mentioned Bulk Update and Delete, but you actually need batching this time.

Bulk Update and Delete are needed when you want to UPDATE/DELETE rows that all match the same filtering criteria which can be expressed in the WHERE clause.

Here, you need JDBC batch updates. You need to set the following configuration property:

<property name="hibernate.jdbc.batch_size" value="50"/>

If you do this, you can simply update the entities, and Hibernate will batch the UPDATE statements for you.

Option 1 is not very useful because it will generate N UPDATE statements that cannot be batched.

Option 2 is not very useful either because it will generate a very complex query, whose Execution Plan is probably more complex than executing everything in a simple batched UPDATE statement.

So, do it like this:

1. Fetch entities using pagination
2. Update them using Hibernate and let it do the batch update for you.

If you have many such entities, use query pagination.

When using bulk updates, Hibernate does not increment the version column used for optimistic locking automatically, as it does for regular entity updates, so you need to increment the version entity attribute explicitly.

Answer 2

You can update a list of object without iterate over all the collection.

List<Integer> demo = Arrays.asList(1,2,3,4);
final String update = "UPDATE User u SET u.enabled = true WHERE u.id IN (?1)";

return super.getEntityManager()
    .createQuery(update)
    .setParameter(1, ids)
    .executeUpdate();