Spring Boot 2 - JdbcTemplate SELECT query example to MySQL database

package com.example.demo;

​

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

​

@SpringBootApplication

public class DemoApplication {

​

    public static void main(String[] args) {

        SpringApplication.run(DemoApplication.class, args);

    }

}

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.RowMapper;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.ResponseBody;

​

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

​

@Controller

public class UsersController {

​

    @Autowired

    private JdbcTemplate jdbcTemplate;

​

    // http://localhost:8080/api/users

    //

    @GetMapping(

        value = "/api/users",

        produces = MediaType.APPLICATION_JSON_VALUE

    )

    @ResponseBody

    public List<UserEntity> getUsers() {

        String query = "SELECT `id`, `name`, `email` FROM `users`";

        List<UserEntity> users = this.jdbcTemplate.query(query, new UserRowMapper());

        return users;

    }

​

    private class UserRowMapper implements RowMapper<UserEntity> {

        @Override

        public UserEntity mapRow(ResultSet resultSet, int rowNumber) throws SQLException {

            UserEntity user = new UserEntity();

            user.setId(resultSet.getLong("id"));

            user.setName(resultSet.getString("name"));

            user.setEmail(resultSet.getString("email"));

            return user;

        }

    }

}

package com.example.demo;

​

public class UserEntity {

​

    private long id;

    private String name;

    private String email;

​

    public UserEntity() { }

​

    public UserEntity(long id, String name, String email) {

        this.id = id;

        this.name = name;

        this.email = email;

    }

​

    public long getId() {

        return this.id;

    }

​

    public void setId(long id) {

        this.id = id;

    }

​

    public String getName() {

        return this.name;

    }

​

    public void setName(String name) {

        this.name = name;

    }

​

    public String getEmail() {

        return this.email;

    }

​

    public void setEmail(String email) {

        this.email = email;

    }

}

db.host=localhost

db.port=3306

db.name=example_db

​

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.url=jdbc:mysql://${db.host}:${db.port}/${db.name}?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=UTC&character_set_server=utf8mb4

spring.datasource.username=root

spring.datasource.password=root

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">

​

    <modelVersion>4.0.0</modelVersion>

​

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>2.6.5</version>

        <relativePath/> <!-- lookup parent from repository -->

    </parent>

​

    <groupId>com.example</groupId>

    <artifactId>demo</artifactId>

    <version>0.0.1-SNAPSHOT</version>

    <name>demo</name>

    <description>Demo project for Spring Boot</description>

​

    <properties>

        <java.version>1.8</java.version>

    </properties>

​

    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

​

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </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.22</version>

        </dependency>

​

    </dependencies>

​

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

        </plugins>

    </build>

​

</project>

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

    ('John', 'john@mail.com'),

    ('Chris', 'chris@mail.com'),

    ('Kate', 'kate@mail.com'),

    ('Denis', 'denis@mail.com');