Mybatis集合的嵌套Select查询

约定不等于承诺〃 2023-06-09 12:23 25阅读 0赞

## 需求： ##

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

## 实现： ##

采用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结果映射

所以，核心就是这一行

更多示例和教程，大家可以参考mybatis官方中文文档进行学习  
[https://mybatis.org/mybatis-3/zh/sqlmap-xml.html\#Result\_Maps][https_mybatis.org_mybatis-3_zh_sqlmap-xml.html_Result_Maps]

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

[https_mybatis.org_mybatis-3_zh_sqlmap-xml.html_Result_Maps]: https://mybatis.org/mybatis-3/zh/sqlmap-xml.html#Result_Maps