mybaitis 嵌套查询

当使用mybaitis查询时，想要展示一个JavaBean包含另一个（多个）JavaBean时，就可以使用mybaitis的collection 标签。

注意： 如果想要展示包含JavaBean的id，这两个JavaBean在建表时候的主键名称要有区别。

Model 1

import lombok.Data;

import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Data
public class TrainDepartment implements Serializable {

    private Long id;
    private String programCode;
    @NotBlank(message = "请添加部门")
    private String department;
    @NotBlank(message = "请添加参加人数")
    private Integer numberPeople;
    
	// 对应collection标签 property
    private List<TrainPeople> peoples;

    private String startingTime;

Model 2

import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

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

@Data
public class TrainPeople implements Serializable {

    private Long peopleId;

    private Long departmentId;

    private String name;

    private Long grade;

    private String certificateNo;

    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date initialReleaseTime;

    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date reviewTime;

    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date certificateValidityPeriod;

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.yankuang.coalquality.account.mapper.TrainDepartmentMapper" >
  <resultMap id="BaseResultMap" type="com.yankuang.coalquality.account.model.TrainDepartment" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="program_code" property="programCode" jdbcType="VARCHAR" />
    <result column="department" property="department" jdbcType="VARCHAR" />
    <result column="number_people" property="numberPeople" jdbcType="INTEGER" />
    <result column="status" property="status" jdbcType="TINYINT" />
    <result column="create_at" property="createAt" jdbcType="TIMESTAMP" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="update_at" property="updateAt" jdbcType="TIMESTAMP" />
    <result column="update_by" property="updateBy" jdbcType="VARCHAR" />
      <collection property="peoples" ofType="com.yankuang.coalquality.account.model.TrainPeople" javaType="java.util.ArrayList">
          <id column="people_id" property="peopleId" jdbcType="BIGINT" />
          <result column="department_id" property="departmentId" jdbcType="BIGINT" />
          <result column="name" property="name" jdbcType="VARCHAR" />
          <result column="people_status" property="peopleStatus" jdbcType="VARCHAR" />
      </collection>
  </resultMap>
  
  <sql id="Base_Column_List" >
    id, program_code, department, number_people, status, create_at, create_by, update_at,
    update_by
  </sql>
   
  <select id="selectList" resultMap="BaseResultMap" parameterType="com.yankuang.coalquality.account.model.TrainDepartment">
          select
          d.id,
          d.program_code,
          d.department,
          d.number_people,
          p.people_id,
          p.department_id,
          p.people_status,
          p.name
          from train_department d
          LEFT JOIN train_program pr ON d.program_code = pr.code and pr.status != 99
          LEFT JOIN train_people p ON d.id = p.department_id and p.status != 99
          <where>
              1=1 and d.status != 99
              <if test="startingTime != null and startingTime !=''">
                  and pr.starting_time LIKE concat(concat('%',#{startingTime}),'%')
              </if>
              <if test="programCode != null and programCode !=''">
                  and d.program_code = #{programCode}
              </if>
          </where>
        ORDER BY d.create_at desc
  </select>
</mapper>

<collection property="peoples" ofType="com.yankuang.coalquality.account.model.TrainPeople" javaType="java.util.ArrayList">
          <id column="people_id" property="peopleId" jdbcType="BIGINT" />
          <result column="department_id" property="departmentId" jdbcType="BIGINT" />
          <result column="name" property="name" jdbcType="VARCHAR" />
          <result column="people_status" property="peopleStatus" jdbcType="VARCHAR" />
      </collection>
  </resultMap>

collection : 一个复杂的类型关联，许多结果将映射为这种类型
property : 这是关联的 JavaBean 中的属性名， 在 TrainDepartment中对应 private List menus;
javaType : property 属性对应的集合类型
ofType : property 集合中的泛型，在 TrainDepartment 中是 TrainPeople

请求结果：