Spring Boot入门(七)：使用MyBatis访问MySql数据库(xml方式)

本系列博客带你一步一步的学习Spring Boot，如帮助到你，不胜荣幸，如有错误，欢迎指正！

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

本系列其它文章如下所示：

Spring Boot入门(一)：使用IDEA创建Spring Boot项目并使用yaml配置文件

Spring Boot入门(二)：使用Profile实现多环境配置管理&如何获取配置文件值

Spring Boot入门(三)：使用Scheduled注解实现定时任务

Spring Boot入门(四)：开发Web Api接口常用注解总结

Spring Boot入门(五)：使用JDBC访问MySql数据库

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. 修改配置文件

在application.yml中添加mybatis配置：

mybatis:
  mapper-locations: classpath:mybatis/*.xml
  type-aliases-package: com.zwwhnly.springbootaction.mybatis.entity

其中，mapper-locations为mybatis xml文件的路径，type-aliases-package为定义的实体所在的包名。

5. 定义数据库实体

定义数据库实体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;
    }
}

6. 编写Dao层代码

定义接口AuthorMapperV2：

package com.zwwhnly.springbootaction.mybatis.xml;

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

import java.util.List;

@Mapper
public interface AuthorMapperV2 {

    int add(@Param("author_name") String authorName, @Param("pen_name") String penName);

    int update(@Param("author_name") String authorName, @Param("pen_name") String penName, @Param("id") Integer id);

    int delete(Integer id);

    Author findAuthor(@Param("id") Integer id);

    List<Author> findAuthorList();
}

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

7. 编写Service层代码

定义类AuthorServiceV2：

package com.zwwhnly.springbootaction.mybatis.xml;

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 AuthorServiceV2 {
    @Autowired
    private AuthorMapperV2 authorMapperV2;

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

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

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

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

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

注意：类添加@Service注解。

8. 添加mybatis xml文件

在resources目录下，新建mybatis文件夹，然后新建AuthorMapper.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.zwwhnly.springbootaction.mybatis.xml.AuthorMapperV2">
    <resultMap id="authorMap" type="Author">
        <result property="authorId" column="author_id"/>
        <result property="authorName" column="author_name"/>
        <result property="penName" column="pen_name"/>
    </resultMap>

    <insert id="add">
        INSERT INTO author(author_name, pen_name)
        VALUES(#{author_name}, #{pen_name});
    </insert>
    <update id="update">
        UPDATE author
        SET author_name = #{author_name,jdbcType=VARCHAR},
        pen_name = #{pen_name,jdbcType=VARCHAR}
        WHERE author_id = #{id,jdbcType=INTEGER};
    </update>
    <delete id="delete">
        DELETE FROM author
        WHERE author_id = #{id};
    </delete>
    <select id="findAuthor" resultMap="authorMap" resultType="Author">
       SELECT author_id, author_name, pen_name
       FROM author
       WHERE author_id = #{id};
    </select>
    <select id="findAuthorList" resultMap="authorMap">
        SELECT author_id, author_name, pen_name
        FROM author;
    </select>
</mapper>

9. 编写Controller代码

新建控制器AuthorControllerV2：

package com.zwwhnly.springbootaction.controller;

import com.alibaba.fastjson.JSONObject;
import com.zwwhnly.springbootaction.mybatis.entity.Author;
import com.zwwhnly.springbootaction.mybatis.xml.AuthorServiceV2;
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 AuthorControllerV2 {
    @Autowired
    private AuthorServiceV2 authorServiceV2;

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

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

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

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

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

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

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

10. 使用Postman验证

10.1 验证新增

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

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

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

10.2 验证更新

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

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

10.3 验证获取列表

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

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

10.4 验证获取单个数据

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

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

10.5 验证删除

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

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

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

11. 源码

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

12. 参考

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