JdbcTemplate Batch Insert Example using Spring Boot

This is an example of how to perform Batch Insert using Spring Boot JdbcTemplate class.

Dependecy:

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.14</version>
</dependency>

application.properties

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

Batch Insert Example

import org.code2care.jdbctemplate.eg.jdbctemplateeg.entities.Employee;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.Arrays;
import java.util.List;

@Repository
public class JDBCTemplateRepo implements CommandLineRunner {

    private final JdbcTemplate jdbcTemplate;

    public JDBCTemplateRepo(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void batchInsertRecords(List<Employee> employees) {
        String sql = "INSERT INTO employee (employeeId, employeeName, employeeDateOfBirth, employeeDepartment, employeeJoiningDate, employeeSalary) VALUES (?, ?, ?, ?, ?, ?)";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Employee employee = employees.get(i);
                ps.setInt(1, employee.getEmployeeId());
                ps.setString(2, employee.getEmployeeName());
                ps.setDate(3, Date.valueOf(employee.getEmployeeDateOfBirth()));
                ps.setString(4, employee.getEmployeeDepartment());
                ps.setDate(5, Date.valueOf(employee.getEmployeeJoiningDate()));
                ps.setInt(6, employee.getEmployeeSalary());
            }

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

    @Override
    public void run(String... args) throws Exception {
        List<Employee> employees = Arrays.asList(
                new Employee(101, "John Smith", LocalDate.of(1990, 5, 15), "HR", LocalDate.of(2020, 1, 1),"Chicago",5000),
                new Employee(201, "Jane Doe", LocalDate.of(1995, 8, 25), "Sales", LocalDate.of(2019, 5, 1),"NYC", 6000),
                new Employee(301, "Bob Mandy", LocalDate.of(1985, 10, 10), "Marketing", LocalDate.of(2021, 2, 1),"Austin", 7000)
        );
        batchInsertRecords(employees);
    }
}

Employee.java

package org.code2care.jdbctemplate.eg.jdbctemplateeg.entities;

import java.time.LocalDate;

public class Employee {

    private int employeeId;
    private String employeeName;
    private LocalDate employeeDateOfBirth;
    private String employeeDepartment;
    private LocalDate employeeJoiningDate;
    private String employeeAddress;
    private int employeeSalary;

    public Employee(int employeeId, String employeeName, LocalDate employeeDateOfBirth, String employeeDepartment, LocalDate employeeJoiningDate, String employeeAddress, int employeeSalary) {
        this.employeeId = employeeId;
        this.employeeName = employeeName;
        this.employeeDateOfBirth = employeeDateOfBirth;
        this.employeeDepartment = employeeDepartment;
        this.employeeJoiningDate = employeeJoiningDate;
        this.employeeAddress = employeeAddress;
        this.employeeSalary = employeeSalary;
    }

    public int getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(int employeeId) {
        this.employeeId = employeeId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }

    public LocalDate getEmployeeDateOfBirth() {
        return employeeDateOfBirth;
    }

    public void setEmployeeDateOfBirth(LocalDate employeeDateOfBirth) {
        this.employeeDateOfBirth = employeeDateOfBirth;
    }

    public String getEmployeeDepartment() {
        return employeeDepartment;
    }

    public void setEmployeeDepartment(String employeeDepartment) {
        this.employeeDepartment = employeeDepartment;
    }

    public LocalDate getEmployeeJoiningDate() {
        return employeeJoiningDate;
    }

    public void setEmployeeJoiningDate(LocalDate employeeJoiningDate) {
        this.employeeJoiningDate = employeeJoiningDate;
    }

    public String getEmployeeAddress() {
        return employeeAddress;
    }

    public void setEmployeeAddress(String employeeAddress) {
        this.employeeAddress = employeeAddress;
    }

    public int getEmployeeSalary() {
        return employeeSalary;
    }

    public void setEmployeeSalary(int employeeSalary) {
        this.employeeSalary = employeeSalary;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "employeeId=" + employeeId +
                ", employeeName='" + employeeName + '\'' +
                ", employeeDateOfBirth=" + employeeDateOfBirth +
                ", employeeDepartment='" + employeeDepartment + '\'' +
                ", employeeJoiningDate=" + employeeJoiningDate +
                ", employeeAddress='" + employeeAddress + '\'' +
                ", employeeSalary=" + employeeSalary +
                '}';
    }
}

SQL Create Table Query:

CREATE TABLE `employee` (
  `employeeId` int(5) NOT NULL,
  `employeeName` varchar(255) NOT NULL,
  `employeeDateOfBirth` date NOT NULL,
  `employeeDepartment` varchar(255) NOT NULL,
  `employeeJoiningDate` date NOT NULL,
  `employeeSalary` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; COMMIT;

Have Questions? Post them here!