Mybatis多对多查询+PageHelpler分页问题

多对多查询

说明：tb_user用户表，tb_role角色表，tb_user_role用户角色关联表

1. 实现表数据的多对多查询并返回
2. 使用PageHelper对查询结果分页

• 用户表

@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_user")
public class TbUser implements Serializable{
    private static final long serialVersionUID = 1L;
    @TableId(value = "uid", type = IdType.AUTO)
    private Integer uid;
    // 用户名称
    private String username;
    // 密码
    private String password;
    // 角色
    @TableField(exist = false)
    private List<TbRole> roles;
    // 创建时间
    @TableField(fill = FieldFill.INSERT)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;
    // 修改时间
    @TableField(fill = FieldFill.INSERT_UPDATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date updateTime;
}

• 角色表

@Data
@EqualsAndHashCode(callSuper = false)
@NoArgsConstructor
@AllArgsConstructor
@TableName("tb_role")
public class TbRole implements Serializable {
    private static final long serialVersionUID = 1L;
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    @JsonProperty(value="rName")
    private String rName;
}

1、Mybatis实现多对多查询

• Controller

    @GetMapping("infolist")
    public Result infolist() {
        List<TbUser> userPageInfo = userInfoService.select();
        return Result.success(200, "查询成功", userPageInfo);
    }

• Service

public interface UserInfoService extends IService<TbUser> {
    List<TbUser> select();
}

// Impl
@Service
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, TbUser> implements UserInfoService {
    @Autowired
    private UserInfoMapper userInfoDao;
    @Override
    public List<TbUser> select() {
        List<TbUser> userList = userInfoDao.selectMap();
        return userList ;
    }
}

• Mapper

@Mapper
public interface UserInfoMapper extends BaseMapper<TbUser> {
    List<TbUser> selectMap();
}

• xml

<resultMap id="userList" type="com.server.entity.TbUser">
    <id property="uid" column="uid" jdbcType="INTEGER"></id>
    <result property="username" column="username"></result>
    <result property="createTime" column="create_time"></result>
    <result property="updateTime" column="update_time"></result>
    <collection property="roles" ofType="com.server.entity.TbRole">
        <id property="id" column="rid"></id>
        <result property="rName" column="r_name"></result>
    </collection>
</resultMap>
<select id="selectMap" resultMap="userList">
    select u.*,r.id rid,r.r_name
    from tb_user u
    left join tb_user_role ur on u.uid = ur.u_id
    left join tb_role r on ur.r_id = r.id
--         limit ${size} offset ${((num-1)*size).intValue()} 这里是分页先不考虑
</select>

2、Mybatis多对多PageHelper问题

2.1 介绍问题

先贴出官网问题

我们常用的分页方式(错误的分页方式)

1. limit分页

<resultMap id="userList" type="com.server.entity.TbUser">
  <id property="uid" column="uid" jdbcType="INTEGER"></id>
  <result property="username" column="username"></result>
  <result property="createTime" column="create_time"></result>
  <result property="updateTime" column="update_time"></result>
  <collection property="roles" ofType="com.server.entity.TbRole">
      <id property="id" column="rid"></id>
      <result property="rName" column="r_name"></result>
  </collection>
</resultMap>
<select id="selectMap" resultMap="userList">
  select u.*,r.id rid,r.r_name
  from tb_user u
  left join tb_user_role ur on u.uid = ur.u_id
  left join tb_role r on ur.r_id = r.id
  limit ${size} offset ${((num-1)*size).intValue()} 
</select>

1. PageHelper.startPage(pageNum,pageSize); 该方法会对代码下方第一个sql查询进行分页处理

上述两种方式都会出现错误，错误原因：

• 上面两种方式的分页查询，对于xml中的sql查询，都是每次执行了一次sql，然后再进行集合实体映射，一次查询出的数据是没有进行集合映射之前的数据
• 例如：【个人理解】一个user中包含3个role，那么查询出来的数据就是三条，这里可以看一下idea控制台的输出，其实是三条数据；然后再根据resultMap配置进行映射进对应的集合，最后成一条user数据。而我们使用Limit或者PageHelper插件分页时，总条数读取的是查询出来的总条数是分条的结果也就是3，而最后因为映射所以实际需要的只是一条数据，分页显示的时候会按照分条结果显示，所以就会有问题。

2.2 解决方法

1、PageHelper方式

• Controller

@GetMapping("infolist")
public Result infolist(@RequestParam(defaultValue = "1") Integer pageNum,
                       @RequestParam(defaultValue = "10") Integer pageSize) {
    PageInfo<TbUser> userPageInfo = userInfoService.select(pageNum,pageSize);
    return Result.success(200, "查询成功", userPageInfo);
}

• Service

// 接口
PageInfo<TbUser> select(Integer pageNum, Integer pageSize);
// 实现
@Override
public PageInfo<TbUser> select(Integer pageNum, Integer pageSize) {
    PageHelper.startPage(pageNum,pageSize);
    List<TbUser> userList = userInfoDao.selectMap();
    PageInfo<TbUser> pageInfo = new PageInfo<>(userList);
    return pageInfo;
}

• Mapper

List<TbUser> selectMap();

• xml

<resultMap id="userList" type="com.server.entity.TbUser">
    <id property="uid" column="uid" jdbcType="INTEGER"></id>
    <result property="username" column="username"></result>
    <result property="createTime" column="create_time"></result>
    <result property="updateTime" column="update_time"></result>
    <collection property="roles" javaType="ArrayList" column="uid" select="selectRole" ofType="com.server.entity.TbRole">
        <id property="id" column="id"></id>
        <result property="rName" column="r_name"></result>
    </collection>
</resultMap>

<select id="selectRole" resultType="com.server.entity.TbRole">
    select r.id, r.r_name
        from tb_role r,tb_user_role ur
    where ur.u_id=#{uid} and ur.r_id = r.id
</select>

<select id="selectMap" resultMap="userList">
    select *
    from tb_user
</select>

接口测试结果查看：

2、Limit

• Controller

@GetMapping("infolist")
public Result infolist(@RequestParam(defaultValue = "1") Integer pageNum,
                       @RequestParam(defaultValue = "10") Integer pageSize) {
    List<TbUser> userPageInfo = userInfoService.select(pageNum,pageSize);
    return Result.success(200, "查询成功", userPageInfo);
}

• Service

//接口
List<TbUser> select(Integer pageNum, Integer pageSize);
//实现
@Override
public List<TbUser> select(Integer pageNum, Integer pageSize) {
    List<TbUser> userList = userInfoDao.selectMap(pageNum,pageSize);
    return userList;
}

• Mapper

List<TbUser> selectMap(@Param("num") Integer pageNum,
                       @Param("size") Integer pageSize);

• xml

<resultMap id="userList" type="com.server.entity.TbUser">
    <id property="uid" column="uid" jdbcType="INTEGER"></id>
    <result property="username" column="username"></result>
    <result property="createTime" column="create_time"></result>
    <result property="updateTime" column="update_time"></result>
    <collection property="roles" javaType="ArrayList" column="uid" select="selectRole" ofType="com.server.entity.TbRole">
        <id property="id" column="id"></id>
        <result property="rName" column="r_name"></result>
    </collection>
</resultMap>
<select id="selectRole" resultType="com.server.entity.TbRole">
    select r.id, r.r_name
    from tb_role r,tb_user_role ur
    where ur.u_id=#{uid} and ur.r_id = r.id
</select>
<select id="selectMap" resultMap="userList">
    select *
    from tb_user
    limit ${size} offset ${((num-1)*size).intValue()}
</select>

【推荐】MybatisPlus通过注解的方式实现多对多查询