Spring Boot: NamedParameterJdbcTemplate batch insert example

In this example, we take a look at how to make use of NamedParameterJdbcTemplate to perform batch insert/update using JDBCTemplate in Spring Boot.

Make sure to create the Employee Pojo class.

import org.code2care.jdbctemplate.eg.jdbctemplateeg.entities.Employee;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.Date;
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) {

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

        String sql = "INSERT INTO employee (employeeId, employeeName, employeeDateOfBirth, employeeDepartment, employeeJoiningDate, employeeSalary) VALUES (:id, :name, :dob, :dept, :joinDate, :salary)";

        SqlParameterSource[] batch = employees.stream()
                .map(employee -> new MapSqlParameterSource()
                        .addValue("id", employee.getEmployeeId())
                        .addValue("name", employee.getEmployeeName())
                        .addValue("dob", Date.valueOf(employee.getEmployeeDateOfBirth()))
                        .addValue("dept", employee.getEmployeeDepartment())
                        .addValue("joinDate", Date.valueOf(employee.getEmployeeJoiningDate()))
                        .addValue("salary", employee.getEmployeeSalary()))
                .toArray(SqlParameterSource[]::new);

        namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    @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);
    }
}

Result:

Other configurations

applications.properties

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

pom.xml

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.code2care.jdbctemplate.eg</groupId>
    <artifactId>jdbctemplateeg</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>jdbctemplateeg</name>
    <description>Spring Boot JDBC Template Example</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.14</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Have Questions? Post them here!