类中包含自己

@Data
public class SubjectVo {
    private String id;
    private int sort;
    private  String title;
    private List<SubjectVo> children=new ArrayList<>();

}



<resultMap id="nectList2" type="com.atguigu.guli.service.edu.entity.vo.SubjectVo">
        <result column="id" jdbcType="VARCHAR" property="id" />
        <result column="sort" jdbcType="INTEGER" property="sort" />
        <result column="title" jdbcType="VARCHAR" property="title" />

       <collection property="children" column="id"  ofType="com.atguigu.guli.service.edu.entity.vo.SubjectVo"  select="selectnectList2"></collection>
    </resultMap>
    <select id="selectnectList2" resultMap="nectList2">
        select id,sort,title from edu_subject where parent_id=#{id}
    </select>

@Data
public class ChapterVo implements Serializable {

    private static final long serialVersionUID = 1L;

    private String id;
    private String title;
    private Integer sort;
    private List<VideoVo> children = new ArrayList<>();
}


 <resultMap id="nectList2" type="com.atguigu.guli.service.edu.entity.vo.ChapterVo">
        <result column="id" jdbcType="VARCHAR" property="id" />
        <result column="sort" jdbcType="INTEGER" property="sort" />
        <result column="title" jdbcType="VARCHAR" property="title" />

        <collection property="children" column="id"  ofType="com.atguigu.guli.service.edu.entity.vo.VideoVo"  select="selectVideoList"></collection>
    </resultMap>
    <select id="getChapterBycourseId" resultMap="nectList2">
        select id,title,sort from edu_chapter where course_id=#{courseid}
    </select>

类中不包含自己

返回类结构(内部类)

public class testDto{

	private Integer id;
	private String name;
	private List<Child> childList;

	public static class Child{
        private Integer id;
	    private String name;
 }
 
}

resultMap编写

<resultMap id="TestDto" type="com.x.y.testDto">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <association property="childList" column="id" select="queryChildList" javaType="java.util.ArrayList" />
</resultMap>

‘select’为嵌套查询的SQL的ID名称
‘column’为要带入的某列的值

<select id="queryTestDto" resultMap="TestDto">
        SELECT id,name FROM test
</select>

<select id="queryTestDto" resultMap="com.x.y.testDto$Child">
        SELECT id,name FROM test
        WHERE parent_id=#{id}
</select>

MyBatis嵌套查询

​ 在进行查询操作时有时会涉及多个表之间互查，可以根据查询出的某一个结果集里的一些字段作为条件去查询其他表格从而进行多表查询。简化了一些逻辑代码，省去了在service层里进行for循环遍历查询。

业务场景

​ 商城项目的订单列表查询

订单表：goods_order

​ 字段（部分）： order_id(主键) , goods_id（商品id）,uid （用户id）, config_id （商品配置参数id）

商品表：goods_details

​ 字段（部分）：goods_id （商品id主键）, goods_name（商品名称） , goods_introduction （商品简介）

商品配置表：goods_config

​ 字段：config_id(主键)，goods_id(商品id) , goods_config(配置详情)

商品外观: goods_suface

​ 字段：suface_id(主键)，goods_id（商品id）,suface(外观详情)

商品图片：goods_img

​ 字段：goods_id(商品id) ， goods_img（图片地址）

商品价格表：goods_price

​ 字段：price_id(价格) ， goods_id (商品id) ，config_id(配置id) ,suface_id（外观id），invetory(库存)，goods_price(价格)

在订单模块中查询订单表得出一个订单列表，把订单中**商品价格id（price_id）**传入商品模块，让商品模块去根据订单的id以及price主键去查询商品订单所需要显示的一些信息

订单模块：

List<Goods_order> goods_orderList = orderMapper.orderList(uid);

for (Goods_order order:goods_orderList
    ) {
    //这里通过服务地址调用接口
    OrderDetails orderDetails = restTemplate.getForObject(webRequest + "priceTable/" + order.getConfig_id(), OrderDetails.class);
    order.setOrderDetails(orderDetails);
}

商品模块：

​ GoodsMapper.xml

<!--  定义一个resultmap  -->
<resultMap id="goodsOrderDetails" type="com.xqj.mishop.utils.OrderDetails">
    <!--  根据price_id 去查询-->
    <id column="price_id" jdbcType="INTEGER" property="price_id" />  
    <!--  查询出来后的字段，cloumn需与表字段对应，jdbcType数据返回类型，property对应pojo类属性  -->
    <result column="goods_id" jdbcType="INTEGER" property="goods_id" /> 
    <result column="config_id" jdbcType="INTEGER" property="config_id" /> <!-- 同上   -->
    <result column="suface_id" jdbcType="INTEGER" property="suface_id" /><!--  同上  -->
    <result column="invetory" jdbcType="INTEGER" property="invetory" /><!--  同上  -->
    <result column="goods_price" jdbcType="DOUBLE" property="goods_price" /><!-- 同上   -->
    <!--  根据config_id去查询商品配置详情，association用于返回单结果 collection返回一个集合 -->
    <association property="goods_config" column="config_id" select="configValue" javaType="STRING"/>
    <!-- 同上，根据suface_id查询外观详情 -->
    <association property="surface" column="suface_id" select="sufaceValue" javaType="STRING"/>
    <!-- 根据goods_id查询图片地址 -->
    <association property="img" column="goods_id" select="goodsOrderImg" javaType="STRING"/>
    <!-- 根据goods_id 查询商品名 -->
    <association property="goods_name" column="goods_id" select="goodsOrderDetailsName" javaType="STRING"/>
</resultMap>

<!-- resultMap对应着上面写的resultmap的id-->
<select id="goodsOrderListDetails"  resultMap="goodsOrderDetails">
    select * from goods_price where price_id = #{price_id}
</select>
<!-- 下面查询语句的id都分别对应着上面association里的 select -->
<select id="goodsPrice" resultType="Goods_price">
    select * from goods_price where goods_id = #{goods_id}
    <if test="config_id != null">
        and config_id = #{config_id}
    </if>
    <if test="suface_id != null">
        and suface_id = #{suface_id}
    </if>
</select>

 <select id="goodsOrderListDetails"  resultMap="goodsOrderDetails">
     select * from goods_price where price_id = #{price_id}
</select>

<select id="configValue" parameterType="integer" resultType="string">
    select goods_config from goods_config where config_id = #{config_id}
</select>

<select id="sufaceValue" parameterType="integer" resultType="string">
    select surface from goods_suface where suface_id = #{suface_id}
</select>

<select id="goodsOrderImg" parameterType="integer" resultType="string">
    select goods_img img from goods_img where goods_id = #{goods_id} limit 1
</select>

<select id="goodsOrderDetailsName" parameterType="integer" resultType="string">
    select goods_name from goods_details where goods_id = #{goods_id}
</select>

运行项目测试：

这里实际上还是对数据库进行了多次查询，只是遍历查询的工作交给了myabtis去做，不用在service层写for循环遍历了。有利有弊就看自己怎么选择了！