Java Web基础入门第四十九讲客户关系管理系统之分页查询

最新推荐文章于 2022-10-14 22:34:38 发布

李阿昀

最新推荐文章于 2022-10-14 22:34:38 发布

阅读量3.4k

点赞数 5

分类专栏： Java Web基础入门文章标签： Java-Web基础

本文链接：https://blog.csdn.net/yerenyuan_pku/article/details/52300613

版权

Java Web基础入门专栏收录该内容

114 篇文章 61 订阅

订阅专栏

前言：上一讲博客《Java Web基础入门第四十八讲 JDBC实现客户关系管理系统模块》已经基本上开发好了查看客户的功能，但我们并不满足于此，在这一讲博客中，我们将实现分页显示。

数据库分页

在这里插入图片描述

分页结构

完成WEB页面的分页显示，需要：

先获得需分页显示的记录总数，然后在WEB页面中显示页码；
根据页码，从数据库中查询相应的记录显示在WEB页面中。

要实现分页查询，我们一定要弄清楚如下分页设计结构图：
在这里插入图片描述
要实现分页查询，就要设计出上图中红色区域的3个Java对象。

实现客户关系管理系统的分页查询

创建实现分页显示的三个对象

首先在cn.liayun.domain包下创建QueryInfo类，用于封装用户的请求参数。该类的具体代码如下：

package cn.liayun.domain;

/**
 * 封装查询信息的实体
 * @author liayun
 *
 */
public class QueryInfo {
	private int currentpage = 1;//用户当前看的页
	private int pagesize = 5;//记住用户想看的页面大小
	private int startindex;//记住用户看的页的数据在数据库的起始位置
	
	public int getCurrentpage() {
		return currentpage;
	}
	public void setCurrentpage(int currentpage) {
		this.currentpage = currentpage;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}
	public int getStartindex() {
		this.startindex = (this.currentpage - 1) * this.pagesize;
		return startindex;
	}
}

然后在cn.liayun.domain包下创建QueryResult类，用于封装查询结果。该类的具体代码如下：

package cn.liayun.domain;

import java.util.List;

/**
 * 用来封装查询结果的实体
 * @author liayun
 *
 */
public class QueryResult {
	private List list;//记住用户看的页的数据
	private int totalrecord;//记住总记录数
	
	public List getList() {
		return list;
	}
	public void setList(List list) {
		this.list = list;
	}
	public int getTotalrecord() {
		return totalrecord;
	}
	public void setTotalrecord(int totalrecord) {
		this.totalrecord = totalrecord;
	}
}

最后，在cn.liayun.domain包下创建PageBean类。该类用于将QueryResult对象中封装的查询结果，生成显示分页数据的PageBean对象。

package cn.liayun.domain;

import java.util.List;

/**
 * 封装页面显示需要的信息
 * @author liayun
 *
 */
public class PageBean {
	private List list;
	private int totalrecord;
	private int pagesize;
	private int totalpage;
	private int currentpage;
	private int previouspage;
	private int nextpage;
	private int[] pagebar;
	
	public List getList() {
		return list;
	}
	public void setList(List list) {
		this.list = list;
	}
	public int getTotalrecord() {
		return totalrecord;
	}
	public void setTotalrecord(int totalrecord) {
		this.totalrecord = totalrecord;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}
	public int getTotalpage() {
		//根据总记录数和页面大小算出来的
		//100	5	20
		//101	5	21
		//99	5	20
		if (this.totalrecord % this.pagesize == 0) {
			this.totalpage = this.totalrecord / this.pagesize;
		} else {
			this.totalpage = this.totalrecord / this.pagesize + 1;
		}
		return totalpage;
	}
	
	public int getCurrentpage() {
		return currentpage;
	}
	public void setCurrentpage(int currentpage) {
		this.currentpage = currentpage;
	}
	public int getPreviouspage() {
		if (this.currentpage - 1 < 1) {
			this.previouspage = 1;
		} else {
			this.previouspage = this.currentpage - 1;
		}
		
		return previouspage;
	}
	public int getNextpage() {
		if (this.currentpage + 1 >= this.totalpage) {
			this.nextpage = this.totalpage;
		} else {
			this.nextpage = this.currentpage + 1;
		}
		return nextpage;
	}
	public int[] getPagebar() {
		/*
		int[] pagebar = new int[this.totalpage];
		for (int i = 1; i <= this.totalpage; i++) {
			pagebar[i - 1] = i;
		}
		this.pagebar = pagebar;
		return pagebar;
		*/
		
		int startpage;//起始页码
		int endpage;//结束页码
		int[] pagebar = null;
		if (this.totalpage <= 10) {
			pagebar = new int[this.totalpage];
			startpage = 1;
			endpage = this.totalpage;
		} else {
			pagebar = new int[10];
			startpage = this.currentpage - 4;
			endpage = this.currentpage + 5;
			
			//总页数=30		3		-1
			//总页数=30		29		34
			// 总页数=30，假设看的是第3页，则startpage=-1
            // 总页数=30，假设看的是第29页，则endpage=34
			if (startpage < 1) {
				startpage = 1;
				endpage = 10;
			}
			
			if (endpage > this.totalpage) {
				endpage = this.totalpage;
				startpage = this.totalpage - 9;
			}
		}
		
		int index = 0;
		for (int i = startpage; i <= endpage; i++) {
			pagebar[index++] = i;
		}
		this.pagebar = pagebar;
		return this.pagebar;
	}
}

