Introduction

In this post we will see an example on batch insertion using Spring JdbcTemplate. We had seen the similar example using XML configuration previously but here we will create annotation based application. So we will see how we can insert a large data-set 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 into database in one shot.

You may also like to read Batch insert using Spring Data JPA.

Prerequisites

Eclipse Neon, Java 1.8, Spring 5.1.8, Gradle 5.4.1, MySQL 8.0.17

Creating Project

Create a gradle based project in Eclipse. The project name is spring-jdbctemplate-batch-insertion.

Updating Build Script

The build.gradle file generated by Eclipse needs to be updated to include the required dependencies for our application.

The content of the build script is given below:

buildscript {
	ext {
		springVersion = '5.1.8.RELEASE'
	}
}

apply plugin: 'java'

sourceCompatibility = 1.8
targetCompatibility = 1.8

repositories {
    mavenLocal()
    mavenCentral()
}

dependencies {
	implementation("org.springframework:spring-core:${springVersion}")
	implementation("org.springframework:spring-beans:${springVersion}")
	implementation("org.springframework:spring-context:${springVersion}")
	implementation("org.springframework:spring-jdbc:${springVersion}")
	implementation("mysql:mysql-connector-java:8.0.17")
}

Configuring Database Properties

Create a file database.properties with the below content under src/main/resources folder to configure database properties for creating datasource.

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/jeejava
spring.datasource.username=root
spring.datasource.password=root

Creating Table in MySQL

We are going to insert data into MySQL table, so we need to create a table in MySQL server under database jeejava.

Create a table called cd with below structure:

CREATE TABLE `cd` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `artist` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Creating DataSource

We need to create datasource in order to communicate with database and perform operations into database.

The below class creates datasource and JdbcTemplate beans so that we can use these two beans throughout the application wherever required.

package com.jeejava.spring.jdbctemplate.batch.insertion.config;

import javax.sql.DataSource;

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:database.properties")
@ComponentScan(basePackages = "com.jeejava.spring.jdbctemplate.batch.insertion")
public class SpringJdbcConfig {

	@Autowired
	private Environment env;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
		dataSource.setUrl(env.getProperty("spring.datasource.url"));
		dataSource.setUsername(env.getProperty("spring.datasource.username"));
		dataSource.setPassword(env.getProperty("spring.datasource.password"));

		return dataSource;
	}

	@Bean
	public JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}

}

Creating Model Class

We are using Java application to insert data. So we will map our database table with Java class attributes.

Therefore create a below model class – cd.

package com.jeejava.spring.jdbctemplate.batch.insertion.model;

public class Cd {

	private Long id;
	private String title;
	private String artist;

        //getters and setters
}

Creating DAO Class

We generally perform database operations in DAO layer.

The below DAO class defines a method for inserting records in batch. Ideally batch size should be more in real application.

package com.jeejava.spring.jdbctemplate.batch.insertion.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.jeejava.spring.jdbctemplate.batch.insertion.model.Cd;

@Repository
public class CdDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	private final int INSERT_BATCH_SIZE = 2;
	private final String SQL_CD_INSERT = "INSERT INTO cd(id,title,artist) values(?,?,?)";

	public void insert(List<Cd> cds) {
		for (int i = 0; i < cds.size(); i += INSERT_BATCH_SIZE) {

			final List<Cd> batchList = cds.subList(i,
					i + INSERT_BATCH_SIZE > cds.size() ? cds.size() : i + INSERT_BATCH_SIZE);

			jdbcTemplate.batchUpdate(SQL_CD_INSERT, new BatchPreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement pStmt, int j) throws SQLException {
					Cd cd = batchList.get(j);
					pStmt.setLong(1, cd.getId());
					pStmt.setString(2, cd.getTitle());
					pStmt.setString(3, cd.getArtist());
				}

				@Override
				public int getBatchSize() {
					return batchList.size();
				}
			});
		}
	}

}

Creating Main Class

We will create a class that has main method to test our application.

Generally DAO layer is injected into service layer but for our simple example we will inject into main class to test the application.

package com.jeejava.spring.jdbctemplate.batch.insertion;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.jeejava.spring.jdbctemplate.batch.insertion.config.SpringJdbcConfig;
import com.jeejava.spring.jdbctemplate.batch.insertion.dao.CdDao;
import com.jeejava.spring.jdbctemplate.batch.insertion.model.Cd;

public class SpringJdbcTemplateBatchInsertApp {

	public static void main(String[] args) {
		AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext();

		context.register(SpringJdbcConfig.class);
		context.refresh();

		CdDao cdDao = context.getBean(CdDao.class);

		System.out.println("Batch Insertion using JdbcTemplate has been started");

		cdDao.insert(getCdList());

		System.out.println("Batch Insertion using JdbcTemplate has been completed");

		context.close();
	}

	private static List<Cd> getCdList() {
		List<Cd> cds = new ArrayList<>();

		Cd cd = new Cd();
		cd.setId(1l);
		cd.setTitle("Title 1");
		cd.setArtist("Artist 1");

		cds.add(cd);

		cd = new Cd();
		cd.setId(2l);
		cd.setTitle("Title 2");
		cd.setArtist("Artits 2");

		cds.add(cd);

		cd = new Cd();
		cd.setId(3l);
		cd.setTitle("Title 3");
		cd.setArtist("Artist 3");

		cds.add(cd);

		cd = new Cd();
		cd.setId(4l);
		cd.setTitle("Title 4");
		cd.setArtist("Artist 4");

		cds.add(cd);

		cd = new Cd();
		cd.setId(5l);
		cd.setTitle("Title 5");
		cd.setArtist("Artist 5");

		cds.add(cd);

		return cds;
	}

}

Testing the Application

Now if you run the above main class. You will see the below output in the console:

Batch Insertion using JdbcTemplate has been started
Batch Insertion using JdbcTemplate has been completed

You will see that all records got inserted into the cd table in MySQL server under database jeejava.

The output is shown into the below image:

batch insert using spring jdbctemplate

Source Code

You can download source code.

Thanks for reading.

Tags:

Leave a Reply

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