Mybatis集合的嵌套Select查询

需求：

现有一张权益表、一张权益规则表，权益表和权益规则表是一对多的关系，即一个权益下有多条权益规则，
想在查询权益集合的时候同时查询出每个权益下的权益规则集合，即一个多对多的查询。

实现：

采用Mybatis集合的嵌套Select查询方式实现

查询结果javaBean

/**
 * @author chenqi
 * @version V1.0
 * @ClassName: GetEquityListVO
 * @Description: 根据权益分组查询权益集合接口VO
 * @Date 2019/10/19 18:41
 */
@Data
@ApiModel
public class GetEquityListVO implements Serializable {

    private static final long serialVersionUID = -6781779476604755789L;

    @ApiModelProperty(value = "权益id")
    private Long equityId;

    @ApiModelProperty(value = "权益名称")
    private String equityName;

    @ApiModelProperty(value = "权益分组(1xx权益,2xx权益,3xx权益,4xx权益)")
    private Integer equityClassification;

    @ApiModelProperty(value = "权益说明")
    private String equityExplain;

    @ApiModelProperty(value = "权益正式图标")
    private String equityActiveIcon;

    @ApiModelProperty(value = "权益下的规则id集合")
    private List<Long> equityRuleIds;
}

mybatis的xml代码

<resultMap id="GetEquityListResultMap" type="com.xxxx.admin.model.vo.equity.GetEquityListVO">
        <id column="equityId" property="equityId" />
        <result column="equityName" property="equityName" />
        <result column="equityClassification" property="equityClassification" />
        <result column="equityActiveIcon" property="equityActiveIcon" />
        <result column="equityExplain" property="equityExplain" />
        <collection column="equityId" property="equityRuleIds" ofType="java.lang.Long" select="getEquityRuleIds"/>
    </resultMap>
    <select id="getEquityList" parameterType="java.lang.Integer" resultMap="GetEquityListResultMap">
        select
            equity_id as equityId,
            equity_name as equityName,
            equity_classification as equityClassification,
            equity_explain as equityExplain,
            equity_active_icon as equityActiveIcon
          from equity_config
          where flag_del = 0 and state = 0 and equity_classification = #{equityClassification}
            order by sort desc
    </select>
    <select id="getEquityRuleIds" parameterType="java.lang.Long" resultType="java.lang.Long">
        select equity_rule_id from equity_config_rule where flag_del = 0 and state = 0 and equity_id = #{equityId}
    </select>

说明：

getEquityList ：查询权益集合sql
getEquityRuleIds ： 查询权益规则集合的sql
GetEquityListResultMap : 权益集合的resultMap结果映射

所以，核心就是这一行

<collection column="equityId" property="equityRuleIds" ofType="java.lang.Long" select="getEquityRuleIds"/>

更多示例和教程，大家可以参考mybatis官方中文文档进行学习
https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps

如果该文章有帮助到您，就留言点个赞吧！您的支持与肯定是我持续更新最大的动力。