How to implement batch update using Spring Data Jpa?

Following this example: Spring Data JPA Batch Inserts, I have created my own way of updating it without having to deal with EntityManager.

The way I did it is first to retrieve all the data that I want to update, in your case, it will be WHERE goodsId=:goodsId AND level=:level. And then I use a for loop to loop through the whole list and setting the data I want

List<GoodsPrice> goodsPriceList = goodsRepository.findAllByGoodsIdAndLevel();
for(GoodsPrice goods : goodsPriceList) {
  goods.setPrice({{price}});
}
goodsRepository.saveAll(goodsPriceList);

Some of the followings are needed for inserts or updates. Having generate_statistics on so that you can see if you are really batching it

// for logging purpose, to make sure it is working
spring.jpa.properties.hibernate.generate_statistics=true
// Essentially key to turn it on
spring.jpa.properties.hibernate.jdbc.batch_size=4
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true

and the log is here

27315 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    603684 nanoseconds spent preparing 4 JDBC statements;
    3268688 nanoseconds spent executing 3 JDBC statements;
    4028317 nanoseconds spent executing 2 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    6392912 nanoseconds spent executing 1 flushes (flushing a total of 3 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)

One more link to be added to this conversation for more reference

Spring Data JPA batch insert/update

Few things to be considered while trying a Batch Insert/ Update using Spring Data JPA

1. GenerationType used for @Id creation of Entity
2. Setting up of Batch size attribute of Hibernate Property
3. Setting order_inserts (for making Batch_size effective on Insert statements) or order_updates (for making Batch_size effective on Update statements) attribute of Hibernate properties
4. Setting batch_versioned_data in order to make order_updates effective for Batch_size implementation

If you're using Hibernate, you do have an option if you're willing to manager the transactions yourself.

Here is the test example

int entityCount = 50;
int batchSize = 25;
 
EntityManager entityManager = null;
EntityTransaction transaction = null;
 
try {
    entityManager = entityManagerFactory().createEntityManager();
 
    transaction = entityManager.getTransaction();
    transaction.begin();
 
    for ( int i = 0; i < entityCount; ++i ) {
        if ( i > 0 && i % batchSize == 0 ) {
            entityManager.flush();
            entityManager.clear();
 
            transaction.commit();
            transaction.begin();
        }
 
        Post post = new Post(String.format( "Post %d", i + 1 ) );
        entityManager.persist( post );
    }
 
    transaction.commit();
} catch (RuntimeException e) {
    if ( transaction != null && transaction.isActive()) {
        transaction.rollback();
    }
    throw e;
} finally {
    if (entityManager != null) {
        entityManager.close();
    }
}

It would also be recommended to set the following properties to something that fits your needs.

<property
    name="hibernate.jdbc.batch_size"
    value="25"
/>
 
<property
    name="hibernate.order_inserts"  
    value="true"
/>
 
<property
    name="hibernate.order_updates"  
    value="true"
/>

All of this was taken from the following article. The best way to do batch processing with JPA and Hibernate

We can enable query batch processing programmatically. If there's a bulk update required processing thousands of records then we can use the following code to achieve the same.

You can define your own batch size and call updateEntityUtil() method to simple trigger an update query either using Spring Data JPA or a native query.

Code Snippet :-

    // Total number of records to be updated
    int totalSize = updateEntityDetails.size();

    // Total number of batches to be executed for update-query batch processing
    int batches = totalSize / batchSize;

    // Calculate last batch size
    int lastBatchSize = totalSize % batchSize;

    // Batching-process indexes
    int batchStartIndex = 0, batchEndIndex = batchSize - 1;


    // Count of modified records in database
    int modifiedRecordsCount = 0;

    while (batches-- > 0) {
        // Call updateEntityUtil to update values in database
        modifiedRecordsCount += updateEntityUtil(batchStartIndex, batchEndIndex, regionCbsaDetails);

        // Update batch start and end indexes
        batchStartIndex = batchEndIndex + 1;
        batchEndIndex = batchEndIndex + batchSize;
    }

    // Execute last batch
    if (lastBatchSize > 0)
        modifiedRecordsCount += updateEntityUtil(totalSize - lastBatchSize, totalSize - 1,
                updateEntityDetails);