SpringBoot学习(十二):Mybatis连表(一对一)使用实践

1. 前置准备

1.1 创建表

-- 学生表
CREATE TABLE `micro_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生表';
-- 测试数据
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (1, '张三');
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (2, '李四');
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (3, '赵武');
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (4, '王麻子');

-- 学生表扩展信息
CREATE TABLE `micro_student_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_id` int unsigned NOT NULL DEFAULT '0' COMMENT '学生表id',
  `phone` char(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生表扩展信息';

-- 测试数据
INSERT INTO `micro_serve`.`micro_student_info`(`id`, `student_id`, `phone`, `birthday`, `create_time`) VALUES (1, 1, '17600000000', '1991-03-12', '2020-09-27 12:15:37');
INSERT INTO `micro_serve`.`micro_student_info`(`id`, `student_id`, `phone`, `birthday`, `create_time`) VALUES (2, 2, '17600000001', '1995-12-10', '2020-09-22 11:55:17');
INSERT INTO `micro_serve`.`micro_student_info`(`id`, `student_id`, `phone`, `birthday`, `create_time`) VALUES (3, 3, '17600000002', '1993-02-09', '2020-09-21 12:15:37');
INSERT INTO `micro_serve`.`micro_student_info`(`id`, `student_id`, `phone`, `birthday`, `create_time`) VALUES (4, 4, '17600000003', '1997-08-23', '2020-09-23 14:55:37');

1.2 创建实体类

新建:StudentModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:00
 */
package com.hui.javalearn.model;

import java.io.Serializable;

import lombok.Data;

/**
 *
 * 学生表
 *
 * 表名: micro_student
 */
@Data
public class StudentModel implements Serializable {
    /**
     *
     * 备注:用户id
     *
     * 表字段: micro_student.id
     */
    private Integer id;

    /**
     *
     * 备注:名称
     *
     * 表字段: micro_student.name
     */
    private String name;
    
    /**
     * @注意！！！！！！！  
     * 学生的扩展信息
     */
    private StudentInfoModel studentInfoModel;
    
    private static final long serialVersionUID = 1L;

    public StudentModel(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public StudentModel() {
        super();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", name=").append(name);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

新建:StudentInfoModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:06:34
 */
package com.hui.javalearn.model;

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

import lombok.Data;

/**
 *
 * 学生表扩展信息
 *
 * 表名: micro_student_info
 */
@Data
public class StudentInfoModel implements Serializable {
    /**
     *
     * 备注:id
     *
     * 表字段: micro_student_info.id
     */
    private Integer id;

    /**
     *
     * 备注:学生表id
     *
     * 表字段: micro_student_info.student_id
     */
    private Integer studentId;

    /**
     *
     * 备注:名称
     *
     * 表字段: micro_student_info.phone
     */
    private String phone;

    /**
     *
     * 备注:生日
     *
     * 表字段: micro_student_info.birthday
     */
    private Date birthday;

    /**
     *
     * 备注:创建时间
     *
     * 表字段: micro_student_info.create_time
     */
    private Date createTime;

    private static final long serialVersionUID = 1L;

    public StudentInfoModel(Integer id, Integer studentId, String phone, Date birthday, Date createTime) {
        this.id = id;
        this.studentId = studentId;
        this.phone = phone;
        this.birthday = birthday;
        this.createTime = createTime;
    }

    public StudentInfoModel() {
        super();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", studentId=").append(studentId);
        sb.append(", phone=").append(phone);
        sb.append(", birthday=").append(birthday);
        sb.append(", createTime=").append(createTime);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

1.3 创建Dao

新建:StudentDao.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:01
 */
package com.hui.javalearn.dao;

import com.hui.javalearn.model.StudentModel;

public interface StudentDao {
    StudentModel selectStudentById(Integer id);
}

新建:StudentInfoDao.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:06:34
 */
package com.hui.javalearn.dao;

import com.hui.javalearn.model.StudentInfoModel;

public interface StudentInfoDao {
    StudentInfoModel selectStudentInfoByStudentId(Integer studentId);
}

1.4 创建Mapper

新建:StudentDao.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.hui.javalearn.dao.StudentDao">
  <resultMap id="BaseResultMap" type="com.hui.javalearn.model.StudentModel">
    <constructor>
      <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="name" javaType="java.lang.String" jdbcType="VARCHAR" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    id, `name`
  </sql>
  <select id="selectStudentById" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from micro_student
    where id = #{id,jdbcType=INTEGER}
  </select>
</mapper>>

新建:StudentInfoDao.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.hui.javalearn.dao.StudentInfoDao">
  <resultMap id="BaseResultMap" type="com.hui.javalearn.model.StudentInfoModel">
    <constructor>
      <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="student_id" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="phone" javaType="java.lang.String" jdbcType="VARCHAR" />
      <arg column="birthday" javaType="java.util.Date" jdbcType="DATE" />
      <arg column="create_time" javaType="java.util.Date" jdbcType="TIMESTAMP" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    id, student_id, `phone`, birthday, create_time
  </sql>
<!-- 根据学生ID查询学生信息 -->
  <select id="selectStudentInfoByStudentId" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from micro_student_info
    where student_id = #{studentId,jdbcType=INTEGER}
  </select>
</mapper>

2. 实现方式

Mybatis通过使用<resultMap>元素中的子元素<association>来处理一对一的关联关系。在<association>元素中，通常可以配置以下属性:

• property: 指定映射到的实体类对象属性，与表字段一一对应。　
• column: 指定表中对应的字段。　
• javaType: 指定映射到实体对象属性的类型。　
• select: 指定引入嵌套查询的子SQL语句，用于关联映射中的嵌套查询。　
• fetchType: 指定在关联查询时是否启用延迟加载，有lazy和eager两个属性值，默认值为lazy（默认关联映射延迟加载）。

MyBatis在映射文件中加载关联关系对象主要通过两种方式：嵌套查询和嵌套结果

• 嵌套查询是指通过执行另一条SQL映射语句来返回预期的复杂类型；
• 嵌套结果是使用嵌套结果映射来处理重复的联合结果的子集。

3. 方式一:嵌套查询

3.1 修改实体类

修改: 学生实体类StudentModel.java，新增学生的扩展信息(studentInfoModel)属性

// 新增这属性，用来储存学生的扩展信息，上面文件已加

/**
 * @注意！！！！！！！  
 * 学生的扩展信息
 */
private StudentInfoModel studentInfoModel;

3.2 修改Mapper

修改: 学生表对应的mapper文件(StudentDao.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.hui.javalearn.dao.StudentDao">
  <resultMap id="BaseResultMap" type="com.hui.javalearn.model.StudentModel">
    <constructor>
      <idArg column="id" javaType="java.lang.Integer" jdbcType="INTEGER" />
      <arg column="name" javaType="java.lang.String" jdbcType="VARCHAR" />
    </constructor>
  </resultMap>
  <sql id="Base_Column_List">
    id, `name`
  </sql>
<!-- 嵌套查询 操作一对一 -->
  <select id="selectStudentById" parameterType="java.lang.Integer" resultMap="oneLinkOneResult">
    select 
    <include refid="Base_Column_List" />
    from micro_student
    where id = #{id,jdbcType=INTEGER}
  </select>
  <resultMap id="oneLinkOneResult" type="com.hui.javalearn.model.StudentModel">
    <id property="id" column="id"/>
    <!-- association使用 select 属性引入另一条SQL语句 -->
    <association property="studentInfoModel" column="id" javaType="com.hui.javalearn.model.StudentInfoModel"
                 select="com.hui.javalearn.dao.StudentInfoDao.selectStudentInfoByStudentId">
    </association>
  </resultMap>
</mapper>

3.3 测试

package com.hui.javalearn;

import com.alibaba.fastjson.JSON;
import com.hui.javalearn.dao.StudentDao;
import com.hui.javalearn.model.StudentModel;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class TestMybatisLinkSearch {
    @Autowired
    private StudentDao studentDao;
    @Test
    void testOneLinkOneSearch(){
        // 查询学生ID=1的学生信息
        StudentModel studentModel = studentDao.selectStudentById(1);
        String s = JSON.toJSONString(studentModel);
        System.out.println(s);
    }
}

输出结果:

{
    "id":1,
    "name":"张三",
    "studentInfoModel":{
        "birthday":668707200000,
        "createTime":1601180137000,
        "id":1,
        "phone":"17600000000",
        "studentId":1
    }
}

虽然使用嵌套查询的方式比较简单，但是嵌套查询的方式要执行多条SQL语句，这对于大型数据集合和列表展示不是很好，因为这样可能会导致成百上千条关联的SOL语句被执行，从而极大地消耗数据库性能，并且会降低查询效率。为此，MyBatis提供了嵌套结果的方式进行关联查询。

@@注意： 生日和创建日期发现Json后有问题

解决方法：

修改字段对应的实体类StudentInfoModel.java,为字段加上@JSONField注解,并设置属性format定义格式。

...
/**
 *
 * 备注:生日
 *
 * 表字段: micro_student_info.birthday
 */
@JSONField(format = "yyyy-MM-dd")
private Date birthday;
/**
 *
 * 备注:创建时间
 *
 * 表字段: micro_student_info.create_time
 */
@JSONField(format = "yyyy-MM-dd HH:mm:ss")
private Date createTime;
...

再次运行:

{
    "id":1,
    "name":"张三",
    "studentInfoModel":{
        "birthday":"1991-03-12",
        "createTime":"2020-09-27 12:15:37",
        "id":1,
        "phone":"17600000000",
        "studentId":1
    }
}

4. 方式二: 嵌套结果

4.1 修改Dao

修改StudentDao.java新增方法: selectStudentById2

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:01
 */
package com.hui.javalearn.dao;

import com.hui.javalearn.model.StudentModel;

public interface StudentDao {
    // 嵌套结果
    StudentModel selectStudentById2(Integer id);
}

4.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.hui.javalearn.dao.StudentDao">
  <!-- 嵌套结果 操作一对一 -->
  <select id="selectStudentById2" resultType="com.hui.javalearn.model.StudentModel"
          resultMap="nestedResult">
      select A.*,B.phone,B.birthday,B.create_time
      from micro_student as A,micro_student_info as B
      <!-- 如果想查出所有列表，则去掉AND A.id = #{id,jdbcType=INTEGER}即可 -->
      where A.id = B.student_id AND A.id = #{id,jdbcType=INTEGER}
  </select>
  <resultMap id="nestedResult" type="com.hui.javalearn.model.StudentModel">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <association property="studentInfoModel" javaType="com.hui.javalearn.model.StudentInfoModel">
      <id property="id" column="id"/>
      <result property="phone" column="phone" />
      <result property="birthday" column="birthday" />
      <result property="createTime" column="create_time" />
    </association>
  </resultMap>
</mapper>

4.3 测试

package com.hui.javalearn;

import com.alibaba.fastjson.JSON;
import com.hui.javalearn.dao.StudentDao;
import com.hui.javalearn.model.StudentModel;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class TestMybatisLinkSearch {
    @Autowired
    private StudentDao studentDao;
    @Test
    void testOneLinkOneSearch2(){
        StudentModel studentModel = studentDao.selectStudentById2(1);
        String s = JSON.toJSONString(studentModel);
        System.out.println(s);
    }
}

输出

{
    "id":1,
    "name":"张三",
    "studentInfoModel":{
        "birthday":"1991-03-12",
        "createTime":"2020-09-27 12:15:37",
        "id":1,
        "phone":"17600000000"
    }
}