数据分页显示

1.分页方式

    1.1：数据库分页

           *原理：在数据库中只查询当页需要显示的记录，返回给显示页面。

           *特点：过滤操作的负荷由数据库承担。传输量小。

           *实现方式：定义一个分页的Bean对象(当前页数，记录条数，总页数，每页记录数)；根据Bean对象的属性查询记录；

           *

     1.2：代码分页

           *原理：在数据库中查询全部记录，返回给显示页面整理。

           *特点：过滤操作的负荷由动态页面组装的部分承担。传数量大。

           *实现方式：查询所有记录；在动态组装页面时设置分页显示。

2.分页实现代码

   2.1：bean+jsp+sql+servlet

       *Bean

public class PaginationBean {

     /**

      * 当前页数

      */

     private int currentPage;

     /**

      * 每页显示数

      */

     private int eachPage;

     /**

      * 总页数

      */

     private int totalPage;

     /**

      * 数据总数

      */

     private int count;

     public int getCurrentPage() {

            return currentPage;

     }

     public void setCurrentPage(int currentPage) {

            this.currentPage = currentPage;

     }

     public int getEachPage() {

            return eachPage;

     }

     public void setEachPage(int eachPage) {

            this.eachPage = eachPage;

     }

     public int getTotalPage() {

            return totalPage;

     }

     public void setTotalPage(int totalPage) {

            this.totalPage = totalPage;

     }

     public int getCount() {

            return count;

     }

     public void setCount(int count) {

            setTotalPage((int)Math.ceil((double)count/(double)eachPage));

            this.count = count;

     }}

*SQL

import java.util.List;

 

import com.lovo.shop.bean.BookBean;

import com.lovo.shop.bean.PaginationBean;

 

public class BookDAO extends BaseDAO{

     public List findBook(BookBean bean,PaginationBean pb){

            String sql = "select top "+pb.getEachPage()+" * from book" +

                          " where  id not in" +

                          " (select top "+(pb.getCurrentPage()*pb.getEachPage())+" id from book" +

                          " order by id desc)" +

                          " order by id desc";

            return find(sql, BookBean.class);

     }

     public List findBookCount(BookBean bean){

            String sql = "select count(*) count from book";

            return find(sql, PaginationBean.class);

     }

}

import java.util.List;

 

import com.lovo.shop.bean.BookBean;

import com.lovo.shop.bean.PaginationBean;

import com.lovo.shop.dao.BookDAO;

 

public class SelectBookService {

     public List selectBook(BookBean bookBean,PaginationBean pb){

            BookDAO dao = new BookDAO();

            List<PaginationBean> list = dao.findBookCount(bookBean);

            List bookList = null;

            if(list.size() > 0){

                   int count = list.get(0).getCount();

                   pb.setCount(count);

                   bookList = dao.findBook(bookBean, pb);

            }

            return bookList;

     }

}

SERVLET

import java.io.IOException;

import java.util.List;

 

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import com.lovo.shop.bean.BookBean;

import com.lovo.shop.bean.PaginationBean;

import com.lovo.shop.service.SelectBookService;

 

public class BookServlet extends HttpServlet {

 

    

     public void doGet(HttpServletRequest request, HttpServletResponse response)

                   throws ServletException, IOException {

            doPost(request, response);

     }

 

    

     public void doPost(HttpServletRequest request, HttpServletResponse response)

                   throws ServletException, IOException {

            String currentPage = request.getParameter("currentPage");

            int page = 0;

            if(currentPage != null){

                   page = Integer.parseInt(currentPage);

            }

           

            PaginationBean pg = new PaginationBean();

            pg.setCurrentPage(page);

            pg.setEachPage(5);

           

            SelectBookService bookService = new SelectBookService();

            List list = bookService.selectBook(new BookBean(), pg);

           

            request.setAttribute("bookList", list);

            request.setAttribute("pb", pg);

            request.getRequestDispatcher("book.jsp").forward(request, response);

     }}

