在Hibernate/JPA中批量插入CollectionTable元素 [英] Bulk insertion of CollectionTable elements in Hibernate / JPA

问题描述

我们将Hibernate 4.2用作JPA 2.0实体的后备库.我们有一个如下实体:

We are using Hibernate 4.2 as the backing library for JPA 2.0 entities. We have an entity like the following:

@Entity
public class MyEntity {
    ....
    @ElementCollection
    @MapKeyColumn(name = "key")
    @Column(name = "value")
    @CollectionTable("MyEntityMap")
    private Map<String, Integer> theMap;
    ...
}

该地图可能包含数千个条目.我已经设置了hibernate.jdbc.batch_size=50，但是当我说entityManager.persist(myEntity)时，Hibernate仍然为映射中的每个条目生成一个插入语句.有没有办法让Hibernate在像INSERT INTO MyEntityMap () VALUES (), (), (), (), ()这样的大容量插入中插入值?

The map potentially has thousands of entries. I have set hibernate.jdbc.batch_size=50, but Hibernate still generates an insert statement for each entry in the map, when I say entityManager.persist(myEntity). Is there a way to make Hibernate insert the values in a bulk insert like INSERT INTO MyEntityMap () VALUES (), (), (), (), ()?

推荐答案

我有完全相同的场景，这就是我最终要做的事情...

I had the exact same scenario, this is what i ended up doing ...

在persistence.xml/等效文件中使用以下配置

Use the below configurations within the persistence.xml/equivalent file

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

您给出的示例是批量插入，上面的配置仅将单个插入语句作为一个批处理发送到数据库，实际上并没有将插入内容重写为

The example you gave is bulk insert, what the above configuration does is only send the individual insert statements as a single batch to the DB, it does not actually rewrite the inserts into

INSERT INTO MyEntityMap () VALUES (), (), (), (), ()

并且可以肯定的是，批处理插入仅会稍微改善性能，而真正的性能改善可以来自批量插入.

and admittedly the batch insert only gives a slight performance improvement, the real performance improvement can be derived from bulk inserts.

批量插入不是ANSI SQL功能，而是单个DB提供程序的功能，MySQL DB连接器驱动程序提供了将批量插入重写为批量插入的配置

Bulk inserts are not a ANSI SQL feature but rather a feature of individual DB providers, the MySQL DB connector driver provides a configuration to rewrite batch inserts into bulk inserts

要启用它，请使用如下所示的连接字符串

to enable it use a connection string like the below

jdbc.url=jdbc:mysql://localhost:3306/macula?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&rewriteBatchedStatements=true

现在，一旦完成此操作，如果尝试运行该应用程序，并在mysql中启用常规查询日志记录，您将看到您的插入内容已被重写为批量插入内容...但您也会得到 BatchedTooManyRowsAffectedException :)

Now once your done with this , if you try to run the application, and enable general query logging in mysql, you will see that your inserts are being rewritten as bulk inserts ... but you also will get is a BatchedTooManyRowsAffectedException:)

这是由

This is thrown by the org.hibernate.jdbc.Expectations.BasicExpectation.verifyOutcome() method (line 67 )

由于休眠对重写一无所知，因此无法理解更新次数，因此我无法找到一种合理的方法来挂钩我的Expectations类的实现...

As hibernate know nothing about the rewrite, it is not able to make sense of the update count, i could not find a reasonable way to hook in my implementation of the Expectations class ...

这有点死胡同，我们不能在休眠状态下使用rewriteBatchedStatements功能！

This is kind of a dead end, we cannot use the rewriteBatchedStatements feature with hibernate !!

好吧，除非您愿意做一个邪恶的骇客，就是...只需将类复制到完全相同的包层次结构下粘贴到您的项目代码中，这意味着休眠将选择复制粘贴的类而不是打包的类在休眠的jar中，然后将if语句注释掉，是的，那么一切都会顺利进行！

Well unless you are willing to do a evil hack that is ... just copy paste the class into your project code under the exact same package hierarchy this would mean that hibernate will pick up your copy pasted class rather than the one packaged within hibernate jar, and then just comment out that if statement ... and yes then everything works smoothly !!

请仔细考虑以上技巧；)

Consider the above hack with care ;)

这篇关于在Hibernate/JPA中批量插入CollectionTable元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！