如何使用Hibernate进行大量插入? [英] How can I do massive insert using Hibernate?

问题描述

对于 Spring Data JPA 和 Hibernate ，我还很陌生，并且在插入大量记录时遇到以下问题.

I am pretty new to Spring Data JPA and Hibernate and I have the following problem with massive record insertion.

我有以下情况:

1) RoomMediaDAO Spring Data JPA界面:

1) A RoomMediaDAO Spring Data JPA interface:

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface RoomMediaDAO extends JpaRepository<RoomMedia, Long> {

   ....................................................................
   ....................................................................
   ....................................................................
}

2)进入另一个类，我使用此 RoomMediaDAO 来保存 RoomMedia 对象的列表.该列表很大很重，因为其中包含许多对象，并且这些对象中的每个对象都包含一个 byte [] 字段，表示映射表上的 BLOB .

2) Into another class I use this RoomMediaDAO to persist a list of RoomMedia object. The list is pretty big and heavy because contains many object and each oof these objects contains a byte[] field representing a BLOB on the mapped table.

因此，我有以下陈述可以持久保存列出mediaListToBeInserted :

So I have the follosing statment that persist the List mediaListToBeInserted:

roomMediaDAO.save(mediaListToBeInserted);

工作正常，但速度很慢，因为它会逐个执行插入操作，实际上在控制台中，我可以看到类似这样的内容:

It work fine but it is very slow because it perform the insert one by one, infact in the console I can see something like this:

Hibernate: insert into room_media (description, media, id_room, time_stamp) values (?, ?, ?, ?)
Hibernate: insert into room_media (description, media, id_room, time_stamp) values (?, ?, ?, ?)
Hibernate: insert into room_media (description, media, id_room, time_stamp) values (?, ?, ?, ?)
Hibernate: insert into room_media (description, media, id_room, time_stamp) values (?, ?, ?, ?)
Hibernate: insert into room_media (description, media, id_room, time_stamp) values (?, ?, ?, ?)
............................................................................
............................................................................
............................................................................

所以这不是一个好的解决方案.

So this is not a good solution.

我该如何对Hibernate说要在一个插入语句中插入更多记录?我的意思是我可以使用Hibernate做类似的事情吗?

How can I say to Hibernate to insert more record with a single insert statment? I mean can I do something like this using Hibernate?

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), 
( Value1, Value2 ),
( Value1, Value2 ),
.................................
.................................
.................................
( Value1, Value2 )

我绝对需要这样的东西来提高批次的性能.

I absolutly need something like this to improve the performance of my batch.

推荐答案

此链接提供了有关执行批处理插入的不同方法的详细说明.在斜体字法中提到了这些方法的一些问题.

This link provides a detailed explanation about different ways of performing batch inserts. Mentioned in Italics are some problems with these approaches.

1. 使用 batch_size (提供的链接中的13.1节)-如果您要交替插入(表中是非顺序插入，则先依次插入Table1和Table2，再重新插入Table1，然后记住)使用order_inserts.
   此外，如果Generator类型为Identity ，则不起作用.
2. 使用 StatelessSession 进行插入. (13.3)-插入的值未存储在一级缓存中.
   可能会导致数据混淆(由于缺少FLC)，还会绕过Hibernate的事件模型和拦截器.
3. 使用 DML样式的操作
   (仅支持使用Select插入，不支持值)
4. 如果您使用的是 Postgres (或者对于其他数据库，请检查那些数据库是否支持任何批量插入操作，然后利用它是一个很好的选择).利用复制管理器功能来上传所有这些内容一口气拿下数据. 性能比较.
   确保使用的PushbackReader不会溢出.我误判了这一点，不得不面对一些刺. BufferOverflow问题.

1. Using batch_size (13.1 section in the link provided) - In case you've alternating inserts (non-sequential inserts into a table, Table1 followed by Table2 and again Table1 and soon) then remember to use order_inserts.
   Also, doesn't work if Generator is of type Identity.
2. Using StatelessSession for insertion. (13.3) - Inserted values are not stored in First Level Cache.
   May result in data aliasing effects (due to missing FLC), Also bypasses Hibernate's event model and interceptors.
3. Using DML-style operations
   (Insert with Select is Only supported, not values)
4. If you're using Postgres (Or for other DB, check if those DB's support any Bulk insert Operation, then leveraging on that is a very good option). Making use of Copy manager features to upload all this data in one shot. Comparison of Performance.
   Make Sure the PushbackReader used is not overflown. I misjudged this and had to face some prod. issues with BufferOverflow.

PS:每种方法都有各自的adv&取消广告选择适合您的一个.

PS : Each of the approaches have their adv & dis-adv. Choose the one that suits you.

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