mybatis级联查询-collection

前期准备

准备数据
创建数据库jilian
在数据库中创建俩个表studen和teacher表，
俩者关系：teacher通过主键id和student表的teacher_id字段相关联（一对多关系）

建表语句

CREATE TABLE teacher (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '教师名称',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='教师表';
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
student_name varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生名称',
teacher_id int(11) DEFAULT NULL COMMENT '教师id',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='学生表';
INSERT INTO jilian.teacher (id, name) VALUES ('1', '李老师');
INSERT INTO jilian.teacher (id, name) VALUES ('2', '王老师');
INSERT INTO jilian.teacher (id, name) VALUES ('3', '钱老师');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('1', '李一', '1');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('2', '李二', '1');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('3', '李三', '1');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('4', '李四', '1');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('5', '王一', '2');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('6', '王三', '2');
INSERT INTO jilian.student (id, student_name, teacher_id) VALUES ('7', '王二', '2');

利用mybatis-generator生成相对应的实体类，dao和mapper

Student.java

package com.java.ting.domain;

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;

/**
 * student
 * @author 
 */
@Getter
@Setter
public class Student implements Serializable {

    /**
     * 主键
     */
    private Integer id;

    /**
     * 学生名称
     */
    private String studentName;

    /**
     * 教师id
     */
    private Integer teacherId;

    private static final long serialVersionUID = 1L;

}

StudentMapper.java

import com.java.ting.domain.Student;

import java.util.List;

public interface StudentMapper {

    int deleteByPrimaryKey(Integer id);

    int insert(Student record);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Student record);

    int updateByPrimaryKey(Student record);
}

StudentMapper.xml

<resultMap id="BaseResultMap" type="com.java.ting.domain.Student">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="student_name" jdbcType="VARCHAR" property="studentName"/>
    <result column="teacher_id" jdbcType="INTEGER" property="teacherId"/>
</resultMap>

<sql id="Base_Column_List">
id, student_name, teacher_id
</sql>

<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from student
    where id = #{id,jdbcType=INTEGER}
</select>

<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from student
where id = #{id,jdbcType=INTEGER}
</delete>

<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.java.ting.domain.Student"
        useGeneratedKeys="true">
    insert into student (student_name, teacher_id)
    values (#{studentName,jdbcType=VARCHAR}, #{teacherId,jdbcType=INTEGER})
</insert>

<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.java.ting.domain.Student"
        useGeneratedKeys="true">
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="studentName != null">
            student_name,
        </if>
        <if test="teacherId != null">
            teacher_id,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="studentName != null">
            #{studentName,jdbcType=VARCHAR},
        </if>
        <if test="teacherId != null">
            #{teacherId,jdbcType=INTEGER},
        </if>
    </trim>
</insert>

<update id="updateByPrimaryKeySelective" parameterType="com.java.ting.domain.Student">
    update student
    <set>
        <if test="studentName != null">
            student_name = #{studentName,jdbcType=VARCHAR},
        </if>
        <if test="teacherId != null">
            teacher_id = #{teacherId,jdbcType=INTEGER},
        </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
</update>

<update id="updateByPrimaryKey" parameterType="com.java.ting.domain.Student">
    update student
    set student_name = #{studentName,jdbcType=VARCHAR},
    teacher_id = #{teacherId,jdbcType=INTEGER}
    where id = #{id,jdbcType=INTEGER}
</update>

Teacher.java

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;

/**
 * teacher
 * @author tingOu
 */
@Getter
@Setter
public class Teacher implements Serializable {
    private Integer id;

    /**
     * 教师名称
     */
    private String name;

    private static final long serialVersionUID = 1L;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }


}

TeacherMapper.java

public interface TeacherMapper {

    int deleteByPrimaryKey(Integer id);

    int insert(Teacher record);

    int insertSelective(Teacher record);

    Teacher selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(Teacher record);

    int updateByPrimaryKey(Teacher record);
}

