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-ji2

• Step 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 context meaning 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);