Tuesday, 5 March 2019

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

🚀 Ready to Master Spring JDBC? 🚀

Get the latest Java tutorials and industry best practices!

Efficient Batch Inserts using NamedParameterJdbcTemplate

In high-performance Java applications, inserting data record-by-record can be slow. This is where Batch Updates come in! In this tutorial, we dive deep into using NamedParameterJdbcTemplate to handle multiple inserts efficiently.

What is a Batch Insert?

A batch insert allows you to group multiple SQL INSERT statements together and send them to the database in one go. This drastically reduces the network overhead and speeds up your application.

Why use Named Parameters?

Standard JdbcTemplate uses ? as placeholders, which can get confusing if you have many columns. NamedParameterJdbcTemplate lets you use descriptive names like :firstName or :email, making your SQL queries much easier to read and maintain.

Key Steps to Implement

Create your Model: Ensure your Java class (e.g., Employee) matches your database table.
Prepare the SQL: Write your insert query using named placeholders.
Map your Data: Use SqlParameterSourceUtils.createBatch to convert a list of objects into an array of parameters.
Execute: Use the batchUpdate() method of the template.

Beginner Tip:

Always use batch processing when dealing with more than a few dozen records to keep your database interactions snappy!

Explore More Tutorials

Continue your Spring JDBC journey with these related videos:

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

Click the below Image to Enlarge:

Spring + NamedParameterJdbcTemplate + 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 batchInsert(List<Employee> employeeList);

}

EmployeeDAOImpl.java

package com.ram.employee.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;

import com.ram.employee.dao.EmployeeDAO;

import com.ram.employee.model.Employee;

public class EmployeeDAOImpl

        extends NamedParameterJdbcDaoSupport implements EmployeeDAO

{

    public void batchInsert(List<Employee> employeeList)

    {

        String sql = "INSERT INTO EMPLOYEE "

                + "(EMPLOYEE_ID, NAME, AGE,SALARY) VALUES (:employeeId, :name, :age,:salary)";

        SqlParameterSource[] parameters = SqlParameterSourceUtils

                .createBatch(employeeList.toArray());

        getNamedParameterJdbcTemplate().batchUpdate(sql, parameters);

    }

}

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 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");

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

        Employee employee2 = new Employee(2, "Dave", 38, 90000);

        Employee employee3 = new Employee(3, "John", 48, 10000);

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

        employeeList.add(employee1);

        employeeList.add(employee2);

        employeeList.add(employee3);

        EmployeeDAO employeeDAO = (EmployeeDAO) context

                .getBean("employeeDAO");

        employeeDAO.batchInsert(employeeList);

        System.out.println(

                "Employee records are inserted successfully.");

    }

}

Output:

Feb 18, 2019 10:34:29 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh

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

Feb 18, 2019 10:34:29 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 10:34:30 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName

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

Mon Feb 18 10:35:07 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.