当前位置：首页 > news >正文

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

news 2025/7/20 9:53:02

场景：页面展示列表，需要查询多的字段，和一的字段。并且还要分页。
这时候直接想到的是手写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 tidfrom basic_resident b LEFT JOIN basic_resident_tags ton b.resident_id = t.resident_idwhere del_flg = '0'<if test="param.name != null and param.name != ''">and b.name like concat('%',#{param.name},'%')orb.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_idlimit #{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 tidfrom basic_resident b LEFT JOIN basic_resident_tags ton b.resident_id = t.resident_idwhere del_flg = '0'<if test="param.name != null and param.name != ''">and b.name like concat('%',#{param.name},'%')orb.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_idlimit #{query.pageNum},#{query.pageSize}</select><!--子查询--><select id="selectTagsByResidentId" resultType="com.vkl.basic.domain.BasicResidentTags">select * from basic_resident_tags where resident_id=#{tid}</select>