Batch update does not works when using JPQL

Question

I'm trying to add a batch update to my spring boot project. But multiple queries are still executed when I check SQL logs and Hibernate stats.

Hibernate stats

290850400 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
3347700 nanoseconds spent preparing 19 JDBC statements;
5919028800 nanoseconds spent executing 19 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
2635900 nanoseconds spent executing 1 flushes (flushing a total of 1 entities and 0 collections);
19447300 nanoseconds spent executing 19 partial-flushes (flushing a total of 18 entities and 18 collections)

Versions

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

SnapshotRepository

@Repository
public interface SnapshotRepository extends JpaRepository<Snapshot, Long> {

    @Modifying
    @Query("UPDATE Snapshot s SET s.fieldValue =?1,s.createdOn=?2 where s.id = ?3 and s.fieldName = ?4")
    int updateSnapshot(String fieldValue, String createdOn, String id, String fieldName);
}

And this repository method is called from the service class.

  for (Map.Entry<String, String> entry : res.getValues().entrySet()) {
      snapshotRepository.updateSnapshot(entry.getValue(), createdOn, id, entry.getKey());
  }

pom.xml

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

In the application.yml, I think I'm configuring all required properties to activate batch update but still no luck.

Please let me know what I'm doing incorrectly.

try setting hibernate.jdbc.batch_size also in hibernate.properties — p3consulting
– p3consulting, Commented Oct 9, 2022 at 9:14
"batch settings" influence on session flush behaviour, in your case @Query("UPDATE ... performs direct updates in DB. Perhaps, you are modifying entities in memory, which forces HBN to flush their state prior issuing SQL update. — Andrey B. Panfilov
– Andrey B. Panfilov, Commented Oct 9, 2022 at 10:52
@AndreyB.Panfilov yes, you are right, I already tested, and the configuration is working fine. I tried to select an entity from DB, update its values, and save it using snapshotRepository.save(snapshot) The batching works perfectly in this case. — JiboOne
– JiboOne, Commented Oct 9, 2022 at 11:11

Christian Beikov · Accepted Answer · 2022-10-11 09:37:03Z

2

Batching only works when Hibernate does the flushing of entities. If you are executing manual queries, Hibernate can't do batching.

The way you are implementing this, Hibernate will reuse the same prepared statement on the database side though, but no JDBC batching.

answered Oct 11, 2022 at 9:37

Christian Beikov

16.6k2 gold badges36 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Batch update does not works when using JPQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related