springboot分页查询

1.创建类

1.Page类

@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. Use类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {

    private int userId;//用户id

    private String userCode;//账号

    private String userName;//用户名

    private String userPwd;//用户密码

    private String userType;//用户类型

    private String userState;//用户状态

    private String isDelete;//删除状态

    private int createBy;//创建人id

    //返回前端时,自动将Date转换成指定格式的json字符串
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date createTime;//创建时间

    private int updateBy;//修改人id

    private Date updateTime;//修改时间

    private String getCode;//追加的属性--创建人

    
}

 

2. 创建Mapper

1.编写方法

public interface UserMapper {

//查询用户总行数的方法
public int selectUserCount(User user);

//分页查询用户的方法
public List<User> selectUserPage(@Param("page") Page page, @Param("user")User user);

}

2.UserMapper.xml编写动态sql

<!--
  //查询用户总行数的方法
  public int selectUserCount(User user)
-->
<select id="selectUserCount" resultType="integer">
    select count(*) from user_info
    <where>
       <if test="userCode != null and userCode != ''">
          and user_code like concat('%', #{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>

   <!--
    //分页查询用户的方法
 public List<User> selectUserPage(@Param("page") Page page, @Param("user")User user)
   -->
<select id="selectUserPage" resultMap="userResult">
    select t1.*, t2.user_code getCode from 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 concat('%', #{user.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>

 

3.创建Service 层

1.UserService

public interface UserService {

//分页查询用户的业务方法
public Page queryUserPage(Page page, User user);

}

2. UserServiceImp

//分页查询用户的业务方法
@Override
public Page queryUserPage(Page page, User user) {

    //查询用户总行数
    int userCount = userMapper.selectUserCount(user);

    //分页查询用户
    List<User> userList = userMapper.selectUserPage(page, user);

    //将查询到的总行数和当前页数据组装到Page对象
    page.setTotalNum(userCount);
    page.setResultList(userList);

    return page;
}

4.创建controller层

 

@RequestMapping("/user-list")
public Result userListPage(Page page, User user){
    //执行业务
    page = userService.queryUserPage(page, user);
    //响应
    return Result.ok(page);
}

 还有一种方法用Mybatis分页插件Pagehelper，会方便很多