📂 集成 - 📃 MyBatis - 《小昱的Spring全家桶微服务实战笔记》

一、概述
二、配置
三、创建实体类
四、注解式Mapper
五、XML式Mapper
- mybatis-config
- 创建xml
六、封装分页
七、常见报错
- The used SELECT statements have a different number of columns
- No serializer found for class
参考资料

一、概述

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。

MyBatis 可以对配置和原生Map使用简单的 XML 或注解，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

mybatis 官网

二、配置

依赖

<!-- MySQL数据库驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>
<!-- MyBatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.0.0</version>
</dependency>

配置

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
    username: root
    password: root
mybatis:
  type-aliases-package: com.example.mybatis.entity
  mapper-locations:
    - classpath:mapper/*.xml
  config-location: classpath:mybatis.xml

Spring Boot 会自动加载 spring.datasource.* 相关配置，数据源就会自动注入到 sqlSessionFactory 中，sqlSessionFactory 会自动注入到 Mapper 中

配置类
在配置类 MyBatisConfig 中添加对 mapper 包扫描的注解 @MapperScan

@Configuration
@MapperScan({"com.example.model.mapper"})
public class MyBatisConfig {
}

或者直接在 Mapper 类上面添加注解 @Mapper，建议使用上面那种，不然每个 mapper 加个注解也挺麻烦的

如果不添加 @MapperScan 的注解，在启动时会报以下错误：

Field mapper in xxx required a bean of type 'xxx' that could not be found.

三、创建实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserEntity implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long id;
    private String userName;
    private String passWord;
    private UserSexEnum userSex;
    private String nickName;

    public UserEntity(String userName, String passWord, UserSexEnum userSex) {
        super();
        this.passWord = passWord;
        this.userName = userName;
        this.userSex = userSex;
    }
}

这里用到一个枚举：

public enum UserSexEnum {
    MAN, WOMAN
}

四、注解式Mapper

package com.example.mybatis.mapper;

import com.example.mybatis.entity.UserEntity;
import com.example.mybatis.enums.UserSexEnum;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {

    @Select("SELECT * FROM user")
    @Results({
            @Result(property = "userSex",  column = "user_sex", javaType = UserSexEnum.class),
            @Result(property = "nickName", column = "nick_name")
    })
    List<UserEntity> getAll();

    @Select("SELECT * FROM user WHERE id = #{id}")
    @Results({
            @Result(property = "userSex",  column = "user_sex", javaType = UserSexEnum.class),
            @Result(property = "nickName", column = "nick_name")
    })
    UserEntity getOne(Long id);

    @Insert("INSERT INTO user(user_name,pass_word,user_sex) VALUES(#{userName}, #{passWord}, #{userSex})")
    void insert(UserEntity user);

    @Update("UPDATE user SET user_name=#{userName},nick_name=#{nickName} WHERE id=#{id}")
    void update(UserEntity user);

    @Delete("DELETE FROM user WHERE id=#{id}")
    void delete(Long id);
}

@Select 是查询类的注解，所有的查询均使用这个
@Result 修饰返回的结果集，关联实体类属性和数据库字段一一对应，如果实体类属性和数据库属性名保持一致，就不需要这个属性来修饰。
@Insert 插入数据库使用，直接传入实体类会自动解析属性到对应的值
@Update 负责修改，也可以直接传入对象
@delete 负责删除

注意使用#号和$号的不同：

// This example creates a prepared statement, something like select * from user where name = ?;
@Select("Select * from user where name = #{name}")
Teacher selectTeachForGivenName(@Param("name") String name);

// This example creates n inlined statement, something like select * from user where name = 'someName';
@Select("Select * from user where name = '${name}'")
Teacher selectTeachForGivenName(@Param("name") String name);

测试：

@RunWith(SpringRunner.class)
@SpringBootTest
public class MybatisTests {
    @Autowired
    private UserMapper userMapper;

    @Test
    public void testInsert() throws Exception {
        userMapper.insert(new UserEntity("aa1", "a123456", UserSexEnum.MAN));
        userMapper.insert(new UserEntity("bb1", "b123456", UserSexEnum.WOMAN));
        userMapper.insert(new UserEntity("cc1", "b123456", UserSexEnum.WOMAN));
    }

    @Test
    public void testQuery() throws Exception {
        List<UserEntity> users = userMapper.getAll();
        System.out.println(users.toString());
    }

    @Test
    public void testUpdate() throws Exception {
        UserEntity user = userMapper.getOne(20l);
        System.out.println(user.toString());
        user.setNickName("yu");
        userMapper.update(user);
        Assert.assertTrue(("yu".equals(userMapper.getOne(20l).getNickName())));
    }
}

五、XML式Mapper

对比注解版, 需要在配置中修改:

mybatis:
    config-location: classpath:mybatis/mybatis-config.xml
    mapper-locations: classpath:mybatis/mapper/*.xml

指定了 Mybatis 基础配置文件和实体类映射文件的地址

mybatis-config

classpath:mybatis/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer"/>
        <typeAlias alias="Long" type="java.lang.Long"/>
        <typeAlias alias="HashMap" type="java.util.HashMap"/>
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap"/>
        <typeAlias alias="ArrayList" type="java.util.ArrayList"/>
        <typeAlias alias="LinkedList" type="java.util.LinkedList"/>
    </typeAliases>
</configuration>

创建xml

classpath:mybatis/mapper/UserMapper.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.example.mybatisxml.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.example.mybatisxml.entity.UserEntity">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="user_name" property="userName" jdbcType="VARCHAR"/>
        <result column="pass_word" property="passWord" jdbcType="VARCHAR"/>
        <result column="user_sex" property="userSex" javaType="com.example.mybatisxml.enums.UserSexEnum"/>
        <result column="nick_name" property="nickName" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, user_name, pass_word, user_sex, nick_name
    </sql>

    <select id="getAll" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM user
    </select>

    <select id="getOne" parameterType="java.lang.Long" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM user
        WHERE id = #{id}
    </select>

    <insert id="insert" parameterType="com.example.mybatisxml.entity.UserEntity">
        INSERT INTO
        user
        (user_name, pass_word, user_sex)
        VALUES
        (#{userName}, #{passWord}, #{userSex})
    </insert>

    <update id="update" parameterType="com.example.mybatisxml.entity.UserEntity">
        UPDATE
        user
        SET
        <if test="userName != null">user_name = #{userName},</if>
        <if test="passWord != null">pass_word = #{passWord},</if>
        nick_name = #{nickName}
        WHERE
        id = #{id}
    </update>

    <delete id="delete" parameterType="java.lang.Long">
        DELETE FROM
        user
        WHERE
        id = #{id}
    </delete>
</mapper>

:::warning 注意：

namespace 与 Mapper 接口名对应
resultMap 的 type 与实体类对应
jdbcType 的值必须全为大写 :::

在 UserMapper.java 中只需要声明相应的方法即可：

public interface UserMapper {
    List<UserEntity> getAll();
    UserEntity getOne(Long id);
    void insert(UserEntity user);
    void update(UserEntity user);
    void delete(Long id);
}

其实就是把上个版本中 Mapper 接口中的 Sql 全部搬到了的 xml 中了。

六、封装分页

PagerHelper是MyBatis分页插件，简单的几行代码就能实现分页，在与SpringBoot整合时，只要整合PagerHelper就自动整合了MyBatis。

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.10</version>
</dependency>

使用方式：

PageHelper.startPage(pageNum, pageSize);
// 之后进行查询操作将自动进行分页
List<Brand> brandList = brandMapper.selectByExample(new BrandExample());
// 通过构造PageInfo对象获取分页信息，如当前页码，总页数，总条数
PageInfo<Brand> pageInfo = new PageInfo<Brand>(list);

我们可以对分页插件进行封装，以便更轻松地使用：

import com.github.pagehelper.PageInfo;
import lombok.Data;
import org.springframework.data.domain.Page;

import java.util.List;

/**
 * 分页数据封装类
 */
