Introduction

We will see example on single and multiple insert using Spring JdbcTemplate. We have seen similar example using XML based configuration but here we will see annotation based configuration to insert single or multiple records into database table. We will use here MySQL server 8.0.17 with Spring JdbcTemplate to insert data into database table.

We can insert one or multiple records into database one by one in a traditional approach. Spring provides insert operation with the help of JdbcTemplate, it inserts or updates records into database in one shot.

You may also read how to create annotation based standalone Spring Application.

Prerequisites

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

Creating Project

Create a gradle based project in Eclipse. The project name is spring-jdbctemplate-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/roytuts
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 roytuts.

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.roytuts.spring.jdbctemplate.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.roytuts.spring.jdbctemplate.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.roytuts.spring.jdbctemplate.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 two methods for inserting single record and multiple records respectively.

package com.roytuts.spring.jdbctemplate.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.roytuts.spring.jdbctemplate.insertion.model.Cd;
@Repository
public class CdDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	private final String SQL_CD_INSERT = "INSERT INTO cd(id,title,artist) values(?,?,?)";
	public void insertSingleRecord(Cd cd) {
		Object[] studentParam = new Object[] { cd.getId(), cd.getTitle(), cd.getArtist() };
		jdbcTemplate.update(SQL_CD_INSERT, studentParam);
	}
	public void insertMultipleRecords(List<Cd> cds) {
		jdbcTemplate.batchUpdate(SQL_CD_INSERT, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pStmt, int j) throws SQLException {
				Cd cd = cds.get(j);
				pStmt.setLong(1, cd.getId());
				pStmt.setString(2, cd.getTitle());
				pStmt.setString(3, cd.getArtist());
			}
			@Override
			public int getBatchSize() {
				return cds.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.roytuts.spring.jdbctemplate.insertion;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.roytuts.spring.jdbctemplate.insertion.config.SpringJdbcConfig;
import com.roytuts.spring.jdbctemplate.insertion.dao.CdDao;
import com.roytuts.spring.jdbctemplate.insertion.model.Cd;
public class SpringJdbcTemplateInsertApp {
	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("Single Insertion using JdbcTemplate has been started");
		Cd cd = new Cd();
		cd.setId(10l);
		cd.setTitle("Single Title");
		cd.setArtist("Single Artist");
		cdDao.insertSingleRecord(cd);
		System.out.println("Single Insertion using JdbcTemplate has been completed");
		System.out.println();
		System.out.println("Multiple Insertion using JdbcTemplate has been started");
		cdDao.insertMultipleRecords(getCdList());
		System.out.println("Multiple 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:

Single Insertion using JdbcTemplate has been started
Single Insertion using JdbcTemplate has been completed
Multiple Insertion using JdbcTemplate has been started
Multiple Insertion using JdbcTemplate has been completed

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

The output is shown into the below image:

single and multiple insert using spring jdbctemplate

From the above image we can see that before insertion the record in the cd table was 0 and after insertion the records are stored into the MySQL server.

Source Code

Thanks for reading.

Tags:

Leave a Reply

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