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();

Answer 3

If you are going to use batch see this chapter of the hibernate documentation

The batch_size is more to memory optimization than query optimization, the query is remain the same, but you can also minimize the roundtrip by the full use of your memory. you need to flush() and clear() each N times depend of the setting of batch_size.

but still . .

Update in 1 statement is much faster than update in multiple statement, so if you :

• Can simply loop and do that in SQL
• No need for cascade that update to other entities
• You really need much faster performance when updating multiple value
• And no need other HQL advantage like prepared statement that prevent sql injection

Then you can consider to just create native query than hql.