Spring Boot 2 - JdbcTemplate UPDATE query example to MySQL database

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collections;

@Controller
public class UsersController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // POST http://localhost:8080/api/users/1/update
    // echo '{"name":"john","email":"john@email.com"}' | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8080/api/users/1/update
    //
    @PostMapping(
            value = "/api/users/{id}/update",
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    @ResponseBody
    public Object updateUser(
            @PathVariable("id") Long userId,
            @RequestBody UserEntity userEntity
    ) throws SQLException {
        String query = "UPDATE `users` SET `name` = ?, `email` = ? WHERE `id` = ?";
        PreparedStatementCreator statementCreator = (Connection connection) -> {
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setString(1, userEntity.getName());
            preparedStatement.setString(2, userEntity.getEmail());
            preparedStatement.setLong(3, userId);
            return preparedStatement;
        };
        int updatesCount = this.jdbcTemplate.update(statementCreator);
        return Collections.singletonMap("result", updatesCount == 1);
    }
}


// DATABASE PREPARATION:

/*

    CREATE DATABASE `example_db`


    CREATE TABLE `users` (
        `id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(100) NOT NULL,
        `email` VARCHAR(255) NOT NULL,
        PRIMARY KEY (`id`)
    )
    ENGINE=InnoDB;


    INSERT INTO `users`
        (`name`, `email`)
    VALUES
        ('Chris', 'chris@mail.com'),
        ('Kate', 'kate@mail.com'),
        ('Denis', 'denis@mail.com');

*/