Spring Data JPA: How to bulk insert data
Discussing 3 Options to bulk insert data in RDBMS
🚀 Java + Spring Boot + SQL Interview + Certification Prep 🚀
🎯 Perfect for aspiring developers, job switchers & interview prep warriors!🔥 What’s Inside (PDF format):
Grokking the Java Interview (Vol 1 & 2)
Grokking the Spring Boot Interview
Grokking the SQL Interview
Java SE 17 Developer Certification (1Z0–829) Guide
250+ Spring Framework Certification Practice Questions
✅ Bonus: Free Sample Copies of Java, Spring Boot & Spring Questions
Introduction
As a Software engineer who works primarily as a backend engineer or full-stack engineer, we often come across use cases where inserting data as bulk into the database is paramount.
Some of the use cases will be like backfilling newly modified tables with old table data, populating secondary database tables with the primary database, or even taking back up of actual data, etc.
In this article, we will discuss how we can insert data in bulk into the database using Spring Data JPA.
Entity
For this example, we are using the Accounts table as shown below. This table gets a unique primary key Id from the accounts_seq generator.
The repository interface extends from JpaRepository which provides many out-of-the-box methods to deal with the database easily.
Option 1
JpaRepository extends from ListCrudRepository which provides saveAll method.
In this option, we will save a list of accounts using this method that will persist in the list of accounts in the accounts table.
Our controller receives a list of accounts as @RequestBody.
We can send an array of accounts as JSON in the request body.
Once we hit the endpoint, our insert logic gets executed, I have enabled JPA logs to see the query.
Our database is updated with recent insertion of records.
As we can see in the query logs saveAll executes one query for each record and does not insert everything in one query using INSERT INTO VALUES.
Option 2
In this option, we will take the Session object in our hand, so that we can get the Connection instance.
Once we have a hands-on Connection instance we can use prepareStatement to add each record as a batch to our insert query.
But keep in mind we are not using Spring Data JPA functionality here, instead, we are using a RAW connection to connect and execute the query.
Postman Request
records are inserted into the database as bulk inserts.
Option 3
In this option, we build our INSERT INTO VALUES query string by ourselves.
We will need a hold on EntityManager which we can inject into the service class.
@PersistenceContext
private EntityManager entityManager;Once we have the query built we can use entityManager.createNativeQuery and pass our built query as an argument.
Once the query instance is built we can perform executeUpdate() that will hit this query on the database and bulk insert the data.
Postman Request
records have persisted in the database.
below is the log of the executed query.
Note:
One best practice here is that we should avoid inserting too many records all at once and insert using batch size like 10k records and add sleep of 1 sec in between each insert so that we don’t hold connection too long, donot send too many records on wire and give the database breathing room while iterating bulk insert iteratively.
Conclusion
In this article, we have discussed 3 options to bulk insert data in the Spring boot app.
We also discussed that we can create a sub batch from a big batch to insert data into the database iteratively.