Insert multiple records with a single query using JPA methods

Question

I have a spring boot application where I want to insert multiple records using a single DB query:-

public void addEvents() {
        String sql = "INSERT INTO Data values(1,'Data 1'), (2,'Data 2'),(3,'Data 3')";
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        entityManager.getTransaction().begin();
        entityManager.createNativeQuery(sql).executeUpdate();
        entityManager.getTransaction().commit();
    }

Entity Class:-

@Entity
@Table
public class Data {
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String name;

    public Data(String name) {
        this.name = name;
    }

    public Data(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Data() {
    }
}

Here Data is an Entity which I am inserting. I need to insert more than 10000 rows in my DB within secs. So firing individual DB calls is too costly. Therefore I had used a single query for this purpose. This query is a native query. Can anyone please tell me if JPA provides an out of the box API to insert multiple entities in a single DB cl?

Answer 1

You can use saveAll method of spring-data to have bulk insert. Here is a code snippet,

List<Data> dataList = <10000 records> //You need to prepare batch of 10000

dataRepository.saveAll(dataList) //Spring boot will handle batches automatically. 

Add following properties in application.properties

spring.jpa.properties.hibernate.jdbc.batch_size=100

You can see generated query using spring.jpa.show-sql

Update: Add following dependency in your pom.xml,

 <dependency>
     <groupId>com.integralblue</groupId>
     <artifactId>log4jdbc-spring-boot-starter</artifactId>
            <version>1.0.2</version>
 </dependency>

Above dependency will show in log if batch is happening or not.