mpper ，嵌套查询 ，关联下一个查询的值当作条件，三层collection嵌套，一对多对多。

关联下一个查询的值当作条件

三层collection嵌套如下：

第一层：

<!--    生成政策指标表格DTO类-->
    <resultMap id="ICBmEvaluationTableDTOResult" type="com.ICBmEvaluationTableDTO">
        <result property="titleName" column="TITLE_NAME" />
        <result property="titleId" column="TITLE_ID" />
        <result property="number" column="NUMBER" />
        <result property="reviewIndications" column="REVIEW_INDICATIONS" />
        <result property="grade" column="GRADE" />
        <result property="optionType" column="OPTION_TYPE" />
        <result property="id" column="ID_" />
        <collection property="icEdPolicyOptionList" column="ID_" ofType="com.ICEdPolicyOption"
        select="queryIcEdPolicyOptionList" />
    </resultMap>

第二，三层：

<!--    生成政策指标表格DTO类的第二个嵌套集合-->
    <resultMap id="icEdPolicyOptionListResult" type="com.ICEdPolicyOption">
        <id property="id_" column="ID_"  />
        <result property="isdeleted" column="ISDELETED" />
        <result property="oper" column="OPER"  />
        <result property="optime" column="OPTIME" />
        <result property="updator" column="UPDATOR"  />
        <result property="updatetime" column="UPDATETIME"  />
        <result property="tenantid" column="TENANTID"  />
        <result property="numberId" column="NUMBER_ID" />
        <result property="optionId" column="OPTION_ID" />
        <result property="checkBox" column="CHECK_BOX" />
        <result property="choice" column="CHOICE" />
        <collection property="icEdPolicyOptionContents" ofType="com.ICEdPolicyOptionContent">
            <id property="id_" column="ID_"  />
            <result property="isdeleted" column="ISDELETED" />
            <result property="oper" column="OPER"  />
            <result property="optime" column="OPTIME" />
            <result property="updator" column="UPDATOR"  />
            <result property="updatetime" column="UPDATETIME"  />
            <result property="tenantid" column="TENANTID"  />
            <result property="optionId" column="OPTION_ID" />
            <result property="optionContentId" column="OPTION_CONTENT_ID" />
            <result property="optionContent" column="OPTION_CONTENT" />
        </collection>
    </resultMap>

第一个select

<!--    通过三级标题查询政策表格    List<ICBmEvaluationTableDTO> queryAllByReviewContentIdAndId_(String titleId);-->
    <select id="queryAllByReviewContentIdAndId_" resultMap="ICBmEvaluationTableDTOResult">
        select
        a.TITLE_NAME, a.TITLE_ID, b.NUMBER, b.REVIEW_INDICATIONS, b.GRADE, b.OPTION_TYPE, b.ID_
        from
        ic_ed_evidence_document_structure a left join ic_ed_policy_content b ON a.TITLE_ID = b.REVIEW_CONTENT_ID
        <where>
            a.ISDELETED = '0' and b.ISDELETED = '0'
            <if test="titleId != null and titleId != ''">and a.TITLE_ID = #{titleId, jdbcType=VARCHAR}</if>
            <if test="evidenceDocumentId != null and evidenceDocumentId != ''" >and b.EVIDENCE_DOCUMENT_ID = #{evidenceDocumentId, jdbcType=VARCHAR}</if>
        </where>
        order by
        cast(substring_index(b.NUMBER,'.',1) as UNSIGNED) asc,
        cast(substring_index(substring_index(b.NUMBER,'.',2),'.',-1) as UNSIGNED) asc,
        cast(substring_index(substring_index(b.NUMBER,'.',3),'.',-1) as UNSIGNED) asc,
        cast(substring_index(b.NUMBER,'.',-1) AS UNSIGNED) asc
    </select>

第一层中的

select="queryIcEdPolicyOptionList"以column="ID_为条件，查询

<!--    第二个集合嵌套的查询-->
    <select id="queryIcEdPolicyOptionList" parameterType="java.lang.Integer" resultMap="icEdPolicyOptionListResult">
        select
        c.ID_, c.ISDELETED,
        c.OPER, c.OPTIME, c.UPDATOR, c.UPDATETIME, c.TENANTID, c.NUMBER_ID, c.OPTION_ID, c.CHECK_BOX, c.CHOICE,
        d.ID_, d.ISDELETED, d.OPER, d.OPTIME, d.UPDATOR, d.UPDATETIME, d.TENANTID, d.OPTION_ID, d.OPTION_CONTENT_ID,
        d.OPTION_CONTENT
        from
        ic_ed_policy_option c left join ic_ed_policy_option_content d ON c.ID_ = d.OPTION_ID
        <where>
            c.ISDELETED = '0' and d.ISDELETED = '0'
            <if test="NUMBER != null and NUMBER != ''">and c.NUMBER_ID = #{ID_, jdbcType=INTEGER}</if>
        </where>
        order by
        cast(substring_index(c.OPTION_ID,'.',1) as UNSIGNED) asc,
        cast(substring_index(substring_index(c.OPTION_ID,'.',2),'.',-1) as UNSIGNED) asc,
        cast(substring_index(substring_index(c.OPTION_ID,'.',3),'.',-1) as UNSIGNED) asc,
        cast(substring_index(substring_index(c.OPTION_ID,'-',1),'.',-1) as UNSIGNED) asc,
        cast(substring_index(c.OPTION_ID,'-',-1) AS UNSIGNED) asc
    </select>

更新10.14-2022

方法二

<resultMap id="SectionResultMap" type="com.iwiti.ghakb.domain.GhaKbSection" >
        <id property="id" column="s_id" jdbcType="INTEGER" />
        <result property="sectionName" column="SECTION_NAME" jdbcType="VARCHAR"/>
        <result property="sectionCode" column="SECTION_CODE" jdbcType="VARCHAR"/>
        <result property="sectionType" column="SECTION_TYPE" jdbcType="CHAR"/>
        <result property="titleId" column="TITLE_ID" jdbcType="INTEGER"/>
        <result property="description" column="DESCRIPTION" jdbcType="VARCHAR" />
        <collection property="entryList" ofType="com.iwiti.ghakb.domain.GhaKbEntry" resultMap="EntryResultMap" />
    </resultMap>

    <resultMap id="EntryResultMap" type="com.iwiti.ghakb.domain.GhaKbEntry">
        <id property="id" column="e_id" jdbcType="INTEGER" />
        <result property="entryName" column="ENTRY_NAME" jdbcType="VARCHAR"/>
        <result property="entryCode" column="ENTRY_CODE" jdbcType="VARCHAR"/>
        <result property="sectionId" column="SECTION_ID" jdbcType="INTEGER"/>
        <collection property="monitorMetricsList" ofType="com.iwiti.ghakb.domain.GhaKbMonitorMetrics" resultMap="MonitorMetricsResultMap" />
    </resultMap>

<resultMap id="MonitorMetricsResultMap" type="com.iwiti.ghakb.domain.GhaKbMonitorMetrics">
        <id property="id" column="m_id" jdbcType="INTEGER" />
        <result property="metricsProperty" column="METRICS_PROPERTY" jdbcType="VARCHAR"/>
        <result property="measurement" column="MEASUREMENT" jdbcType="CHAR"/>
        <result property="definition" column="DEFINITION" jdbcType="VARCHAR"/>
        <result property="calculationMethod" column="CALCULATION_METHOD" jdbcType="VARCHAR"/>
        <result property="guide" column="GUIDE" jdbcType="CHAR"/>
        <result property="metricsExplain" column="METRICS_EXPLAIN" jdbcType="VARCHAR"/>
        <result property="entryId" column="ENTRY_ID" jdbcType="INTEGER"/>
    </resultMap>

三层嵌套，一对多对多；

实体类

public class GhaKbSection implements Serializable {
    private List<GhaKbEntry> entryList;

    private static final long serialVersionUID = 1L;


}

public class GhaKbEntry implements Serializable {
    private List<GhaKbMonitorMetrics> monitorMetricsList;
}

public class GhaKbMonitorMetrics implements Serializable {

}

sql:

   <select id="list" resultType="com.iwiti.ghakb.domain.GhaKbSection" resultMap="SectionResultMap">
        select s.section_name, s.section_code, s.section_type, s.title_id, s.description, s.id_ as s_id,
               e.entry_name, e.entry_code, e.section_id,e.id_ as e_id,
               m.METRICS_PROPERTY, m.MEASUREMENT, m.DEFINITION, m.CALCULATION_METHOD, m.GUIDE, m.METRICS_EXPLAIN, m.id_ as m_id
        from gha_kb_title t right join gha_kb_section s on t.id_ = s.title_id
                            left join gha_kb_entry e on s.ID_ = e.SECTION_ID
                            left join gha_kb_monitor_metrics m on e.ID_ = m.ENTRY_ID
        <where>
            <if test="titleId != null">
                t.id_ = #{titleId}
            </if>
        </where>
    </select>