【Mybatis】——resultMap实现主从表递归查询

前提

    最近在重构系统中题库部分，其中涉及到题目的多层的父子结构的题型（类似于树结构），而且其中还有每个题目还涉及到从表的关系。之前从程序还是可以实现递归的方式，还没有直接从sql实现的递归的查询，故特此记录一下。

表结构关系

主表 t_problem_main

从表t_problem_sub

t_problem_main中可能存在类似树结构的递归关系，t_problem_main中任何一条数据与t_problem_sub均是一对多的关系。

resultMap作用

Mybatis中最复杂的元素就是resultMap，主要作用就是映射规则，级联的更新，定制类型转化器，是一个结果集的映射关系。目前版本的mybatis只支持resultMap的查询。

resultMap元素

此处只是简单介绍resultMap元素中涉及的一些基本元素，具体的内容还是大家自己去深入学习。

  <resultMap>
        <constructor> //适用于不存在没有参数的构造方法
            <idArg></idArg>
            <arg></arg>
        </constructor>
        <id/>//这个对象的主键
        <association/>//一对一级联
        <collection/>//一对多级联
        <discriminator>//鉴别器
            <case/>
        </discriminator>
    </resultMap>

业务

提供一个题干id，查询出这个题目的完整信息。此题目可能有多层子题目，而且每个题目下均带有选项内容（对应从表）

实现思路

先查询父节点id，利用<association>元素的一对一级联查询出父题干带的选项内容，其次利用父节点id作为p_id递归查询其下带有的子题详情利用的mybatis中<collection>

具体实现

<!--题干完整的map-->
    <resultMap id="MainQuestionMap" type="com.dmsdbj.itoo.examinationEvaluation.entity.ext.ProblemModel">
        <id property="id" column="mainId"/>
        <result property="pId" column="pMainId"/>
        <association property="problemSubEntityList" column="mainId" select="querySubOption">
            <id property="id" column="psId"/>
            <result property="imageId" column="subImageId"/>
            <result property="thumbId" column="subThumbId"/>
            <result property="imageName" column="subImageName"/>
            <result property="resource" column="subResource"/>
        </association>
        <collection property="problemModelList" column="mainId" select="querySubQuestion"/>
    </resultMap>
    <!--子题的Map-->
    <resultMap id="subQuestionMap" type="com.dmsdbj.itoo.examinationEvaluation.entity.ext.ProblemModel">
        <id column="mainId" property="id"/>
        <association property="problemSubEntityList" column="mainId" select="querySubOption">
            <id property="id" column="psId"/>
            <result property="imageId" column="subImageId"/>
            <result property="thumbId" column="subThumbId"/>
            <result property="imageName" column="subImageName"/>
            <result property="resource" column="subResource"/>
        </association>
        <collection property="problemModelList" column="mainId" select="querySubQuestion"/>
    </resultMap>

    <!--查询父题干的sql-->
    <select id="queryMainQuestion" resultMap="MainQuestionMap">
        SELECT
            pm.id AS mainId,
            question_content AS questionContent,
            answer,
            analysis,
            p_id AS pMainId,
            problem_id AS problemId,
            degree,
            chapter,
            question_code AS questionCode,
            pm.image_name AS imageName,
            pm.image_id AS imageId,
            pm.thumb_id AS thumbId,
            blank_count AS blankCount,
            child_order AS childOrder,
            pm.remark,
            is_effective AS isEffective,
            is_out_of_order AS isOutOfOrder,
            is_alias AS isAlias,
            is_paper AS isPaper,
            pm.resource
        FROM
            t_problem_main pm
        WHERE
            id = #{problemId} and history_flag = 0 and pm.is_delete=0
    </select>

    <!--查询子题干的sql-->
    <select id="querySubQuestion" resultMap="subQuestionMap">
        SELECT
            pm.id AS mainId,
            question_content AS questionContent,
            answer,
            analysis,
            p_id AS pId,
            problem_id AS problemId,
            degree,
            chapter,
            question_code AS questionCode,
            pm.image_name AS imageName,
            pm.image_id AS imageId,
            pm.thumb_id AS thumbId,
            blank_count AS blankCount,
            child_order AS childOrder,
            pm.remark,
            is_effective AS isEffective,
            is_out_of_order AS isOutOfOrder,
            is_alias AS isAlias,
            is_paper AS isPaper,
            pm.resource
        FROM
            t_problem_main pm
        WHERE
            p_id = #{pid} and history_flag = 0 and pm.is_delete=0
        ORDER BY
          child_order
    </select>

    <!--查询选项sql-->
    <select id="querySubOption" resultType="com.dmsdbj.itoo.examinationEvaluation.entity.ProblemSubEntity">
        SELECT
            id,
            option_order AS optionOrder,
            problem_main_id AS problemMainId,
            image_id AS imageId,
            image_name AS imageName,
            resource,
          remark,
          operator
        FROM
            t_problem_sub
        WHERE
            problem_main_id = #{id} and is_delete = 0
        ORDER BY option_order
    </select>

总结

   利用这用方式减少递归查询过程中对数据库的打开和关闭操作，在用户使用上时间得到减少，但是同时也把业务的主要压力放到了数据库所在的服务器上。说真的也不知道这两个因素具体怎么考虑最好，问了leader说暂时也无法判断哪种很好一些，唉，现在是这么做吧，最起码用户感受起来还是稍好一些的。