Spring data JPA repository saveAll 未生成大容量插入查询

Question

我使用Spring data 2.2.8和hibernate5.4.17.Final版本。数据库是oracle 11g，当我保存repository.saveAll（列表）数据时，它不会生成批量插入查询。它为每个记录生成一个查询。

以下是实体序列相关信息

@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());

波纹管是Hibernate设置

@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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

以下是Hibernate统计数据

    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对所有记录进行预paer单次插入查询，我们就可以提高性能

是否有可能对多条记录进行单批次插入查询？下面是示例查询，我正在尝试存档

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花时间按实体对插入进行分组，从而创建更大的批。