常见的分页方式

来源：互联网发布：黄连上清丸河南淘宝编辑：程序博客网时间：2024/05/01 17:44

1.概念

物理分页：利用数据库本身提供的分页方式，如mysql的limit，oracle的rownum，好处是效率高，不好的地方就是不同的数据库有不同的搞法。如hibernate采用的是物理分页。

逻辑分页：利用游标分页，好处是数据库兼容，坏处是效率太低。

真分页：确定要显示的页面内容数量，按需查询，效率高，但访问数据库频繁，大型网站都用真分页。

假分页：一次性从数据库查出所有的数据并在页面上显示。

2.常见的分页实现方式

sql:

MySQL ：SELECT * FROM tablename LIMIT M,N;(M从哪里开始，N数据的条数)

Oracle: select * from (select s.* ,rownum rn from (select * from tablename) s where rownum<=10) where rn>==1;

存储过程

create or replace package testpackage as type test_cursor is ref cursor;end testpackage;create or replace procedure fenye(tableName in varchar2,pageSize in number,pageNow in number,myrows out number,myPageCount out number,p_cursor out testpackage.test_cursor)is v_sql varchar2(1000);v_begin number:=(pageNow-1)*pageSize+1;v_end number:=pageNow*pageSize;beginv_sql:='select * from (select t1.*,ROWNUM rn from (select * from'||tableName||') t1 where rownum<='||v_end||')where rn>='||v_begin;open p_cursor for v_sql;v_sql:='select count(*) from '||tableName;execute immediate v_sql into myrows;if mod(myrows,pageSize)=0 then myPageCount:=myrows/pageSize;elsemyPageCount:=myrows/pageSize+1;end if;close p_cursor;end;

虽然手写sql可以解决问题，但不利于代码的重用。幸好已经有人帮我们封装了mybatis分页工具PageHelper，只需做相应配置就可以调用分页。

a.添加maven依赖

<dependency>    <groupId>com.github.pagehelper</groupId>    <artifactId>pagehelper</artifactId>    <version>4.1.6</version></dependency><!-- https://mvnrepository.com/artifact/com.github.jsqlparser/jsqlparser --><dependency>    <groupId>com.github.jsqlparser</groupId>    <artifactId>jsqlparser</artifactId>    <version>0.9.5</version></dependency>

b.在mybatis配置文件中配置插件

<plugins>        <!-- mybatis分页插件 -->        <plugin interceptor="com.github.pagehelper.PageHelper">            <property name="dialect" value="oracle" />        </plugin>    </plugins>

c.service层代码

package com.wang.ff.entity.sUser.service;import java.util.List;import org.springframework.stereotype.Service;import com.github.pagehelper.Page;import com.github.pagehelper.PageHelper;import com.github.pagehelper.PageInfo;import com.wang.ff.entity.sUser.domain.SUser;import com.wang.ff.entity.sUser.persistence.SUserMapper;import com.wang.ff.util.BaseService;@Servicepublic class UserService extends BaseService<SUserMapper, SUser>{public SUser getUserByName(String userName){return this.dao.getUserByName(userName);}/** * 分页查询用户信息 * @param page * @return */public PageInfo<SUser> getAllUsers(Page<SUser> page){if(page.getPageNum()==0){page.setPageNum(1);}String order = page.getOrderBy();PageHelper.startPage(page.getPageNum(), page.getPageSize(), order);List<SUser> list = this.dao.selectAll();PageInfo<SUser> info = new PageInfo<SUser>(list);return info;}}

d.controller代码

package com.wang.ff.controller;import javax.annotation.Resource;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.servlet.ModelAndView;import com.github.pagehelper.Page;import com.github.pagehelper.PageInfo;import com.wang.ff.entity.sUser.domain.SUser;import com.wang.ff.entity.sUser.service.UserService;@Controller@RequestMapping(value="/user")public class UserController {@Resourceprivate UserService userService;@RequestMapping(value="/getUser")public ModelAndView getUser(Page<SUser> page){page.setPageSize(5);page.setOrderBy("ID DESC");PageInfo<SUser> pageInfo = this.userService.getAllUsers(page);ModelAndView mv = new ModelAndView("user");mv.addObject("pageInfo",pageInfo);return mv;}}

e.use.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><%@ page language="java" session="false" pageEncoding="UTF-8"%><html><head><title>Home</title></head><body><h1>Hello world!  </h1>  <table>    <thead>    <th>序号</th>    <th>姓名</th>    <th>密码</th>    </thead>    <tbody>    <c:forEach items="${pageInfo.list}" var="item"><tr>    <td>${item.id}</td><td>${item.userName}</td><td>${item.password}</td></tr></c:forEach>    </tbody><tfoot><tr><td colspan="3" align="center"><c:if test="${pageInfo.hasPreviousPage}"><a href="#" onclick="javascript:conditionPageQuery(${pageInfo.prePage});">上一页</a></c:if><c:forEach items="${pageInfo.navigatepageNums}" var="curretPageNum" varStatus="status">                   <c:choose>                      <c:when test="${curretPageNum==pageInfo.pageNum}">                           <li  class="disabled"><a href="#" >${curretPageNum}</a></li>                       </c:when>                        <c:otherwise>                           <li><a href="#" onclick="javascript:conditionPageQuery(${curretPageNum});">${curretPageNum}</a></li>                      </c:otherwise>                    </c:choose>                  </c:forEach><c:if test="${pageInfo.hasNextPage}"><a href="#" onclick="javascript:conditionPageQuery(${pageInfo.nextPage});">下一页</a></c:if></td></tr></tfoot></table></body></html><script type="text/javascript">function conditionPageQuery(currentPageNum){  window.location.href="${ctx}/user/getUser?pageNum="+currentPageNum;}</script>

好吧，你不能指望一个做后台的能把页面搞得多么炫……

0 0