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 package import lombok.Builder; import lombok.Data; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.SequenceGenerator; @Data @Entity(name = "student") @Builder public class StudentEntity { @Id @GeneratedValue(generator="student_sequence") @SequenceGenerator(name="student_sequence", sequenceName="student_sequence" ) private long id; @Column private String name; @Column private String email; } - Step 3. (optional) If you don't use
spring.jpa.hibernate.ddl-auto=create=> need to make student_sequence by hand
-- demo.student_sequence definition create table student_sequence (next_val bigint) engine=InnoDB insert into student_sequence values ( 1 ) - Step 4. Enable batch_size
# apllication.properties spring.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 package import com.hieunh1801.demo.entity.StudentEntity; import com.hieunh1801.demo.repository.StudentRepository; import lombok.extern.slf4j.Slf4j; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.util.StopWatch; import java.util.ArrayList; import java.util.List; @Slf4j @SpringBootTest class DemoApplicationTests { @Autowired private StudentRepository studentRepository; @Test void insertStudentSeparately() { log.info("insert 5000 students separately"); StopWatch watch = new StopWatch(); watch.start(); for (int i = 0; i < 5000; i++) { StudentEntity studentEntity = this.createStudent(i); this.studentRepository.save(studentEntity); } watch.stop(); log.info("end insert students separately in {} ms", watch.getTotalTimeMillis()); // 16372 ms } @Test void insertStudentsByBatch() { log.info("insert 5000 students by batch"); StopWatch watch = new StopWatch(); watch.start(); List<StudentEntity> students = new ArrayList<>(); for (int i = 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 } private StudentEntity createStudent(Integer index) { return StudentEntity.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 contextmeaning save to RAM => of course out of memory. You need to flush it!!!
for (int i = 0; i < 5000; i++) { students.add(this.createStudent(i)); if (i%100 == 0) { // save and flush every 100 entity this.studentRepository.saveAllAndFlush(students); students.clear(); } } this.studentRepository.saveAllAndFlush(students); 
Top comments (0)