Batch Insert using Spring JdbcTemplate

Last Updated on March 3rd, 2021 at 03:14 pm

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:

batch insert spring jdbc template

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

batch insert spring jdbc template

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

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *