Java Spring Boot: batch insert data
Published: 2023-09-03 16:34:52

## 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
```java
// ... 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
```sql
-- demo.student_sequence definition
create table student_sequence (next_val bigint) engine=InnoDB

insert into student_sequence values ( 1 )
```

- Step 4. Enable batch_size

```bash
# 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
```java
// ... 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!!!
```java
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);
```