前言:上一讲博客《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 }]页,
<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>
至此,客户关系管理系统的分页查询就完成了,测试结果如下: