This tutorial will show you how you can insert a large dataset or perform batch insert into a database at once using Spring JdbcTemplate
.
Sometimes you need to insert or update large number of records in the database. It’s not a good idea to insert multiple records into database one by one in a traditional approach. It will hit the application’s performance.
Spring provides batch operations with the help of JdbcTemplate
, it inserts or updates records in chunks into database in one shot.
Related Posts:
Prerequisites
At least JDK 1.8, Gradle 6.4.1 – 6.7.1, Maven 3.6.3, Spring Boot 2.2.6 – 2.4.3, MySQL 8.0.17 – 8.0.22
Project Setup
Create either maven or gradle based project in your favorite IDE or tool. The name of the project is spring-jdbctemplate-batch-insert.
If you are creating gradle based project then use below build.gradle script:
buildscript {
ext {
springBootVersion = '2.2.6.RELEASE' to 2.4.3
}
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
plugins {
id 'java-library'
id 'org.springframework.boot' version "${springBootVersion}"
}
sourceCompatibility = 12
targetCompatibility = 12
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}") {
exclude group: 'com.zaxxer', module: 'HikariCP'
}
implementation('mysql:mysql-connector-java:8.0.17') //8.0.22
//required for jdk 9 or above
runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}
If you are creating maven based project then you can use below pom.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-jdbctemplate-batch-insert</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>12</maven.compiler.source>
<maven.compiler.target>12</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<exclusions>
<exclusion>
<artifactId>HikariCP</artifactId>
<groupId>com.zaxxer</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
MySQL Table
You need to create a table called student under roytuts database in MySQL server to store data.
CREATE TABLE IF NOT EXISTS `student` (
`student_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`student_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_dob` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_email` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Model Class
You need to create a POJO class that will map table and Java class together.
package com.roytuts.spring.jdbctemplate.batch.insert.model;
public class Student {
private Integer studentId;
private String studentName;
private String studentDob;
private String studentEmail;
private String studentAddress;
//getters and setters
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName=" + studentName + ", studentDob=" + studentDob
+ ", studentEmail=" + studentEmail + ", studentAddress=" + studentAddress + "]";
}
}
DAO Class
Data access layer is used to interact with database to perform operations.
I am inserting data in batch. For this example I have declared the batch size only 2.
I make this class annotated with @Repository
to scan the bean automatically.
package com.roytuts.spring.jdbctemplate.batch.insert.dao;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.roytuts.spring.jdbctemplate.batch.insert.model.Student;
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private final int INSERT_BATCH_SIZE = 2;
private final String SQL_STUDENT_INSERT = "INSERT INTO student(student_name,student_dob,student_email,student_address) values(?,?,?,?)";
public void insert(List<Student> students) {
for (int i = 0; i < students.size(); i += INSERT_BATCH_SIZE) {
final List<Student> batchList = students.subList(i,
i + INSERT_BATCH_SIZE > students.size() ? students.size() : i + INSERT_BATCH_SIZE);
jdbcTemplate.batchUpdate(SQL_STUDENT_INSERT, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement pStmt, int j) throws SQLException {
Student student = batchList.get(j);
pStmt.setString(1, student.getStudentName());
pStmt.setString(2, student.getStudentDob());
pStmt.setString(3, student.getStudentEmail());
pStmt.setString(4, student.getStudentAddress());
}
@Override
public int getBatchSize() {
return batchList.size();
}
});
}
}
}
Database Configuration
You need database configuration in order to establish database connectivity and perform database operations.
I keep the database settings in jdbc.properties file under src/main/resources folder.
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=root
The corresponding Java configuration class is given where I create JdbcTemplate
bean.
package com.roytuts.spring.jdbctemplate.batch.insert.config;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
@Configuration
@PropertySource("classpath:jdbc.properties")
public class AppConfig {
@Autowired
private Environment environment;
@Bean
public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(environment.getRequiredProperty("jdbc.driverClassName"));
dataSource.setUrl(environment.getRequiredProperty("jdbc.url"));
dataSource.setUsername(environment.getRequiredProperty("jdbc.username"));
dataSource.setPassword(environment.getRequiredProperty("jdbc.password"));
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate;
}
}
Main Class
A class having main method and @SpringBootApplication
in Spring Boot application will deploy the app easily.
package com.roytuts.spring.jdbctemplate.batch.insert;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import com.roytuts.spring.jdbctemplate.batch.insert.dao.StudentDao;
import com.roytuts.spring.jdbctemplate.batch.insert.model.Student;
@SpringBootApplication
public class SpringJdbcTemplateBatchInsertApp implements CommandLineRunner {
@Autowired
private StudentDao studentDao;
public static void main(String[] args) {
SpringApplication.run(SpringJdbcTemplateBatchInsertApp.class, args);
}
@Override
public void run(String... args) throws Exception {
List<Student> students = prepareStudents();
studentDao.insert(students);
}
private static List<Student> prepareStudents() {
List<Student> students = new ArrayList<Student>();
Student student = new Student();
student.setStudentName("Sumit");
student.setStudentDob("01-01-1980");
student.setStudentEmail("sumit@email.com");
student.setStudentAddress("Garifa");
students.add(student);
student = new Student();
student.setStudentName("Gourab");
student.setStudentDob("01-01-1982");
student.setStudentEmail("gourab@email.com");
student.setStudentAddress("Garia");
students.add(student);
student = new Student();
student.setStudentName("Debina");
student.setStudentDob("01-01-1982");
student.setStudentEmail("debina@email.com");
student.setStudentAddress("Salt Lake");
students.add(student);
student = new Student();
student.setStudentName("Souvik");
student.setStudentDob("01-01-1992");
student.setStudentEmail("souvik@email.com");
student.setStudentAddress("Alipore");
students.add(student);
student = new Student();
student.setStudentName("Liton");
student.setStudentDob("01-01-1990");
student.setStudentEmail("liton@email.com");
student.setStudentAddress("Salt Lake");
students.add(student);
return students;
}
}
Testing the Application
Now I will create a main class to test our application. I am creating few sample student objects and trying to insert from the main class.
Initially you do not have any datum in student table:

Once we run the above main class, we get the data inserted:

That’s all about inserting data in batch using Spring JdbcTemplate
API.