MyBatis级联查询

一对一级联

首先我们看两个实体，国家和城市：

import org.apache.ibatis.type.Alias;

import java.util.Date;

// 当使用包扫描的方式时，使用该注解修改默认（类名第一个字母小写）的别名，可以避免别名重复的情况，建议使用包名+类名
// <package name="com.pengjs.kkb.mybatis.model"/>
@Alias(value = "country")
public class Country {
    private Integer id;

    private String country;

    private Byte status;

    private Date created;

    private Date modified;

    // getter and setter
}

@Alias(value = "city")
public class City {
    private Integer id;

    private Integer countryId;

    private String city;

    private Byte status;

    private Date created;

    private Date modified;

    // getter and setter
}

一个城市对应一个国家，是一对一的关系，一对一关系查询使用association标签。
现在我们要实现查询城市的同时也查询对应的国家，我们封装了这么一个类，CityPlus继承City，并且有一个属性Country：

/**
 * @author: Doooook
 * @date: 2020-06-21 10:08
 */
@EqualsAndHashCode(callSuper = true)
@Data
public class CityPlus extends City implements Serializable {

    private Country country;

}

一对一级联一步查询

看看xml，使用association标签，property="country"需要跟CityPlus对象的country属性对应：

CityMapper.xml

  <resultMap id="cityPlusResultMap" type="com.pengjs.kkb.mybatis.api.bean.CityPlus" extends="BaseResultMap">
    <association property="country" javaType="com.pengjs.kkb.mybatis.model.Country">
      <id column="country_id" property="id"/>
      <result column="country" property="country"/>
      <result column="country_status" property="status"/>
      <result column="country_created" property="created"/>
      <result column="country_modified" property="modified"/>
    </association>
  </resultMap>

  <select id="getCityCountry" resultMap="cityPlusResultMap" parameterType="java.lang.Integer">
    select A.id as id,
       A.city as city,
       A.country_id as country_id,
       A.status as status,
       A.created as created,
       A.modified as modified,
       B.id as country_id,
       B.country as country,
       B.status as country_status,
       B.created as country_created,
       B.modified as country_modified
    from city as A, country as B
    where A.country_id = B.id and A.id = #{id,jdbcType=INTEGER}
  </select>

测试查询：

image.png

一对一级联分步查询（单个入参）

看看xml，使用传参的方式，select配置的是CountryMapper的selectByPrimaryKey方法，入参column为country_id，需要与selectCityPlusByIdUnderStep方法的出参country_id一致：

CityMapper.xml

  <resultMap id="cityPlusResultMapStep" type="com.pengjs.kkb.mybatis.api.bean.CityPlus" extends="BaseResultMap">
    <association property="country"
                 select="com.pengjs.kkb.mybatis.mapper.CountryMapper.selectByPrimaryKey"
                 column="country_id">
    </association>
  </resultMap>

  <select id="selectCityPlusByIdUnderStep" resultMap="cityPlusResultMapStep" parameterType="java.lang.Integer">
    select
        id,
        country_id,
        city,
        status,
        created,
        modified
    from city
        where id = #{id,jdbcType=INTEGER}
  </select>

CountryMapper.xml

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

测试：

image.png

一对一级联分步查询（多个入参）

看看xml：

CityMapper.xml

  <!-- 级联查询传递多个参数 -->
  <resultMap id="cityPlusResultMapStepMultiParams" type="com.pengjs.kkb.mybatis.api.bean.CityPlus" extends="BaseResultMap">
    <association property="country"
                 select="com.pengjs.kkb.mybatis.mapper.CountryMapper.selectCountryByIdAndName"
                 column="{id=country_id,country=country}">
    </association>
  </resultMap>

  <select id="selectCityPlusByIdUnderStepMultiParams" parameterType="java.lang.Integer" resultMap="cityPlusResultMapStepMultiParams">
    select
        id,
        country_id,
        city,
        status,
        created,
        modified,
        "China" as country
    from city
        where id = #{id,jdbcType=INTEGER}
  </select>

CountryMapper.xml

  <select id="selectCountryByIdAndName" resultMap="BaseResultMap">
    select * from country
        where id = #{id} and country=#{country}
  </select>

测试：

image.png

一对多级联查询

CountryPlus.java

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.pengjs.kkb.mybatis.model.City;
import com.pengjs.kkb.mybatis.model.Country;
import lombok.Data;
import lombok.EqualsAndHashCode;

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

/**
 * @author: Jason
 * @date: 2020-06-21 10:08
 */
@EqualsAndHashCode(callSuper = true)
@Data
@JsonIgnoreProperties(value = {"handler"})
public class CountryPlus extends Country implements Serializable {

    private List<City> cityList;

}

一对多单步查询（单个对象）

