Spring Data JPA - Batch entity inserts and updates

Table of Contents

How to implement batch update using Spring Data Jpa?

Short answer: saveAll

How to implement batch update using Spring Data Jpa? I have a Goods Entity, and for different user levels, there are different prices.

e.g.

goodsId level price
  1       1     10
  1       2     9
  1       3     8

When update goods, I want to batch update these prices, like below:

@Query(value = "update GoodsPrice set price = :price where goodsId=:goodsId and level=:level")
void batchUpdate(List<GoodsPrice> goodsPriceList);

One way to do it is to first retrieve all the data that we want to update. For this 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 we 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 updated. 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)

Reading material

  1. https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
  2. https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#addBatch%28java.lang.String%29
  3. https://vladmihalcea.com/the-best-way-to-do-batch-processing-with-jpa-and-hibernate/
  4. https://www.baeldung.com/spring-data-jpa-batch-inserts
  5. https://www.baeldung.com/jpa-hibernate-batch-insert-update

Links to this note