[学习笔记] SpringMVC之数据分页

# 学习 # · 2021-03-01

使用PageHelper插件实现分页

1、在pom.xml文件添加如下依赖:

<!-- mybatis分页插件依赖 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

2、在Mybatis配置文件(mybatis-config.xml)中配置拦截器插件:

<configuration>
<plugins>
    <!-- com.github.pagehelper为PageHelper类所在包名 -->
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <property name="dialect" value="mysql"/>

        <!-- 该参数默认为false -->
        <!-- 设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用 -->
        <!-- 和startPage中的pageNum效果一样-->
        <property name="offsetAsPageNum" value="true"/>

        <!-- 该参数默认为false -->
        <!-- 设置为true时,使用RowBounds分页会进行count查询 -->
        <property name="rowBoundsWithCount" value="true"/>

        <!-- 设置为true时,如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->
        <!-- (相当于没有执行分页查询,但是返回结果仍然是Page类型)-->
        <property name="pageSizeZero" value="true"/>

        <!-- 3.3.0版本可用 - 分页参数合理化,默认false禁用 -->
        <!-- 启用合理化时,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页 -->
        <!-- 禁用合理化时,如果pageNum<1或pageNum>pages会返回空数据 -->
        <property name="reasonable" value="false"/>

        <!-- 3.5.0版本可用 - 为了支持startPage(Object params)方法 -->
        <!-- 增加了一个`params`参数来配置参数映射,用于从Map或ServletRequest中取值 -->
        <!-- 可以配置pageNum,pageSize,count,pageSizeZero,reasonable,不配置映射的用默认值 -->
        <!-- 不理解该含义的前提下,不要随便复制该配置 -->
        <property name="params" value="pageNum=start;pageSize=limit;"/>

        <!-- always总是返回PageInfo类型,check检查返回类型是否为PageInfo,none返回Page -->
        <property name="returnPageInfo" value="check"/>
    </plugin>
</plugins>
</configuration>

3、在Mapper中添加SQL:

<!-- 根据用户名查询 -->
<select id="selectUserByUserName" parameterType="java.lang.String" resultMap="BaseResultMap">
    SELECT *
    FROM t_user
    WHERE 1 = 1
    <if test="userName != null and userName !=''">
        AND USER_NAME = #{userName,jdbcType=VARCHAR}
    </if>
    ORDER BY USER_ID
</select>

4、在Dao接口中添加方法:

List<User> selectUserByUserName(@Param("userName") String userName);

5、在Service添加分页查询方法:

PageInfo<User> queryByPage(String userName,Integer pageNo,Integer pageSize);

6、实现类添加对应实现:

@Override
public PageInfo<User> queryByPage(String userName, Integer pageNo,Integer pageSize) {
    pageNo = pageNo == null?1:pageNo;    // 设置当前页码
    pageSize = pageSize == null?10:pageSize;    // 设置每页数据条数
    PageHelper.startPage(pageNo, pageSize);
    List<User> list = userDao.selectUserByUserName(userName);
    // 用PageInfo对结果进行包装
    PageInfo<User> page = new PageInfo<User>(list);
    return page;
}

7、单元测试类添加分页查询测试方法:

@Test
public void queryByPageTest(){
    PageInfo<User> page =  userService.queryByPage(null, 1, 1);
    System.out.println(page);
}

手写分页工具类

1、编写分页工具类:

public class PageSupport {
    //当前页码-来自于用户输入
    private int currentPageNo = 1;

    //总数量(表)
    private int totalCount = 0;

    //页面容量
    private int pageSize = 0;

    //总页数-totalCount/pageSize(+1)
    private int totalPageCount = 1;

    public int getCurrentPageNo() {
        return currentPageNo;
    }

    public void setCurrentPageNo(int currentPageNo) {
        if(currentPageNo > 0){
            this.currentPageNo = currentPageNo;
        }
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        if(totalCount > 0){
            this.totalCount = totalCount;
            //设置总页数
            this.setTotalPageCountByRs();
        }
    }
    public int getPageSize() {
        return pageSize;
    }

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

    public int getTotalPageCount() {
        return totalPageCount;
    }

    public void setTotalPageCount(int totalPageCount) {
        this.totalPageCount = totalPageCount;
    }

    public void setTotalPageCountByRs(){
        if(this.totalCount % this.pageSize == 0){
            this.totalPageCount = this.totalCount / this.pageSize;
        }else if(this.totalCount % this.pageSize > 0){
            this.totalPageCount = this.totalCount / this.pageSize + 1;
        }else{
            this.totalPageCount = 0;
        }
    }

}

2、Mapper接口中定义方法:

public int getUserCount(@Param("userName") String userName)throws Exception;

public List<User> getUserList(@Param("userName") String userName, @Param(value="from")Integer currentPageNo,@Param(value="pageSize")Integer pageSize)throws Exception;

3、Mapper的映射文件中的SQL语句:

<!-- 根据用户名计数 -->
<select id="getUserCount" resultType="Int">
    SELECT count(*) as count
    FROM t_user
    WHERE 1 = 1
    <if test="userName != null and userName !=''">
        AND USER_NAME = #{userName,jdbcType=VARCHAR}
    </if>
</select>

<!-- 根据用户名查询 -->
<select id="getUserList" resultMap="BaseResultMap">
    SELECT *
    FROM t_user
    WHERE 1 = 1
    <if test="userName != null and userName !=''">
        AND USER_NAME = #{userName,jdbcType=VARCHAR}
    </if>
    ORDER BY USER_ID limit #{from},#{pageSize}
</select>

4、Service及其实现类中实现分页:

@Override
public List<User> getUserList(String userName,Integer currentPageNo,Integer pageSize) throws Exception {
    // TODO Auto-generated method stub
    return mapper.getUserList((currentPageNo-1)*pageSize, pageSize);
}

@Override
public int getUserCount(String userName)throws Exception {
    // TODO Auto-generated method stub
    return mapper.getAppInfoCount(userName);
}

5、Controller中实现分页:

@RequestMapping(value="/list")
public String getAppInfoList(Model model,HttpSession session,
                             @RequestParam(value="userName",required=false) String userName,
                             @RequestParam(value="pageIndex",required=false) String pageIndex){
    List<User> userList = null;

    //页面容量
    int pageSize = 10;

    //当前页码
    Integer currentPageNo = 1;
    if(pageIndex != null){
        try{
            currentPageNo = Integer.valueOf(pageIndex);
        }catch (NumberFormatException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    //总数量(表)
    int totalCount = 0;
    try {
        totalCount = appInfoService.getAppInfoCount(querySoftwareName, queryStatus, queryCategoryLevel1, queryCategoryLevel2, queryCategoryLevel3, queryFlatformId, devId);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    //总页数
    PageSupport pages = new PageSupport();
    pages.setCurrentPageNo(currentPageNo);
    pages.setPageSize(pageSize);
    pages.setTotalCount(totalCount);
    int totalPageCount = pages.getTotalPageCount();

    //控制首页和尾页
    if(currentPageNo < 1){
        currentPageNo = 1;
    }else if(currentPageNo > totalPageCount){
        currentPageNo = totalPageCount;
    }

    //分页查询
    try {
        userList = userService.getUserList(userName, currentPageNo, pageSize);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    model.addAttribute("userList", userList);
    model.addAttribute("pages", pages);
    return "list";
}
如无特殊说明,本博所有文章均为博主原创。

如若转载,请注明出处:一木林多 - https://www.l5v.cn/archives/268/

评论