修改数据访问层(dao、dao.impl)

首先，将CustomerDao接口的代码修改为：

package cn.liayun.dao;

import java.util.List;

import cn.liayun.domain.Customer;
import cn.liayun.domain.QueryResult;

public interface CustomerDao {

	void add(Customer c);

	void update(Customer c);

	void delete(String id);

	Customer find(String id);

	List<Customer> getAll();
	
	//获取到页面数据和页面大小
	public QueryResult pageQuery(int startindex, int pagesize);

}

然后修改CustomerDao接口的CustomerDaoImpl实现类的代码为：

package cn.liayun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.liayun.dao.CustomerDao;
import cn.liayun.domain.Customer;
import cn.liayun.domain.QueryResult;
import cn.liayun.exception.DaoException;
import cn.liayun.utils.JdbcUtils;

public class CustomerDaoImpl implements CustomerDao {
	
	@Override
	public void add(Customer c) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into customer(id,name,gender,birthday,cellphone,email,preference,type,description) values(?,?,?,?,?,?,?,?,?)";
			st = conn.prepareStatement(sql);
			st.setString(1, c.getId());
			st.setString(2, c.getName());
			st.setString(3, c.getGender());
			st.setDate(4, new java.sql.Date(c.getBirthday().getTime()));
			st.setString(5, c.getCellphone());
			st.setString(6, c.getEmail());
			st.setString(7, c.getPreference());
			st.setString(8, c.getType());
			st.setString(9, c.getDescription());
			
			st.executeUpdate();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Override
	public void update(Customer c) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?";
			st = conn.prepareStatement(sql);
			st.setString(1, c.getName());
			st.setString(2, c.getGender());
			st.setDate(3, new java.sql.Date(c.getBirthday().getTime()));
			st.setString(4, c.getCellphone());
			st.setString(5, c.getEmail());
			st.setString(6, c.getPreference());
			st.setString(7, c.getType());
			st.setString(8, c.getDescription());
			st.setString(9, c.getId());
			st.executeUpdate();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Override
	public void delete(String id) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from customer where id=?";
			st = conn.prepareStatement(sql);
			st.setString(1, id);
			st.executeUpdate();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Override
	public Customer find(String id) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from customer where id=?";
			st = conn.prepareStatement(sql);
			st.setString(1, id);
			rs = st.executeQuery();
			if (rs.next()) {
				Customer c = new Customer();
				c.setBirthday(rs.getDate("birthday"));
				c.setCellphone(rs.getString("cellphone"));
				c.setDescription(rs.getString("description"));
				c.setEmail(rs.getString("email"));
				c.setGender(rs.getString("gender"));
				c.setId(rs.getString("id"));
				c.setName(rs.getString("name"));
				c.setPreference(rs.getString("preference"));
				c.setType(rs.getString("type"));
				return c;
			}
			return null;
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	@Override
	public List<Customer> getAll() {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from customer";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			List<Customer> list = new ArrayList<Customer>();
			while (rs.next()) {
				Customer c = new Customer();
				c.setBirthday(rs.getDate("birthday"));
				c.setCellphone(rs.getString("cellphone"));
				c.setDescription(rs.getString("description"));
				c.setEmail(rs.getString("email"));
				c.setGender(rs.getString("gender"));
				c.setId(rs.getString("id"));
				c.setName(rs.getString("name"));
				c.setPreference(rs.getString("preference"));
				c.setType(rs.getString("type"));
				list.add(c);
			}
			return list;
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
	//获取到页面数据以及总记录数
	public QueryResult pageQuery(int startindex, int pagesize) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		
		QueryResult qr = new QueryResult();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from customer limit ?,?";
			st = conn.prepareStatement(sql);
			st.setInt(1, startindex);
			st.setInt(2, pagesize);
			rs = st.executeQuery();
			List<Customer> list = new ArrayList<Customer>();
			while (rs.next()) {
				Customer c = new Customer();
				c.setBirthday(rs.getDate("birthday"));
				c.setCellphone(rs.getString("cellphone"));
				c.setDescription(rs.getString("description"));
				c.setEmail(rs.getString("email"));
				c.setGender(rs.getString("gender"));
				c.setId(rs.getString("id"));
				c.setName(rs.getString("name"));
				c.setPreference(rs.getString("preference"));
				c.setType(rs.getString("type"));
				list.add(c);
			}
			qr.setList(list);
			
			// 还要进行一次查询，查询出总记录数
			sql = "select count(*) from customer";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			if (rs.next()) {
				qr.setTotalrecord(rs.getInt(1));
			}
			return qr;
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			JdbcUtils.release(conn, st, rs);
		}
	}
	
}

