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. 源码
源码地址:,欢迎下载。
12. 参考
spring boot 揭秘与实战(二) 数据存储篇 - mybatis整合
欢迎扫描下方二维码关注个人公众号:申城异乡人。
上一篇: Python进阶:值传递,引用传递?不存在的,是赋值传递
下一篇: 递推和迭代的比较