Hibernate批量更新已开启但Oracle环境仍执行单条查询

我在Spring Boot项目中配置了批量插入功能，虽然看起来批量已经激活，但查看Hibernate日志时仍看到多条单独的INSERT语句。

配置参数 #

hibernate.jdbc.batch_size=5

Hibernate统计信息 #

295647400 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
3693300 nanoseconds spent preparing 21 JDBC statements;
5752515000 nanoseconds spent executing 20 JDBC statements;
1275544900 nanoseconds spent executing 4 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
1287992700 nanoseconds spent executing 1 flushes (flushing a total of 19 entities and 0 collections);
735000 nanoseconds spent executing 2 partial-flushes (flushing a total of 1 entities and 1 collections)

Hibernate日志 #

2022-10-09 19:21:16,192 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger: insert into SYSTEM.SNAPSHOT (CREATED_ON, ENTITY_PROPER_NAME, ID) values (?, ?, ?)
2022-10-09 19:21:16,192 DEBUG org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: Reusing batch statement
2022-10-09 19:21:16,192 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger: insert into SYSTEM.SNAPSHOT (CREATED_ON, ENTITY_PROPER_NAME, ID) values (?, ?, ?)
2022-10-09 19:21:16,192 DEBUG org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: Reusing batch statement
2022-10-09 19:21:16,192 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger: insert into SYSTEM.SNAPSHOT (CREATED_ON, ENTITY_PROPER_NAME, ID) values (?, ?, ?)
2022-10-09 19:21:16,193 DEBUG org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: Reusing batch statement
2022-10-09 19:21:16,193 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger: insert into SYSTEM.SNAPSHOT (CREATED_ON, ENTITY_PROPER_NAME, ID) values (?, ?, ?)
2022-10-09 19:21:16,193 DEBUG org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl: Reusing batch statement
2022-10-09 19:21:16,193 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger: insert into SYSTEM.SNAPSHOT (CREATED_ON, ENTITY_PROPER_NAME, ID) values (?, ?, ?)
2022-10-09 19:21:16,193 DEBUG org.jboss.logging.DelegatingBasicLogger: Executing batch size: 5

期望的批量语句 #

我原本期望Hibernate为Oracle生成类似这样的批量插入语句：

INSERT ALL 
    INTO Snapshot ( created_on, entity_proper_name, id ) VALUES ( ?, ?, ? )
    INTO Snapshot ( created_on, entity_proper_name, id ) VALUES ( ?, ?, ? )
    INTO Snapshot ( created_on, entity_proper_name, id ) VALUES ( ?, ?, ? )
SELECT 1 FROM dual;

但通过Jprofiler或直接查看Oracle数据库，实际执行的语句和Hibernate日志一致，执行次数和单独插入时相同。

Hibernate批量更新功能在Oracle数据库中是否正常工作？

代码片段 #

环境版本 #

Spring Boot v2.7.1 
Spring v5.3.21
Java 17.0.3.1
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

application.yml #

spring:
  datasource:
    url: jdbc:oracle:thin:@localhost:1521/db
    username: user
    password: password
    driver-class-name: oracle.jdbc.OracleDriver
  jpa:
    database-platform: org.hibernate.dialect.Oracle12cDialect
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
        format_sql: false
        jdbc:
          fetch_size: 100
          batch_size: 5
        order_updates: true
        order_inserts: true
        batch_versioned_data: true
        generate_statistics: true

Snapshot实体类 #

@Entity
@Table(name = "SNAPSHOT", schema = "SYSTEM", catalog = "")
public class Snapshot {
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @Id
    @Column(name = "ID")
    private long id;
    @Basic
    @Column(name = "CREATED_ON")
    private String createdOn;
    // 其他字段和getter/setter省略
}

Snapshot服务层代码 #

@Transactional
public void execute() {
    // 省略其他逻辑
    for (int i = 0; i < snapshots.size(); i++) {  
        snapshots.get(i).setFieldValue(fieldValue);
        snapshots.get(i).setCreatedOn(createdOn);
    }

    snapshotRepository.saveAll(snapshots);
    // 省略其他逻辑
}

pom.xml依赖 #

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <version>21.7.0.0</version>
</dependency>

首先明确：Hibernate的JDBC批量和你期望的Oracle INSERT ALL 是两种不同的批量实现方式，你的配置其实已经启用了JDBC批量，只是日志显示方式让你误以为是单条执行。

为什么日志里显示多条INSERT语句？ #

Hibernate的DEBUG日志会把批量中的每条语句都打印出来，但实际上这些语句是通过JDBC的addBatch()和executeBatch()方法批量提交给数据库的，并非逐条执行。从统计信息里的executing 4 JDBC batches也能证明这一点——20条语句被合并成了4个批次（20/5=4），完全符合你设置的batch_size=5。

如何让Hibernate生成Oracle的INSERT ALL语句？ #

如果你想要Oracle特有的INSERT ALL语法，需要启用Hibernate的Native批量插入，需满足以下条件：

1. 保持当前的org.hibernate.dialect.Oracle12cDialect方言配置
2. 调整实体的ID生成策略：
   • 若使用序列，必须设置序列的allocationSize等于batch_size，同时开启新生成器映射：

     @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "snapshot_seq")
     @SequenceGenerator(name = "snapshot_seq", sequenceName = "SYSTEM.SNAPSHOT_SEQ", allocationSize = 5)
     @Id
     @Column(name = "ID")
     private long id;
     

   • 或者Oracle 12c及以上版本可改用IDENTITY自增列策略
3. 确保已配置hibernate.order_inserts=true和hibernate.jdbc.batch_versioned_data=true（你已配置）

验证批量是否生效的正确方式 #

不要仅依赖Hibernate日志，可通过以下方式验证：

• 查询Oracle的V$SQL视图，检查是否有INSERT ALL语句执行
• 统计数据库执行次数，批量插入的执行次数应为总记录数除以batch_size（向上取整）
• 对比单条插入与批量插入的执行时间，批量插入应有明显性能提升

总结 #

你的Hibernate批量功能已经正常工作，只是采用的是JDBC批量而非Oracle特有的INSERT ALL语法。如果需要后者，只需调整ID生成策略及序列配置即可。

内容的提问来源于stack exchange，提问作者JiboOne