【无标题】

Mybatis在一对多的resultMap返回结果时分页组件失效问题

mybatis在做分页的时候，是对这一整个sql做的分页处理，limit在最后面。就是直接对查询出的数据条数进行limit分页，而不是先把数据装进resultMap映射里，然后再对resultMap条数进行分页，这就导致会出现明明分页条数设为10，但映射到对象时可能就只有3-4条这样，因为分页组件直接对总查询条数进行分页后再映射到resultMap对象里的。

解决方案: 先查询主表,再子查询

因为分页组件是根据主查询的条数进行分页过滤
我的resultMap

<resultMap id="areaMap" type="com.ysz.exam.entity.vo.AreaQueryVO">
        <result column="areaName" property="areaName"/>
        <result column="createTime" property="createTime"/>
        <collection property="cityName" ofType="string" select="getCity" column="{id=id,cityParams=cityParams,addressParams=addressParams}">
        </collection>
        <collection property="address" ofType="string" select="getAddress" column="{id=id,addressParams=addressParams,cityParams=cityParams}">
        </collection>
    </resultMap>

我的主查询跟子查询：

<!--    主查询-->
    <select id="queryByParams" resultMap="areaMap">
        select A.areaName as areaName,
        A.createTime as createTime,
        A.id,

        case
        when '${areaQueryRequest.cityName}' IS NULL then
        NULL
        else
        '${areaQueryRequest.cityName}'
        end as cityParams,
        case
        when '${areaQueryRequest.address}' IS NULL then
        NULL
        else
        '${areaQueryRequest.address}'
        end as addressParams

        from area A   inner join (select C.cityName, C.areaId,D.address
        from city C
        inner join city_address D on C.id = D.cityId) M
        on A.id = M.areaId
        <where>
            <if test="areaQueryRequest.areaName != null and areaQueryRequest.areaName != ''">
                and A.areaName LIKE CONCAT(CONCAT('%', #{areaQueryRequest.areaName}), '%')
            </if>
            <if test="areaQueryRequest.cityName != null and areaQueryRequest.cityName != ''">
                and M.cityName LIKE CONCAT(CONCAT('%', #{areaQueryRequest.cityName}), '%')
            </if>
            <if test="areaQueryRequest.address != null and areaQueryRequest.address != ''">
                and M.address LIKE CONCAT(CONCAT('%', #{areaQueryRequest.address}), '%')
            </if>
            <if test="areaQueryRequest.FromTime != null ">
                and DATE_FORMAT(createTime,'%Y-%m-%d') >= #{areaQueryRequest.FromTime}
            </if>
            <if test="areaQueryRequest.ToTime != null">
                and DATE_FORMAT(createTime,'%Y-%m-%d') &lt;= #{areaQueryRequest.ToTime}
            </if>
        </where>
        group by A.id order by A.id
    </select>
<!--    子查询-->
    <select id="getCity" resultType="String">
        SELECT C.cityName, C.areaId
        FROM city C
        INNER JOIN city_address D ON C.id = D.cityId
        <where>
         C.areaId =#{id}
            <if test="cityParams != null and cityParams != ''">
                and cityName LIKE CONCAT(CONCAT('%', #{cityParams}), '%')
            </if>
            <if test="addressParams != null and addressParams != ''">
                and D.address LIKE CONCAT(CONCAT('%', #{addressParams}), '%')
            </if>
        </where>
        GROUP BY C.cityName, C.areaId order by C.areaId
    </select>
<!--    子查询-->
    <select id="getAddress" resultType="String">
        select D.address, C.cityName, C.areaId
        from city C
        inner join city_address D on C.id = D.cityId
        <where>
            C.areaId =#{id}
            <if test="cityParams != null and cityParams != ''">
                and C.cityName LIKE CONCAT(CONCAT('%', #{cityParams}), '%')
            </if>
            <if test="addressParams != null and addressParams != ''">
                and address LIKE CONCAT(CONCAT('%', #{addressParams}), '%')
            </if>
        </where>
    </select>

说明:

<collection property="address" ofType="string" select="getAddress" column="{id=id,addressParams=addressParams,cityParams=cityParams}">

property：对象中要映射的字段
ofType:映射类型
select:子查询id
column：传递到子查询的参数

这里涉及到两个注意点:

1.是resultMap有两个List集合要子查询映射:

那么我们就要设置两条子查询分别映射，同时在子查询时是根据子查询返回的第一个字段映射到collection标签中，所以就要保证查询出的想要的字段放在第一位。

:2.是怎么把外层接收的参数传递到子查询中:

分两个步骤:
1:把外层接收的参数设为查询的结果字段：

case
        when '${areaQueryRequest.cityName}' IS NULL then
        NULL
        else
        '${areaQueryRequest.cityName}'
        end as cityParams,

2：在collection标签的column属性中，根据主查询的结果集筛选出想要的字段，然后传递给子查询.
合适我们就能在子查询中用到外层的参数了:

<if test="cityParams != null and cityParams != ''">
                and C.cityName LIKE CONCAT(CONCAT('%', #{cityParams}), '%')
            </if>

ok!这只是作为自己的笔记，有大佬感觉哪里写的有问题欢迎指出.