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:

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

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