前端传入pageIndex(当前页码,从1开始)和pageSize(每页条数),以dubbo+mysql为例:
一、普通的分页:先查询count总数,如果count大于0,说明有数据,再查询这一页的数据。查询这一页的数据mysql语法是limit startIndex,pageSize,startIndex指从第几条数据开始查,pageSize指需要查几条数据。
(1)dto:主要是获取startIndex(开始记录index)
package com.demo.dto;
import java.io.Serializable;
public class PageParam implements Serializable {
private static final long serialVersionUID = -5552159160388121108L;
private static final int MIN_PAGE_SIZE = 1;
private static final int MAX_PAGE_SIZE = 1000;
private static final int DEFAULT_PAGE_SIZE = 10;
/**
* 当前页码,从1开始。
*/
private int pageIndex;
/**
* 每页记录数。
*/
private int pageSize = DEFAULT_PAGE_SIZE;
/**
* 构造函数
*/
public PageParam() {
}
/**
* 构造函数。
*
* @param pageIndex
* 当前页码,从1开始。
* @param pageSize
* 每页记录数。
*/
public PageParam(int pageIndex, int pageSize) {
setPageIndex(pageIndex);
setPageSize(pageSize);
}
/**
* 得到开始记录index。
*
* @return 开始记录index。
*/
public int getStartIndex() {
return (pageIndex - 1) * pageSize;
}
/**
* @return the pageIndex
*/
public int getPageIndex() {
return pageIndex;
}
/**
* @param pageIndex
* the pageIndex to set
*/
public void setPageIndex(int pageIndex) {
if (pageIndex < 1){
throw new RuntimeException("page index should > 0");
}
this.pageIndex = pageIndex;
}
/**
* @return the pageSize
*/
public int getPageSize() {
return pageSize;
}
/**
* @param pageSize
* the pageSize to set
*/
public void setPageSize(int pageSize) {
if (pageSize >= MIN_PAGE_SIZE && pageSize <= MAX_PAGE_SIZE){
this.pageSize = pageSize;
}else {
throw new RuntimeException(String.format("page size should be ranged in [%s, %s]",MIN_PAGE_SIZE,MAX_PAGE_SIZE));
}
}
}
(2)controller:
@RequestMapping("/geUserList")
public HttpResult getUserList(int pageIndex, int pageSize) {
try {
PageParam pageParam = new PageParam(pageIndex, pageSize);
Pager<User> users= userService.getPagerUser(pageParam);
return HttpResult.getSuccessInstance(users);
} catch (Exception e) {
return HttpResult.getFailedInstance("分页获取出错!");
}
}
(3)impl:
@Override
public Pager<User> getPagerUser(PageParam pageParam) {
Assert.notNull(pageParam);
List<User> users= new ArrayList<>();
int totalCount = userMapper.getUserTotalCount();
if(totalCount > 0){
users= userMapper.getUserList(pageParam.getStartIndex(),pageParam.getPageSize());
}
return new Pager<>(totalCount, robots);
}
(4)mapper:
<!--查询总数-->
<select id="getUserTotalCount" resultType="int">
select count(1) from t_user
where is_delete = 0
</select>
<!--查询列表-->
<select id="getUserList" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_user
where is_delete = 0
limit #{startIndex},#{pageSize}
</select>
二、pageHelper,与上述区别:
普通的分页需要写两条SQL,pageHelper只需要写查询列表的SQL即可,总数会自动查询,因为总数是自动的,所以列表查询SQL一定要有order by语句,指定排序。并且pageHelper不需要处理获取startIndex,直接使用传入的pageIndex和pageSize即可。
1、api:
(1)pom依赖:
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.8</version>
</dependency>
(2)dto:
package com.demo.dto;
import java.io.Serializable;
public class PagerQuery implements Serializable {
private Integer pageIndex;
private Integer pageSize;
//省略所有get和set
}
package com.demo.dto;
public class UserQueryDTO extends PagerQuery{
private String nickName;
public String getNickName() {
return nickName;
}
public void setNickName(String nickName) {
this.nickName = nickName;
}
}
2、service:
(1)pom依赖:
<!--pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.10</version>
</dependency>
(2)在application.properties中配置pagerHelper信息:
#pagehelper
pagehelper.helper-dialect=mysql
pagehelper.reasonable=true
pagehelper.support-methods-arguments=true
pagehelper.params=count=countSql
(3)impl:
@Override
public PageInfo<UserDTO> getPagerUser(UserQueryDTO query) {
//PageParam pageParam = new PageParam(query.getPageIndex(),query.getPageSize());
PageHelper.startPage(query.getPageIndex(), query.getPageSize(), true);
List<UserDTO> userDTOList = userDao.getPagerUser(query);
PageInfo<UserDTO> pageInfo = new PageInfo<UserDTO>(userDTOList);
return pageInfo;
}
(4)mapper:
List<UserDTO> getPagerUser(@Param("query") UserQueryDTO query);
<!--pagerHelp分页查询-->
<select id="getPagerUser" resultType="com.demo.dto.UserDTO">
select id,
user_nick_name userNickName,
user_name userName,
user_nick_name userNickName
from t_user
where is_delete = 0
<if test="query.nickName !='' and query.nickName!=null"> and user_nick_name = #{query.nickName}</if>
order by id
</select>
3、rest:
(1)pom依赖:
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.8</version>
</dependency>
(2)controller接口:
/**
* 获取分页用户
* @return
*/
@RequestMapping("/getPagerUser")
public ResponseMessage getPagerUser(@RequestBody UserQueryDTO userQueryDTO){
PageInfo<UserDTO> users = userService.getPagerUser(userQueryDTO);
return ResponseMessage.success(users);
}
自行计算pageIndex和pageSize:
long pages = (totalCount + PAGE_SIZE - 1) / PAGE_SIZE;
for(int i = 1; i <= pages; i++) {
PageHelper.startPage(i, PAGE_SIZE, false);
//数据库查询
}
三、pageHelper注意事项:
1、reasonable参数:pagehelper的reasonable 默认为false,遇到查询页数大于总页数时,查询为空;当reasonable设置为true时,遇到查询页数大于总页数时,查询最后一页数据。
实际应用中,调用处(如前端)一般会根据totalCount总数计算总页数,如分页条数为10条,总数有13条,则只有两页。此时如果reasonable设置为true且调用处未设置页数上限,如传入3、4等大于2的数字,仍然可以查出来数据(等同于最后一页的数据)。reasonable可以在配置文件中设置,还可以在分页查询时指定此参数:调用startPage时传入reasonable参数 为false。