tk.mybatis分页插件PageHelper多表嵌套关联查询分页出错问题解决

其实Mybatis-PageHelper插件官方文档上已经有说明不支持嵌套结果的映射。但是可以采用子查询的方式间接实现支持。

修改前：

    <resultMap type="com.ruoyi.system.domain.SysProcessDef" id="SysProcessDefResult">
        <result property="defId"    column="def_id"    />
        <result property="defName"    column="def_name"    />
        <result property="defType"    column="def_type"    />
        <result property="typeValue"    column="type_value"    />
        <result property="applicantIdentity"    column="applicant_identity"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <collection  property="processNodeDefList"   javaType="java.util.List"        resultMap="SysProcessNodeDefResult" />
    </resultMap>

    <sql id="selectSysProcessDefVo">
        SELECT
            p.def_id,
            p.def_name,
            p.def_type,
            p.type_value,
            p.applicant_identity,
            p.del_flag,
            p.create_by,
            p.create_time,
            p.update_by,
            p.update_time,
            p.remark,
            n.node_id,
            n.order_num,
            n.approver,
            n.cc
        FROM
            sys_process_def p
        LEFT JOIN sys_process_node_def n ON n.process_def_id = p.def_id
    </sql>

    <select id="selectSysProcessDefList" parameterType="com.ruoyi.system.domain.SysProcessDef" resultMap="SysProcessDefResult">
        <include refid="selectSysProcessDefVo"/>
        <where>  
            <if test="defName != null  and defName != ''"> and p.def_name like concat('%', #{defName}, '%')</if>
            <if test="delFlag != null  and delFlag != ''"> and p.del_flag = #{delFlag}</if>
            <if test="defType != null  and defType != ''"> and p.def_type = #{defType}</if>
            <if test="typeValue != null  and typeValue != ''"> and p.type_value = #{typeValue}</if>
            <if test="applicantIdentity != null  and applicantIdentity != ''"> and FIND_IN_SET( #{applicantIdentity}, applicant_identity ) </if>
        </where>
    </select>

上面这种关联查询会导致分页插件总页码和预期的不一致。

修改后：

    <resultMap type="com.ruoyi.system.domain.SysProcessDef" id="SysProcessDefResult">
        <result property="defId"    column="def_id"    />
        <result property="defName"    column="def_name"    />
        <result property="defType"    column="def_type"    />
        <result property="typeValue"    column="type_value"    />
        <result property="applicantIdentity"    column="applicant_identity"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
        <collection  property="processNodeDefList" ofType="com.ruoyi.system.domain.SysProcessNodeDef"
                     select="selectSysProcessNodeDefByDefId" column="def_id" />
    </resultMap>

     <sql id="selectSysProcessDefVo">
        SELECT
            p.def_id,
            p.def_name,
            p.def_type,
            p.type_value,
            p.applicant_identity,
            p.del_flag,
            p.create_by,
            p.create_time,
            p.update_by,
            p.update_time,
            p.remark
        FROM
            sys_process_def p
    </sql>

    <select id="selectSysProcessNodeDefByDefId" resultMap="SysProcessNodeDefResult">
        SELECT
            node_id,
            process_def_id,
            order_num,
            approver,
            cc,
            create_by,
            create_time,
            update_by,
            update_time,
            remark
        FROM
            sys_process_node_def
        where process_def_id = #{defId}
    </select>

    <resultMap type="com.ruoyi.system.domain.SysProcessNodeDef" id="SysProcessNodeDefResult">
        <result property="nodeId"    column="node_id"    />
        <result property="processDefId"    column="process_def_id"    />
        <result property="orderNum"    column="order_num"    />
        <result property="approver"    column="approver"    />
        <result property="cc"    column="cc"    />
    </resultMap>

上面将selectSysProcessDefVo修改为单表查询，返回结果collection指向一个子查询selectSysProcessNodeDefByDefId，column="def_id"指定了子查询的参数列。