快捷導(dǎo)航

Mybatis使用collection映射一對多查詢分頁問題詳解

更新時間：2025年12月04日 09:20:17 作者：小柒v

本文通過修改SQL查詢語句,解決了在分頁條件下關(guān)聯(lián)查詢多表時,多的一端數(shù)據(jù)不正確的問題,從而實現(xiàn)了正常的展示

Mybatis使用collection映射一對多查詢分頁

場景

頁面展示列表，需要關(guān)聯(lián)查詢另外1張表多的字段，分頁。

/**
     * 標(biāo)簽
     */
    private List<BasicResidentTags> tags;

@Data
@TableName("basic_resident_tags")
public class BasicResidentTags{

    private static final long serialVersionUID=1L;

    /**
     * 標(biāo)簽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有兩條

加上分頁條件，多的一端只有一條數(shù)據(jù)。

修改之后的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>