记录整理期末实训项目中的分页功能实现,以备不时之需,直接上代码:
0.建表(准备测试数据)
/*
Navicat MySQL Data Transfer
Date: 2019-12-24 15:21:58
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for books
-- ----------------------------
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` varchar(200) NOT NULL,
`name` varchar(100) NOT NULL,
`price` double DEFAULT NULL,
`pnum` int(11) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of books
-- ----------------------------
INSERT INTO `books` VALUES ('1001', 'java编程思想', '98', '100', '计算机');
INSERT INTO `books` VALUES ('1002', '西游记', '10', '50', '文学');
INSERT INTO `books` VALUES ('1003', '九阴真经', '20', '30', '武侠');
INSERT INTO `books` VALUES ('1004', '365夜睡前好故事', '19.8', '50', '少儿');
INSERT INTO `books` VALUES ('1006', '三只小猪', '9.8', '50', '少儿');
INSERT INTO `books` VALUES ('1007', '中华上下五千年', '28', '100', '少儿');
INSERT INTO `books` VALUES ('1008', '三国演义', '9.8', '50', '文学');
1.DBUtil(数据库连接封装类)
package com.ambow.util;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class DBUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/bookstore";
private static String username = "root";
private static String password = "root";
static {
try {
/**
* 加载驱动
*/
Class.forName(driver);
}catch(Exception ex) {
ex.printStackTrace();
}
}
public static Connection getConnection() {
/**
* 创建连接对象
*/
Connection conn=null;
try {
conn=(Connection) DriverManager.getConnection(url,username,password);
}catch(Exception ex) {
ex.printStackTrace();
}
return conn;
}
/**
* 关闭相关流
*/
public static void close(ResultSet rs,Statement st,Connection conn) {
try {
if(rs!=null) {
rs.close();
}
if(st!=null) {
st.close();
}
if(conn!=null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
public static void closePst(ResultSet rs,PreparedStatement pst,Connection conn) {
try {
if(rs!=null) {
rs.close();
}
if(pst!=null) {
pst.close();
}
if(conn!=null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
}
2.Book(实体类)
package com.ambow.domain;
public class Book {
private int id;
private String name;
private float price;
private int pnum;
private String category;
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 float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
public int getPnum() {
return pnum;
}
public void setPnum(int pnum) {
this.pnum = pnum;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
}
3.PageModel(分页模型类)
package com.ambow.domain;
import java.util.List;
/**
* 封装分页信息
* @author Administrator
*
*/
public class PageModel<E> {
//结果集
private List<E> list;
//查询记录数
private int totalRecords;
//每页多少条数据
private int pageSize;
//第几页
private int pageNo;
/**
* 总页数
* @return
*/
public int getTotalPages() {
return (totalRecords + pageSize - 1) / pageSize;
}
/**
* 取得首页
* @return
*/
public int getTopPageNo() {
return 1;
}
/**
* 上一页
* @return
*/
public int getPreviousPageNo() {
if (pageNo <= 1) {
return 1;
}
return pageNo - 1;
}
/**
* 下一页
* @return
*/
public int getNextPageNo() {
if (pageNo >= getBottomPageNo()) {
return getBottomPageNo();
}
return pageNo + 1;
}
/**
* 取得尾页
* @return
*/
public int getBottomPageNo() {
return getTotalPages();
}
/**
* get set函数
* @return
*/
public List<E> getList() {
return list;
}
public void setList(List<E> list) {
this.list = list;
}
public int getTotalRecords() {
return totalRecords;
}
public void setTotalRecords(int totalRecords) {
this.totalRecords = totalRecords;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
}
4.BookDao(业务逻辑层,操作数据库等操作,返回值为PageModel对象)
package com.ambow.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 com.ambow.domain.Book;
import com.ambow.domain.PageModel;
import com.ambow.util.DBUtil;
public class BookDao {
public PageModel<Book> findData(String pageNo, String pageSize){
PageModel<Book> pageModel=null;
Connection conn= DBUtil.getConnection();
String sql="select * from books limit ?,?";
PreparedStatement pst=null;
ResultSet rs=null;
Book rec=null;
List<Book> list=new ArrayList<Book>();
try {
pst=conn.prepareStatement(sql);
//pstm.setInt(1, (pageNo-1)*pageSize);
//pstm.setInt(2, pageNo*pageSize);
pst.setInt(1, (Integer.parseInt(pageNo)-1)*Integer.parseInt(pageSize));
pst.setInt(2, Integer.parseInt(pageSize));
rs=pst.executeQuery();
while(rs.next()){
rec=new Book();
rec.setId(rs.getInt("id"));
rec.setName(rs.getString("name"));
rec.setPrice(rs.getFloat("price"));
rec.setPnum(rs.getInt("pnum"));
rec.setCategory(rs.getString("category"));
list.add(rec);
}
ResultSet rs2=pst.executeQuery("select count(*) from books");
int total=0;
if(rs2.next()){
total=rs2.getInt(1);//总的数据条数
}
pageModel=new PageModel<Book>();
pageModel.setPageNo(Integer.parseInt(pageNo));
pageModel.setPageSize(Integer.parseInt(pageSize));
pageModel.setTotalRecords(total);
pageModel.setList(list);
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.closePst(rs, pst, conn);
}
return pageModel;
}
public static void main(String[] args) {
BookDao client=new BookDao();
PageModel<Book> pageModel=client.findData("2","4");
List<Book> list=pageModel.getList();
for(Book a:list){
System.out.print("ID:"+a.getId()+",名称:"+a.getName()+",价格:"+a.getPrice());
System.out.println();
}
System.out.print("当前页:"+pageModel.getPageNo()+" ");
System.out.print("共"+pageModel.getTotalPages()+"页 ");
System.out.print("首页:"+pageModel.getTopPageNo()+" ");
System.out.print("上一页:"+pageModel.getPreviousPageNo()+" ");
System.out.print("下一页:"+pageModel.getNextPageNo()+" ");
System.out.print("尾页:"+pageModel.getBottomPageNo()+" ");
System.out.print("共"+pageModel.getTotalRecords()+"条记录");
System.out.println();
}
}
5.BookServlet(控制层,servlet,连接前端和业务层,获得PageModel对象后跳转到新页面展示分页数据信息)
package com.ambow.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ambow.dao.BookDao;
import com.ambow.domain.Book;
import com.ambow.domain.PageModel;
/**
* Servlet implementation class recomment
*/
@WebServlet("/getBook")
public class BookServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
String pageSize = request.getParameter("pageSize");// 每页显示行数
String pageNo = request.getParameter("pageNo");// 当前显示页次
if (pageSize == null) {// 为空时设置默认页大小为10
pageSize = "10";
}
if (pageNo == null) {// 为空时设置默认为第1页
pageNo = "1";
}
//System.out.println("pageNo="+pageNo+"pageSize="+pageSize);
// 保存分页参数,传递给下一个页面
request.setAttribute("pageSize", pageSize);
request.setAttribute("pageNo", pageNo);
//新建Dao对象,获取pageModel
BookDao client=new BookDao();
PageModel<Book> pageModel=client.findData(pageNo,pageSize);
request.setAttribute("pageModel", pageModel);//前端获取这个值
request.getRequestDispatcher("result.jsp").forward(request, response);
}
}
6.index.jsp(程序入口,调到servlet层)
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!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=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<a href="${pageContext.request.contextPath}/getBook">查看所有图书信息</a>
</body>
</html>
7.result.jsp(展示分页结果的页面)
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ page import="java.util.*" %>
<%@ page import="com.ambow.domain.Book" %>
<%@ page import="com.ambow.domain.PageModel" %>
<%
String pageSize = (String) request.getAttribute("pageSize");
String pageNo = (String) request.getAttribute("pageNo");
PageModel<Book> pageModel=(PageModel<Book>) request.getAttribute("pageModel");
List<Book> list=pageModel.getList();
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<!-- <script src="/haha/js/jquery.min.js" type="text/javascript"></script> -->
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<style type="text/css">
table{
width: 60%;
margin: auto;
border: solid 1px sandybrown;
}
caption{
font-weight: bold;
font-size: 24px;
}
#dataTable td{
border: solid 1px sandybrown;
}
#pageTable{
margin-top: 10px;
}
</style>
</head>
<body>
<table id="dataTable">
<caption>所有信息</caption>
<tr align="center">
<td><b>ID</b></td>
<td><b>图书名称</b></td>
<td><b>图书价格</b></td>
<td><b>图书数量</b></td>
<td><b>图书类别</b></td>
</tr>
<%
if(list==null||list.size()<1){
%><p align="center">还没有任何数据!</p>
<%}else{
for(Book rec:list){
%>
<tr>
<td><%=rec.getId() %></td>
<td><%=rec.getName() %></td>
<td><%=rec.getPrice() %></td>
<td><%=rec.getPnum() %></td>
<td><%=rec.getCategory() %></td>
</tr>
<%
}
}
%>
</table>
<form name="form1" action="${pageContext.request.contextPath}/getBook" method="post">
<TABLE id="pageTable">
<TR>
<TD align="left">
共<%=pageModel.getTotalRecords()%>条/<a>每页</a>
<select name="pageSize"
onchange="document.all.pageNo.value='1';document.all.form1.submit();">
<option value="5" <%if(pageSize.equals("5")){%>
selected="selected" <%}%>>5</option>
<option value="10" <%if(pageSize.equals("10")){%>
selected="selected" <%}%>>10</option>
<option value="20" <%if(pageSize.equals("20")){%>
selected="selected" <%}%>>20</option>
<option value="30" <%if(pageSize.equals("30")){%>
selected="selected" <%}%>>30</option>
</select>条</TD>
<TD align="right">
<a href="javascript:document.all.pageNo.value='<%= pageModel.getTopPageNo() %>';document.all.form1.submit();">首页</a>
<a href="javascript:document.all.pageNo.value='<%= pageModel.getPreviousPageNo() %>';document.all.form1.submit();">上一页</a>
<a href="javascript:document.all.pageNo.value='<%= pageModel.getNextPageNo()%>';document.all.form1.submit();">下一页</a>
<a href="javascript:document.all.pageNo.value='<%= pageModel.getBottomPageNo()%>';document.all.form1.submit();">尾页</a>
<a>第</a>
<select name="pageNo" onchange="document.all.form1.submit();">
<%
int pageCount = pageModel.getTotalPages();
%>
<%
for (int i = 1; i <= pageCount; i++) {
%>
<option value="<%=i%>" <%if(pageNo.equals(i+"")){%>
selected="selected" <%}%>><%=i%></option>
<%
}
%>
</select><a>页</a>/共<%=pageModel.getTotalPages()%>页</TD>
</TR>
</TABLE>
</form>
</body>
</html>
9.展示效果
项目源码:下载地址