看看CountrMapper.xml，一对多使用collection标签：

  <resultMap id="countryCityResultMap" type="com.pengjs.kkb.mybatis.api.bean.CountryPlus" extends="BaseResultMap">
    <collection property="cityList" ofType="com.pengjs.kkb.mybatis.model.City">
      <id column="city_id" property="id"/>
      <result column="city" property="city"/>
      <result column="country_id" property="countryId"/>
      <result column="city_status" property="status"/>
      <result column="city_created" property="created"/>
      <result column="city_modified" property="modified"/>
    </collection>
  </resultMap>

  <select id="getCountryCity" resultMap="countryCityResultMap" parameterType="java.lang.Integer">
    select
       A.id as id,
       A.country as country,
       A.status as status,
       A.created as created,
       A.modified as modified,
       B.id as city_id,
       B.city as city,
       B.country_id as country_id,
       B.status as city_status,
       B.created as city_created,
       B.modified as city_modified
    from country as A left join city as B on A.id = B.country_id
        where A.id = #{id,jdbcType=INTEGER}
  </select>

测试：

image.png

一对多单步查询（多个对象）

看看CountryMapper.xml

  <select id="getCountryCityList" resultMap="countryCityResultMap">
    select
       A.id as id,
       A.country as country,
       A.status as status,
       A.created as created,
       A.modified as modified,
       B.id as city_id,
       B.city as city,
       B.country_id as country_id,
       B.status as city_status,
       B.created as city_created,
       B.modified as city_modified
    from country as A left join city as B on A.id = B.country_id
  </select>

  <resultMap id="countryCityResultMap" type="com.pengjs.kkb.mybatis.api.bean.CountryPlus" extends="BaseResultMap">
    <collection property="cityList" ofType="com.pengjs.kkb.mybatis.model.City">
      <id column="city_id" property="id"/>
      <result column="city" property="city"/>
      <result column="country_id" property="countryId"/>
      <result column="city_status" property="status"/>
      <result column="city_created" property="created"/>
      <result column="city_modified" property="modified"/>
    </collection>
  </resultMap>

测试：

image.png

一对多分步查询

看看CountryMapper.xml：

  <select id="getCountryCityMultiSteps" resultMap="countryPlusResultMapStep" parameterType="java.lang.Integer">
    select
       A.id as id,
       A.country,
       A.status,
       A.created,
       A.modified
    from country A
        where A.id = #{id,jdbcType=INTEGER}
  </select>

  <resultMap id="countryPlusResultMapStep" type="com.pengjs.kkb.mybatis.api.bean.CountryPlus" extends="BaseResultMap">
    <!--- fetchType="lazy"使用懒加载 ->
    <collection property="cityList" fetchType="lazy"
                select="com.pengjs.kkb.mybatis.mapper.CityMapper.selectCityByCountryId"
                column="id">
    </collection>
  </resultMap>

image.png

复杂关联关系查询

一对一、一对多混合查询

StudentMapper.xml

  <resultMap id="StudentLectureResultMap" type="com.pengjs.kkb.mybatis.api.bean.StudentLectureScoreBean" extends="BaseResultMap">
    <!-- 使用传参的方式查询另一个SQL -->
    <!-- 一个学生对应一张学生卡 -->
    <association property="studentSelfCard" fetchType="eager" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentSelfCardMapper.findStudentSelfcardByStudentId"/>
<!--    <association property="studentSelfCard" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentSelfCardMapper.findStudentSelfcardByStudentId"/>-->
      <!-- 一个学生对应多条课程记录，一个课程对应一个课程分数 -->
    <collection property="studentLectureBeanList" fetchType="lazy" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentLectureMapper.findStudentLectureByStudentId"/>
<!--    <collection property="studentLectureBeanList" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentLectureMapper.findStudentLectureByStudentId"/>-->
  </resultMap>
  <select id="getStudentLecture" parameterType="int" resultMap="StudentLectureResultMap">
    select * from t_student where id = #{id}
  </select>

鉴别器

<resultMap id="StudentHealthResultMap" type="com.pengjs.kkb.mybatis.api.bean.StudentLectureScoreHealthBean" extends="BaseResultMap">
    <!-- 鉴别器，它是一种可以选择具体实现类（子类）的级联，根据性别返回 -->
    <discriminator javaType="int" column="sex">
      <case value="1" resultMap="maleStudentMap"/>
      <case value="2" resultMap="femaleStudentMap"/>
    </discriminator>
  </resultMap>

  <resultMap id="maleStudentMap" type="com.pengjs.kkb.mybatis.api.bean.MaleStudentBean" extends="BaseResultMap">
    <!-- 使用传参的方式查询另一个SQL -->
    <!-- 一个学生对应一张学生卡 -->
    <association property="studentSelfCard" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentSelfCardMapper.findStudentSelfcardByStudentId"/>
    <!-- 一个学生对应多条课程记录，一个课程对应一个课程分数 -->
    <collection property="studentLectureBeanList" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentLectureMapper.findStudentLectureByStudentId"/>
    <collection property="studentHealthMaleList" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentHealthMaleMapper.findStudentHealthMaleByStuId"/>
  </resultMap>

  <resultMap id="femaleStudentMap" type="com.pengjs.kkb.mybatis.api.bean.FemaleStudentBean" extends="BaseResultMap">
    <!-- 使用传参的方式查询另一个SQL -->
    <!-- 一个学生对应一张学生卡 -->
    <association property="studentSelfCard" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentSelfCardMapper.findStudentSelfcardByStudentId"/>
    <!-- 一个学生对应多条课程记录，一个课程对应一个课程分数 -->
    <collection property="studentLectureBeanList" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentLectureMapper.findStudentLectureByStudentId"/>
    <collection property="studentHealthFemaleList" column="id" select="com.pengjs.kkb.mybatis.mapper.StudentHealthFemaleMapper.findStudentHealthFemaleByStuId"/>
  </resultMap>

  <select id="getStudentHealth" parameterType="int" resultMap="StudentHealthResultMap">
    select * from t_student where id = #{id}
  </select>

