本系列博客记录自己学习 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.YAML 中配置数据源:
- 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 整合 https://juejin.im/post/5859bef2b123db0065924f53
来源: https://www.cnblogs.com/zwwhnly/p/11002520.html