run mysql as boot_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：

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.1.1

mysql

mysql-connector-java

5.1.35

com.alibaba

fastjson

1.2.47

说明：引入了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 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 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 getAuthorList() {

List authorList = this.authorService.findAuthorList();

Map 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.源码

10.参考