Docker 安装 mysql:5.7 并进行数据库测试

安装mysql 5.7 容器

1. 执行安装命令

$ sudo docker run -p 3306:3306 --name email_mysql \
-v /app/mysql/conf:/etc/mysql/conf.d \
-v /app/mysql/logs:/var/log/mysql \
-v /app/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

2. 查看执行结果

3. 使用navicat等数据库连接工具，连接mysql容器，默认用户名为root，密码即root

4. 创建数据库person，创建标user，使用utf8mb4编码格式。

SpringBoot编写查询逻辑

从底层开始

1. 新建mapper，命名：UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lucfzy.docker.dao.UserMapper">
    <resultMap id="BaseResultMap" type="com.lucfzy.docker.domain.User">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
    </resultMap>
    <sql id="Base_Column_List">
        id, `name`, age
    </sql>
    <select id="selectAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user
    </select>
</mapper>

2. 新建dao层，命名UserMapper.java

package com.lucfzy.docker.dao;

import com.lucfzy.docker.domain.User;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    List<User> selectAll();

}

3. 新增domain对象，命名User.java

package com.lucfzy.docker.domain;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private String id;

    private String name;

    private String age;

}

4. 编写sevice层，命名UserService.java

package com.lucfzy.docker.service;

import com.lucfzy.docker.dao.UserMapper;
import com.lucfzy.docker.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<User> doSearchUserInfo() {
        return userMapper.selectAll();
    }

}

5. 编写controller层，命名UserController.java

package com.lucfzy.docker.controller;

import com.alibaba.fastjson.JSONObject;
import com.lucfzy.docker.domain.User;
import com.lucfzy.docker.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/getUserInfo")
    public Object getUserInfo() {
        List<User> users = userService.doSearchUserInfo();
        return JSONObject.toJSON(users);
    }

}

6. 编写配置文件application.yml

server:
  port: 8080

spring:
  datasource:
    url: jdbc:mysql://192.168.231.128:3306/person?serverTimezone=GMT%2B8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: mapper/*.xml
  type-aliases-package: com.lucfzy.docker.dao

7. 编写pom.xml文件

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.75</version>
</dependency>

接口测试

使用postman等请求发送工具，进行接口请求测试。

GET http://192.168.231.128:8080/getUserInfo