中,如果查询的结果集过多,会导致响应缓慢,甚至是服务器崩溃。对于数据量大的查询,我们使用分页技术。
如何实现分页?
数据库分页,即sql语句实现分页。
mysql中分页的sql语句:
select * from news_type limit m,size; |
m - 从第m+1条开始查询
size – 查询的条数
2.1.1 PageData类
package com.whos.jsp.util;
publicclass PageData {
privateintrowsCount = 0;//总记录数 privateintpageCount = 0;//总页数 privateintcurrentPage = 1;//当前第几页 privateintpageSize = 5;//每页显示几行
publicint getRowsCount() { returnrowsCount; } publicvoid setRowsCount(int rowsCount) { this.rowsCount = rowsCount; } publicint getPageCount() { returnpageCount; } publicvoid setPageCount(int pageCount) { this.pageCount = pageCount; } publicint getCurrentPage() { returncurrentPage; } publicvoid setCurrentPage(int currentPage) { this.currentPage = currentPage; } publicint getPageSize() { returnpageSize; } publicvoid setPageSize(int pageSize) { this.pageSize = pageSize; } } |
2.1.2 NewsTypeDao类
//查询总记录数 publicint getCount(){
int count = 0;
try { conn = DBConnection.getConnection(); String sql = "select count(*) from news_type"; stmt =conn.createStatement(); rs =stmt.executeQuery(sql);
if (rs.next()){ count = rs.getInt(1); } } catch (SQLException e) { //TODO Auto-generated catch block e.printStackTrace(); }finally{ DBConnection.close(null,stmt, pstmt, rs); }
return count;
} |
//分页查询 public List<NewsType> getNewsType(PageData pageData) {
List<NewsType> lst = new ArrayList<NewsType>();
try { conn = DBConnection.getConnection(); stmt =conn.createStatement(); String sql ="select * from news_typelimit " + (pageData.getCurrentPage() - 1) * pageData.getPageSize() + "," + pageData.getPageSize() + ""; rs =stmt.executeQuery(sql);
while (rs.next()) {
NewsType type = new NewsType(); type.setTypeId(rs.getInt(1)); type.setTypeName(rs.getString(2));
lst.add(type); } } catch (SQLException e) { //TODO Auto-generated catch block e.printStackTrace(); } finally { DBConnection.close(null,stmt, pstmt, rs); }
return lst;
} |
2.1.3 NewsTypeServlet
publicvoid doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//当前查询第几页 String currentPage = request.getParameter("currentPage");
NewsTypeDao dao = new NewsTypeDao();
PageData pageData = new PageData();
if (currentPage!=null){ pageData.setCurrentPage(Integer.parseInt(currentPage)); }
//计算总的记录数 int rowsCount = dao.getCount(); pageData.setRowsCount(rowsCount);
//计算总页数 intpageCount = 0; if (pageData.getRowsCount()%pageData.getPageSize()==0){ pageCount = pageData.getRowsCount()/pageData.getPageSize(); }else pageCount = pageData.getRowsCount()/pageData.getPageSize()+1; pageData.setPageCount(pageCount);
List<NewsType> lst = dao.getNewsType(pageData);
request.setAttribute("pageData", pageData); request.setAttribute("typeList", lst);
request.getRequestDispatcher("/list.jsp").forward(request, response); } |
2.1.4 list.jsp
<%@ page language="java"import="java.util.*"pageEncoding="UTF-8"isELIgnored="false"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core"prefix="c"%> <c:setvalue="${pageContext.request.contextPath}"var="path"></c:set>
<!DOCTYPEHTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head>
</head>
<body> <tableborder="1"width="300px;"> <tr> <td>主题编号</td> <td>主题名称</td> </tr>
<c:forEachitems="${typeList}"var="type"> <tr> <td>${type.typeId }</td> <td>${type.typeName }</td> </tr> </c:forEach>
<tr> <td colspan="2"align="right"> <c:iftest="${pageData.currentPageeq 1}"> 首页 上一页 </c:if> <c:if test="${pageData.currentPagene 1}"> <a href="${path }/newsType?currentPage=1">首页</a> <a href="${path }/newsType?currentPage=${pageData.currentPage-1}">上一页</a> </c:if> <c:if test="${pageData.currentPageeq pageData.pageCount}"> 下一页 尾页 </c:if> <c:if test="${pageData.currentPagene pageData.pageCount}"> <a href="${path }/newsType?currentPage=${pageData.currentPage+1}">下一页</a> <a href="${path }/newsType?currentPage=${pageData.pageCount}">尾页</a> </c:if> ${pageData.currentPage } / ${pageData.pageCount }页 </td> </tr> </table> </body> </html>
|