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整合