@Data
public class CommonPage<T> {
    private Integer pageNum;
    private Integer pageSize;
    private Integer totalPage;
    private Long total;
    private List<T> list;

    /**
     * 将PageHelper分页后的list转为分页信息
     */
    public static <T> CommonPage<T> restPage(List<T> list) {
        CommonPage<T> result = new CommonPage<T>();
        PageInfo<T> pageInfo = new PageInfo<T>(list);
        result.setTotalPage(pageInfo.getPages());
        result.setPageNum(pageInfo.getPageNum());
        result.setPageSize(pageInfo.getPageSize());
        result.setTotal(pageInfo.getTotal());
        result.setList(pageInfo.getList());
        return result;
    }

    /**
     * 将SpringData分页后的list转为分页信息
     */
    public static <T> CommonPage<T> restPage(Page<T> pageInfo) {
        CommonPage<T> result = new CommonPage<T>();
        result.setTotalPage(pageInfo.getTotalPages());
        result.setPageNum(pageInfo.getNumber());
        result.setPageSize(pageInfo.getSize());
        result.setTotal(pageInfo.getTotalElements());
        result.setList(pageInfo.getContent());
        return result;
    }
}

使用：

List<Admin> adminList = adminService.list();
CommonPage.restPage(adminList)

七、常见报错

The used SELECT statements have a different number of columns

这是因为使用union的两个SQL语句产生的记录的表结构不一致. 必须是结构完全一致的记录集合才可以使用UNION。

No serializer found for class

如果需要将某个类转成json串的话，那么必须要求其属性为public，或者提供public的get()方法，也可以添加lombok的@Getter注解。

参考资料

Spring Boot(六)：如何优雅的使用 Mybatis