JPA - batch / bulk update - what's the best 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:

• Is that all you need for a batch update? Anything else I need to add? I set hibernate.jdbc.batch_size", "20"
• Is optimistic locking enabled by default? (I don't have @Version in my essence, though)
• What do I need to do to ensure optimistic blocking, if not @Version?

Option 2:

Create a single query using Select Case syntax or using the Criteria API

Questions:

• Is the dispenser still going on? (In one big request)
• Is this better than the first approach in terms of performance?
• What is the recommended approach of these two options? Any other better approach?

Answer 1

If you intend to use the package, see this chapter of the documentation for sleep mode.

Batch_size optimizes memory more than query optimization, the request remains unchanged, but you can also minimize backtracking while fully utilizing your memory. you need to clear () and clear () every N times of the value of the batch_size parameter.

but still..

The update in instruction 1 is much faster than the update in several operations, so if you:

• You can just loop and do it in SQL
• No cascade needed to update for other objects
• You really need a lot better performance when updating multiple values.
• And you don’t need another advantage of HQL, as a prepared expression that prevents SQL injection

Then you can consider creating your own query than hql.

Answer 2

You mentioned Bulk Update and Delete in the title of the question, but this time you really need to do batch processing.

Bulk updating and deletion are necessary if you want to update UPDATE / DELETE strings that meet the same filtering criteria that can be expressed in WHERE.

Here you need JDBC batch updates. As described in this article , you need to set the following configuration property:

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

If you do, you can just update the entities, and Hibernate will execute the UPDATE statements for you.

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

Option 2 is not very useful either because it will generate a very complex query, whose execution plan is probably more complicated than doing everything in a simple batch UPDATE statement.

So do it like this:

• Retrieving objects using pagination
• Update them with Hibernate and give it a batch update for you.

If you have many of these objects, use pagination as described in this article .

Answer 3

You can update the list of objects without going through the entire 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();