今天在和前端联调的时候,突然被告知接口分页有问题,不准确,随后debug发现的确如此:
xml代码:
<resultMap id="getRoleInfoMap" type="com.lylb.casecenter.entity.vo.RoleVO">
<id column="roleId" property="id"/>
<result column="roleName" property="name"/>
<collection property="permissionList" ofType="com.lylb.casecenter.entity.vo.PermissionVO">
<id column="permissionId" property="permissionId"/>
<result column="name" property="name"/>
</collection>
</resultMap>
<select id="getRoleList" resultMap="getRoleInfoMap">
SELECT
r.id roleId,
r.NAME roleName,
p.id permissionId,
p.name
FROM
role_permission rp
LEFT JOIN role r ON rp.role_id = r.id
LEFT JOIN permission p ON rp.permission_id = p.id
WHERE
r.`status` = '0'
AND r.deleted = '0'
</select>
随后查看MyBatis Plus 官方文档发现:
而我这里恰恰如此,left join的表 并没有参与where条件过滤。
解决方案:
将关联查询分开查询,将结果合并展示即可
更改之后代码
<resultMap id="getRoleInfoMap" type="com.lylb.casecenter.entity.vo.RoleVO">
<id column="roleId" property="id"/>
<result column="roleName" property="name"/>
<collection property="permissionList" ofType="com.lylb.casecenter.entity.vo.PermissionVO"
javaType="java.util.List" select="getPermission" column="roleId">
</collection>
</resultMap>
<resultMap id="getPermissionMap" type="com.lylb.casecenter.entity.vo.PermissionVO">
<id column="permissionId" property="permissionId"/>
<result column="name" property="name"/>
</resultMap>
<select id="getRoleList" resultMap="getRoleInfoMap">
SELECT
r.id roleId,
r.NAME roleName
FROM role r
WHERE r.`status` = '0' AND r.deleted = '0'
</select>
<select id="getPermission" resultType="com.lylb.casecenter.entity.vo.PermissionVO">
select
rp.role_id roleId,
p.id permissionId,
p.name
from role_permission rp
LEFT JOIN permission p ON rp.permission_id = p.id
where rp.role_id = #{roleId}
</select>