【SpringBoot 2学习笔记】《八》SpringBoot2数据库访问之整合MyBatis

My Batis 的官方定义:MyBatis是支持定制化SQL、存储过程以及高级映射的优秀的持久层框架。MyBatis可以对配置和原生Map使用简单的XML或注解,将接口和Java的POJO映射成数据库中的记录。可以看出,MyBatis是基于一种SQL到POJO的模型,它需要我们提供SQL、映射关系(XML或者注解,目前以XML 为主)和POJO 。与此同时,它还能支持动态SQL,以适应需求的变化,当前MyBatis渐渐成为市场的主流持久框架。MyBatis的配置文件包括两个大的部分,一是基础配置文件,一个是映射文件。在MyBatis中也可以使用注解来实现映射,只是由于功能和可读性的限制,在实际的项目使用得比较少,所以不介绍使用注解配置SQL的方式。

6.2.1 工程文件中添加依赖包:pom.xml

    <!-- 导入mybatis依赖包 -->
	<dependency>
	    <groupId>org.mybatis.spring.boot</groupId>
	    <artifactId>mybatis-spring-boot-starter</artifactId>
	    <version>2.1.1</version>
	</dependency>

mybatis-spring-boot-starter依赖:

  • 自动检测现有的DataSource
  • 创建并注册SqlSessionFactory的实例,该实例使用SqlSessionFactoryBean将该DataSource作为输入进行传递
  • 创建并注册从SqlSessionFactory中获取的SqlSessionTemplate的实例。
  • 自动扫描您的mappers,将它们链接到SqlSessionTemplate并将其注册到Spring上下文,以便将它们注入到您的bean中。

即当我们引入mybatis-spring-boot-starter之后,只需要在application.properties定义一个DataSource,它会自动创建使用该DataSource的SqlSessionFactoryBean以及SqlSessionTemplate。同时会自动扫描我们指定的mappers,注册到Spring上下文中。

6.2.2 配置数据源

application.properties配置MySQL数据源。

server.servlet.context-path=/gavin
server.port=9001

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://rm-wz98XXXXuo.mysql.rds.aliyuncs.com:3306/usermng?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
spring.datasource.username=gavin
spring.datasource.password=gavinpw

注意:现在有些文章中可能存在记述错误的地方。

spring.datasource.driverClassName如果设定为:com.mysql.jdbc.Driver,会引起编译错误,给出的提示中已经说明了修改方法,将ClassName变更为:com.mysql.cj.jdbc.Driver

Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

全局配置文件 application.properties中spring.datasource下只配置了数据库地址、连接驱动、账号、密码,默认使用的是HikariDataSource数据源(com.zaxxer.hikari.HikariDataSource)。

6.2.3 使用mybatis-generator-gui自动生成代码

详细使用参考【11.1 使用mybatis-generator-gui自动生成mybatis关联代码】

也可以下载直接编译好的可执行的文件:

1. 生成用户信息代码
package com.gavinbj.confmng.persistence.entity;

import java.io.Serializable;
import java.util.Date;

/**
 * UserInfo
 * @author gavinbj
 */
public class UserInfo implements Serializable {
    private String userId;

    /**
     * 用户姓名
     */
    private String userName;

    /**
     * 个人介绍
     */
    private String introduce;

    /**
     * 移动电话
     */
    private String mobilephone;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 生日
     */
    private Date birthday;

    /**
     * 性别
     */
    private String gender;

    private static final long serialVersionUID = 1L;

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getIntroduce() {
        return introduce;
    }

    public void setIntroduce(String introduce) {
        this.introduce = introduce;
    }

    public String getMobilephone() {
        return mobilephone;
    }

