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);