Mybatis分页查询案例

前言

今天再写项目时刚好碰到Mybatis分页查询展示数据，现将实现过程整理出来以便后续再碰到类似需求回来瞅一眼。

数据准备

1、数据库表（user_info）

在这里插入图片描述

2、前端页面

在这里插入图片描述

代码实现

1、User实体类

package com.liming.pojo;import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.ToString;
import java.util.Date;/*** user_info表的实体类:*/
@Data
@ToString
public class User {private int userId;//用户idprivate String userCode;//账号private String userName;//用户名private String userPwd;//用户密码private String userType;//用户类型private String userState;//用户状态private String isDelete;//删除状态private int createBy;//创建人//返回前端时,自动将Date转换成指定格式的json字符串@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")private Date createTime;//创建时间private int updateBy;//修改人private Date updateTime;//修改时间// 自定义属性，数据库没有该字段private String getCode;public User() {}public User(int userId, String userCode, String userName, String userPwd,String userType, String userState, String isDelete, int createBy,Date createTime, int updateBy, Date updateTime) {this.userId = userId;this.userCode = userCode;this.userName = userName;this.userPwd = userPwd;this.userType = userType;this.userState = userState;this.isDelete = isDelete;this.createBy = createBy;this.createTime = createTime;this.updateBy = updateBy;this.updateTime = updateTime;}
}

page实体类

package com.liming.page;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;import java.util.List;/*** 分页信息实体类** @author 黎明* @version 1.0* @date 2023/8/7 16:02*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Page {//当前页码private Integer pageNum;//每页显示行数private Integer pageSize;//总行数private Integer totalNum;//总页数private Integer pageCount;//limit函数参数一每页起始行（起始索引）private Integer limitIndex;//存储当前页查询到的数据的List<?>集合private List<?> resultList;//计算总页数public Integer getPageCount() {return totalNum % pageSize == 0 ? totalNum / pageSize : totalNum / pageSize + 1;}//计算limit函数参数一每页起始行public Integer getLimitIndex() {return pageSize * (pageNum - 1);}
}

2、UserMapper

package com.liming.mapper;import com.liming.page.Page;
import com.liming.pojo.User;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** user_info mapper接口** @author 黎明* @version 1.0* @date 2023/7/23 21:04*/
public interface UserMapper {/*** 查询用户总行数的方法** @param user 分页的选择条件* @return 总记录数*/public Integer selectUserCount(User user);/*** 分页查询用户的方法** @param page 分页对象* @param user 分页的选择条件* @return 当前页用户信息*/public List<User> selectUserPage(@Param("page") Page page,@Param("user") User user);
}

3、UserMapper映射文件

<!--查询用户总行数的方法-->
<select id="selectUserCount" resultType="integer">select count(*)from user_info<where><if test="userCode != null and userCode != ''">and user_code like "%"#{userCode}"%"</if><if test="userType != null and userType != ''">and user_type = #{userType}</if><if test="userState != null and userState != ''">and user_state = #{userState}</if>and is_delete = 0</where></select><!--分页查询用户的方法--><select id="selectUserPage" resultType="user">select t1.*,t2.user_code as getCodefrom user_info t1,user_info t2<where>and t1.create_by = t2.user_id<if test="user.userCode != null and user.userCode != ''">and  t1.user_code like "%"#{userCode}"%"</if><if test="user.userType != null and user.userType != ''">and t1.user_type = #{user.userType}</if><if test="user.userState != null and user.userState != ''">and t1.user_state = #{user.userState}</if>and t1.is_delete = 0</where>limit #{page.limitIndex},#{page.pageSize}</select>

4、UserService

package com.liming.service;import com.liming.page.Page;
import com.liming.pojo.User;/*** user_info的service接口* @author 黎明* @date 2023/8/7 17:06* @version 1.0*/
public interface UserService {// 分页查询用户的业务方法public abstract Page queryUserPage(Page page,User user);
}

5、UserServiceImpl

/*** 分页查询用户的业务方法** @param page page对象* @param user 条件* @return page对象*/
@Override
public Page queryUserPage(Page page, User user) {// 查询用户总行数Integer userCount = userMapper.selectUserCount(user);// 分页查询用户List<User> users = userMapper.selectUserPage(page, user);//将查询到的总行数和当前页数据组装到Page对象page.setPageCount(userCount);page.setResultList(users);return page;
}

6、UserController

package com.liming.controller;import com.liming.page.Page;
import com.liming.pojo.Result;
import com.liming.pojo.User;
import com.liming.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;/*** @author 黎明* @version 1.0* @date 2023/8/7 16:16*/
@RestController
@RequestMapping("/user")
public class UserController {@Autowiredprivate UserService userService;/*** 分页查询用户的url接口/user/user-list** 参数Page对象用于接收请求参数页码pageNum、每页行数pageSize;* 参数User对象用于接收请求参数用户名userCode、用户类型userType、用户状态userState;** 返回值Result对象向客户端响应组装了所有分页信息的Page对象;*/@RequestMapping("/user-list")public Result userListPage(Page page, User user){//执行业务page = userService.queryUserPage(page, user);//响应return Result.ok(page);}
}