Spring Boot入门(六)：使用MyBatis访问MySql数据库(注解方式)

本系列博客记录自己学习Spring Boot的历程，如帮助到你，不胜荣幸，如有错误，欢迎指正！

本篇博客我们讲解下在Spring Boot中使用MyBatis访问MySql数据库的简单用法。

1.前期准备

假设你的机器已经安装好了MySql，我们先执行如下语句创建数据库和表：

CREATE DATABASE springbootaction_db;

create table author
(
  author_id   int auto_increment comment '作者id' primary key,
  author_name varchar(20) not null comment '姓名',
  pen_name    varchar(20) not null comment '笔名'
)
comment '作者';

2.修改pom文件

pom文件引入mybatis的starter pom和mysql的驱动，因后面要编写控制器，因此也引入下阿里巴巴的fastjson：

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.35</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.47</version>
</dependency>

说明：引入了mybatis-spring-boot-starter后，可以不再引用spring-boot-starter-jdbc,因为前者已经依赖于后者。

3.配置数据源

在resources/application.yml中配置数据源：

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

4.定义数据库实体

定义数据库实体Author：

package com.zwwhnly.springbootaction.mybatis.entity;

import com.alibaba.fastjson.annotation.JSONField;

public class Author {
    @JSONField(name = "author_id")
    private Integer authorId;
    @JSONField(name = "author_name")
    private String authorName;
    @JSONField(name = "pen_name")
    private String penName;

    public Integer getAuthorId() {
        return authorId;
    }

    public void setAuthorId(Integer authorId) {
        this.authorId = authorId;
    }

    public String getAuthorName() {
        return authorName;
    }

    public void setAuthorName(String authorName) {
        this.authorName = authorName;
    }

    public String getPenName() {
        return penName;
    }

    public void setPenName(String penName) {
        this.penName = penName;
    }
}

5.编写Dao层代码

定义接口AuthorMapper：

package com.zwwhnly.springbootaction.mybatis.annotation;

import com.zwwhnly.springbootaction.mybatis.entity.Author;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AuthorMapper {
    @Insert("insert into author(author_name, pen_name) values(#{author_name}, #{pen_name})")
    int add(@Param("author_name") String authorName, @Param("pen_name") String penName);

    @Update("update author set author_name = #{author_name}, pen_name = #{pen_name} where author_id = #{id}")
    int update(@Param("author_name") String authorName, @Param("pen_name") String penName, @Param("id") Integer id);

    @Delete("delete from author where author_id = #{id}")
    int delete(Integer id);

    @Select("select author_id as authorId, author_name as authorName, pen_name as penName from author where author_id = #{id}")
    Author findAuthor(@Param("id") Integer id);

    @Select("select author_id as authorId, author_name as authorName, pen_name as penName from author")
    List<Author> findAuthorList();
}

注意：接口要添加@Mapper注解。

6.编写Service层代码

定义类AuthorService：

package com.zwwhnly.springbootaction.mybatis.annotation;

import com.zwwhnly.springbootaction.mybatis.entity.Author;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class AuthorService {
    @Autowired
    private AuthorMapper authorMapper;

    public int add(String authorName, String penName) {
        return this.authorMapper.add(authorName, penName);
    }

    public int update(String authorName, String penName, Integer id) {
        return this.authorMapper.update(authorName, penName, id);
    }

    public int delete(Integer id) {
        return this.authorMapper.delete(id);
    }

    public Author findAuthor(Integer id) {
        return this.authorMapper.findAuthor(id);
    }

    public List<Author> findAuthorList() {
        return this.authorMapper.findAuthorList();
    }
}

注意：类添加@Service注解。

7.编写Controller代码

新建控制器AuthorController：

package com.zwwhnly.springbootaction.controller;

import com.alibaba.fastjson.JSONObject;
import com.zwwhnly.springbootaction.mybatis.entity.Author;
import com.zwwhnly.springbootaction.mybatis.annotation.AuthorService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

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

@RestController
@RequestMapping(value = "/mybatis/author")
public class AuthorController {
    @Autowired
    private AuthorService authorService;

    /**
     * 查询作者列表
     */
    @RequestMapping(value = "getAuthorList", method = RequestMethod.GET)
    public Map<String, Object> getAuthorList() {
        List<Author> authorList = this.authorService.findAuthorList();
        Map<String, Object> param = new HashMap<>();
        param.put("total", authorList.size());
        param.put("rows", authorList);
        return param;
    }

    /**
     * 查询单个作者信息
     */
    @RequestMapping(value = "/getAuthor/{authorId:\\d+}", method = RequestMethod.GET)
    public Author getAuthor(@PathVariable Integer authorId) {
        Author author = this.authorService.findAuthor(authorId);
        if (author == null) {
            throw new RuntimeException("查询错误");
        }
        return author;
    }

    /**
     * 新增
     */
    @RequestMapping(value = "add", method = RequestMethod.POST)
    public void add(@RequestBody JSONObject jsonObject) {
        String authorName = jsonObject.getString("authorName");
        String penName = jsonObject.getString("penName");

        try {
            this.authorService.add(authorName, penName);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("新增错误");
        }
    }

    /**
     * 更新
     */
    @RequestMapping(value = "/update/{authorId:\\d+}", method = RequestMethod.PUT)
    public void update(@PathVariable Integer authorId, @RequestBody JSONObject jsonObject) {
        Author author = this.authorService.findAuthor(authorId);
        String authorName = jsonObject.getString("authorName");
        String penName = jsonObject.getString("penName");

        try {
            this.authorService.update(authorName, penName, author.getAuthorId());
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("更新错误");
        }
    }

    /**
     * 删除
     */
    @RequestMapping(value = "/delete/{authorId:\\d+}", method = RequestMethod.DELETE)
    public void delete(@PathVariable Integer authorId) {
        try {
            this.authorService.delete(authorId);
        } catch (Exception e) {
            throw new RuntimeException("删除错误");
        }
    }
}

8.使用Postman验证

8.1验证新增

因为新增是Post请求，因此这里我们使用下Postman工具：

调用完接口，发现数据库新增数据成功。

然后用同样的方法新增下鲁迅的信息。

8.2验证更新

调用更新接口将鲁迅的名字从周作人修改为周树人：

调用完接口，发现数据库更新数据成功。

8.3验证获取列表

在浏览器访问http://localhost:8080/mybatis/author/getAuthorList，返回数据如下：

{
  "total": 2,
  "rows": [
    {
      "authorId": 1,
      "authorName": "王卫国",
      "penName": "路遥"
    },
    {
      "authorId": 2,
      "authorName": "周树人",
      "penName": "鲁迅"
    }
  ]
}

8.4验证获取单个数据

在浏览器访问http://localhost:8080/mybatis/author/getAuthor/1，返回如下数据：

{
  "authorId": 1,
  "authorName": "王卫国",
  "penName": "路遥"
}

8.5验证删除

调用删除接口，将鲁迅的数据删除：

此时访问http://localhost:8080/mybatis/author/getAuthorList，返回数据只有1条了：

{
  "total": 1,
  "rows": [
    {
      "authorId": 1,
      "authorName": "王卫国",
      "penName": "路遥"
    }
  ]
}

9.源码

源码地址：https://github.com/zwwhnly/springboot-action.git，欢迎下载。

10.参考

Spring Boot 揭秘与实战（二） 数据存储篇 – MyBatis整合