Hibernate batch update is turned on but still executes eache query seperatly-CodePudding

I'm trying to add a batch update to my spring boot project. The batch seems activated, but when I check the hibernate logs, there are still multiple queries.

hibernate.jdbc.batch_size=5

hibernate stats

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 log

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

I'm expecting hibernate to generate a statement like this below for Oracle Database.

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;

When I'm checking the executed statement into Jprofiler or directly into Oracle, it's the same as shown in the hibernate logs. The execution count is also the same as it should be if you run inserts separately.

Does the Hibernate batch work for the Oracle database?

Code snippets

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

@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;
    ...
}

Snapshot service

@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>

CodePudding user response：

I believe it's because of the allocation size provided by Hibernate.

In your entity class can you try changing the allocationSize?

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SNAPSHOT_SEQ")
@SequenceGenerator(name = "SNAPSHOT_SEQ", sequenceName = "SNAPSHOT_SEQ", allocationSize = 150)
@Column(name = "ID", nullable = false)
private Long id;

CodePudding user response：

I found this hint:

To enable JDBC batching, set the property hibernate.jdbc.batch_size to an integer between 10 and 50.

Maybe anything less then 10 will not be taken seriously, e.g. 5. Would be a nasty behavior to siliently ignore everything else, though.

At other places two more settings for updates are mentioned, hibernate.order_updates and hibernate.jdbc.batch_versioned_data, but those are set true anyway too in your config.