分页查询相关知识点

分页查询：

1.为什么要使用分页:
主要解决页面的数据加载的过多，导致页面访问速度过慢。
分页查询分类:
逻辑分页:
一次性将数据库中所有记录全部查询处理.查出来以后存放到一个List集合中.使用List集
合中方法subList()对List集合进行截取.
优点:减少与数据库交互次数.
缺点:数据量非常大.查询时间比较长.
物理分页:
不是一次性将所有数据全部查询出来.查询第一页:发送一条查询10条的SQL语句.查询下
一页数据:又发送一条查询后10条的SQL.
优点:数据量非常大.查询时间比较短.
缺点:与数据库交互次数变多.

MYSQL数据库进行分页操作:

使用关键字:limit.
select * from 表 where 条件 limit begin,size;
begin:从哪开始查询.
size:每次查询记录数.

分页查询的分析:

分页需要完成的效果如下：

 <table border="1px" >
        <tr>
            <th>图书ID</th>
            <th>图书名</th>
            <th>作者</th>
            <th>出版社</th>
            <th>价格</th>
            <th>入库时间</th>
        </tr>
        <c:forEach items="${pageBean.bookList}" var="book">
            <tr>
                <th>${book.bid}</th>
                <th>${book.name}</th>
                <th>${book.author}</th>
                <th>${book.publish}</th>
                <th>${book.price}</th>
                <th>${book.crtTime}</th>
            </tr>
        </c:forEach>
            <tr align="center">
                <td colspan="3">
                    第${pageBean.currentPage}/
                    ${pageBean.totalPage}页
                    总记录数${pageBean.totalSize}
                    每页显示${pageBean.pageSize}
                    <c:if test="${pageBean.currentPage!=1}">
                        <a href="${pageContext.request.contextPath}/book?methodName=selectUserListByPage&currentPage=1"> [首页]</a>
                        <a href="${pageContext.request.contextPath}/book?methodName=selectUserListByPage&currentPage=${pageBean.currentPage-1}"> [上一页]</a>
                    </c:if>
                  <c:if test="${pageBean.currentPage!=pageBean.totalPage}">
                      <a href="${pageContext.request.contextPath}/book?methodName=selectUserListByPage&currentPage=${pageBean.currentPage+1}">  [下一页]</a>
                      <a href="${pageContext.request.contextPath}/book?methodName=selectUserListByPage&currentPage=${pageBean.totalPage}">  [尾页]</a>
                  </c:if>
                </td>
            </tr>
  </table>```
浏览器向服务器传递数据:
当前页数:
服务器向浏览器传递数据:
当前页数
总页数
总记录数
每页记录数
每页数据
这里需要用到MVC设计模式
jstl标签

Servlet层

public class BookServlet extends BaseServlet {

public  void selectUserListByPage(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException {
    String currentPageStr = request.getParameter("currentPage");
    Integer currentPage;
    if(currentPageStr==null ||"".equals(currentPageStr)){
    currentPage=1;
    }else {
        currentPage=Integer.parseInt(currentPageStr);
    }
    System.out.println(currentPage);
    BookServer bookServer = new BookServer();

    PageBean pageBean = bookServer.selectUserListByPage(currentPage);
    request.getSession().setAttribute("pageBean",pageBean);
    response.sendRedirect(request.getContextPath()+"/BookList.jsp");
}

}

## Server层

public class BookServer {

public PageBean selectUserListByPage(Integer currentPage) throws SQLException {
    PageBean pageBean = new PageBean();
    //设置当前页数
    pageBean.setCurrentPage(currentPage);
    //设置总记录数
    BookDao bookDao = new BookDao();
    Integer totalSize = bookDao.selectUserListByPage();
    pageBean.setTotalSize(totalSize);
    //设置每页记录数
    Integer pageSize=2;
    pageBean.setPageSize(pageSize);
    //设置总页数
    Integer totalPage=totalSize % pageSize==0? (totalSize / pageSize ):(totalSize / pageSize)+1;
    pageBean.setTotalPage(totalPage);
    //设置当前页数据
    Integer begin =(currentPage-1)*pageSize;
    List<Book> bookList = bookDao.selectBookByName(begin, pageSize);

    pageBean.setBookList(bookList);
    System.out.println(pageBean);
    return pageBean;
}

}

## Dao层

public class BookDao {
public Integer selectUserListByPage() throws SQLException {
return new QueryRunner(JDBCUtil.getDateSource()).query(“select *from book”,new BeanListHandler(Book.class)).size();
}
public List selectBookByName(Integer begin,Integer pageSize) throws SQLException {
return new QueryRunner(JDBCUtil.getDateSource()).query(“select * from book limit ?,?”,new BeanListHandler(Book.class) ,begin,pageSize);
}
}

## 通用servlet层：

public class BaseServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String methodName = request.getParameter(“methodName”);
try {
Method method = this.getClass().getMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
// System.out.println(method);
if(method!=null||"".equals(method)){
method.invoke(this,request,response);
}
} catch (Exception e) {
e.printStackTrace();
}

}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doPost(request, response);
}

}