EN
Spring Boot 2 - JdbcTemplate INSERT query example to MySQL database
3 points
In this article, we would like to show how to execute INSERT query to MySQL database in Spring Boot 2 application that uses JdbcTemplate API.
Final result:
Project structure:
DemoApplication.java file:
xxxxxxxxxx1
package com.example.demo;2
3
import org.springframework.boot.SpringApplication;4
import org.springframework.boot.autoconfigure.SpringBootApplication;5
6
7
public class DemoApplication {8
9
public static void main(String[] args) {10
SpringApplication.run(DemoApplication.class, args);11
}12
}
UsersController.java file:
xxxxxxxxxx1
package com.example.demo;2
3
import org.springframework.beans.factory.annotation.Autowired;4
import org.springframework.http.MediaType;5
import org.springframework.jdbc.core.JdbcTemplate;6
import org.springframework.jdbc.core.PreparedStatementCreator;7
import org.springframework.jdbc.core.RowMapper;8
import org.springframework.jdbc.support.GeneratedKeyHolder;9
import org.springframework.jdbc.support.KeyHolder;10
import org.springframework.stereotype.Controller;11
import org.springframework.web.bind.annotation.PostMapping;12
import org.springframework.web.bind.annotation.RequestBody;13
import org.springframework.web.bind.annotation.ResponseBody;14
15
import java.sql.*;16
import java.util.List;17
18
19
public class UsersController {20
21
22
private JdbcTemplate jdbcTemplate;23
24
// POST http://localhost:8080/api/user/create25
// echo '{"name":"john","email":"john@email.com"}' | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8080/api/user/create26
//27
(28
value = "/api/user/create",29
consumes = MediaType.APPLICATION_JSON_VALUE,30
produces = MediaType.APPLICATION_JSON_VALUE31
)32
33
public UserEntity createUser( UserEntity userEntity) throws SQLException {34
long userId = this.insertUser(userEntity);35
return this.fetchUser(userId);36
}37
38
private long insertUser(UserEntity userEntity) throws SQLException {39
String query = "INSERT INTO `users`\n" +40
"\t(`name`, `email`)\n" +41
"VALUES\n" +42
"\t(?, ?)";43
KeyHolder keyHolder = new GeneratedKeyHolder();44
PreparedStatementCreator statementCreator = (Connection connection) -> {45
PreparedStatement preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);46
preparedStatement.setString(1, userEntity.getName());47
preparedStatement.setString(2, userEntity.getEmail());48
return preparedStatement;49
};50
int updatesCount = this.jdbcTemplate.update(statementCreator, keyHolder);51
if (updatesCount == 1) {52
Number generatedKey = keyHolder.getKey();53
if (generatedKey == null) {54
throw new SQLException("Getting user id error.");55
}56
return generatedKey.longValue();57
}58
throw new SQLException("Expected one row insert."); // should never happen59
}60
61
private UserEntity fetchUser(long userId) {62
String query = "SELECT `id`, `name`, `email`\n" +63
"FROM `users`\n" +64
"WHERE `id` = ?\n" +65
"LIMIT 1";66
PreparedStatementCreator statementCreator = (Connection connection) -> {67
PreparedStatement preparedStatement = connection.prepareStatement(query);68
preparedStatement.setLong(1, userId);69
return preparedStatement;70
};71
List<UserEntity> users = this.jdbcTemplate.query(statementCreator, new UserRowMapper());72
if (users.isEmpty()) {73
return null;74
}75
return users.get(0);76
}77
78
private class UserRowMapper implements RowMapper<UserEntity> {79
80
public UserEntity mapRow(ResultSet resultSet, int rowNumber) throws SQLException {81
UserEntity user = new UserEntity();82
user.setId(resultSet.getLong("id"));83
user.setName(resultSet.getString("name"));84
user.setEmail(resultSet.getString("email"));85
return user;86
}87
}88
}
UserEntity.java file:
xxxxxxxxxx1
package com.example.demo;2
3
public class UserEntity {4
5
private long id;6
private String name;7
private String email;8
9
public UserEntity() { }10
11
public UserEntity(long id, String name, String email) {12
this.id = id;13
this.name = name;14
this.email = email;15
}16
17
public long getId() {18
return this.id;19
}20
21
public void setId(long id) {22
this.id = id;23
}24
25
public String getName() {26
return this.name;27
}28
29
public void setName(String name) {30
this.name = name;31
}32
33
public String getEmail() {34
return this.email;35
}36
37
public void setEmail(String email) {38
this.email = email;39
}40
}
application.properties file:
xxxxxxxxxx1
db.host=localhost2
db.port=33063
db.name=example_db4
5
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver6
spring.datasource.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&character_set_server=utf8mb47
spring.datasource.username=root8
spring.datasource.password=root
pom.xml file:
xxxxxxxxxx1
2
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"3
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">4
5
<modelVersion>4.0.0</modelVersion>6
7
<parent>8
<groupId>org.springframework.boot</groupId>9
<artifactId>spring-boot-starter-parent</artifactId>10
<version>2.6.5</version>11
<relativePath/> <!-- lookup parent from repository -->12
</parent>13
14
<groupId>com.example</groupId>15
<artifactId>demo</artifactId>16
<version>0.0.1-SNAPSHOT</version>17
<name>demo</name>18
<description>Demo project for Spring Boot</description>19
20
<properties>21
<java.version>1.8</java.version>22
</properties>23
24
<dependencies>25
<dependency>26
<groupId>org.springframework.boot</groupId>27
<artifactId>spring-boot-starter-web</artifactId>28
</dependency>29
30
<dependency>31
<groupId>org.springframework.boot</groupId>32
<artifactId>spring-boot-starter-test</artifactId>33
<scope>test</scope>34
</dependency>35
36
<dependency>37
<groupId>org.springframework.boot</groupId>38
<artifactId>spring-boot-starter-jdbc</artifactId>39
</dependency>40
41
<dependency>42
<groupId>mysql</groupId>43
<artifactId>mysql-connector-java</artifactId>44
<version>8.0.22</version>45
</dependency>46
</dependencies>47
48
<build>49
<plugins>50
<plugin>51
<groupId>org.springframework.boot</groupId>52
<artifactId>spring-boot-maven-plugin</artifactId>53
</plugin>54
</plugins>55
</build>56
57
</project>58
xxxxxxxxxx1
CREATE DATABASE `example_db`2
3
4
CREATE TABLE `users` (5
`id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,6
`name` VARCHAR(100) NOT NULL,7
`email` VARCHAR(255) NOT NULL,8
PRIMARY KEY (`id`)9
)10
ENGINE=InnoDB;