TeacherMapper.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.java.ting.mapper.TeacherMapper">

    <resultMap id="BaseResultMap" type="com.java.ting.domain.Teacher">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>
    
    <sql id="Base_Column_List">
        id, `name`
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from teacher
        where id = #{id,jdbcType=INTEGER}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from teacher
        where id = #{id,jdbcType=INTEGER}
    </delete>

    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.java.ting.domain.Teacher"
            useGeneratedKeys="true">
        insert into teacher (`name`)
        values (#{name,jdbcType=VARCHAR})
    </insert>

    <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.java.ting.domain.Teacher"
            useGeneratedKeys="true">
        insert into teacher
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                `name`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>

    </insert>

    <update id="updateByPrimaryKeySelective" parameterType="com.java.ting.domain.Teacher">
        update teacher
        <set>
            <if test="name != null">
                `name` = #{name,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

    <update id="updateByPrimaryKey" parameterType="com.java.ting.domain.Teacher">
        update teacher
        set `name` = #{name,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

新增一个TeacherDto用来接口级联查询出的数据

import com.java.ting.domain.Student;
import lombok.Getter;
import lombok.Setter;

import java.util.List;

/**
 * 测试级联-一对多
 *
 * @author LISHUANG
 * @date 2020/1/14
 */
@Getter
@Setter
public class TeacherDto {

    /**
     * 主键
     */
    private Integer id;

    /**
     * 教师名称
     */
    private String name;
    /**
     * 学生信息集合
     */
    private List<Student> studentList;

}

在StudentMapper类中新建一个方法

/**
 * 根据教师名称获取信息
 * @param teacherId
 * @return 学生信息
 */
List<Student> findStudentByTeacherId(Integer teacherId);

对应的mapper

<select id="findStudentByTeacherId" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from student
    where
    teacher_id = #{teacherId}
</select>

在TeacherMapper类中新建俩个方法用来测试俩种方式

List<TeacherDto> getTeacherAndStudentById();

List<TeacherDto> getTeacherAndStudentByIdLeftJoin();

对应的mapper

<!-- 第一种 这个会多次查询关联表 n+1次-->
<resultMap id="BaseResultList" type="com.java.ting.dto.jilian.TeacherDto">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <collection property="studentList" ofType="com.java.ting.domain.Student"
                select="com.java.ting.mapper.StudentMapper.findStudentByTeacherId"
                column="{teacherId = id}"/>
</resultMap>


<!-- 第二种 利用左链接查询 效率快-->
<resultMap id="BaseResultLeftJoin" type="com.java.ting.dto.jilian.TeacherDto">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <collection property="studentList" resultMap="BaseStudent" />
</resultMap>

<resultMap id="BaseStudent" type="com.java.ting.domain.Student">
    <id column="student_id" jdbcType="INTEGER" property="id"/>
    <result column="student_name" jdbcType="VARCHAR" property="studentName"/>
    <result column="teacher_id" jdbcType="INTEGER" property="teacherId"/>
</resultMap>

<sql id="Base_Column_List_left">
    t.id, t.`name`, s.id as student_id, s.student_name as student_name, s.teacher_id as teacher_id
</sql>

<!-- 第二种 end-->

<select id="getTeacherAndStudentById" resultMap="BaseResultList">
    select
    <include refid="Base_Column_List"/>
    from teacher
</select>

<select id="getTeacherAndStudentByIdLeftJoin" resultMap="BaseResultLeftJoin">
    select
    <include refid="Base_Column_List_left"/>
    from teacher t
    left JOIN student s on t.id = s.teacher_id
</select>

新建个JiLianController用来测试

import com.alibaba.fastjson.JSON;
import com.java.ting.dto.jilian.TeacherDto;
import com.java.ting.mapper.TeacherMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * 级联测试类
 *
 * @author LISHUANG
 * @date 2020/1/14
 */
@RestController
@RequestMapping(value = "/web")
@Slf4j
@Api
public class JiLianController {

    @Autowired
    private TeacherMapper teacherMapper;

    /**
     * mybatis级联测试-collection使用select标签
     * <p>和数据库的交互次数 1+n 数据量越大效率越慢<pre>1：代表查询教师表的次数；</pre><pre>n：代表查询学生表的次数</pre></p>
     * @return List<TeacherDto> 教师和学生关联数据
     */
    @ApiOperation(value = "mybatis级联测试-collection使用select", notes = "Mybatis")
    @PostMapping(value = "/jilian/collection/select")
    public List<TeacherDto> getCollectionSelect() {
        List<TeacherDto> list = teacherMapper.getTeacherAndStudentById();
        log.info("展示数据级联--{}", JSON.toJSON(list));
        return list;
    }

    /**
     * mybatis级联测试-collection使用左连接
     *<p>直接使用sql进行查询一次只和数据库交互一次,比{@link TeacherMapper#getTeacherAndStudentById}方法用时更少</p>
     *
     * @return  List<TeacherDto> 教师和学生关联数据
     */
    @ApiOperation(value = "mybatis级联测试-collection使用左连接", notes = "Mybatis")
    @PostMapping(value = "/jilian/leftJoin")
    public List<TeacherDto> getLeftJoin() {
        List<TeacherDto> list = teacherMapper.getTeacherAndStudentByIdLeftJoin();
        log.info("展示数据-----{}", JSON.toJSON(list));
        return list;

    }
}

调用接口进行对比

• 启动项目后调用http://localhost:xxx/web/jilian/collection/select发现使用select的会和数据库进行4次交互，如下图（使用idea插件MyBatis Log Plugin）

  1  restore sql from selection  - ==>
  select id, `name`
   FROM teacher;
  ------------------------------------------------------------------------------------------------------------------------
  2  restore sql from selection  - ==>
  select id, student_name, teacher_id
   FROM student
   WHERE teacher_id = 1;
  ------------------------------------------------------------------------------------------------------------------------
  3  restore sql from selection  - ==>
  select id, student_name, teacher_id
   FROM student
   WHERE teacher_id = 2;
  ------------------------------------------------------------------------------------------------------------------------
  4  restore sql from selection  - ==>
  select id, student_name, teacher_id
   FROM student
   WHERE teacher_id = 3;
  ------------------------------------------------------------------------------------------------------------------------

• 调用http://localhost:6850/web/jilian/leftJoin只会和数据库进行一次交互

  --  5  2020-01-15 15:12:38.105 [http-nio-6850-exec-2] DEBUG c.j.t.m.T.getTeacherAndStudentByIdLeftJoin - ==>
  select t.id, t.`name`, s.id as student_id, s.student_name as student_name, s.teacher_id as teacher_id
   FROM teacher t
   LEFT JOIN student s
   ON t.id = s.teacher_id;
  ------------------------------------------------------------------------------------------------------------------------

• 俩者五次调用的用时

  次数	getTeacherAndStudentById方法	getTeacherAndStudentByIdLeftJoin方法
  1	130ms	50ms
  2	114ms	53ms
  3	118ms	50ms
  4	119ms	50ms
  5	118ms	58ms

使用左连接的getTeacherAndStudentByIdLeftJoin的方法基本上都比使用拼接查询的getTeacherAndStudentById方法快上俩倍以上

详解俩种方法

1. 第一种使用拼接查询的方法

   <resultMap id="BaseResultList" type="com.java.ting.dto.jilian.TeacherDto">
       <id column="id" jdbcType="INTEGER" property="id"/>
       <result column="name" jdbcType="VARCHAR" property="name"/>
       <collection property="studentList" ofType="com.java.ting.domain.Student"
                   select="com.java.ting.mapper.StudentMapper.findStudentByTeacherId"
                   column="{teacherId = id}"/>
   </resultMap>

• property 中的参数要和TeacherDto实体类中的list属性名一样

• ofType是该放回的实体类类型，

• select的参数是关联的使用哪个查询方法；

• column=”{teacherId = id} 中{}中的字段对应关系，teacherId 是Student实体对应的字段，id是上面result中相对应，column中的字段俩者在表中必须有关联关系。不然无法建立关系。

   数据库的列名或者列标签别名。与传递给select中配置的方法名参数名称相同。注意： 在处理组合键时，可以使用column=“{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句。这就会把prop1和prop2设置到目标嵌套选择语句的参数对象中。
  

2. 使用链接查询

   <!-- 第二种 利用左链接查询 效率快-->
   <resultMap id="BaseResultLeftJoin" type="com.java.ting.dto.jilian.TeacherDto">
       <id column="id" jdbcType="INTEGER" property="id"/>
       <result column="name" jdbcType="VARCHAR" property="name"/>
       <collection property="studentList" resultMap="BaseStudent" />
   </resultMap>
   
   <resultMap id="BaseStudent" type="com.java.ting.domain.Student">
       <id column="student_id" jdbcType="INTEGER" property="id"/>
       <result column="student_name" jdbcType="VARCHAR" property="studentName"/>
       <result column="teacher_id" jdbcType="INTEGER" property="teacherId"/>
   </resultMap>
   
   <sql id="Base_Column_List_left">
       t.id, t.`name`, s.id as student_id, s.student_name as student_name, s.teacher_id as teacher_id
   </sql>
   
   <!-- 第二种 end-->

   第二种和第一种的区别就是在collection中使用定制的resultMap和sql字段

• collection中的property和第一种一样都是TeacherDto实体类对应的属性
• collection中的resultMap是使用地址的resultMap的id
• 定制的resultMap中的result的column一定要和上面的resultMap的字段不能重复否则无法正确的展示出先要的数据
• select标签中也要使用定制的 最好使用别名进行区分，此时要和定制的resultMap中的column中字段保持一致，因为如果不使用别名可能teacher表和student表中的字段名重复，导致数据展示异常