2.mybatis-plus 多表关联条件分页查询 分页数bug解决

背景：
之前写的博客中 遗留了一个todo，今天来补上这个bug

https://blog.csdn.net/huangchong0107/article/details/127427061

1.controller层

    /**
     * 多表关联分页 条件查询
     * @param dto
     * @return IPage<UserVO>
     */
    @PostMapping("/userInfoPage")
    public IPage<UserVO> findByPage(@RequestBody UserInfoPageDTO dto) {
        return userService.findByPage(dto);
    }

其中参数 和 返回值类如下：

@Data
@EqualsAndHashCode
public class UserInfoPageDTO extends PageQuery {

    @ApiModelProperty("用户名")
    private String userName;

    private Integer sex;

    @ApiModelProperty("邮箱")
    private String email;

    @ApiModelProperty("手机号")
    private String phone;
}

@Data
public class UserVO implements Serializable {

    @ApiModelProperty("用户主表id")
    private Long id;

    @ApiModelProperty("用户名")
    private String userName;

    private Integer sex;

    @ApiModelProperty("邮箱")
    private String email;

    @ApiModelProperty("手机号")
    private String phone;

    @ApiModelProperty("密码")
    private String password;

    private Integer isDelete;

    private Date createTime;

    private Date updateTime;
    @ApiModelProperty("用户对应的明细集合")
    private List<UserDetailVO> details;

}

2.service层及其实现层

public interface IUserService extends IService<User> {

    IPage<UserVO> findByPage(UserInfoPageDTO dto);
}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public IPage<UserVO> findByPage(UserInfoPageDTO dto) {
        Page<UserVO> page = new Page<>(dto.currentPage, dto.pageSize);
        IPage<UserVO> queryVoPage = userMapper.findByPage(page, dto);
        return queryVoPage;
    }
}

3.mapper层

@Mapper
public interface UserMapper extends BaseMapper<User> {
    /**
     * 条件查询分页
     * @param page
     * @param dto
     * @return
     */
    IPage<UserVO> findByPage(Page<UserVO> page, @Param("dto") UserInfoPageDTO dto);

    /**
     * 根据主表用户id 查询对应的用户明细
     * @param userId
     * @return
     */
    List<UserDetailVO> getDetailsByUserId(@Param("userId") Long userId);
}

4.mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.up.user.mapper.UserMapper">

    <resultMap id="page_user_vo" type="com.up.openfeign.api.user.vo.UserVO">
        <id column="userId" jdbcType="BIGINT" property="id"/>
        <result column="user_name" jdbcType="VARCHAR" property="userName"/>
        <result column="sex" jdbcType="TINYINT" property="sex"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="is_delete" jdbcType="TINYINT" property="isDelete"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <collection property="details" javaType="java.util.ArrayList" column="userId"
                    ofType="com.up.openfeign.api.user.vo.UserDetailVO" select="getDetailsByUserId" />
    </resultMap>

    <resultMap id="page_user_detail_vo" type="com.up.openfeign.api.user.vo.UserDetailVO">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="user_id" jdbcType="BIGINT" property="userId"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="hobby" jdbcType="VARCHAR" property="hobby"/>
    </resultMap>

    <select id="getDetailsByUserId" resultMap="page_user_detail_vo">
        SELECT
              *
        FROM
             t_user_detail ud
      where
        ud.user_id=#{userId}
    </select>

    <select id="findByPage" resultMap="page_user_vo" parameterType="com.up.openfeign.api.user.dto.UserInfoPageDTO">
        select DISTINCT u.id as
        userId,u.user_name,u.sex,u.email,u.phone,u.password,u.is_delete,u.create_time,u.update_time
        from t_user u left join t_user_detail ud on u.id=ud.user_id
        <where>
            <if test="dto.userName !='' and dto.userName != null">
                and u.user_name = #{dto.userName,jdbcType=VARCHAR}
            </if>
            <if test="dto.sex != null">
                and u.sex = #{dto.sex,jdbcType=TINYINT}
            </if>
            <if test="dto.email !='' and dto.email != null">
                and u.email = #{dto.email,jdbcType=VARCHAR}
            </if>
            <if test="dto.phone != null and dto.phone!='' ">
                and u.phone = #{dto.phone,jdbcType=VARCHAR}
            </if>
        </where>
        ORDER BY u.update_time DESC
    </select>
</mapper>