Batch Insert using Spring JdbcTemplate

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:

Batch insert using Spring Data JPA

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.

Source Code

Download

Roy Tutorials