MybatisPlus多表分页查询

数据库

数据库为三张表，user表、role表和user_role表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  `deleted` int(1) UNSIGNED ZEROFILL NOT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `role_desc` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  `deleted` int(1) NOT NULL COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `user_id` int(11) NOT NULL COMMENT '用户编号',
  `role_id` int(11) NOT NULL COMMENT '角色编号',
  PRIMARY KEY (`user_id`, `role_id`) USING BTREE,
  INDEX `FK_Reference_3`(`role_id`) USING BTREE,
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_Reference_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

Controller层

@RequestMapping("/view")
//pn是每次传回来的当前页
public String view(Model model,
                   @RequestParam(required = false, defaultValue = "1", value = "pn") Integer pn) {
    IPage<User> page = userService.selectByPage(pn, 5);
    //此处得到的page对象,包含了current（当前页）,pages（总页数），total（总记录数），records（记录，就是查询到的List集合）
    model.addAttribute("page", page);
    model.addAttribute("jumpUrl", "/user/view?pn=");
    return "user/list";
}

Service层

IPage<User> selectByPage(int start, int size);

@Override
public IPage<User> selectByPage(int start, int size) {
    Page<User> page = new Page<>(start, size);
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    userMapper.getUserPage(page, wrapper);
    return page;
}

mapper层

IPage<User> getUserPage(IPage<User> page, @Param("ew") QueryWrapper<User> queryWrapper);

<resultMap id="userMap" type="top.alin.bbs.entity.User">
    <id property="id" column="id"/>
    <result property="username" column="username"/>
    <result property="password" column="password"/>
    <result property="createTime" column="create_time"/>
    <collection property="roles" ofType="top.alin.bbs.entity.Role" resultMap="roleMap"/>
</resultMap>

<resultMap id="roleMap" type="top.alin.bbs.entity.Role">
    <id property="id" column="role_id"/>
    <result property="roleName" column="role_name"/>
    <result property="roleDesc" column="role_desc"/>
</resultMap>
<select id="getUserPage" resultMap="userMap">
    SELECT
        u.*,
        r.id,r.role_name,r.role_desc
    FROM
        user u
        left join user_role ur on u.id = ur.user_id
        left join role r on r.id = ur.role_id
        where u.deleted = 0
        ${ew.customSqlSegment}
</select>