分页操作
进行网页开发,那么就少不了分页查询。分页查询在mysql中是最方便的,使用mysql的关键字LIMIT(mysql独有),即可进行分页查询。如下是mysql的分页查询的sql语句:
SELECT * FROM hero LIMIT ?, ? # 第一个?表示的是从那条数据开始查询 # 第二个?表示的是每次查询的数据的条数
而在Oracle中也有一个方言,专门用来Oracle数据库的分页查询,关键字为:ROWNUM 和 row_number()
下面使用mysql演示分页:
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> <title>分页操作</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <link href="table.css" rel="stylesheet" type="text/css"> </head> <body> <div align="center"><h1 style="color: #000000; align:center">演示分页查询</h1></div> <table cellpadding="1" border="1" cellspacing="0" bordercolor="black" align="center"> <tr class="tab-2"> <td>编号</td> <td>英雄名字</td> <td>英雄Job</td> <td>英雄上司编号</td> <td>英雄雇佣日期</td> <td>英雄薪水</td> <td>英雄奖金</td> <td>英雄类型</td> </tr> <%-- <c:if test="${null ne heros }"> --%> <c:forEach items="${heros }" var="hero"> <tr class="tab-5"> <td>${hero.herono }</td> <td>${hero.heroname }</td> <td>${hero.herojob }</td> <td>${hero.heromgr }</td> <td>${hero.herohiredate }</td> <td>${hero.herosal }</td> <td>${hero.herocomm }</td> <td>${hero.herotype }</td> </tr> </c:forEach> <%-- </c:if> --%> </table><br/><br/> <div align="center"> <c:if test="${num-1 > 0 }"> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=${num-1}">上一页</a> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=${num-1}">第${num-1 }页</a> </c:if> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=${num}">第${num }页</a> <c:if test="${num+1 <= all }"> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=${num+1}">第${num+1 }页</a> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=${num+1}">下一页</a> </c:if> <a href="<c:url value='/SplitServlet'/>?method=splitenum&num=1">刷新</a> <font color="red" size="5px">总共 ${all } 页</font> </div> </body> </html>
dao:
package cn.geore.splitpage; import java.sql.SQLException; import java.util.List; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import priv.geore.toolutils.jdbc.FirmQueRunner; public class SplitPageDao { QueryRunner runner = new FirmQueRunner(); /** * 查询每页的数据 * @param page * @param count * @return * @throws SQLException */ public List<Hero> findHero(int page, int count) throws SQLException { String sql = "SELECT * FROM hero LIMIT ?,?"; Object[] params = {(page-1)*4,count}; return (List<Hero>) runner.query(sql, new BeanListHandler<Hero>(Hero.class), params); } /** * 查询页数 * @return * @throws SQLException */ public Number count() throws SQLException { String sql = "SELECT count(*) FROM hero"; return runner.query(sql, new ScalarHandler<Number>()); } }
servlet:
package cn.geore.splitpage; import java.io.IOException; import java.sql.SQLException; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import priv.geore.toolutils.web.FirmHttpServlet; public class SplitServlet extends FirmHttpServlet { SplitPageDao dao = new SplitPageDao(); /** * 分页查找数据 * @param request * @param response * @return * @throws ServletException * @throws IOException */ public String splitenum(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { int num = Integer.valueOf(request.getParameter("num")); List<Hero> heros = null; int all = 0; try { heros = dao.findHero(num, 4); all = (int) Math.floor(dao.count().intValue() / 4.0); } catch (SQLException e) { throw new RuntimeException(e); } request.setAttribute("heros", heros); request.setAttribute("num", num); request.setAttribute("all", all); return "f:/index.jsp"; } }
运行截图: