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

冷不防 2023-10-10 14:43 4阅读 0赞

![6e67e29b7b224ec1826069dbe445acc8.png][]

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

三层collection嵌套如下：

第一层：

<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>

第二，三层：

<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

<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**

**方法二**

**三层嵌套，一对多对多；**

**实体类**

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>

[6e67e29b7b224ec1826069dbe445acc8.png]: https://img-blog.csdnimg.cn/6e67e29b7b224ec1826069dbe445acc8.png