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

小柒v

已于 2023-07-31 08:45:06 修改

阅读量2.3k

点赞数 2

文章标签： mybatis 数据库

于 2023-07-28 11:32:10 首次发布

本文链接：https://blog.csdn.net/qq_44982110/article/details/131976265

版权

场景：页面展示列表，需要查询多的字段，和一的字段。并且还要分页。
这时候直接想到的是手写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。满足分页正常展示多的一端。

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