使用JPA + Hibernate进行大量插入 [英] Massive insert with JPA + Hibernate

问题描述

我需要使用EJB 3，Hibernate，Spring Data和Oracle进行大量插入。最初，我使用Spring Data，代码如下：

  talaoAITDAO.save（taloes）; 
  

其中talaoAITDAO是一个Spring Data JpaRepository 子类和taloes是TalaoAIT的集合实体。在此实体中，其各自的ID具有以下形式：

  @Id 
 @Column（name =ID_TALAO_AIT） 
 @SequenceGenerator（name =SQ_TALAO_AIT，sequenceName =SQ_TALAO_AIT，allocationSize = 1000）
 @GeneratedValue（strategy = GenerationType.SEQUENCE，generator =SQ_TALAO_AIT）
 private Long id; 
  

此外，此实体没有相关的实体可以进行级联插入。

我的问题在于，所有实体都是单独插入的（例如 INSERT INTO TABLE（col1，col2）VALUES（val1，val2））。偶尔会导致超时，所有插入操作都会回滚。我想将这些单独的插入转换为批量插入（例如 INSERT INTO TABLE（col1，col2）VALUES（val11，val12），（val21，val22），（val31，val32），... ）。

研究替代方案以提高性能，我发现此页面在hibernate文档中，超出
Hibernate batch size confusion and 这个其他页面。基于它们，我写了这段代码：

  Session session = super.getEntityManager（）。unwrap（Session.class）; 
 int batchSize = 1000; 
 for（int i = 0; i< taloes.size（）; i ++）{
 TalaoAIT talaoAIT = taloes.get（i）; 
 session.save（talaoAIT）; 
 if（i％batchSize == 0）{
 session.flush（）; 
 session.clear（）; 
} 
 taloes.add（talaoAIT）; 
} 
 session.flush（）; 
 session.clear（）; 
  

另外，在peristence.xml中，我添加了这些属性：

 < property name =hibernate.jdbc.batch_sizevalue =1000/> 
< property name =order_insertsvalue =true/> 
  

然而，尽管在我的测试中我发现了一些细微差别（主要是大集合和大批量），它并没有那么大。在日志控制台中，我看到Hibernate继续进行单独的插入，而不是将其替换为大量插入。在我的实体中，我使用了一个序列生成器，我相信这不是问题（根据Hibernate文档，如果我使用的是身份生成器，我会遇到问题）。

所以，我的问题是这里可能会丢失什么。一些配置？有些方法没有用过吗？

谢谢，

Rafael Afonso。

解决方案

几件事。

首先你的配置属性是错误的 order_inserts 必须是 hibernate.order_inserts 。目前你的设置被忽略，你没有改变一件事。

接下来使用 EntityManager ，而不是做所有那些令人讨厌的休眠事情。 EntityManager 也有一个 flush 和 clear 方法。这至少应该清理你的方法。如果没有这个命令，这将有助于清理会话并防止对所有对象进行脏检查。

  EntityManager em = getEntityManager（）; 
 int batchSize = 1000; 
 for（int i = 0; i< taloes.size（）; i ++）{
 TalaoAIT talaoAIT = taloes.get（i）; 
 em.persist（talaoAIT）; 
 if（i％batchSize == 0）{
 em.flush（）; 
 em.clear（）; 
} 
 taloes.add（talaoAIT）; 
} 
 em.flush（）; 
 em.clear（）; 
  

接下来，您不应该让批次变大，因为这会导致内存问题， 50并测试哪个/哪些性能最好。有一点，脏检查将需要更多时间，然后清空并清空数据库。你想找到这个甜蜜点。

I need to do a massive insert using EJB 3, Hibernate, Spring Data and Oracle. Originally, I am using Spring Data and code is below:

talaoAITDAO.save(taloes);

Where talaoAITDAO is a Spring Data JpaRepository subclass and taloes is a Collection of TalaoAIT entity. In this entity, Its respective ID has this form:

@Id
@Column(name = "ID_TALAO_AIT")
@SequenceGenerator(name = "SQ_TALAO_AIT", sequenceName = "SQ_TALAO_AIT", allocationSize = 1000)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SQ_TALAO_AIT")
private Long id;

Also this entity has no related entities to do cascade insert.

My problem here, is that all entities are individually inserted (such as INSERT INTO TABLE(col1, col2) VALUES (val1, val2)). Occasionally, it can cause a timeout and all insertions will be rolled back. I would want convert these individual inserts in batch inserts (such as INSERT INTO TABLE(col1, col2) VALUES (val11, val12), (val21, val22), (val31, val32), ...).

Studying alternatives to improve performance, I found this page in hibernate documentation, beyond Hibernate batch size confusion and this other page. Based on them, I wrote this code:

Session session = super.getEntityManager().unwrap(Session.class);
int batchSize = 1000;
for (int i = 0; i < taloes.size(); i++) {
    TalaoAIT talaoAIT = taloes.get(i);
    session.save(talaoAIT);
    if(i % batchSize == 0) {
        session.flush();
        session.clear();
    }
    taloes.add(talaoAIT);
}
session.flush();
session.clear();

Also, in peristence.xml, I added these properties:

<property name="hibernate.jdbc.batch_size" value="1000" />
<property name="order_inserts" value="true" />

However, although in my tests I had perceived a subtle difference (mainly with big collections and big batch sizes), it was not so big as desirable. In logging console, I saw that Hibernate continued to do individual inserts, not replacing them for massive insert. As in my entity, I am using a Sequence generator I believe that it is not problem (according Hibernate documentation, I would had problem if I was using Identity generator).

So, my question is what can be missing here. Some configuration? Some method not used?

Thanks,

Rafael Afonso.

解决方案

A couple of things.

First your configuration properties are wrong order_inserts must be hibernate.order_inserts . Currently your setting is ignored and you haven't changed a thing.

Next use the EntityManager instead of doing all that nasty hibernate stuff. The EntityManager also has a flush and clear method. This should at least cleanup your method. Without the order this helps a little to cleanup the session and preventing dirty-checks on all the objects in there.

EntityManager em = getEntityManager();
int batchSize = 1000;
for (int i = 0; i < taloes.size(); i++) {
    TalaoAIT talaoAIT = taloes.get(i);
    em.persist(talaoAIT);
    if(i % batchSize == 0) {
        em.flush();
        em.clear();
    }
    taloes.add(talaoAIT);
}
em.flush();
em.clear();

Next you shouldn't make your batches to large as that can cause memory problems, start with something like 50 and test which/what performs best. There is a point at which dirty-checking is going to take more time then flusing and clearing to the database. You want to find this sweet spot.

这篇关于使用JPA + Hibernate进行大量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！