How to implement batch update using Spring Data Jpa?

Question

How to implement batch update using Spring Data Jpa? I have a Goods Entity, and for diff user level there are diff price,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);

but it throws exception,

Caused by: java.lang.IllegalArgumentException: Name for parameter binding must not be null or empty! For named parameters you need to use @Param for query method parameters on Java versions < 8.  

So how to implement batch update using Spring data Jpa correctly?

Answer 1

I think this is not possible with Spring Data JPA according to the docs. You have to look at plain JDBC, there are a few methods regarding batch insert/updates.

However, you can do it with Hibernate fairly easy.

Answer 2

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