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

7

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,609
22
40

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

3

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

28,144
8
75
101

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? – java dev Jan 05 '22 at 09:18

score 2 · Answer 3 · answered Jun 06 '22 at 13:15

2

since mysql doesn't support sequenece and hibernate batch insert is disabled for GenerationType.IDENTITY , AUTO, TABLE generate sequence manually at your entity class :

@Id
@GeneratedValue(generator = "voucher_sequence")
@GenericGenerator(name = "voucher_sequence",strategy = "increment")

answered Jun 06 '22 at 13:15

saba

332
2
14

1

do you happen to know if this workaround is safe? I debugged it a bit, and it seems like it just takes max(id) from the table and uses it moving forward. It only did it once and hasn't repeated in the following transactions. AFAIK This is very dangerous in a multi-threaded environment. – KidCrippler Jun 18 '22 at 21:34

Bulk insert with Spring Boot and Spring Data JPA not working

3 Answers3

Linked