JSP

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

     <head>

 

     </head>

 

     <body>

            <c:forEach items="${bookList}" var="book">

                   <div>

                          ${book.name}

                   </div>

            </c:forEach>

 

            <table>

                   <tr>

                          <td>

                                 <c:if test="${pb.currentPage>0}">

                                        <a href="showBook?currentPage=${pb.currentPage-1 }">上一页</a>

                                 </c:if>

                          </td>

                          <c:forEach begin="0" end="${pb.totalPage - 1}" var="num">

                                 <td>

                                        <a href="showBook?currentPage=${num }">${num+1 }</a>

                                 </td>

                          </c:forEach>

                          <td>

                                 <c:if test="${pb.currentPage<pb.totalPage-1}">

                                        <a href="showBook?currentPage=${pb.currentPage+1 }">下一页</a>

                                 </c:if>

                          </td>

                   </tr>

            </table>

     </body>

</html>

   2.2：rownum——rownum每次必须从1开始分配；rownum在其SQL语句的order by之前就生成。

         *先排序，再产生rownum，再嵌套

         *排序——如果主键与排序字段排序后顺序一致，则可以简化

SELECT * FROM (
--避免行号从1开始计数
  SELECT ROWNUM r,t.* FROM (
  --避免在排序前行号就固定了
   SELECT empno,ename,hiredate FROM emp ORDER BY hiredate DESC
  ) t   
) t
WHERE t.r BETWEEN 4 AND 6

   

2.3：row_number（）

 

SELECT * FROM (
  SELECT empno,ename,hiredate,
     row_number() over(ORDER BY hiredate DESC) r--以over里的条件生成行号，并取别名r。
  FROM emp
) t
WHERE t.r BETWEEN 4 AND 6

2.4:存储过程

CREATE OR REPLACE PROCEDURE sp_page(
p_field_list VARCHAR2,
p_field_order VARCHAR2,
p_table VARCHAR2,
p_page_row NUMBER,
p_page_num NUMBER,
resultset OUT Sys_Refcursor
)
AS
  sqlstr VARCHAR2(4000);
  r_start NUMBER;
  r_end NUMBER;
BEGIN
   --分页算法
   r_end:=p_page_row*p_page_num;
   r_start:=(p_page_row-1)*p_page_num+1;
   
   sqlstr:= 'SELECT * FROM ( SELECT '||p_field_list
   ||',row_number() over(ORDER BY '||p_field_order||' ) r FROM '
   ||p_table||') t WHERE t.r BETWEEN :r1 AND :r2';
   
   OPEN resultset FOR sqlstr USING r_start,r_end;
END;

 

--测试(PL/SQL，JDBC)

DECLARE
   rs SYS_REFCURSOR;
   v_empno emp.empno%TYPE;
   v_ename emp.ename%TYPE;
   r NUMBER;
BEGIN
   sp_page('empno,ename','hiredate DESC','emp_big',10,10,rs);
   FETCH rs INTO v_empno,v_ename,r;
   WHILE(rs%FOUND)
   LOOP
     dbms_output.put_line(r||','||v_empno||','||v_ename);
     FETCH rs INTO v_empno,v_ename,r;
   END LOOP;
   CLOSE rs;
END;

--jdbc
		Connection conn = null;
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@192.168.1.110:1521:orcl";
		conn = DriverManager.getConnection(url, "scott", "tiger");
		String sql = "{CALL sp_page(?,?,?,?,?,?)}";
		CallableStatement call = conn.prepareCall(sql);

		call.setString(1, "empno,ename");
		....
		call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
		call.executeUpdate();
		
		ResultSet rs = (ResultSet) call.getObject(6);
		ResultSetMetaData rmd = rs.getMetaData();
		while (rs.next()) {
			for (int i = 0; i < rmd.getColumnCount(); i++) {
				System.out.print(rs.getString(i + 1) + " ");
			}
			System.out.println("");
		}
		rs.close();
		call.close();
		conn.close();
	}