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;
Facing issues? Have Questions? Post them here! I am happy to answer!
Author Info:
Rakesh (He/Him) has over 14+ years of experience in Web and Application development. He is the 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
More Posts related to Java,
- Get the current timestamp in Java
- Java Stream with Multiple Filters Example
- Java SE JDBC with Prepared Statement Parameterized Select Example
- Fix: UnsupportedClassVersionError: Unsupported major.minor version 63.0
- [Fix] Java Exception with Lambda - Cannot invoke because object is null
- 7 deadly java.lang.OutOfMemoryError in Java Programming
- How to Calculate the SHA Hash Value of a File in Java
- Java JDBC Connection with Database using SSL (https) URL
- How to Add/Subtract Days to the Current Date in Java
- Create Nested Directories using Java Code
- Spring Boot: JDBCTemplate BatchUpdate Update Query Example
- What is CA FE BA BE 00 00 00 3D in Java Class Bytecode
- Save Java Object as JSON file using Jackson Library
- Adding Custom ASCII Text Banner in Spring Boot Application
- [Fix] Java: Type argument cannot be of primitive type generics
- List of New Features in Java 11 (JEPs)
- Java: How to Add two Maps with example
- Java JDBC Transition Management using PreparedStatement Examples
- Understanding and Handling NullPointerException in Java: Tips and Tricks for Effective Debugging
- Steps of working with Stored Procedures using JDBCTemplate Spring Boot
- Java 8 java.util.Function and BiFunction Examples
- The Motivation Behind Generics in Java Programming
- Get Current Local Date and Time using Java 8 DateTime API
- Java: Convert Char to ASCII
- Deep Dive: Why avoid java.util.Date and Calendar Classes
More Posts:
- Java code to check Internet Connection on Android Device Programmatically - Android
- How to Open Trash on Mac - MacOS
- How to create a SharePoint list from an existing list with same columns, views, formatting, data - SharePoint
- Install RabbitMQ on Docker - Docker
- Cannot access Windows application shortcuts on Start menu and Taskbar - Windows
- Install Homebrew (brew) on Mac M2 - MacOS
- Clear browsing history in Safari on Mac Ventura 13 - MacOS
- WARNING: pip is being invoked by an old script wrapper. This will fail in a future version of pip. - PIP