Batch Insert using Spring JdbcTemplate

This tutorial will show you how we can insert a large dataset or perform batch insert into a database at once using Spring JdbcTemplate.

Sometimes we 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

Eclipse 2019-12, At least JDK 1.8, Gradle 6.4.1, Maven 3.6.3, Spring Core/Context/Jdbc, MySQL 8.0.17

Project Setup

Create either maven or gradle based project in Eclipse. The name of the project is spring-jdbctemplate-batch-insert.

If you are creating gradle based project then use below build.gradle script:

plugins {
    id 'java-library'
}

repositories {
    jcenter()
}

dependencies {
	implementation 'org.springframework:spring-core:5.2.7.RELEASE'
	implementation 'org.springframework:spring-context:5.2.7.RELEASE'
    implementation 'org.springframework:spring-jdbc:5.2.7.RELEASE'
    implementation 'mysql:mysql-connector-java:8.0.17'
    
    //required for JDK 9 or above
    implementation '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:

<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>
	<packaging>jar</packaging>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>at least 1.8</java.version>
	</properties>
	
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-core</artifactId>
			<version>5.2.7.RELEASE</version>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-context</artifactId>
			<version>5.2.7.RELEASE</version>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.2.7.RELEASE</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.17</version>
		</dependency>
		
		<!--required only if jdk 9 or higher version is used-->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
                <configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table

We need to create a table called student under roytuts database in MySQL server.

CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `student_dob` varchar(20) CHARACTER SET utf8mb4 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

We 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.

We are inserting data in batch. For this example we have declared the batch size only 2.

We 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

We need database configuration in order to establish database connectivity and perform database operations.

We 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 we create JdbcTemplate bean.

We scan the packages to pick the Spring annotated classes as beans.

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.ComponentScan;
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")
@ComponentScan(basePackages = "com.roytuts.spring.jdbctemplate.batch.insert")
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;
	}

}

Testing the Application

Now we will create a main class to test our application. We are creating few sample student objects and trying to insert from the main class.

We are using annotation based spring configuration to execute our application.

Initially we 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

Thanks for reading.

Related posts

Leave a Comment