Records Insertion Using Spring Jdbc Template
This tutorial will show you how you can insert an object or multiple objects into a database using Spring JdbcTemplate
. So you are going to see how to insert single record and multiple records into database table. I am going to use PreparedStatement
and BatchPreparedStatementSetter
for inserting multiple records.
Sometimes you need to insert or update one or few records in the database. You can insert one or multiple records into database one by one in a traditional approach. Spring provides insert operation with the help of JdbcTemplate
, it inserts or updates records into database in one shot.
Prerequisites
Java 8+/19, Spring Boot 2.4.0/2.4.4/3.0.5, Gradle 6.5.1 – 6.8.3, Maven 3.6.3/3.8.5, MySQL 8.0.31
Project Setup
You can create either gradle or maven based project in your favorite IDE or tool. For gradle based project use the following build.gradle script:
buildscript {
ext {
springBootVersion = '2.4.0' to 2.4.4
}
repositories {
maven {
url 'https://plugins.gradle.org/m2/'
}
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
plugins {
id 'java-library'
id 'org.springframework.boot' version "${springBootVersion}"
}
sourceCompatibility = 12
targetCompatibility = 12
repositories {
mavenCentral()
jcenter()
}
dependencies {
implementation("org.springframework.boot:spring-boot-starter:${springBootVersion}")
implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
implementation('mysql:mysql-connector-java:8.0.17') //to 8.0.22
//required for JDK 9 or above
implementation 'javax.xml.bind:jaxb-api:2.4.0-b180830.0359'
}
For maven based project you can use the following pom.xml file:
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.roytuts</groupId>
<artifactId>spring-jdbctemplate-insert-single-multiple-records</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>19</maven.compiler.source>
<maven.compiler.target>19</maven.compiler.target>
</properties>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.0.5</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version>
</dependency>
<!--required only if jdk 9 or higher version is used -->
<dependency>
<groupId>javax.xml.bind</groupId>
<artifactId>jaxb-api</artifactId>
<version>2.4.0-b180830.0359</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
MySQL Table
I am creating one table student under roytuts database for testing single record insertion and multiple records insertion.
CREATE TABLE `student` (
`student_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
`student_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_dob` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_email` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
`student_address` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Datasource Configuration
I am creating an application.properties file under class path folder src/main/resources for declaring the database details.
#datasource
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=root
Now I am creating the Java based configuration to create DataSource
and JdbcTemplate
.
@Configuration
public class DbConfig {
@Autowired
private Environment environment;
@Bean
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(environment.getProperty("jdbc.driverClassName"));
dataSource.setUrl(environment.getProperty("jdbc.url"));
dataSource.setUsername(environment.getProperty("jdbc.username"));
dataSource.setPassword(environment.getProperty("jdbc.password"));
return dataSource;
}
@Bean
public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
return jdbcTemplate;
}
}
Model
The following model class maps the database table student.
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
private Integer studentId;
private String studentName;
private String studentDob;
private String studentEmail;
private String studentAddress;
public Student() {
}
public Student(Integer studentId, String studentName, String studentDob, String studentEmail,
String studentAddress) {
this.studentId = studentId;
this.studentName = studentName;
this.studentDob = studentDob;
this.studentEmail = studentEmail;
this.studentAddress = studentAddress;
}
// getters and setters
@Override
public String toString() {
return "Student [studentId=" + studentId + ", studentName=" + studentName + ", studentDob=" + studentDob
+ ", studentEmail=" + studentEmail + ", studentAddress=" + studentAddress + "]";
}
}
Mapper
To actually map table columns to Java fields in the Student class.
public class StudentDaoMapper implements RowMapper<Student> {
@Override
public Student mapRow(ResultSet rs, int arg1) throws SQLException {
Student student = new Student();
student.setStudentId(rs.getInt("student_id"));
student.setStudentName(rs.getString("student_name"));
student.setStudentDob(rs.getString("student_dob"));
student.setStudentEmail(rs.getString("student_email"));
student.setStudentAddress(rs.getString("student_address"));
return student;
}
}
Repository
This is not a Spring Data JPA repository but it’s a DAO layer code for interacting with database for performing the required operations for inserting records into the student table.
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
private final String SQL_STUDENT_INSERT = "INSERT INTO student(student_name,student_dob,student_email,student_address) values(?,?,?,?)";
public void insertSingleStudent(Student student) {
Object[] studentParam = new Object[] { student.getStudentName(), student.getStudentDob(),
student.getStudentEmail(), student.getStudentAddress() };
jdbcTemplate.update(SQL_STUDENT_INSERT, studentParam);
}
public void insertMultipleStudents(List<Student> students) {
jdbcTemplate.batchUpdate(SQL_STUDENT_INSERT, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement pStmt, int j) throws SQLException {
Student student = students.get(j);
pStmt.setString(1, student.getStudentName());
pStmt.setString(2, student.getStudentDob());
pStmt.setString(3, student.getStudentEmail());
pStmt.setString(4, student.getStudentAddress());
}
@Override
public int getBatchSize() {
return students.size();
}
});
}
}
Spring Boot Main Class
A class with main method and @SpringBootApplication
is sufficient to start the application. I am running the application in CLI by implementing the interface CommandLineRunner
.
@SpringBootApplication
public class SpringInsertSingleMultipleRecordsApp implements CommandLineRunner {
@Autowired
private StudentDao studentDao;
public static void main(String[] args) {
SpringApplication.run(SpringInsertSingleMultipleRecordsApp.class, args);
}
@Override
public void run(String... args) throws Exception {
studentDao.insertSingleStudent(populateStudent());
studentDao.insertMultipleStudents(populateStudents());
}
private Student populateStudent() {
Student student = new Student();
student.setStudentName("Sumit");
student.setStudentDob("01-01-1980");
student.setStudentEmail("sumit@email.com");
student.setStudentAddress("Garifa");
return student;
}
private List<Student> populateStudents() {
List<Student> students = new ArrayList<Student>();
Student student = new Student();
student.setStudentName("Sumit");
student.setStudentDob("01-01-1980");
student.setStudentEmail("sumit@email.com");
student.setStudentAddress("Garifa");
students.add(student);
student = new Student();
student.setStudentName("Anjisnu");
student.setStudentDob("01-01-1982");
student.setStudentEmail("gourab@email.com");
student.setStudentAddress("Garia");
students.add(student);
student = new Student();
student.setStudentName("Debina");
student.setStudentDob("01-01-1982");
student.setStudentEmail("debina@email.com");
student.setStudentAddress("Salt Lake");
students.add(student);
student = new Student();
student.setStudentName("Souvik");
student.setStudentDob("01-01-1992");
student.setStudentEmail("souvik@email.com");
student.setStudentAddress("Alipore");
students.add(student);
student = new Student();
student.setStudentName("Liton");
student.setStudentDob("01-01-1990");
student.setStudentEmail("liton@email.com");
student.setStudentAddress("Salt Lake");
students.add(student);
return students;
}
}
Testing Single And Multiple Records Insertion
Running the above class will insert the single record as well as multiple records into the table. The table will have the following data:
That’s all about inserting single and multiple records into database table using Spring JdbcTemplate
.