Spring数据JPA存储库saveAll未生成大容量插入查询

Question

我使用的是Spring数据2.2.8和hibernate 5.4.17的最终版本。数据库是oracle11g当我使用repository.saveAll(list(保存数据时，它不会生成大容量插入查询。它为每条记录生成一个查询。

以下是实体序列相关信息

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "PAYG_RECONCILIATION_IDS")
@SequenceGenerator(name = "PAYG_RECONCILIATION_IDS", sequenceName = "PAYG_RECONCILIATION_IDS")
@Column
private Long paygReconciliationId;

下面是保存实体代码

paygReconciliationRepository.saveAll(paygReconciliationMap.values());

以下是休眠设置

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(dataSource);
em.setPackagesToScan("com.cubic.cts.core.persistence.main.dto", "com.cubic.frm.persistence.main.dto");
JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
Map<String, Object> properties = new HashMap<>();
properties.put("hibernate.show_sql", true);
properties.put("hibernate.generate_statistics", true);
properties.put("hibernate.jdbc.batch_size", 50);
properties.put("hibernate.order_inserts", true);
properties.put("hibernate.order_updates", true);
properties.put("hibernate.jdbc.batch_versioned_data", true);
properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
properties.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
em.setJpaPropertyMap(properties);
return em;
}

在刷新一个批次之后，下面是查询生成的

Hibernate: insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

以下是休眠统计

2021-07-13 13:15:26,261 [INFO ] scheduler_Worker-1 StatisticalLoggingSessionEventListener - Session Metrics {
466400 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
867200 nanoseconds spent preparing 13 JDBC statements;
7763800 nanoseconds spent executing 11 JDBC statements;
51541800 nanoseconds spent executing 8 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
4921200 nanoseconds spent executing 1 flushes (flushing a total of 400 entities and 400 collections);
214407100 nanoseconds spent executing 7 partial-flushes (flushing a total of 2200 entities and 2200 collections)
}

在上面的stat 7763800中执行13个JDBC语句，并且它有51541800执行8个JDBC批处理。我认为，如果我们可以使hibernate为所有记录预处理单插入查询，我们就可以归档一些性能

是否有可能对多条记录进行单批插入查询？下面是我试图归档的示例查询

insert into payg_reconciliation (version, inserted_dtm, bc_pay_sale_txn_payment_id, bc_pay_amount, bc_pay_cch_settlement_date, last_source_updated_dtm, merchant_id, payg_recon_status_id, bankcard_payment_id, declined_flag, subsystem_enum, retrieval_ref_nbr, payg_reconciliation_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

为什么hibernate不生成单插入查询，我在设置中遗漏了什么吗？提前谢谢。

Answer 1

在application.properties.中设置以下属性

spring.jpa.properties.hibernate.jdbc.batch_size=4
spring.jpa.properties.hibernate.order_inserts=true

第一个属性告诉Hibernate分四批收集插入。order_inserts属性告诉Hibernate花费时间按实体对插入进行分组，从而创建更大的批。