    public void setMobilephone(String mobilephone) {
        this.mobilephone = mobilephone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public boolean equals(Object that) {
        if (this == that) {
            return true;
        }
        if (that == null) {
            return false;
        }
        if (getClass() != that.getClass()) {
            return false;
        }
        UserInfo other = (UserInfo) that;
        return (this.getUserId() == null ? other.getUserId() == null : this.getUserId().equals(other.getUserId()))
            && (this.getUserName() == null ? other.getUserName() == null : this.getUserName().equals(other.getUserName()))
            && (this.getIntroduce() == null ? other.getIntroduce() == null : this.getIntroduce().equals(other.getIntroduce()))
            && (this.getMobilephone() == null ? other.getMobilephone() == null : this.getMobilephone().equals(other.getMobilephone()))
            && (this.getEmail() == null ? other.getEmail() == null : this.getEmail().equals(other.getEmail()))
            && (this.getBirthday() == null ? other.getBirthday() == null : this.getBirthday().equals(other.getBirthday()))
            && (this.getGender() == null ? other.getGender() == null : this.getGender().equals(other.getGender()));
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((getUserId() == null) ? 0 : getUserId().hashCode());
        result = prime * result + ((getUserName() == null) ? 0 : getUserName().hashCode());
        result = prime * result + ((getIntroduce() == null) ? 0 : getIntroduce().hashCode());
        result = prime * result + ((getMobilephone() == null) ? 0 : getMobilephone().hashCode());
        result = prime * result + ((getEmail() == null) ? 0 : getEmail().hashCode());
        result = prime * result + ((getBirthday() == null) ? 0 : getBirthday().hashCode());
        result = prime * result + ((getGender() == null) ? 0 : getGender().hashCode());
        return result;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", userId=").append(userId);
        sb.append(", userName=").append(userName);
        sb.append(", introduce=").append(introduce);
        sb.append(", mobilephone=").append(mobilephone);
        sb.append(", email=").append(email);
        sb.append(", birthday=").append(birthday);
        sb.append(", gender=").append(gender);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}
2. 用户Mapper文件
<?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.gavinbj.confmng.persistence.mapper.UserInfoMapper">
  <resultMap id="BaseResultMap" type="com.gavinbj.confmng.persistence.entity.UserInfo">
    <id column="user_id" jdbcType="VARCHAR" property="userId" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
    <result column="introduce" jdbcType="VARCHAR" property="introduce" />
    <result column="mobilephone" jdbcType="VARCHAR" property="mobilephone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="birthday" jdbcType="DATE" property="birthday" />
    <result column="gender" jdbcType="VARCHAR" property="gender" />
  </resultMap>
  <sql id="Example_Where_Clause">
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
    user_id, user_name, introduce, mobilephone, email, birthday, gender
  </sql>
  <select id="selectByExample" parameterType="com.gavinbj.confmng.persistence.entity.UserInfoExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from user_info
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
    <if test="limit != null">
      <if test="offset != null">
        limit ${offset}, ${limit}
      </if>
      <if test="offset == null">
        limit ${limit}
      </if>
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.String" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from user_info
    where user_id = #{userId,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String">
    delete from user_info
    where user_id = #{userId,jdbcType=VARCHAR}
  </delete>
  <delete id="deleteByExample" parameterType="com.gavinbj.confmng.persistence.entity.UserInfoExample">
    delete from user_info
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="com.gavinbj.confmng.persistence.entity.UserInfo">
    insert into user_info (user_id, user_name, introduce, 
      mobilephone, email, birthday, 
      gender)
    values (#{userId,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}, #{introduce,jdbcType=VARCHAR}, 
      #{mobilephone,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{birthday,jdbcType=DATE}, 
      #{gender,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.gavinbj.confmng.persistence.entity.UserInfo">
    insert into user_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="userId != null">
        user_id,
      </if>
      <if test="userName != null">
        user_name,
      </if>
      <if test="introduce != null">
        introduce,
      </if>
      <if test="mobilephone != null">
        mobilephone,
      </if>
      <if test="email != null">
        email,
      </if>
      <if test="birthday != null">
        birthday,
      </if>
      <if test="gender != null">
        gender,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="userId != null">
        #{userId,jdbcType=VARCHAR},
      </if>
      <if test="userName != null">
        #{userName,jdbcType=VARCHAR},
      </if>
      <if test="introduce != null">
        #{introduce,jdbcType=VARCHAR},
      </if>
      <if test="mobilephone != null">
        #{mobilephone,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        #{email,jdbcType=VARCHAR},
      </if>
      <if test="birthday != null">
        #{birthday,jdbcType=DATE},
      </if>
      <if test="gender != null">
        #{gender,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.gavinbj.confmng.persistence.entity.UserInfoExample" resultType="java.lang.Long">
    select count(*) from user_info
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map">
    update user_info
    <set>
      <if test="record.userId != null">
        user_id = #{record.userId,jdbcType=VARCHAR},
      </if>
      <if test="record.userName != null">
        user_name = #{record.userName,jdbcType=VARCHAR},
      </if>
      <if test="record.introduce != null">
        introduce = #{record.introduce,jdbcType=VARCHAR},
      </if>
      <if test="record.mobilephone != null">
        mobilephone = #{record.mobilephone,jdbcType=VARCHAR},
      </if>
      <if test="record.email != null">
        email = #{record.email,jdbcType=VARCHAR},
      </if>
      <if test="record.birthday != null">
        birthday = #{record.birthday,jdbcType=DATE},
      </if>
      <if test="record.gender != null">
        gender = #{record.gender,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
    update user_info
    set user_id = #{record.userId,jdbcType=VARCHAR},
      user_name = #{record.userName,jdbcType=VARCHAR},
      introduce = #{record.introduce,jdbcType=VARCHAR},
      mobilephone = #{record.mobilephone,jdbcType=VARCHAR},
      email = #{record.email,jdbcType=VARCHAR},
      birthday = #{record.birthday,jdbcType=DATE},
      gender = #{record.gender,jdbcType=VARCHAR}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.gavinbj.confmng.persistence.entity.UserInfo">
    update user_info
    <set>
      <if test="userName != null">
        user_name = #{userName,jdbcType=VARCHAR},
      </if>
      <if test="introduce != null">
        introduce = #{introduce,jdbcType=VARCHAR},
      </if>
      <if test="mobilephone != null">
        mobilephone = #{mobilephone,jdbcType=VARCHAR},
      </if>
      <if test="email != null">
        email = #{email,jdbcType=VARCHAR},
      </if>
      <if test="birthday != null">
        birthday = #{birthday,jdbcType=DATE},
      </if>
      <if test="gender != null">
        gender = #{gender,jdbcType=VARCHAR},
      </if>
    </set>
    where user_id = #{userId,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.gavinbj.confmng.persistence.entity.UserInfo">
    update user_info
    set user_name = #{userName,jdbcType=VARCHAR},
      introduce = #{introduce,jdbcType=VARCHAR},
      mobilephone = #{mobilephone,jdbcType=VARCHAR},
      email = #{email,jdbcType=VARCHAR},
      birthday = #{birthday,jdbcType=DATE},
      gender = #{gender,jdbcType=VARCHAR}
    where user_id = #{userId,jdbcType=VARCHAR}
  </update>
</mapper>
3. Mapper文件
package com.gavinbj.confmng.persistence.mapper;

import java.io.Serializable;
import java.util.List;
import org.apache.ibatis.annotations.Param;

/**
 * DAO公共基类,由MybatisGenerator自动生成请勿修改
 * @param <Model> The Model Class 这里是泛型不是Model类
 * @param <PK> The Primary Key Class 如果是无主键,则可以用Model来跳过,如果是多主键则是Key类
 * @param <E> The Example Class
 */
public interface MyBatisBaseDao<Model, PK extends Serializable, E> {
    long countByExample(E example);

    int deleteByExample(E example);

    int deleteByPrimaryKey(PK id);

    int insert(Model record);

    int insertSelective(Model record);

    List<Model> selectByExample(E example);

    Model selectByPrimaryKey(PK id);

    int updateByExampleSelective(@Param("record") Model record, @Param("example") E example);

    int updateByExample(@Param("record") Model record, @Param("example") E example);

    int updateByPrimaryKeySelective(Model record);

    int updateByPrimaryKey(Model record);
}
package com.gavinbj.confmng.persistence.mapper;

import com.gavinbj.confmng.persistence.entity.UserInfo;
import com.gavinbj.confmng.persistence.entity.UserInfoExample;
import org.springframework.stereotype.Repository;

/**
 * UserInfoMapper继承基类
 */
@Repository
public interface UserInfoMapper extends MyBatisBaseDao<UserInfo, String, UserInfoExample> {
}

6.2.4 使用MyBatis接口

1. @MapperScan扫描加载MyBaits的Mapper
package com.gavinbj.confmng;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 *  @SpringBootApplication 标注这是SpringBoot项目的主程序类
 */
@MapperScan("com.gavinbj.confmng.persistence.mapper")
@SpringBootApplication
public class ConfManageApplication {

	public static void main(String[] args) {
		SpringApplication.run(ConfManageApplication.class, args);
	}

}
2. UserInfoService & UserInfoServiceImpl
package com.gavinbj.confmng.service;

import java.util.List;

import com.gavinbj.confmng.persistence.entity.UserInfo;

/**
 * 用户信息
 * 
 * @author gavinbj
 *
 */
public interface UserInfoService {
	
	public UserInfo getUserByPK(String userId);

    public List<UserInfo> selectUserInfos(String userName);

}
package com.gavinbj.confmng.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import com.gavinbj.confmng.persistence.entity.UserInfo;
import com.gavinbj.confmng.persistence.entity.UserInfoExample;
import com.gavinbj.confmng.persistence.mapper.UserInfoMapper;
import com.gavinbj.confmng.service.UserInfoService;

/**
 * 用户信息
 * 
 * @author gavinbj
 *
 */
@Service
public class UserInfoServiceImpl implements UserInfoService {

	@Autowired 
	private UserInfoMapper userInfoMapper;
	
	/**
	 * 根据用户ID进行主键检索
	 */
	@Override
	public UserInfo getUserByPK(String userId) {
		
		return this.userInfoMapper.selectByPrimaryKey(userId);
	}

	/**
	 * 根据用户名模糊检索
	 * 
	 */
	@Override
	public List<UserInfo> selectUserInfos(String userName) {
		UserInfoExample userExample = new UserInfoExample();
		userExample.createCriteria().andUserNameLike(userName);
		
		return this.userInfoMapper.selectByExample(userExample);
	}

}
3. Mybatis的Example用法介绍

Example类用来构建一个动态的where子句,根据数据表中的每个非BLOB列可以被包括在where子句中。Example类包含一个内部静态类Criteria以及包含一个用where子句中的条件列表。

创建Criteria对象可以使用Example类中的createCriteria()或者 or() 。使用createCriteria() 创建的Criteria ,它会自动为List属性添加一个Criteria对象,这使得它更容易写一个简单的where子句。

Criteria 内部类的每个属性都包含 andXXX 方法,以及如下的标准的SQL查询方法:

IS NULL 		- 指相关的列必须为NULL 
IS NOT NULL 	- 指相关的列必须不为NULL 
= (equal) 		- 指相关的列必须等于方法参数中的值 
<> (not equal) 	- 指相关的列必须不等于方法参数中的值

(greater than) 	- 指相关的列必须大于方法参数中的值 
= (greater than or equal) 	- 指相关的列必须大于等于方法参数中的值 
< (less than) 	- 指相关的列必须小于于方法参数中的值 
<= (less than or equal) 	- 指相关的列必须小于等于方法参数中的值 
LIKE 			- 指相关的列必须 “like” 方法参数中的值. 这个方法不用必须加入 ‘%’, 您必须设置方法参数中的值. 
NOT LIKE 		- 指相关的列必须 “not like” 方法参数中的值. 这个方法不用必须加入 ‘%’, 您必须设置方法参数中的值. 
BETWEEN 		- 指相关的列必须在 “between” 方法参数中的两个值之间. 
NOT BETWEEN 	- 指相关的列必须不在 “not between” 方法参数中的两个值之间. 
IN 				- 指相关的列必须在传入的方法参数的list中. 
NOT IN 			- 指相关的列必须不在传入的方法参数的list中.
4. 创建REST测试接口
package com.gavinbj.confmng.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.gavinbj.common.bean.BaseResult;
import com.gavinbj.common.bean.ResponseUtils;
import com.gavinbj.confmng.persistence.entity.UserInfo;
import com.gavinbj.confmng.service.UserInfoService;

@RestController
@RequestMapping("/api/user")
public class UserController {

	@Autowired
	UserInfoService userInfoService;

	/**
	 * 根据用户名进行模糊检索
	 * 
	 */
	@GetMapping("/users")
	public BaseResult<List<UserInfo>> searchUsers(@RequestParam(value = "userName", required = true) String userName) {

		List<UserInfo> lstUsers = this.userInfoService.selectUserInfos(userName);

		return ResponseUtils.makeOKRsp(lstUsers);
	}

	/**
	 * 检索用户信息
	 * 
	 */
	@GetMapping("/users/{userId}")
	public BaseResult<UserInfo> getUserById(@PathVariable("userId") String userId) {

		UserInfo userInfo = userInfoService.getUserByPK(userId);

		return ResponseUtils.makeOKRsp(userInfo);
	}

}

验证测试结果如下:

{
	"status": 0,
	"code": 1003,
	"msg": "处理成功!",
	"data": {
		"userId": "gavin",
		"userName": "盖文",
		"introduce": "大学教授",
		"mobilephone": "13940981276",
		"email": "gavin@qq.com",
		"birthday": "2019-10-07T00:00:00.000+0000",
		"gender": "男"
	}
}
【SpringBoot 2学习笔记】《八》SpringBoot2数据库访问之整合MyBatis【SpringBoot 2学习笔记】《八》SpringBoot2数据库访问之整合MyBatis 黑白猿 发布了24 篇原创文章 · 获赞 12 · 访问量 1万+ 私信 关注
上一篇:如何从Freemarker调用JavaScript函数?


下一篇:Freemarker + xml 实现Java导出word