StudentLectureScoreHealthBean

@EqualsAndHashCode(callSuper = true)
@Data
@JsonIgnoreProperties(value = { "handler" })
public class StudentLectureScoreHealthBean extends Student implements Serializable {

    private static final long serialVersionUID = 152987146649893684L;
    private StudentSelfCard studentSelfCard;
    private List<StudentLectureBean> studentLectureBeanList;

}

MaleStudentBean

/**
 * @author: Jason
 * @date: 2020-06-26 18:59
 */
@Data
public class MaleStudentBean extends StudentLectureScoreHealthBean implements Serializable {

    private StudentSelfCard studentSelfCard;
    private List<StudentLectureBean> studentLectureBeanList;
    private List<StudentHealthMale> studentHealthMaleList;

}

FemaleStudentBean

@Data
public class FemaleStudentBean extends StudentLectureScoreHealthBean implements Serializable {

    private StudentSelfCard studentSelfCard;
    private List<StudentLectureBean> studentLectureBeanList;
    private List<StudentHealthFemale> studentHealthFemaleList;

}

MyBatis懒加载

StudentController.java

import com.nrsc.mybatis.service.StudentService;
import com.nrsc.mybatis.vo.StudentSelfcardBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @Author: Jason
 * @Description: Created by Jason in 14:43 2019/5/12
 * @Modified By:
 */
@RequestMapping(value = "/student")
@RestController
public class StudentController {

    @Autowired
    private StudentService studentService;

    @GetMapping(value = "/findStudentCard/{id}", name = "根据学生ID查询学生及其学生卡信息")
    @ResponseBody
    public StudentSelfcardBean findStudentCardService(@PathVariable(value = "id") int id) {
        return studentService.findStudentCard(id);
    }

    /**
     * 只有查询结果是List结构的才会懒加载，如果是一对一的就一个对象结果就直接一次查询出来了
     * @return
     */
    @GetMapping(value = "/findStudentCardList", name = "根据学生ID查询学生及其学生卡信息List")
    @ResponseBody
    public List<StudentSelfcardBean> findStudentCardList() {
        return studentService.findStudentCardList();
    }

}

StudentServiceImplr.java

import com.nrsc.mybatis.domian.StudentSelfCard;
import com.nrsc.mybatis.mapper.StudentMapper;
import com.nrsc.mybatis.service.StudentService;
import com.nrsc.mybatis.vo.StudentSelfcardBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: Jason
 * @date: 2020-06-27 09:32
 */
@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Override
    public StudentSelfcardBean findStudentCard(int id) {
        StudentSelfcardBean studentCard = studentMapper.findStudentCard(id);
        StudentSelfCard studentSelfCard = studentCard.getStudentSelfCard();
        return studentCard;
    }

    @Override
    public List<StudentSelfcardBean> findStudentCardList() {
        List<StudentSelfcardBean> studentSelfcardBeanList = studentMapper.findStudentCardList();
        StudentSelfCard studentSelfCard = studentSelfcardBeanList.get(0).getStudentSelfCard();
        return studentSelfcardBeanList;
    }

}

StudentMapper.xml

  <resultMap id="StudentCardResultMap" type="com.nrsc.mybatis.vo.StudentSelfcardBean" extends="BaseResultMap">
    <!-- 可以直接extends="BaseResultMap" -->
    <!--<id column="id" property="id" jdbcType="INTEGER" />
    <result column="cnname" property="cnname" jdbcType="VARCHAR" />
    <result column="sex" property="sex" jdbcType="TINYINT" />
    <result column="selfcard_no" property="selfcardNo" jdbcType="INTEGER" />
    <result column="note" property="note" jdbcType="VARCHAR" />
    <result column="status" property="status" jdbcType="TINYINT" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />
    <result column="modified" property="modified" jdbcType="TIMESTAMP" />-->
    <!-- 使用传参的方式查询另一个SQL -->
    <association property="studentSelfCard"
                 column="id"
                 fetchType="lazy"
                 javaType="com.nrsc.mybatis.domian.StudentSelfCard"
                 select="com.nrsc.mybatis.mapper.StudentSelfCardMapper.findStudentSelfcardByStudentId"/>
  </resultMap>

  <select id="findStudentCard" parameterType="int" resultMap="StudentCardResultMap">
    select * from t_student where id = #{id}
  </select>

  <select id="findStudentCardList" parameterType="int" resultMap="StudentCardResultMap">
    select * from t_student where status = 0
  </select>