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;
Result
-

Facing issues? Have Questions? Post them here! I am happy to answer!


Author Info:

Rakesh (He/Him) is a seasoned developer with over 10 years of experience in web and app development, and a deep knowledge of operating systems. Author of insightful How-To articles for Code2care.

Follow him on: X

You can also reach out to him via e-mail: rakesh@code2care.org

Copyright © Code2care 2024 | Privacy Policy | About Us | Contact Us | Sitemap