Bulk insert with Spring Boot and Spring Data JPA not working

Question

I know that there are many similar questions about this argument, but I really need a working solution.

I'm trying to configure Spring Boot and Spring Data JPA in order to make bulk insert in a batch.

The target is: commit each N-records, not every single record when making repository.save() action.

What I've tried since now in the application.properties:

spring.jpa.properties.hibernate.jdbc.batch_size=100
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.generate_statistics=true

But with no success. I've monitored the database and records are persisted in tables one-by-one, not 100-by-100 like I've configured.

UPDATE

Here's the implementation:

@Component
public class BulkInsert {

    @Autowired
    MyRepository repository;

    public void process() {

        PodamFactory podamFactory = new PodamFactoryImpl();

        for(int i=0;i<10000;i++) {
            MyEntity myEntity = podamFactory.manufacturePojo(MyEntity.class);
            repository.save(myEntity);
        }

    }
}

Here's the entity:

@Entity
@Table(name="MYTABLE")
@NamedQuery(name="MyEntity.findAll", query="SELECT m FROM MyEntity m")
public class MyEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(name="DESCRIPTION")
    private String description;

    @Id
    @Column(name="ID")
    private String id;

    public MyEntity() {
    }

    // getters and setters

}

And the repository:

public interface MyRepository extends CrudRepository<MyEntity, String> {

}

You should provide your code how did you try to implement this... — Cepr0, Jun 05 '18 at 07:32
I've simply used a CrudRepository interface and I've generated @Entities with an Eclipse plugin. It works, but it commits every record — Alessandro C, Jun 05 '18 at 07:37
I've edited the question with the snippet of the implementation — Alessandro C, Jun 05 '18 at 07:46

Vetras · Answer 1 · 2019-07-18T11:22:49.143

6

In my case the bulk inserts were not working even with these configurations.

Turns out that if the entities use GenerationType.IDENTITY identifier generator, Hibernate will silently disable batch inserts/updates.

Maybe this will help others.

Source: http://kyriakos.anastasakis.net/2015/06/12/batch-inserts-with-spring-data-and-mysql/

I'm using:

MySql 5.6
Spring boot 2.1.9
JPA & Hibernate

edited Jul 18 '19 at 11:22

answered Jul 18 '19 at 10:30

Vetras

1,314
18
34

Cepr0 · Accepted Answer · 2020-04-27T16:00:22.937

2

Try to change your code like this:

public void process() {

    PodamFactory podamFactory = new PodamFactoryImpl();
    List<MyEntity> myEntities = new ArrayList<>(10000);

    for(int i = 0; i < 10000; i++) {
        myEntities.add(podamFactory.manufacturePojo(MyEntity.class));
    }

    repository.save(myEntities); // for Spring Boot prior 2.0
    // repository.saveAll(myEntities); - for Spring Boot since 2.0
}

P.S. don't forget to turn on spring.jpa.show-sql to see result

UPDATE

Please also check my another answer about bulk insert: How to do bulk (multi row) inserts with JpaRepository?

edited Apr 27 '20 at 16:00

answered Jun 05 '18 at 07:52

Cepr0

24,708
7
67
95

It seems to work! So the trick is to call only one save. I thought that every save would cached before persist. – Alessandro C Jun 05 '18 at 08:01
If you have a transaction running it should cache the save calls. If there is no transaction around your process method the save call itself opens a transaction since the repository methods are annotated with transactional. – C. Weber Jun 05 '18 at 11:10
Batching with spring data jpa https://medium.com/@clydecroix/batching-database-writes-in-spring-479bee626fbf?sk=8ee224e83a830a6cce92fa4e3e76967e – Clyde D'Cruz Mar 24 '20 at 18:05
@AlessandroC Can you please explain this line myEntities.add(podamFactory.manufacturePojo(MyEntity.class)); I came across this issue i am not able to understand this line. – V. Monisha Apr 27 '20 at 15:47
@V.Monisha podam is simply a layer that allows you to populate a bean with random values, you don't need it if you have real values. – Alessandro C Apr 27 '20 at 16:12
@AlessandroC Actually, I have the same problem and posted question on https://stackoverflow.com/questions/61454033/java-time-format-datetimeparseexception-text-03-03-2020-03-03-2020-could-not/61454778?noredirect=1#comment108716917_61454778 If you have any idea please share with me.. – V. Monisha Apr 29 '20 at 06:39
@C.Weber Can you elaborate? I upgraded to spring 2.X.X from 1.X.X and now my repository CRUD methods are NOT saving to database... I think something is messed up with Transactions /Entity manager because I see it go into the Spring AOP classes framework and jump into a Infinite Loop – ennth Apr 14 '21 at 16:50
@Cepr0 does ```repository.save()``` save all rows in cache(persistence context) or does it hit database each batch size? – devloper152 Jan 05 '22 at 09:18

Bulk insert with Spring Boot and Spring Data JPA not working

2 Answers2

Linked