最近卡了一个功能就是分页，查了很多资料，分页大概是两种类型：一种是把数据库的东西全部查出来然后放在session里，用list一页一页传到页面，这样的消耗比较大;另一种就是使用sql语句的limit来进行数据库分页查询。我使用的是后者。
大致逻辑： (1)需要currentPage，count属性。
(2)需要注意current不能点击。
(3)全使用a标签进行页面跳转。并附上请求页码。
(4)初始化查询0页，并用filter装入list中，在页面显示的时候方便遍历。
(5)过程：页面加载->filter查询初始数据装入request->页面遍历并计算出页码请求附带在url后->请求发出后filter使用getParameter获得页码对数据库进行查询，并装入list中->页面加载的时候遍历list出现新数据。
页面如下：
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@page contentType="text/html; charset=utf-8" %> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>分页列表</title> </head> <body> <center> <% int currenPage=((Integer)request.getAttribute("currenPage")).intValue(); int count=((Integer)request.getAttribute("count")).intValue(); %> <table border="1px"> <tr> <td>ID</td> <td>用户名</td> <td>性别</td> <td>年龄</td> </tr> <c:forEach var="usr" items="${list}"> <tr> <td>${usr.id}</td> <td>${usr.name}</td> <td>${usr.sex}</td> <td>${usr.age}</td> </tr> </c:forEach> </table> <% int prePage=currenPage-1; if(currenPage==1) prePage=currenPage; %> <a href="Demo2.jsp?<%="curren="+prePage%>">上一页</a> <% int i=1; int end=currenPage+5; if(currenPage>5){ i=currenPage-5; } if(end>count/10){ end=count/10; System.out.println("end="+end); } for(;i<=end;i++) { System.out.println("i="+i); if(i == (currenPage)){ %> [<%=currenPage%>] <% }else{ %> <a href="Demo2.jsp?<%="curren="+i%>"><%=i%></a> <% } } %> <% int nextPage=currenPage+1; if(nextPage>count/10) nextPage--; %> <a href="Demo2.jsp?<%="curren="+nextPage%>">下一页</a> </center> </body> </html>
Filter如下
package filter; import java.io.IOException; import java.util.List; import javax.servlet.Filter; import javax.servlet.FilterChain; import javax.servlet.FilterConfig; import javax.servlet.ServletException; import javax.servlet.ServletRequest; import javax.servlet.ServletResponse; import javax.servlet.annotation.WebFilter; import dividedpage.SelectService; import model.test_u; /** * Servlet Filter implementation class divideFilter */ @WebFilter("/Demo2.jsp") public class divideFilter implements Filter { private static final long serialVersionUID = 1L; private int start=0; private int size=10; private SelectService ss; private List<test_u> list; /** * Default constructor. */ public divideFilter() { // TODO Auto-generated constructor stub ss = new SelectService(); } /** * @see Filter#destroy() */ public void destroy() { // TODO Auto-generated method stub } /** * @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain) */ public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { // TODO Auto-generated method stub // place your code here String cu=request.getParameter("curren"); if(cu!=null){ start=Integer.parseInt(cu); } System.out.println(start); list = ss.selectLimit((start-1)*size, size); int count = ss.getConut(); request.setAttribute("list", list); request.setAttribute("count", count); request.setAttribute("currenPage", start); // pass the request along the filter chain System.out.println("执行过滤"); chain.doFilter(request, response); } /** * @see Filter#init(FilterConfig) */ public void init(FilterConfig fConfig) throws ServletException { // TODO Auto-generated method stub } }
JDBC如下
package DAO; import java.sql.*; public class Connect2DB { String driver="com.mysql.jdbc.Driver"; Connection con; String url="jdbc:mysql://localhost:3306/MyData"; String user="root"; String pwd="qwert123"; public Connect2DB(){ connection2MYSQL() ; } public void connection2MYSQL() { try { Class.forName(driver); con=DriverManager.getConnection(url,user,pwd); if(!con.isClosed()) System.out.println("连接成功"); } catch (Exception e) { e.printStackTrace(); } } public Connection getConn(){ return con; } }
package DAO; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import model.test_u; public class OperatorDB { private Connection con; public OperatorDB(){ con=new Connect2DB().getConn(); } public void addUser(test_u u){ String sql="insert into test_u(id,name,sex,age) values(?,?,?,?)"; PreparedStatement ps; try { ps=con.prepareStatement(sql); ps.setInt(1, u.getId()); ps.setString(2, u.getName()); ps.setString(3, u.getSex()); ps.setString(4, u.getAge()); ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void delUserById(int id){ String sql="delete from test_u where stu_id = ?"; PreparedStatement ps; try { ps=con.prepareStatement(sql); ps.setInt(1, id); ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public List<test_u> selectLimit(int start,int size){ String sql = "select * from test_u limit ?,?"; List<test_u> result=new ArrayList<test_u>(); PreparedStatement ps; try { ps = con.prepareStatement(sql); ps.setInt(1, start); ps.setInt(2, size); ResultSet rs = ps.executeQuery(); while(rs.next()){ int id=rs.getInt("id"); String name=rs.getString("name"); String sex=rs.getString("sex"); String age=rs.getString("age"); test_u t=new test_u(id,name,sex,age); result.add(t); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result; } public int getCount(){ String sql="SELECT COUNT(*) FROM test_u"; int rowCount = 0; try { PreparedStatement ps; ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(sql); rs.next(); rowCount = rs.getInt(1); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rowCount; } public void close(){ try { if(!con.isClosed()) con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
Service如下
package dividedpage; import java.util.List; import DAO.OperatorDB; import model.test_u; public class SelectService { public List<test_u> selectLimit(int start,int size){ OperatorDB odb=new OperatorDB(); List<test_u> list=odb.selectLimit(start, size); odb.close(); return list; } public int getConut(){ OperatorDB odb=new OperatorDB(); int count = odb.getCount(); odb.close(); return count; } }
Bean如下
package model; public class test_u { private int id; private String name; private String sex; private String age; public test_u(){ } public test_u(int id, String name, String sex, String age) { super(); this.id = id; this.name = name; this.sex = sex; this.age = age; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } }
数据表如下，插入100条记录
感觉代码很冗余，页面不够干净，不过也训练了分页的思想。
下列标签栏全是a标签，上一页current-1，下一页current+1；需要注意页面边界（最大，最小页）。查询limit大概是（（current-1）*size，size）这样的公式。
目录树如下：
JSTL需要下载个jar包，很容易找到，添加他们进path就好。
