JPA - 批量/批量更新 - 什么是更好的方法？ [英] JPA - Batch/Bulk Update - What is the better approach?

问题描述

我发现JPA不支持以下更新：

 更新人员p set p.name =：name_1 where p .id =：id_1，
 p.name =：name_2其中p.id =：id_2，
 p.name =：name_3其中p.id =：id_3 
 .... 
 //它可以继续，取决于输入的大小。可能在100s 
  

所以我有两个选择：

选项1：
$ b

 查询q = em.createQuery（Update Person p set p.name =：name where p.id =：id）; 
 
对于（int x = 0; PersonsList.length; x ++）{
 //添加名称和id参数
 em.executeUpdate（）; 
 
  

问题：

1. 这是批量更新所需的全部功能吗？还有什么我需要添加？
   我设置了 hibernate.jdbc.batch_size，20


2. 默认启用乐观锁吗？尽管我的实体中没有@Version）
   
3. 为了执行乐观锁定，如果不是@Version，我需要做什么？

>



选项2：
$ b 使用选择案例语法或使用 Criteria API





问题：





1. 批次是否仍然在这里发生？（在一个大查询中）


2. 在性能方面比第一种方法更好？


3. 这两个选项中推荐的方法是什么？还有其他更好的方法吗？






解决方案

在您的问题标题中，您提到了批量更新和删除，但实际上您需要批量处理。 b

批量更新和删除是需要更新/删除所有符合相同筛选条件的行，可以在WHERE子句中表示。





在这里，您需要JDBC批量更新。如这篇文章所述，您需要设置以下配置属性：



 < property name =hibernate.jdbc.batch_sizevalue =50/ > 
  

如果你这样做，你可以简单地更新实体，Hibernate会为你批量更新UPDATE语句。





选项1并不是非常有用，因为它会生成N个不能批量处理的UPDATE语句。





选项2并不是非常有用，因为它会生成一个非常复杂的查询，其执行计划可能比在简单的批处理UPDATE语句中执行所有操作复杂。

所以，做到这一点像这样：
$ b


1. 使用分页获取实体


2. 使用Hibernate更新它们并让它执行批处理如果您有很多这样的实体，请按照 com / the-best-way-do-batch-processing-with-jpa-and-hibernate /rel =nofollow noreferrer>这篇文章。
   
   

   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?

   解决方案

   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. As explained in this article, 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 pagination as explained in this article.

   这篇关于JPA - 批量/批量更新 - 什么是更好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！