mybatis多层嵌套查询(多对多)

依赖:

			<dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.4.3</version>
            </dependency>

实体类Setmeal:

@Data
@TableName("t_setmeal")
public class Setmeal implements Serializable {
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String name;
    private String code;
    private String helpCode;
    private String sex;//套餐适用性别：0不限 1男 2女
    private String age;//套餐适用年龄
    private Float price;//套餐价格
    private String remark;
    private String attention;
    private String img;//套餐对应图片存储路径
    @TableField(exist = false)
    private List<CheckGroup> checkGroups;//体检套餐对应的检查组，多对多关系
}

实体类CheckGroup:

@Data
@TableName("t_checkgroup")
public class CheckGroup {
    @TableId(type = IdType.AUTO)
    private Integer id;//主键
    private String code;//编码
    private String name;//名称
    private String helpCode;//助记
    private String sex;//适用性别
    private String remark;//介绍
    private String attention;//注意事项
    @TableField(exist = false)
    private List<CheckItem> checkItems;//一个检查组合包含多个检查项
}

实体类CheckItem:

@Data
@TableName("t_checkitem")
public class CheckItem {
    @TableId(type = IdType.AUTO)
    private Integer id;//主键
    private String code;//项目编码
    private String name;//项目名称
    private String sex;//适用性别
    private String age;//适用年龄（范围），例如：20-50
    private Float price;//价格
    private String type;//检查项类型，分为检查和检验两种类型
    private String remark;//项目说明
    private String attention;//注意事项
}

中间表t_setmeal_checkgroup

中间表t_checkgroup_checkitem

可以看出Setmeal里面包含多个CheckGroup，而CheckGroup包括多个CheckItem

mapper层

• CheckItemMapper

/**
     * 根据检查组得到检查项
     * @param checkgroupId
     * @return
     */
    List<CheckItem> findCheckItemById(@Param("checkgroupId") Integer checkgroupId);

CheckItemMapper.xml

<!--根据检查组id查询检查项信息-->
    <select id="findCheckItemById" resultType="com.zhubayi.common.pojo.CheckItem">
        select * from t_checkitem
        where id
        in (select checkitem_id from t_checkgroup_checkitem where checkgroup_id=#{checkgroupId})
    </select>

• CheckGroupMapper

/**
     * 根据体验套餐的id得到检查项的分组
     * @param setmealId
     * @return
     */
    List<CheckGroup> findCheckGroupBySetmealId(@Param("setmealId") Integer setmealId);

CheckGroupMapper.xml

    <resultMap type="com.zhubayi.common.pojo.CheckGroup" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="help_code" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
    </resultMap>
    
	<resultMap type="com.zhubayi.common.pojo.CheckGroup"
               id="findByIdResultMap"
               extends="baseResultMap">
        <collection property="checkItems"
                    javaType="ArrayList"
                    ofType="com.zhubayi.common.pojo.CheckItem"
                    column="id"
                    select="com.zhubayi.provider.mapper.CheckItemMapper.findCheckItemById">
        </collection>
    </resultMap>
    <!--根据套餐id查询检查项信息-->
    <select id="findCheckGroupBySetmealId" resultMap="findByIdResultMap">
        select * from t_checkgroup
        where id
                  in (select checkgroup_id from t_setmeal_checkgroup where setmeal_id=#{id})
    </select>

column="id"应该是把CheckGroup的id当作参数传给findCheckGroupBySetmealId

• SetmealMapper

/**
     * 根据id查询套餐信息
     * @param id
     * @return
     */
    Setmeal findById(@Param("id") int id);

SetmealMapper.xml

    <resultMap type="com.zhubayi.common.pojo.Setmeal" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="code" property="code"/>
        <result column="help_code" property="helpCode"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <result column="price" property="price"/>
        <result column="remark" property="remark"/>
        <result column="attention" property="attention"/>
        <result column="img" property="img"/>
    </resultMap>
    <!--column="id"应该就是t_setmeal的id，然后传过去-->
    <resultMap type="com.zhubayi.common.pojo.Setmeal"
               id="findByIdResultMap"
               extends="baseResultMap">
        <collection property="checkGroups"
                    javaType="ArrayList"
                    ofType="com.zhubayi.common.pojo.CheckGroup"
                    column="id"
                    select="com.zhubayi.provider.mapper.CheckGroupMapper.findCheckGroupBySetmealId">
        </collection>
    </resultMap>
    <select id="findById" resultMap="findByIdResultMap">
        select * from t_setmeal  where id=#{id}
    </select>

测试

一个setmeal里面有多个checkGroup，checkGroup里面有多个checkItems