分页设计

题外话：该分页显示是用 “表示层-控制层-DAO层-数据库”的设计思想实现的，有什么需要改进的地方大家提出来，共同学习进步。废话不多说了，开始进入主题，详细步骤如下所示：

1.DAO层-数据库

JDBCUtils 类用于打开和关闭数据库，核心代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCUtils { private Connection conn= null ; private PreparedStatement pstmt= null ;  /** * connect 连接数据库 * @return */ public Connection connect(){ String user= "root" ; String password= "1234" ; String driverClass = "com.mysql.jdbc.Driver" ; String jdbcUrl = "jdbc:mysql://localhost:3306/book" ; try { Class.forName(driverClass); conn = DriverManager.getConnection(jdbcUrl, user, password); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } /** * close 关闭数据库 * @param conn * @param pstmt * @param resu */ public void close(Connection conn,PreparedStatement pstmt,ResultSet result){ if (conn != null ){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block } } if (pstmt != null ){ try { pstmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (result != null ){ try { result.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

UserDao 类中的方法 getPage() 和方法 listUser() 分别用来计算总页数和查询指定页的数据，核心代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

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 com.db.JDBCUtils; public class UserDao { /** * 计算总的页数 * @return */ public int getPage(){ int recordCount= 0 ,t1= 0 ,t2= 0 ; PreparedStatement pstmt= null ; ResultSet result= null ; JDBCUtils jdbc= new JDBCUtils(); Connection conn=jdbc.connect(); String sql= "select count(*) from books" ; try { pstmt=conn.prepareStatement(sql); result=pstmt.executeQuery(); result.next(); recordCount=result.getInt( 1 ); t1=recordCount% 5 ; t2=recordCount/ 5 ; } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { jdbc.close(conn, pstmt, result); } if (t1 != 0 ){ t2=t2+ 1 ; } return t2; } /** * 查询指定页的数据 * @param pageNo * @return */ public List<User> listUser( int pageNo){ PreparedStatement pstmt= null ; ResultSet result= null ; List<User> list= new ArrayList<User>(); int pageSize= 5 ; int page=(pageNo- 1 )* 5 ; JDBCUtils jdbc= new JDBCUtils(); Connection conn=jdbc.connect(); String sql= "select * from books order by id limit ?,?" ; try { pstmt=conn.prepareStatement(sql); pstmt.setInt( 1 , page); pstmt.setInt( 2 , pageSize); result=pstmt.executeQuery(); while (result.next()){ User user= new User(); user.setId(result.getInt( 1 )); user.setName(result.getString( 2 )); user.setNumber(result.getString( 3 )); list.add(user); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { jdbc.close(conn, pstmt, result); } return list; } }

User 类用于存储查询到的数据，核心代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

public class User { private int id; private String name; private String number; 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 getNumber() { return number; } public void setNumber(String number) { this .number = number; } }

2.控制层

ListUser 类内部调用 UserDao 对象查询数据并指派页面显示数据，核心代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

import java.io.IOException; import java.io.PrintWriter; import java.util.ArrayList; 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.dao.User; import com.dao.UserDao; public class ListUser extends HttpServlet { public ListUser() { super (); } public void destroy() { super .destroy(); // Just puts "destroy" string in log // Put your code here } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setCharacterEncoding( "utf-8" ); int pageNo = 1 ; UserDao userdao= new UserDao(); List<User> lists= new ArrayList<User>(); String pageno=request.getParameter( "pageNos" ); if (pageno != null ){ pageNo=Integer.parseInt(pageno); } lists=userdao.listUser(pageNo); int recordCount=userdao.getPage(); request.setAttribute( "recordCount" , userdao.getPage()); request.setAttribute( "listss" , lists); request.setAttribute( "pageNos" , pageNo); request.getRequestDispatcher( "userlist.jsp" ).forward(request, response); } public void init() throws ServletException { // Put your code here } }

3.表示层

输出页面 userlist.jsp ,使用 EL 和 JSTL 输出查询结果，核心代码如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

<%@ page language= "java" import = "java.util.*" pageEncoding= "UTF-8" %> <%@ taglib prefix= "c" uri= "http://java.sun.com/jsp/jstl/core" %> <%@ taglib uri= "http://java.sun.com/jsp/jstl/fmt" prefix= "fmt" %>  <%@ taglib uri= "http://java.sun.com/jsp/jstl/functions" prefix= "fn" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+ "://" +request.getServerName()+ ":" +request.getServerPort()+path+ "/" ; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" > <html> <head> <base href= "<%=basePath%>" > <title>My JSP 'userlist.jsp' starting page</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 rel= "stylesheet" type= "text/css" href= "styles.css" > --> <style type= "text/css" > th,td{width: 150px;border: 2px solid gray;text-align: center;} body{text-align: center;} a{text-decoration: none;} table {border-collapse: collapse;} </style> </head> <body> <h2 align= "center" >图书信息</h2> <table align= "center" > <tr><td>书号</td><td>书名</td><td>库存量</td></tr> </table> <table align= "center" > <c:forEach items= "${listss}" var= "person" > <tr> <td class = "hidden-480" >${person.id}</td> <td class = "hidden-480" >${person.name }</td> <td class = "hidden-480" >${person.number }</td> </tr> </c:forEach> </table> <br> <c: if test= "${pageNos>1 }" > <a href= "ListUser?pageNos=1" >首页</a> <a href= "ListUser?pageNos=${pageNos-1 }" >上一页</a> </c: if > <c: if test= "${pageNos <recordCount }" > <a href= "ListUser?pageNos=${pageNos+1 }" >下一页</a> <a href= "ListUser?pageNos=${recordCount }" >末页</a> </c: if > <form action= "ListUser" > <h4 align= "center" >共${recordCount}页   <input type= "text" value= "${pageNos}" name= "pageNos" size= "1" >页 <input type= "submit" value= "到达" > </h4> </form> </body> </html>