SpringBoot2.0数据访问（一）JDBCTemplate

本文作者：Spring_ZYL
意见反馈：spring_zyl@163.com
文章来源：https://www.jianshu.com/u/1b5928185b73
版权声明：本文版权归作者所有，转载请注明出处

一、运行环境

• IDE：IDEA 2018.1.1
• Tomcat：8.5
• 数据库：MySQL
• 测试工具：Postman

一、创建一个SpringBoot项目

1.使用IDEA创建项目

• Spring Boot版本：2.0.2
• 选中的依赖：Web、MySQL、JDBC

4.png

2.项目目录结构

6.png

3.pom文件内容

<?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.demo</groupId>
    <artifactId>springboot-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>springboot-jdbc</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

二、创建数据库

创建用户表users

7.png

添加两条数据

12.png

三、配置数据库链接

在resources中创建application.yml文件，并输入参数

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://127.0.0.1:3306/springboot_db
    driver-class-name: com.mysql.jdbc.Driver

这样的配置，默认使用HikariDataSource数据源

com.zaxxer.hikari.HikariDataSource

四、实例测试

1.创建一个Controller类

此类通过Spring自带的JdbcTemplate类来查询数据库中的数据

package com.demo.springboot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;


@RestController
@RequestMapping("/jdbc")
public class JdbcController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @RequestMapping("/list")
    public List<Map<String, Object>> queryUsers(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from users");
        return list;
    }
}

2.测试结果

使用Postman工具，发送 http://127.0.0.1:8080/jdbc/list 请求，返回结果如下

[
    {
        "id": 1,
        "username": "xiaoming",
        "password": "1234",
        "phone": "15090909090"
    },
    {
        "id": 2,
        "username": "mimi",
        "password": "123",
        "phone": "15222222222"
    }
]

如下图所示：

14.png