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=rootThe 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.