修改service层(service层对web层提供所有的业务服务)

首先，修改BusinessService接口的代码为：

package cn.liayun.service;

import java.util.List;

import cn.liayun.domain.Customer;
import cn.liayun.domain.PageBean;
import cn.liayun.domain.QueryInfo;

//业务类，统一对Web层提供所有服务
public interface BusinessService {

	void addCustomer(Customer c);

	void updateCustomer(Customer c);

	void deleteCustomer(String id);

	Customer findCustomer(String id);

	List<Customer> getAllCustomer();
	
	public PageBean pageQuery(QueryInfo queryInfo);

}

然后修改BusinessService接口的BusinessServiceImpl实现类的代码为：

package cn.liayun.service.impl;

import java.util.List;

import cn.liayun.dao.CustomerDao;
import cn.liayun.dao.impl.CustomerDaoImpl;
import cn.liayun.domain.Customer;
import cn.liayun.domain.PageBean;
import cn.liayun.domain.QueryInfo;
import cn.liayun.domain.QueryResult;
import cn.liayun.service.BusinessService;

//此业务层代码很少，所以称为薄薄的业务层
public class BusinessServiceImpl implements BusinessService {
	private CustomerDao dao = new CustomerDaoImpl();
	
	@Override
	public void addCustomer(Customer c) {
		dao.add(c);
	}
	
	@Override
	public void updateCustomer(Customer c) {
		dao.update(c);
	}
	
	@Override
	public void deleteCustomer(String id) {
		dao.delete(id);
	}
	
	@Override
	public Customer findCustomer(String id) {
		return dao.find(id);
	}
	
	@Override
	public List<Customer> getAllCustomer() {
		return dao.getAll();
	}
	
	public PageBean pageQuery(QueryInfo queryInfo) {
		//调用Dao获取到页面数据
		QueryResult qr = dao.pageQuery(queryInfo.getStartindex(), queryInfo.getPagesize());
		
		//根据Dao的查询结果，生成页面显示需要的PageBean
		PageBean bean = new PageBean();
		bean.setCurrentpage(queryInfo.getCurrentpage());
		bean.setList(qr.getList());
		bean.setPagesize(queryInfo.getPagesize());
		bean.setTotalrecord(qr.getTotalrecord());
		return bean;
	}
}

修改web层

修改ListCustomerServlet，用于处理用户分页查询请求。ListCustomerServlet的具体代码如下：

package cn.liayun.web.controller;

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 cn.liayun.domain.PageBean;
import cn.liayun.domain.QueryInfo;
import cn.liayun.service.BusinessService;
import cn.liayun.service.impl.BusinessServiceImpl;
import cn.liayun.utils.WebUtils;

