Nguyễn Hữu Hiếu
Posted on
Java Spring Boot: batch insert data
Problem
- Inserting one is very boring => Want to insert many at once. This is for you
Solution
Step 1. Please follow this guide to improve your sql log first. Because normal log is not enough in this case
https://dev.to/hieunh1801/java-spring-boot-log-sql-query-more-efficiently-ji2Step 2. Using SequenceGenerator
// ... your packageimportlombok.Builder;importlombok.Data;importjavax.persistence.Column;importjavax.persistence.Entity;importjavax.persistence.GeneratedValue;importjavax.persistence.Id;importjavax.persistence.SequenceGenerator;@Data@Entity(name="student")@BuilderpublicclassStudentEntity{@Id@GeneratedValue(generator="student_sequence")@SequenceGenerator(name="student_sequence",sequenceName="student_sequence")privatelongid;@ColumnprivateStringname;@ColumnprivateStringemail;}
- Step 3. (optional) If you don't use
spring.jpa.hibernate.ddl-auto=create
=> need to make student_sequence by hand
-- demo.student_sequence definitioncreatetablestudent_sequence(next_valbigint)engine=InnoDBinsertintostudent_sequencevalues(1)
- Step 4. Enable batch_size
# apllication.propertiesspring.jpa.hibernate.ddl-auto=update# auto update/create column, table ...spring.jpa.properties.hibernate.jdbc.batch_size=5# insert 5 entity at once
- Step 5. Run Test with 5000 entity
// ... your packageimportcom.hieunh1801.demo.entity.StudentEntity;importcom.hieunh1801.demo.repository.StudentRepository;importlombok.extern.slf4j.Slf4j;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importorg.springframework.util.StopWatch;importjava.util.ArrayList;importjava.util.List;@Slf4j@SpringBootTestclassDemoApplicationTests{@AutowiredprivateStudentRepositorystudentRepository;@TestvoidinsertStudentSeparately(){log.info("insert 5000 students separately");StopWatchwatch=newStopWatch();watch.start();for(inti=0;i<5000;i++){StudentEntitystudentEntity=this.createStudent(i);this.studentRepository.save(studentEntity);}watch.stop();log.info("end insert students separately in {} ms",watch.getTotalTimeMillis());// 16372 ms}@TestvoidinsertStudentsByBatch(){log.info("insert 5000 students by batch");StopWatchwatch=newStopWatch();watch.start();List<StudentEntity>students=newArrayList<>();for(inti=0;i<5000;i++){students.add(this.createStudent(i));}this.studentRepository.saveAll(students);watch.stop();log.info("end insert students by batch in {} ms",watch.getTotalTimeMillis());// 2880 ms}privateStudentEntitycreateStudent(Integerindex){returnStudentEntity.builder().name("Student Name"+index).email("Student Email"+index).build();}}
- Step 6. OutOfMemory: if you save 100,000 entities at once then JPA will save it in
persistent context
meaning save to RAM => of course out of memory. You need to flush it!!!
for(inti=0;i<5000;i++){students.add(this.createStudent(i));if(i%100==0){// save and flush every 100 entitythis.studentRepository.saveAllAndFlush(students);students.clear();}}this.studentRepository.saveAllAndFlush(students);
Top comments(0)
Subscribe
For further actions, you may consider blocking this person and/orreporting abuse