【Java】【33】分页处理

前言：

数据量太大时，前端就需要做翻页显示了，即点击第几页就拉取第几页的数据

正文：

1，在xml中写查询语句

Service层

注：用了rowBounds其实就是假分页了，会把数据查到内存里来，再去对应的数据集合给到前端。真分页的话，数据库查询XML里Oracle和MySQL写法不同，要注意

@Autowired TheDao theDao;

public PageModel<UserVo> getClerkListByBlur(QueryVo query) {
    RowBounds rowBounds = new RowBounds((query.getPage() - 1) * query.getRows(), query.getRows());
    int total = this.theDao.getCount(query);

    PageModel<UserVo> pageModel = new PageModel<UserVo>();
    pageModel.setPageNo(query.getPage());
    pageModel.setPageSize(query.getRows());
    pageModel.setTotalRecords(total);

    List<UserVo> list = this.theDao.getList(query, rowBounds); //rowBounds
    pageModel.setList(list);

    return pageModel;
}

Dao层

int getCount(QueryVo info);

List<UserVo> getList(QueryVo info, RowBounds rowBounds);

数据库查询XML

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bf.dnh.dao.ClerkDao" >
    <resultMap id="UserVoMap" type="com.bf.xx.params.base.UserVo" >
        <id column="Id" property="id" jdbcType="VARCHAR" />     
        <result column="Name" property="name" jdbcType="VARCHAR" />
        <result column="Province" property="province" jdbcType="VARCHAR" />
        <result column="City" property="city" jdbcType="VARCHAR" />
        <result column="Gender" property="gender" jdbcType="SMALLINT" />
        <result column="Create_Time" property="createTime" jdbcType="TIMESTAMP" />
    </resultMap>

    <select id="getCount" resultType="int">
        select count(1) from (
          <include refid="sql_getList" />
        ) T
    </select>

    <select id="getList" resultMap="UserVoMap">
        <include refid="sql_getList" />
        ORDER BY T.CREATE_TIME DESC
    </select>

    -- mysql 真分页写法
    <select id="getTestList" resultMap="UserVoMap">
        SELECT T.* 
        FROM USER T
        ORDER BY T.NAME DESC
        <if test="page != 0">
            LIMIT #{start}, #{rows}
        </if>
    </select>

    <sql id="sql_getList">
        SELECT T.*
          FROM USER T
         WHERE T.STATUS = 1
        <if test="keyword != null and keyword != ''">
           AND T.NAME LIKE '%${keyword}%'
        </if>
    </sql>

</mapper>

2，mybatis的映射查询

Service层

@Autowired TheMapper theMapper;

public PageModel<UserVo> getClerkListByBlur(QueryVo query) {
    Example example = new Example(QueryVo.class);
    example.createCriteria()
        .andEqualTo("status", 1)
        .andLike("name", query.getKeyword());

    PageModel<UserVo> pageModel = new PageModel<UserVo>();
    pageModel.setPageNo(query.getPage());
    pageModel.setPageSize(query.getRows());

    int total = this.theMapper.selectCountByExample(query);
    pageModel.setTotalRecords(total);

    RowBounds rowBounds = new RowBounds((query.getPage() - 1) * query.getRows(), query.getRows());
    List<UserVo> list = this.theMapper.selectByExampleAndRowBounds(example, rowBounds);
    pageModel.setList(list);

    return pageModel;
}

Dao层

import com.bf.base.entity.QueryVo;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;

@Repository
public interface TheMapper extends Mapper<QueryVo> {
}

3，基础类

QueryVo类

public class QueryVo {
    private int page; //页数

    private int rows; //每页显示条数

    private String keyword;

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

    public String getKeyword() {
        return keyword;
    }

    public void setKeyword(String keyword) {
        this.keyword = keyword;
    }
}

-- 真分页的话，需要增加一个参数start，开始条数

private int start; //由page*rows计算得来

public int getStart() {
    start = (page - 1) * rows;
    return start;
}

public void setStart(int start) {
    this.start = start;
}

PageModel类

package com.bf.base.params;

import java.util.List;

/**
 * 封装分页信息
 *
 */
public class PageModel<E> {

    //结果集
    private List<E> list;

    //查询记录数
    private int totalRecords;

    //每页多少条数据
    private int pageSize;

    //第几页
    private int pageNo;

    @Override
    public String toString() {
        return "PageModel [list=" + list + ", totalRecords=" + totalRecords + ", pageSize=" + pageSize + ", pageNo="
                + pageNo + "]";
    }

    /**
     * 总页数
     * @return
     */
    public int getTotalPages() {
        return (totalRecords + pageSize - 1) / pageSize;
    }

    /**
     * 取得首页
     * @return
     */
    public int getTopPageNo() {
        return 1;
    }

    /**
     * 上一页
     * @return
     */
    public int getPreviousPageNo() {
        if (pageNo <= 1) {
            return 1;
        }
        return pageNo - 1;
    }

    /**
     * 下一页
     * @return
     */
    public int getNextPageNo() {
        if (pageNo >= getBottomPageNo()) {
            return getBottomPageNo();
        }
        return pageNo + 1;
    }

    /**
     * 取得尾页
     * @return
     */
    public int getBottomPageNo() {
        return getTotalPages();
    }

    public List<E> getList() {
        return list;
    }

    public void setList(List<E> list) {
        this.list = list;
    }

    public int getTotalRecords() {
        return totalRecords;
    }

    public void setTotalRecords(int totalRecords) {
        this.totalRecords = totalRecords;
    }

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }
}