Mybatis使用collection映射一对多查询分页问题详解_Java

mybatis使用collection映射一对多查询分页

场景

页面展示列表，需要关联查询另外1张表多的字段，分页。

/**
     * 标签
     */
    private list<basicresidenttags> tags;

@data
@tablename("basic_resident_tags")
public class basicresidenttags{

    private static final long serialversionuid=1l;

    /**
     * 标签id
     */
    @tableid(value = "id",type = idtype.auto)
    private integer id;
    /**
     * 名称
     */
    private string name;
    /**
     * 颜色
     */
    private string color;
    /**
     * 居民id
     */
    private integer residentid;

}

原来的sql这样写

<!--一对多映射-->
    <resultmap id="many" type="com.vkl.basic.domain.vo.admin.basicresidentlistvo">
        <id property="residentid" column="resident_id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="householder" column="house_holder"/>
        <result property="residentname" column="resident_name"/>
        <result property="outsider" column="outsider"/>
        <result property="room" column="room"/>
        <collection property="tags" oftype="com.vkl.basic.domain.basicresidenttags">
            <id column="tagsid" property="id"></id>
            <result column="tagsname" property="name"></result>
            <result column="color" property="color"></result>
            <result column="tid" property="residentid"></result>
        </collection>
    </resultmap>
<select id="selectpagelist" resulttype="com.vkl.basic.domain.vo.admin.basicresidentlistvo" resultmap="many"
            parametertype="com.vkl.basic.domain.bo.admin.basicresidentadminbo">
        select b.resident_id,b.`name`,b.sex,b.house_holder,b.resident_name,b.outsider,b.room,
        t.id as tagsid,t.`name` as tagsname,t.color,t.resident_id as tid
        from basic_resident b left join basic_resident_tags t
        on b.resident_id = t.resident_id
        where del_flg = '0'
        <if test="param.name != null and param.name != ''">
            and b.name like concat('%',#{param.name},'%')
            or
            b.resident_name like concat('%',#{param.name},'%')
        </if>
        <if test="param.tags != null and param.tags != ''">
            and t.name = #{param.tags}
        </if>
        order by b.resident_id
        limit #{query.pagenum},#{query.pagesize}
    </select>

正常查询tags有两条

加上分页条件，多的一端只有一条数据。

修改之后的sql

分页满足正常展示多的一端。

<!--一对多映射-->
    <resultmap id="many" type="com.vkl.basic.domain.vo.admin.basicresidentlistvo">
        <id property="residentid" column="resident_id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="householder" column="house_holder"/>
        <result property="residentname" column="resident_name"/>
        <result property="outsider" column="outsider"/>
        <result property="room" column="room"/>
        <collection property="tags" oftype="com.vkl.basic.domain.basicresidenttags"
                    column="tid" select="selecttagsbyresidentid">
        </collection>
    </resultmap>
    
    <!--主查询条件-->
    <select id="selectpagelist" resulttype="com.vkl.basic.domain.vo.admin.basicresidentlistvo" resultmap="many"
            parametertype="com.vkl.basic.domain.bo.admin.basicresidentadminbo">
        select b.resident_id,b.`name`,b.sex,b.house_holder,b.resident_name,b.outsider,b.room,
        t.id as tagsid,t.`name` as tagsname,t.color,t.resident_id as tid
        from basic_resident b left join basic_resident_tags t
        on b.resident_id = t.resident_id
        where del_flg = '0'
        <if test="param.name != null and param.name != ''">
            and b.name like concat('%',#{param.name},'%')
            or
            b.resident_name like concat('%',#{param.name},'%')
        </if>
        <if test="param.tags != null and param.tags != ''">
            and t.name = #{param.tags}
        </if>
        order by b.resident_id
        limit #{query.pagenum},#{query.pagesize}
    </select>
    
    <!--子查询-->
    <select id="selecttagsbyresidentid" resulttype="com.vkl.basic.domain.basicresidenttags">
        select * from basic_resident_tags where resident_id=#{tid}
    </select>