Bulk insert with Spring Boot and Spring Data JPA not working
Solution 1
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?
Solution 2
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
Alessandro C
Updated on July 26, 2022Comments
-
Alessandro C almost 2 years
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> { }
-
Alessandro C almost 6 yearsIt seems to work! So the trick is to call only one save. I thought that every save would cached before persist.
-
C. Weber almost 6 yearsIf 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.
-
Clyde D'Cruz about 4 yearsBatching with spring data jpa medium.com/@clydecroix/…
-
V. Monisha about 4 years@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.
-
Alessandro C about 4 years@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.
-
V. Monisha almost 4 years@AlessandroC Actually, I have the same problem and posted question on stackoverflow.com/questions/61454033/… If you have any idea please share with me..
-
ennth about 3 years@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
-
devloper152 over 2 years@Cepr0 does
repository.save()
save all rows in cache(persistence context) or does it hit database each batch size?