任务描述
任务要求
使用IDEA开发工具构建一个项目多模块工程。study-springboot-chapter02学习关于Springboot集成MyBatis的技术知识点
- 基于study-springboot工程，新建一个Maven项目，坐标groupId（com.cbitedu）、artifactId(study-springboot-chapter02)，其他默认
- 继承study-springboot工程依赖
- 源代码地址：gitee.com/ossbar/stud…
任务收获
- 如何集成第三方持久化技术Mybatis
- 如何引入MySQL数据库依赖
- Spring Boot中整合MyBatis完成关系型数据库的增删改查操作（Mybatis+XML模式实现）。
- 学会使用JUnit完成单元测试
任务准备
环境要求
- JDK1.8+
- MySQL8.0.27+
- Maven 3.6.1+
- IDEA/VSCode
数据库准备
创建数据库platform，并创建用户表和初始化用户表数据。
/*
Navicat Premium Data Transfer
Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 80029
Source Host : localhost:3306
Source Schema : platform
Target Server Type : MySQL
Target Server Version : 80029
File Encoding : 65001
Date: 20/08/2022 14:13:09
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_sys_userinfo
-- ----------------------------
DROP TABLE IF EXISTS t_sys_userinfo;
CREATE TABLE t_sys_userinfo (
user_id varchar(32) NOT NULL,
username varchar(50) NOT NULL COMMENT '用户名',
password varchar(100) DEFAULT NULL COMMENT '密码',
salt varchar(20) DEFAULT NULL COMMENT '盐',
email varchar(100) DEFAULT NULL COMMENT '邮箱',
mobile varchar(100) DEFAULT NULL COMMENT '手机号',
status tinyint DEFAULT NULL COMMENT '状态 0：禁用 1：正常',
create_user_id varchar(32) NULL DEFAULT NULL COMMENT '创建者ID',
create_time varchar(32) DEFAULT NULL COMMENT '创建时间',
userimg varchar(255) DEFAULT NULL COMMENT '用户头像',
zip varchar(10) DEFAULT NULL COMMENT '邮政编码',
sort_num int DEFAULT NULL COMMENT '排序号',
user_type varchar(10) DEFAULT NULL COMMENT '用户类型',
post_id varchar(32) DEFAULT NULL COMMENT '所属岗位',
sex varchar(4) DEFAULT NULL COMMENT '性别',
USER_REALNAME varchar(50) DEFAULT NULL COMMENT '真实姓名',
user_theme varchar(255) DEFAULT NULL COMMENT '用户选择皮肤',
user_card varchar(18) DEFAULT NULL COMMENT '身份证号码',
birthday varchar(20) DEFAULT NULL COMMENT '出生年月',
native_place varchar(255) DEFAULT NULL COMMENT '家庭住址',
nation varchar(255) DEFAULT NULL COMMENT '民族',
update_user_id varchar(32) DEFAULT NULL COMMENT '创建者ID',
update_time varchar(32) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (user_id) USING BTREE,
UNIQUE INDEX username(username ASC) USING BTREE
) COMMENT = '系统用户' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_sys_userinfo
-- ----------------------------
INSERT INTO t_sys_userinfo VALUES ('1', 'admin', '9ec9750e709431dad22365cabc5c625482e574c74adaebba7dd02f1129e4ce1d', 'YzcmCZNvbXocrsz9dm8e', 'cc@bluefairy.com', '18929423839', 1, '1', '2016-11-11 11:11:11', NULL, NULL, NULL, NULL, NULL, NULL, '系统管理员', 'green', NULL, NULL, NULL, NULL, NULL, NULL);
SET FOREIGN_KEY_CHECKS = 1;
工程目录要求
复制study-springboot-chapter01修改成study-springboot-chapter02
任务实施
如何在Spring Boot中整合MyBatis完成关系型数据库的增删改查操作。
模块：study-springboot-chapter02
任务实施步骤如下：
1、复制 study-springboot-chapter01快速创建study-springboot-chapter02模块
2、study-springboot-chapter02模块的pom.xml中引入MyBatis的Starter以及MySQL Connector依赖，具体如下：
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
关于mybatis-spring-boot-starter的版本需要注意：
2.1.x版本适用于：MyBatis 3.5+、Java 8+、Spring Boot 2.1+
3、在application-dev.properties中配置mysql的连接配置
#指定Mybatis的Mapper文件
mybatis.mapper-locations=classpath:mappers/*xml
#指定Mybatis的实体目录
mybatis.type-aliases-package=com.cbitedu.springboot.mybatis.entity
# 数据库驱动：
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 数据源名称
spring.datasource.name=defaultDataSource
# 数据库连接地址
spring.datasource.url=jdbc:mysql://localhost:3306/platform?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&useSSL=false
# 数据库用户名&密码：
spring.datasource.username=root
spring.datasource.password=root
4、完成任务准备中的数据库工作，创建表和初始化数据
5、创建t_sys_userinfo表的映射对象TsysUserinfo
可以先创建BaseDomain基类，封装常用的对象属性：所谓万能字段：创建人、创建时间、修改人、修改时间、版本等
package com.cbitedu.springboot.mybatis.domain;
import java.io.Serializable;
import java.util.Map;
/**
* Title:面向所有实体类的基类 Copyright: Copyright (c) 2017 Company:creatorblue.co.,ltd
*
* @author creatorblue.co.,ltd
* @version 1.0
*/
public abstract class BaseDomain <T> implements Serializable {
private static final long serialVersionUID = 1L;
protected static final String DATE_FORMAT = "yyyy-MM-dd";
protected static final String TIME_FORMAT = "HH:mm:ss";
protected static final String DATE_TIME_FORMAT = "yyyy-MM-dd HH:mm:ss";
protected static final String TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss.S";
/**
* 创建人
*/
private String createUserId;
/**
* 创建时间
*/
private String createTime;
/**
* 修改人
*/
private String updateUserId;
/**
* 修改时间
*/
private String updateTime;
/**
* 删除标识
*/
private String delFlag;
public String getCreateUserId() {
return createUserId;
}
public void setCreateUserId(String createUserId) {
this.createUserId = createUserId;
}
public String getCreateTime() {
return createTime;
}
public String getDelFlag() {
return delFlag;
}
public void setDelFlag(String delFlag) {
this.delFlag = delFlag;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getUpdateUserId() {
return updateUserId;
}
public void setUpdateUserId(String updateUserId) {
this.updateUserId = updateUserId;
}
public String getUpdateTime() {
return updateTime;
}
public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}
}
映射对象TsysUserinfo
package com.cbitedu.springboot.mybatis.domain;
import java.util.List;
/**
* 用户信息
* @author
*
*/
public class TsysUserinfo extends BaseDomain {
private static final long serialVersionUID = 1L;
/**
* 用户ID
*/
private String userId;
/**
* 用户名
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 盐
*/
private String salt;
/**
* 邮箱
*/
private String email;
/**
* 手机号
*/
private String mobile;
@Override
public String toString() {
return "TsysUserinfo{" +
"userId='" + userId + ''' +
", username='" + username + ''' +
", password='" + password + ''' +
", salt='" + salt + ''' +
", email='" + email + ''' +
", mobile='" + mobile + ''' +
", status='" + status + ''' +
", roleIdList=" + roleIdList +
", orgIdList=" + orgIdList +
", orgId='" + orgId + ''' +
", userRealname='" + userRealname + ''' +
", orgnames='" + orgnames + ''' +
", userimg='" + userimg + ''' +
", zip='" + zip + ''' +
", sortNum=" + sortNum +
", userType='" + userType + ''' +
", postIdList=" + postIdList +
", sex='" + sex + ''' +
", userTheme='" + userTheme + ''' +
", userCard='" + userCard + ''' +
", birthday='" + birthday + ''' +
", nativePlace='" + nativePlace + ''' +
", nation='" + nation + ''' +
'}';
}
/**
* 状态 0：禁用 1：正常
*/
private String status;
/**
* 角色ID列表
*/
private List<String> roleIdList;
private List<String> orgIdList;
/**
* 所属机构
*/
private String orgId;
/**
* 用户真实姓名
*/
private String userRealname;
/**
* 所属机构
*/
private String orgnames;
/**
* 用户头像
*/
private String userimg;
/**
* 邮政编码
*/
private String zip;
/**
* 排序号
*/
private Integer sortNum;
/**
* 用户类型
*/
private String userType;
/**
* 所属岗位
*/
private List<String> postIdList;
/**
* 性别
*/
private String sex;
private String userTheme;
/**
* 身份证号码 db_column: user_card
*/
private String userCard;
/**
* 出生年月 db_column: birthday
*/
private String birthday;
/**
* 家庭住址 db_column: native_place
*/
private String nativePlace;
/**
* 民族 db_column: nation
*/
private String nation;
public String getUserTheme() {
return userTheme;
}
public void setUserTheme(String userTheme) {
this.userTheme = userTheme;
}
public String getUserCard() {
return userCard;
}
public void setUserCard(String userCard) {
this.userCard = userCard;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getNativePlace() {
return nativePlace;
}
public void setNativePlace(String nativePlace) {
this.nativePlace = nativePlace;
}
public String getNation() {
return nation;
}
public void setNation(String nation) {
this.nation = nation;
}
/**
* 设置：
*
* @param userId
*/
public void setUserId(String userId) {
this.userId = userId;
}
/**
* 获取：
*
* @return String
*/
public String getUserId() {
return userId;
}
/**
* 设置：用户名
*
* @param username
* 用户名
*/
public void setUsername(String username) {
this.username = username;
}
/**
* 获取：用户名
*
* @return String
*/
public String getUsername() {
return username;
}
/**
* 设置：密码
*
* @param password
* 密码
*/
public void setPassword(String password) {
this.password = password;
}
/**
* 获取：密码
*
* @return String
*/
public String getPassword() {
return password;
}
/**
* 设置：邮箱
*
* @param email
* 邮箱
*/
public void setEmail(String email) {
this.email = email;
}
/**
* 获取：邮箱
*
* @return String
*/
public String getEmail() {
return email;
}
/**
* 设置：手机号
*
* @param mobile
* 手机号
*/
public void setMobile(String mobile) {
this.mobile = mobile;
}
/**
* 获取：手机号
*
* @return String
*/
public String getMobile() {
return mobile;
}
/**
* 设置：状态 0：禁用 1：正常
*
* @param status
* 状态 0：禁用 1：正常
*/
public void setStatus(String status) {
this.status = status;
}
/**
* 获取：状态 0：禁用 1：正常
*
* @return Integer
*/
public String getStatus() {
return status;
}
public List<String> getRoleIdList() {
return roleIdList;
}
public void setRoleIdList(List<String> roleIdList) {
this.roleIdList = roleIdList;
}
public String getSalt() {
return salt;
}
public void setSalt(String salt) {
this.salt = salt;
}
public List<String> getOrgIdList() {
return orgIdList;
}
/**
* 设置：机构ID列表
*
* @param orgIdList
* 机构ID列表
*/
public void setOrgIdList(List<String> orgIdList) {
this.orgIdList = orgIdList;
}
public String getOrgnames() {
return orgnames;
}
/**
* 设置：机构名称
*
* @param orgnames
* 机构名称
*/
public void setOrgnames(String orgnames) {
this.orgnames = orgnames;
}
public String getUserimg() {
return userimg;
}
/**
* 设置：用户头像
* @param userimg 用户头像
*/
public void setUserimg(String userimg) {
this.userimg = userimg;
}
public String getZip() {
return zip;
}
/**
* 设置：邮政编码
* @param zip 邮政编码
*/
public void setZip(String zip) {
this.zip = zip;
}
public Integer getSortNum() {
return sortNum;
}
/**
* 设置：排序号
* @param sort_num 排序号
*/
public void setSortNum(Integer sortNum) {
this.sortNum = sortNum;
}
public String getUserType() {
return userType;
}
/**
* 设置：用户类型
* @param user_type:内部用户、第三方用户、外部用户
*/
public void setUserType(String userType) {
this.userType = userType;
}
public List<String> getPostIdList() {
return postIdList;
}
/**
* 设置：所属岗位
* @param post_id 所属岗位
*/
public void setPostIdList(List<String> postIdList) {
this.postIdList = postIdList;
}
public String getSex() {
return sex;
}
/**
* 设置：性别
* @param sex 性别
*/
public void setSex(String sex) {
this.sex = sex;
}
public String getUserRealname() {
return userRealname;
}
/**
* 设置：用户真实姓名
* @param user_realname 用户真实姓名
*/
public void setUserRealname(String userRealname) {
this.userRealname = userRealname;
}
public String getOrgId() {
return orgId;
}
/**
* 设置：所属机构
* @param orgId 所属机构
*/
public void setOrgId(String orgId) {
this.orgId = orgId;
}
}
6、编写Mapper的XML文件：TsysUserinfoMapper.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.cbitedu.springboot.mybatis.mappers.TsysUserinfoMapper">
<select id="selectObjectById" resultType="com.cbitedu.springboot.mybatis.domain.TsysUserinfo">
select * from
t_sys_userinfo where user_id = #{userid}
</select>
<select id="getAllUserinfo" resultType="com.cbitedu.springboot.mybatis.domain.TsysUserinfo">
select u.* from
t_sys_userinfo u
</select>
<insert id="insertTsysUserinfo" parameterType="com.cbitedu.springboot.mybatis.domain.TsysUserinfo">
insert into t_sys_userinfo
(
user_id,
user_realname,
username,
password,
salt,
email,
mobile,
status,
userimg,
zip,
sort_num,
user_type,
sex,
user_theme,
user_card,
birthday,
native_place,
nation,
create_user_id,
create_time,
update_user_id,
update_time
)
values
(
#{userId},
#{userRealname},
#{username},
#{password},
#{salt},
#{email},
#{mobile},
#{status},
#{userimg},
#{zip},
#{sortNum},
#{userType},
#{sex},
#{userTheme},
#{userCard},
#{birthday},
#{nativePlace},
#{nation},
#{createUserId,jdbcType=VARCHAR},
#{createTime,jdbcType=VARCHAR},
#{updateUserId,jdbcType=VARCHAR},
#{updateTime,jdbcType=VARCHAR}
)
</insert>
<update id="update" parameterType="com.cbitedu.springboot.mybatis.domain.TsysUserinfo">
update t_sys_userinfo
<set>
<if test="userRealname != null">user_realname = #{userRealname}, </if>
<if test="username != null">username = #{username}, </if>
<if test="email != null">email = #{email}, </if>
<if test="mobile != null">mobile = #{mobile}, </if>
<if test="status != null">status = #{status}, </if>
<if test="userimg != null">userimg = #{userimg},</if>
<if test="zip != null">zip = #{zip},</if>
<if test="sortNum != null">sort_num = #{sortNum},</if>
<if test="userType != null">user_type = #{userType},</if>
<if test="sex != null">sex = #{sex},</if>
<if test="userTheme != null and userTheme.trim() != ''">
user_theme = #{userTheme,jdbcType=VARCHAR},
</if>
<if test="userCard != null and userCard.trim() != ''">
user_card = #{userCard,jdbcType=VARCHAR},
</if>
<if test="birthday != null and birthday.trim() != ''">
birthday = #{birthday,jdbcType=VARCHAR},
</if>
<if test="nativePlace != null and nativePlace.trim() != ''">
native_place = #{nativePlace,jdbcType=VARCHAR},
</if>
<if test="createUserId != null and createUserId.trim() != ''">
create_user_id = #{createUserId,jdbcType=VARCHAR},
</if>
<if test="createTime != null and createTime.trim() != ''">
create_time = #{createTime,jdbcType=VARCHAR},
</if>
<if test="updateUserId != null and updateUserId.trim() != ''">
update_user_id = #{updateUserId,jdbcType=VARCHAR},
</if>
<if test="updateTime != null and updateTime.trim() != ''">
update_time = #{updateTime,jdbcType=VARCHAR},
</if>
<if test="nation != null and nation.trim() != ''">
nation = #{nation,jdbcType=VARCHAR}
</if>
</set>
where user_id = #{userId}
</update>
<delete id="deleteUser">
delete from t_sys_userinfo where user_id= #{userid}
</delete>
<delete id="deleteBatch">
delete from t_sys_userinfo where user_id in
<foreach item="userId" collection="array" open="(" separator=","
close=")">
#{userId}
</foreach>
</delete>
</mapper>
7、创建接口：TsysUserinfoMapper。在接口中定义两个数据操作，一个插入，一个查询，用于后续单元测试验证。
package com.cbitedu.springboot.mybatis.mappers;
import org.apache.ibatis.annotations.Mapper;
import com.cbitedu.springboot.mybatis.domain.TsysUserinfo;
@Mapper
public interface TsysUserinfoMapper {
/**
* 新增用户信息
* @param tsysUserinfo
*/
public void insertTsysUserinfo(TsysUserinfo tsysUserinfo);
/**
* 查询用户详细信息
* @param userid
*
* @return
*/
public TsysUserinfo getTsysUserinfoById(String userid);
/**
* 删除用户信息
* @param userid
*/
public void deleteUser(String userid);
}
8、创建Spring Boot主类，并扫描到Mybatis接口
package com.cbitedu.springboot;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = "com.cbitedu.springboot.mybatis.mappers")
public class StudySpringbootChapter02Application {
public static void main(String[] args) {
SpringApplication.run(StudySpringbootChapter02Application.class, args);
}
}
9、创建单元测试：MybatisTest，测试新增、修改、删除业务操作。
package com.cbitedu.springboot.mybatis;
import com.cbitedu.springboot.mybatis.domain.TsysUserinfo;
import com.cbitedu.springboot.mybatis.mappers.TsysUserinfoMapper;
import com.cbitedu.springboot.property.PropertiesTest;
import com.cbitedu.springboot.web.HelloWorldController;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.core.AutoConfigureCache;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import static org.junit.Assert.assertEquals;
/**
* Spring Boot mybatis 测试
*
* Created by cbitedu on 18/08/2021.
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisTest {
private static final Logger logger = LoggerFactory.getLogger(MybatisTest.class);
@Autowired
TsysUserinfoMapper tsysUserinfoMapper;
/**
* 测试新增用户信息
*/
@Test
public void testInsert() {
TsysUserinfo tsysUserinfo=new TsysUserinfo();
tsysUserinfo.setUserId("3000");
tsysUserinfo.setUsername("Mybatis001");
tsysUserinfo.setPassword("2222");
tsysUserinfoMapper.insertTsysUserinfo(tsysUserinfo);
}
/**
* 测试删除用户信息
*/
@Test
public void testDelete() {
tsysUserinfoMapper.deleteUser("2000");
}
/**
* 测试查询用户信息
*/
@Test
public void testSelectUser() {
TsysUserinfo tsysUserinfo=new TsysUserinfo();
tsysUserinfo= tsysUserinfoMapper.selectObjectById("1");
logger.info("查询出来的用户基本信息"+tsysUserinfo.toString());
}
}
实验实训
- 使用Mybatis的注解模式实现操作MySQL数据库
