Bulk/Batch insert using Spring Data JPA and Mysql database

Is batch insert not working as expected as you are using the Mysql database with Spring Data JPA?

Adding these dependencies also hasn’t helped ?

spring.jpa.properties.hibernate.jdbc.batch_size:5000

spring.jpa.properties.hibernate.order_inserts:true

spring.jpa.properties.hibernate.order_updates: true

Don't worry you are in the right place. Let's explore together the ways to solve it.

As I always say if you are using spring data JPA and haven’t added these lines in your application.properties while developing you won’t come across this issue, your application performance will go down

spring.jpa.show-sql: true

Reasons for MySQL not allowing batch insert with JPA

Using Sequence Generators to auto-generate id column — Neither MySQL 5.7 nor 8.0 support SEQUENCE objects. Consider a below entity class with service class

Tweet entity class

Service class to bulk insert records using jpa

If you try to use sequence and insert 5 records using JPA, you will see 11 queries executed to insert 5 records, so we should not consider using sequence with Mysql

Logs while using sequence

The best way to auto-generate column id is below:

Let's re-run the insertion of 5 records again. you will see 5 queries executed to insert 5 records not bad than sequence. It happens since the IDENTITY generator disables JDBC batch inserts

Log while using identity generator

Conclusion

Here it comes. JPA with MySQL won't support batching. we have to either use JDBC Template or query DSL-SQL or jooq.

To implement query DSL-SQL, click here.

To implement JDBC Template, click here