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

场景：页面展示列表，需要查询多的字段，和一的字段。并且还要分页。
这时候直接想到的是手写sql。

/**
     * 标签
     */
    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有两条。加上分页条件，多的一端只有一条数据。

修改之后的sq。满足分页正常展示多的一端。文章来源地址https://www.toymoban.com/news/detail-614721.html

<!--一对多映射-->
    <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>

到了这里，关于Mybatis使用collection映射一对多查询分页问题的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！