Posted in Spring JDBC

Single and Multiple Records Insert using Spring JdbcTemplate

Records Insertion Using Spring Jdbc Template

This tutorial will show you how you can insert an object or multiple objects into a database using Spring JdbcTemplate. So you are going to see how to insert single record and multiple records into database table. I am going to use PreparedStatement and BatchPreparedStatementSetter for inserting multiple records.

Sometimes you need to insert or update one or few records in the database. You 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.

Prerequisites

Java 8+/19, Spring Boot 2.4.0/2.4.4/3.0.5, Gradle 6.5.1 – 6.8.3, Maven 3.6.3/3.8.5, MySQL 8.0.31

Project Setup

You can create either gradle or maven based project in your favorite IDE or tool. For gradle based project use the following build.gradle script:

buildscript {
	ext {
		springBootVersion = '2.4.0' to 2.4.4
	}
	
    repositories {
    	maven {
    		url 'https://plugins.gradle.org/m2/'
    	}
    }
    
    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 {
    mavenCentral()
    jcenter()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
	
	implementation('mysql:mysql-connector-java:8.0.17') //to 8.0.22
	
	//required for JDK 9 or above
    implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}

For maven based project you can use the following 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-insert-single-multiple-records</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>19</maven.compiler.source>
		<maven.compiler.target>19</maven.compiler.target>
	</properties>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.0.5</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>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.31</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.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
</project>

MySQL Table

I am creating one table student under roytuts database for testing single record insertion and multiple records insertion.

CREATE TABLE `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(10) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Datasource Configuration

I am creating an application.properties file under class path folder src/main/resources for declaring the database details.

#datasource
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=root

Now I am creating the Java based configuration to create DataSource and JdbcTemplate.

@Configuration
public class DbConfig {

    @Autowired
    private Environment environment;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(environment.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(environment.getProperty("jdbc.url"));
        dataSource.setUsername(environment.getProperty("jdbc.username"));
        dataSource.setPassword(environment.getProperty("jdbc.password"));

        return dataSource;
    }

    @Bean
    public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
        return jdbcTemplate;
    }

}

Model

The following model class maps the database table student.

public class Student implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer studentId;
    private String studentName;
    private String studentDob;
    private String studentEmail;
    private String studentAddress;

    public Student() {
    }

    public Student(Integer studentId, String studentName, String studentDob, String studentEmail,
            String studentAddress) {
        this.studentId = studentId;
        this.studentName = studentName;
        this.studentDob = studentDob;
        this.studentEmail = studentEmail;
        this.studentAddress = studentAddress;
    }

    // getters and setters

    @Override
    public String toString() {
        return "Student [studentId=" + studentId + ", studentName=" + studentName + ", studentDob=" + studentDob
                + ", studentEmail=" + studentEmail + ", studentAddress=" + studentAddress + "]";
    }

}

Mapper

To actually map table columns to Java fields in the Student class.

public class StudentDaoMapper implements RowMapper<Student> {

    @Override
    public Student mapRow(ResultSet rs, int arg1) throws SQLException {
        Student student = new Student();

        student.setStudentId(rs.getInt("student_id"));
        student.setStudentName(rs.getString("student_name"));
        student.setStudentDob(rs.getString("student_dob"));
        student.setStudentEmail(rs.getString("student_email"));
        student.setStudentAddress(rs.getString("student_address"));

        return student;
    }

}

Repository

This is not a Spring Data JPA repository but it’s a DAO layer code for interacting with database for performing the required operations for inserting records into the student table.

@Repository
public class StudentDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final String SQL_STUDENT_INSERT = "INSERT INTO student(student_name,student_dob,student_email,student_address) values(?,?,?,?)";

    public void insertSingleStudent(Student student) {
        Object[] studentParam = new Object[] { student.getStudentName(), student.getStudentDob(),
                student.getStudentEmail(), student.getStudentAddress() };

        jdbcTemplate.update(SQL_STUDENT_INSERT, studentParam);
    }

    public void insertMultipleStudents(List<Student> students) {
        jdbcTemplate.batchUpdate(SQL_STUDENT_INSERT, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement pStmt, int j) throws SQLException {
                Student student = students.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 students.size();
            }

        });
    }

}

Spring Boot Main Class

A class with main method and @SpringBootApplication is sufficient to start the application. I am running the application in CLI by implementing the interface CommandLineRunner.

@SpringBootApplication
public class SpringInsertSingleMultipleRecordsApp implements CommandLineRunner {

    @Autowired
    private StudentDao studentDao;

    public static void main(String[] args) {
        SpringApplication.run(SpringInsertSingleMultipleRecordsApp.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        studentDao.insertSingleStudent(populateStudent());
        studentDao.insertMultipleStudents(populateStudents());
    }

    private Student populateStudent() {
        Student student = new Student();

        student.setStudentName("Sumit");
        student.setStudentDob("01-01-1980");
        student.setStudentEmail("sumit@email.com");
        student.setStudentAddress("Garifa");

        return student;
    }

    private List<Student> populateStudents() {
        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("Anjisnu");
        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 Single And Multiple Records Insertion

Running the above class will insert the single record as well as multiple records into the table. The table will have the following data:

insert single and multiple records using spring jdbctemplate

That’s all about inserting single and multiple records into database table using Spring JdbcTemplate.

Source Code

Download

Leave a Reply

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