mybatis分页 and mybatis-plus分页

1.my-batis

• 百度 使用PageHelper
• 自定义mybatis分页类 先了解分页sql使用

mysql数据库基础：分页查询（LIMIT），分页查询的语法，注意事项，案例演示_mysql limit 页数-CSDN博客

（1） 定义分页参数类，分页结果类

@Data
@AllArgsConstructor
public class PageParam {
    private int pageNum; // 当前页码，从1开始
    private int pageSize; // 每页显示的条数

}

@Data
public class PageResult<T> {
        private int pageNum; // 当前页码
        private int pageSize; // 每页条数
        private int totalRecord; // 总记录数
        private int totalPage; // 总页数
        private List<T> list; // 当前页的数据列表

    }

(2) service层

接口

public interface UserService {

    PageResult<User> getUsersByPage(PageParam pageParam);
}

实现类

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;
    @Override
    public PageResult<User> getUsersByPage(PageParam pageParam) {

//        SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;
//        .起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

        int offset = (pageParam.getPageNum() - 1) * pageParam.getPageSize();
        int pageSize = pageParam.getPageSize();
        List<User> list=userMapper.selectUsersByPage(offset,pageSize);
        int totalRecords=userMapper.countUsers();
        PageResult<User> pageResult=new PageResult<>();
        pageResult.setList(list);
        pageResult.setTotalRecord(totalRecords);
        pageResult.setPageNum(pageParam.getPageNum()); //第几页
        pageResult.setPageSize(pageParam.getPageSize()); //一页多少条数据
        //总页数计算
        pageResult.setTotalPage(totalRecords%pageParam.getPageSize()==0?totalRecords/pageParam.getPageSize():totalRecords/pageParam.getPageSize()+1);

        return pageResult;
    }
}

(3) mapper层

List<User> selectUsersByPage(@Param("offset") int offset, @Param("pageSize") int pageSize);

@Select("select count(*) from user")
int countUsers();

xml

<select id="selectUsersByPage" parameterType="com.example.crucialfunctiontest.model.dto.PageParam" resultType="com.example.crucialfunctiontest.model.entity.User">
    SELECT * FROM user

    LIMIT
    <if test="offset != null and pageSize != null">
        #{offset}, #{pageSize}
    </if>

</select>

(4) controller

@Resource
private UserService userService;


@GetMapping("/page/{currentPage}/{pageSize}")
public PageResult<User> page(@PathVariable("currentPage") Integer currentPage,
                   @PathVariable("pageSize") Integer pageSize){
    //构造分页参数
    PageParam pageParam=new PageParam(currentPage,pageSize);
    // 调用service层
    PageResult<User> pageResult=userService.getUsersByPage(pageParam);

    return pageResult;
}

(5) 测试结果

---

2.mybatis-plus

实体类

@TableName(value ="user")
@Data
public class User implements Serializable {
 
    @TableId(type = IdType.AUTO)
    private Long id;
    private String username;
    private String userAccount;
    private String avatarUrl;
    private String signature;
    private String introduction;
    private Integer gender;  //0 女 1男
    private String userPassword;
    private String phone;
    private String email;
    private Integer userStatus;
    private Date createTime;
    private Date updateTime;

    @TableLogic  //逻辑删除注解 非常重要 看Yml文件配置
    private Integer isDelete;
    private Integer userRole;
    private String tags;
    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

依赖导入(略)

分页拦截器配置

@Configuration
public class MPConfig {//配置分页拦截器
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor=new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

controller

@GetMapping("/page/{currentPage}/{pageSize}")
public BaseResponse<IPage<User>> getPage(@PathVariable int currentPage,@PathVariable int pageSize){
    IPage<User> page=userService.getPage(currentPage,pageSize);
    return new BaseResponse<>(200,page,"查询成功","分页查询用户");
}

service

• UserService接口

IPage<User> getPage(int currentPage, int pageSize); //当前所在页 每页多少条

• 接口实现类

@Override
public IPage<User> getPage(int currentPage, int pageSize) {
    //userMapper需要继承BaseMapper
    QueryWrapper<User> q=new QueryWrapper<>();
    IPage page = new Page(currentPage, pageSize);
    userMapper.selectPage(page,q); //分页查询条件
    return page;
}

• 测试结果

补充 按条件分页查询

@GetMapping("/page/{currentPage}/{pageSize}")
public BaseResponse<IPage<User>> getPageByCondition(@PathVariable int currentPage,@PathVariable int pageSize,User user){
    System.out.println("user is "+user);
   IPage<User> page=userService.getPageByCondition(currentPage,pageSize,user);
    return new BaseResponse<>(200,page,"查询成功","分页查询用户");
}

service实现

  @Override
    public IPage<User> getPageByCondition(int currentPage, int pageSize,User user) {
//组合查询条件
        QueryWrapper<User> q=new QueryWrapper<>();
        String username = user.getUsername();
        if(StringUtils.isNotBlank(username)){
            q.like("username",username);
        }
        String signature = user.getSignature();
        if(StringUtils.isNotBlank(signature)){
            q.like("signature",signature);
        }

        IPage page = new Page(currentPage, pageSize);
        userMapper.selectPage(page,q); //分页查询条件

        return page;
    }

测试