Tuesday, 5 March 2019

Spring + JdbcTemplate + How to insert multiple records using batch update

🚀 Master Java & Spring with Ram N Java! 🚀

Want more high-quality coding tutorials? Don't miss out on our latest updates!

Efficiently Inserting Multiple Records using Batch Update

In many real-world applications, you need to save a large list of items to your database at once. Instead of calling the database for every single item, which is slow, Spring JDBC allows you to use Batch Updates to perform multiple inserts in one single step.

Why Use Batch Updates?

Normally, if you insert 100 records one by one, your application has to talk to the database 100 times. With batching, you send all 100 records in one go. This reduces "network trips" and makes your application much faster!

The batchUpdate() Method

The JdbcTemplate class provides a method called batchUpdate(). This method takes two main things:

SQL Query: The standard INSERT statement with placeholders (?).
BatchPreparedStatementSetter: An object that helps you set the values for each record in the batch.

How It Works Step-by-Step

Prepare your list of objects (like a List<Employee>).
Write your SQL INSERT query.
Implement setValues() to map your object data to the SQL parameters.
Implement getBatchSize() to tell Spring how many records are in the list.

Pro Performance Tip:

Use Batch Updates whenever you are processing bulk data or importing CSV/Excel files into your database to ensure your application remains responsive.

Related Spring Tutorials

Check out these other helpful videos from our channel:

Click here to watch on Youtube:
https://www.youtube.com/watch?v=-AV9E2kB0Dw&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge:

Spring + JdbcTemplate + How to insert multiple records using batch update

Employee.sql

CREATE DATABASE org_db;

CREATE TABLE `employee` (

  `EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(100) NOT NULL,

  `AGE` INT(10) NOT NULL,

  `SALARY` INT(10) DEFAULT NULL,

  PRIMARY KEY (`EMPLOYEE_ID`)

) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

pom.xml

<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/maven-v4_0_0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.ram.core</groupId>

    <artifactId>SpringDemo</artifactId>

    <packaging>jar</packaging>

    <version>1.0-SNAPSHOT</version>

    <name>SpringDemo</name>

    <url>http://maven.apache.org</url>

    <properties>

        <spring.version>5.0.5.RELEASE</spring.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>3.8.1</version>

            <scope>test</scope>

        </dependency>

        <!-- Spring 5 dependencies -->

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-core</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-context</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-jdbc</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <!-- MySQL database driver -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>8.0.11</version>

        </dependency>

    </dependencies>

</project>

Employee.java

package com.ram.employee.model;

public class Employee

{

    private int employeeId;

    private String name;

    private int age;

    private int salary;

    public Employee(int employeeId, String name, int age, int salary)

    {

        super();

        this.employeeId = employeeId;

        this.name = name;

        this.age = age;

        this.salary = salary;

    }

    public int getEmployeeId()

    {

        return employeeId;

    }

    public void setEmployeeId(int employeeId)

    {

        this.employeeId = employeeId;

    }

    public String getName()

    {

        return name;

    }

    public void setName(String name)

    {

        this.name = name;

    }

    public int getAge()

    {

        return age;

    }

    public void setAge(int age)

    {

        this.age = age;

    }

    public int getSalary()

    {

        return salary;

    }

    public void setSalary(int salary)

    {

        this.salary = salary;

    }

    @Override

    public String toString()

    {

        return "Employee [employeeId=" + employeeId + ", name=" + name

                + ", age=" + age + ", salary=" + salary + "]";

    }

}

EmployeeDAO.java

package com.ram.employee.dao;

import java.util.List;

import com.ram.employee.model.Employee;

public interface EmployeeDAO

{

    public void insertEmployeeRecords(List<Employee> employeeList);

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.util.ArrayList;

import java.util.List;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl

        extends JdbcDaoSupport implements EmployeeDAO

{

    public void insertEmployeeRecords(List<Employee> employeeList)

    {

        String sql = "INSERT INTO EMPLOYEE "

                + "(EMPLOYEE_ID, NAME, AGE,SALARY) VALUES (?, ?, ?,?)";

        List<Object[]> batchArgsList = new ArrayList<Object[]>();

        for (Employee employee : employeeList)

        {

            Object[] objectArray = { employee.getEmployeeId(),

                    employee.getName(), employee.getAge(),

                    employee.getSalary() };

            batchArgsList.add(objectArray);

        }

        getJdbcTemplate().batchUpdate(sql, batchArgsList);

    }

}

applicationContext.xml

<beans xmlns="http://www.springframework.org/schema/beans"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

    xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">

    <bean id="dataSource"

        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="com.mysql.jdbc.Driver" />

        <property name="url" value="jdbc:mysql://localhost:3306/org_db" />

        <property name="username" value="root" />

        <property name="password" value="root" />

    </bean>

    <bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">

        <property name="dataSource" ref="dataSource" />

    </bean>

</beans>

App.java

package com.ram.core;

import java.util.ArrayList;

import java.util.List;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class App

{

    public static void main(String[] args)

    {

        ApplicationContext context = new ClassPathXmlApplicationContext(

                "applicationContext.xml");

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        Employee employee1 = new Employee(1, "Peter", 28, 70000);

        Employee employee2 = new Employee(2, "Dave", 30, 20000);

        Employee employee3 = new Employee(3, "Ram", 45, 50000);

        List<Employee> employeeList = new ArrayList<Employee>();

        employeeList.add(employee1);

        employeeList.add(employee2);

        employeeList.add(employee3);

        employeeDAO.insertEmployeeRecords(employeeList);

        System.out.println(

                "Employee records are inserted successfully.");

    }

}

Output:

Feb 18, 2019 9:52:38 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Mon Feb 18 09:52:38 IST 2019]; root of context hierarchy

Feb 18, 2019 9:52:38 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions

INFO: Loading XML bean definitions from class path resource [applicationContext.xml]

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

Feb 18, 2019 9:52:39 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

Mon Feb 18 09:53:29 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Employee records are inserted successfully.