//处理用户的分页请求
@WebServlet("/ListCustomerServlet")
public class ListCustomerServlet extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {	
		try {
			/*
             * 如果用户带查询条件过来，就将查询条件封装到QueryInfo里面去。
             * 如果用户没有带查询条件过来，也即第一次查询，也要生成一个QueryInfo，使用默认查询条件，即从第1页查看5条记录。
             */
			QueryInfo info = WebUtils.request2Bean(request, QueryInfo.class);
			BusinessService service = new BusinessServiceImpl();
			PageBean pagebean = service.pageQuery(info);
			request.setAttribute("pagebean", pagebean);
			request.getRequestDispatcher("/WEB-INF/jsp/listcustomer.jsp").forward(request, response);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			request.setAttribute("message", "查看用户失败！！！");
			request.getRequestDispatcher("message.jsp").forward(request, response);
		}
		
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

接下来就要修改客户显示界面(listcustomer.jsp)了，分页显示这一块，有很多细节需要我们注意，比如：

当我们浏览第一页的数据时，上一页超链接就不能显示出来；当我们浏览尾页的数据时，下一页超链接也不能显示出来；
当我们浏览到某一页时，页码条中的这一页就应该显示为普通文字，而不是超链接，即该页是无法点击再次查看的；
除了点击上一页和下一页，我们还应可自由跳转到任何页面；
我们还应可自由控制每页可显示的记录数。

下面我们就按照以上细节来修改客户显示界面(listcustomer.jsp)，修改后的listcustomer.jsp页面代码为：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="/liayun" prefix="liayun" %>
<!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>
<style type="text/css">
	.even {
		background-color: #FFFF00
	}
	.odd {
		background-color: #FFCCFF
	}
	TR:HOVER {
		background-color: #FF99FF
	}
</style>
</head>
<body style="text-align: center;">
	<table border="1px" width="85%" align="center">
		<tr>
			<td>客户姓名</td>
			<td>性别</td>
			<td>生日</td>
			<td>手机</td>
			<td>邮箱</td>
			<td>爱好</td> 
			<td>类型</td> 
			<td>备注</td>
			<td>操作</td>
		</tr>
		
		<c:forEach var="c" items="${requestScope.pagebean.list }" varStatus="status">
			<tr class="${status.count % 2 == 0 ? 'even' : 'odd'}">
				<td>${c.name }</td>
				<td>${c.gender }</td>
				<td>${c.birthday }</td>
				<td>${c.cellphone }</td>
				<td>${c.email }</td>
				<td>${liayun:sub(c.preference) }</td>
				<td>${c.type }</td>
				<td>${liayun:sub(c.description) }</td>
				<td>
					<a href="${pageContext.request.contextPath}/EditCustomerServlet?id=${c.id }">修改</a>
					<a href="javascript:void(0)" onclick="del('${c.id }')">删除</a>
				</td>
			</tr>		
		</c:forEach>
	</table>
	<br/>
	
	
	<script type="text/javascript">
		function del(id) {
			if (window.confirm("您确定删除吗？")) {
				location.href = '${pageContext.request.contextPath}/DeleteCustomerServlet?id=' + id;
			}
		}
	
		function gotopage(currentpage) {
			//alert(currentpage);
			if (currentpage < 0 || currentpage != parseInt(currentpage) || currentpage > ${pagebean.totalpage }) {
				alert("请输入有效值!!");
				document.getElementById("pagenum").value = '';
			} else {
				var pagesize = document.getElementById("pagesize").value;
				
				window.location.href = '${pageContext.request.contextPath}/ListCustomerServlet?currentpage=' + currentpage + '&pagesize=' + pagesize;
			}
			
		}
		
		function changesize(pagesize,oldvalue) {
			if (pagesize < 0 || pagesize != parseInt(pagesize)) {
				alert("请输入合法值！！！");
				document.getElementById("pagesize").value = oldvalue;
			} else {
				//一改变页面大小，当前页就不要传了，那就从第1页开始显示
				window.location.href = '${pageContext.request.contextPath}/ListCustomerServlet?pagesize=' + pagesize;
			}
		}
	</script>
	
	共[${pagebean.totalrecord }]条记录，
	每页<input type="text" id="pagesize" value="${pagebean.pagesize }" onchange="changesize(this.value,${pagebean.pagesize })" style="width: 30px" maxlength="2"/>条，
	共[${pagebean.totalpage }]页，
	当前第[${pagebean.currentpage }]页，
	&nbsp;&nbsp;&nbsp;
	<c:if test="${pagebean.currentpage!=1 }">
		<a href="javascript:void(0)" onclick="gotopage(${pagebean.previouspage})">上一页</a>
	</c:if>
	
	<c:forEach var="pagenum" items="${pagebean.pagebar }">
		<c:if test="${pagenum==pagebean.currentpage }">
			<font color="red">${pagenum }</font>
		</c:if>
		<c:if test="${pagenum!=pagebean.currentpage }">
			<a href="javascript:void(0)" onclick="gotopage(${pagenum})">${pagenum }</a>
		</c:if>
	</c:forEach>
	
	<c:if test="${pagebean.currentpage!=pagebean.totalpage }">
		<a href="javascript:void(0)" onclick="gotopage(${pagebean.nextpage})">下一页</a>
	</c:if>
	<input type="text" id="pagenum" style="width: 30px" />
	<input type="button" value="GO" onclick="gotopage(document.getElementById('pagenum').value)"/>
</body>
</html>