本系列博客记录自己学习Spring Boot的历程,如帮助到你,不胜荣幸,如有错误,欢迎指正!
本篇博客我们讲解下在Spring Boot中使用MyBatis访问MySql数据库的简单用法。
假设你的机器已经安装好了MySql,我们先执行如下语句创建数据库和表:
CREATE DATABASE springbootdemo_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 '作者';
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,因为前者已经依赖于后者。
在resources/application.yml中配置数据源:
spring: datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/springbootdemo_db username: root password:
定义数据库实体Author:
package com.zwwhnly.springbootdemo.mybatis.entity; import com.alibaba.fastjson.annotation.JSONField; public class Author { @JSONField(name = "author_id") private Long authorId; @JSONField(name = "author_name") private String authorName; @JSONField(name = "pen_name") private String penName; public Long getAuthorId() { return authorId; } public void setAuthorId(Long 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; } }
定义接口AuthorMapper:
package com.zwwhnly.springbootdemo.mybatis.annotation; import com.zwwhnly.springbootdemo.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") Long id); @Select("select author_id as authorId, author_name as authorName, pen_name as penName from author") List<Author> findAuthorList(); }
定义类AuthorService:
package com.zwwhnly.springbootdemo.mybatis.annotation; import com.zwwhnly.springbootdemo.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(); } }
新建控制器AuthorController:
package com.zwwhnly.springbootdemo.controller; import com.alibaba.fastjson.JSONObject; import com.zwwhnly.springbootdemo.mybatis.entity.Author; import com.zwwhnly.springbootdemo.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("删除错误"); } } }
因为新增是Post请求,因此这里我们使用下Postman工具:
调用完接口,发现数据库新增数据成功。
然后用同样的方法新增下鲁迅的信息。
调用更新接口将鲁迅的名字从周作人修改为周树人:
调用完接口,发现数据库更新数据成功。
在浏览器访问http://localhost:8080/mybatis/author/getAuthorList,返回数据如下:
{ "total": 2, "rows": [ { "authorId": 1, "authorName": "王卫国", "penName": "路遥" }, { "authorId": 2, "authorName": "周树人", "penName": "鲁迅" } ] }
在浏览器访问http://localhost:8080/mybatis/author/getAuthor/1,返回如下数据:
{ "authorId": 1, "authorName": "王卫国", "penName": "路遥" }
调用删除接口,将鲁迅的数据删除:
此时访问http://localhost:8080/mybatis/author/getAuthorList,返回数据只有1条了:
{ "total": 1, "rows": [ { "authorId": 1, "authorName": "王卫国", "penName": "路遥" } ] }
原文地址: Spring Boot入门(六):使用MyBatis访问MySql数据库(注解方式)
博客地址: http://www.zwwhnly.com
源码地址: https://github.com/zwwhnly/springbootdemo.git
欢迎大家下载,有问题可以多多交流。
Spring Boot 揭秘与实战(二) 数据存储篇 - MyBatis整合