一.概述
在上一篇高级查询中,已经对查询对象进行了封装,在本篇文章中,主要来分析分页的封装和分页和高级查询的合并.
1. 真分页:
物理分页:每次翻页都从数据库中查询 优点:不会造成内存溢出 缺点:翻页的速度比较慢
2.假分页:
逻辑分页:一次性将所有的数据查询出来,放在内存中 优点:分页的速度比较快 缺点:可能造成内存溢出
二.分页对象的封装
@Data
//分页的结果对象
public class PageResult {
// 结果集数据
private List listData;
// 总条数
private Integer totalCount;
// 当前页
private Integer currentPage;
// 总页数
private Integer totalPage;
// 上一页
private Integer prePage;
// 下一页
private Integer nextPage;
// 页面大小
private Integer pageSize;
// 首页
private Integer beginPage = 1;
private PageIndex pageIndex;
public PageResult() {
}
public PageResult(List listData, Integer totalCount, Integer currentPage,
Integer pageSize) {
this.listData = listData;
this.totalCount = totalCount;
this.currentPage = currentPage;
this.pageSize = pageSize;
// 计算的数据
this.totalPage = this.totalCount % this.pageSize == 0 ? this.totalCount
/ this.pageSize : this.totalCount / this.pageSize + 1;
this.prePage = this.currentPage - 1 >= 1 ? this.currentPage - 1 : 1;
this.nextPage = this.currentPage + 1 <= this.totalPage ? this.currentPage + 1
: this.totalPage;
// 创建PageIndex的对象
this.pageIndex = PageIndex.getPageIndex(5, currentPage, totalPage);
}
}
// 分页算法对象
@Data
public class PageIndex {
private Integer beginIndex;
private Integer endIndex;
/**
* 首页 上页 [1] 2 3 4 5 6 7 8 9 10 下页 末页 当前第1/18页一共53条记录 每页5 条
* @param totalIndexCount 总索引数
* @param currentPage 当前页
* @param totalPage 总页数
* @return
*/
public static PageIndex getPageIndex(Integer totalIndexCount,
Integer currentPage, Integer totalPage) {
Integer startPageIndex = currentPage
- (totalIndexCount % 2 == 0 ? totalIndexCount / 2 - 1
: totalIndexCount / 2);
Integer endPageIndex = currentPage + totalIndexCount / 2;
if (startPageIndex < 1) {
startPageIndex = 1;
if (totalPage >= totalIndexCount)
endPageIndex = totalIndexCount;
else
endPageIndex = totalPage;
}
if (endPageIndex > totalPage) {
endPageIndex = totalPage;
if ((endPageIndex - totalIndexCount) > 0)
startPageIndex = endPageIndex - totalIndexCount + 1;
else
startPageIndex = 1;
}
return new PageIndex(startPageIndex, endPageIndex);
}
public PageIndex(Integer beginIndex, Integer endIndex) {
this.beginIndex = beginIndex;
this.endIndex = endIndex;
}
}
三.查询对象的封装
@Data
public class ProductQueryObject extends QueryObject {
private String productName;
private BigDecimal minSalePrice;
private BigDecimal maxSalePrice;
// 商品分类的编号
private Long dir_id;
// 关键字 商品的名称/品牌/供应商
private String keyword;
// 获取查询的条件和参数,自定义的查询方法
protected void customizedQuery() {
if (StringUtil.hasLength(productName)) {
super.addQuery(" productName LIKE ?", "%" + productName + "%");
}
if (minSalePrice != null) {
super.addQuery(" salePrice >=?", minSalePrice);
}
if (maxSalePrice != null) {
super.addQuery(" salePrice <=?", maxSalePrice);
}
if (dir_id != null && dir_id != -1L) {
super.addQuery(" dir_id =?", dir_id);
}
if (StringUtil.hasLength(keyword)) {
super.addQuery(
" productName LIKE ? OR brand LIKE ? OR supplier LIKE ?",
"%" + keyword + "%", "%" + keyword + "%", "%" + keyword
+ "%");
}
}
}
//公用的查询对象
@Data
public class QueryObject {
// 存放sql的条件
private List<String> conditions = new ArrayList<>();
// 存放sql中的参数的值
private List<Object> params = new ArrayList<>();
private Integer currentPage;;
private Integer pageSize;
// 拼接sql语句
public String getQuery() {
StringBuilder sb = new StringBuilder();
customizedQuery();
// 拼接sql
for (int i = 0; i < conditions.size(); i++) {
if (i == 0) {
sb.append(" WHERE ");
} else {
sb.append(" AND ");
}
sb.append(conditions.get(i));
}
return sb.toString();
}
// 专门暴露给子类实现
protected void customizedQuery() {
}
//将子类中的条件和参数传递给当前类中的对应的集合
protected void addQuery(String condition, Object... params) {
//当有多个条件的时候,应该在条件前后加上()
if(params.length>1){
this.conditions.add("("+condition+")");
}else{
this.conditions.add(condition);
}
this.params.addAll(Arrays.asList(params));
}
}
四.serverlet封装
@WebServlet("/product")
public class ProductServlet extends HttpServlet {
protected void list(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
ProductQueryObject qo = getProductQueryObject(req);
// 获取分页对象的数据
PageResult page = productDao.pageQuery(qo);
req.setAttribute("page", page);
req.setAttribute("qo", qo);
req.getRequestDispatcher("/WEB-INF/views/product/list.jsp").forward(
req, resp);
}
// 封装查询对象
private ProductQueryObject getProductQueryObject(HttpServletRequest req) {
// 获取请求参数
String productName = req.getParameter("productName");
String minSalePrice = req.getParameter("minSalePrice");
String maxSalePrice = req.getParameter("maxSalePrice");
String dir_id = req.getParameter("dir_id");
String keyword = req.getParameter("keyword");
String currentPage = req.getParameter("currentPage");
String pageSize = req.getParameter("pageSize");
// 默认的页面
Integer iCurrentPage = 1;
// 默认的页面大小
Integer iPageSize = 3;
if (StringUtil.hasLength(currentPage)) {
iCurrentPage = Integer.valueOf(currentPage);
}
if (StringUtil.hasLength(pageSize)) {
iPageSize = Integer.valueOf(pageSize);
}
// 封装查询对象
ProductQueryObject qo = new ProductQueryObject();
qo.setProductName(productName);
if (StringUtil.hasLength(minSalePrice)) {
qo.setMinSalePrice(new BigDecimal(minSalePrice));
}
if (StringUtil.hasLength(maxSalePrice)) {
qo.setMaxSalePrice(new BigDecimal(maxSalePrice));
}
if (StringUtil.hasLength(dir_id)) {
qo.setDir_id(Long.valueOf(dir_id));
}
if (StringUtil.hasLength(keyword)) {
qo.setKeyword(keyword);
}
qo.setCurrentPage(iCurrentPage);
qo.setPageSize(iPageSize);
return qo;
}
}
五.dao查询的封装
public class BaseDAO {
public PageResult query(QueryObject qo,String tableName,Class clz) {
// 获取商品的结果集
String sql = "SELECT * FROM "+tableName+" " + qo.getQuery() + " limit ?,?";
// 获取高级查询的参数
List<Object> params = qo.getParams();
Integer beginIndex = (qo.getCurrentPage() - 1) * qo.getPageSize();
// 重新创建一个新的集合,用来存放高级查询的数据和分页的数据
List<Object> newParams = new ArrayList<>();
newParams.addAll(params);
newParams.add(beginIndex);
newParams.add(qo.getPageSize());
List listData = JdbcTemplate.query(sql,
new BeanListHandler(clz),
newParams.toArray());
// 获取总条数
Integer totalCount = JdbcTemplate.query(
"Select count(*) FROM "+tableName+" "+ qo.getQuery(),
new IResultSetHandler<Long>() {
@Override
public Long handle(ResultSet rs) {
try {
if (rs.next()) {
return rs.getLong(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return 0L;
}
}, qo.getParams().toArray()).intValue();
PageResult result = new PageResult(listData, totalCount,
qo.getCurrentPage(), qo.getPageSize());
return result;
}
}
// 商品类的规范
public interface IProductDAO {
/**
* 高级查询+分页
* @param qo 高级查询的数据和分页的数据
* @return 返回分页之后的结果
*/
PageResult pageQuery(ProductQueryObject qo);
}
public class ProductDAOImpl extends BaseDAO implements IProductDAO {
@Override
public PageResult pageQuery(ProductQueryObject qo) {
return super.query(qo,"product",Product.class);
}
}
六.前端的页面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>货品显示列表</title>
<!-- 新 Bootstrap 核心 CSS 文件 -->
<link rel="stylesheet"
href="/bootstrap/bootstrap.min.css">
<!-- 可选的Bootstrap主题文件(一般不用引入) -->
<link rel="stylesheet"
href="/bootstrap/bootstrap-theme.min.css">
<!-- jQuery文件。务必在bootstrap.min.js 之前引入 -->
<script src="/bootstrap/jquery.min.js"></script>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="/bootstrap/bootstrap.min.js"></script>
<script type="text/javascript">
function go(currentPage) {
//将当前页的数据设置给currentPage隐藏域
document.getElementById("currentPage").value = currentPage;
//提交表单
document.forms[0].submit();
}
</script>
</head>
<body>
<marquee direction="right" >
<a href="${pageContext.request.contextPath}/product?cmd=edit">添加货品</a>
</marquee>
<!-- 高级查询 开始-->
<form action="${pageContext.request.contextPath}/product" method="post">
<input name="currentPage" id="currentPage" value="" type="hidden" />
商品的名称:<input name="productName" value="${qo.productName}" /> 商品零售价:<input
type="number" name="minSalePrice" value="${qo.minSalePrice}" /> -<input
type="number" name="maxSalePrice" value="${qo.maxSalePrice}" />
商品分类:<select name="dir_id">
<option value='-1'>..请选择...</option>
<c:forEach items="${dirList}" var="dir">
<option value='${dir.id}'
${qo.dir_id==dir.id?'selected=selected':''}>${dir.dirName}</option>
</c:forEach>
</select> 关键字:<input name="keyword" placeholder="名称/品牌/供应商"
value="${qo.keyword}" /> <input type="submit" value="查询" /> <input
type="reset" value="重置" />
</form>
<!-- 高级查询 结束-->
<form action="${pageContext.request.contextPath}/product" method="post" id="form2">
<table border="1" cellpadding="0" cellspacing="0" width="90%">
<tr>
<th>货品编号</th>
<th>货品名称</th>
<th>货品品牌</th>
<th>货品分类</th>
<th>供 应 商</th>
<th>零 售 价</th>
<th>成 本 价</th>
<th>折  扣</th>
<th>操  作</th>
</tr>
<c:if test="${empty page.listData}">
<tr>
<td colspan="9" style="text-align: center; color: red;">亲,没有查询到您需要的数据~~~</td>
</tr>
</c:if>
<c:forEach items="${page.listData}" var="pro" varStatus="vs">
<tr>
<td>${pro.id}</td>
<td>${pro.productName}</td>
<td>${pro.brand}</td>
<td>${pro.dir_id}</td>
<td>${pro.supplier}</td>
<td>${pro.salePrice}</td>
<td>${pro.costPrice}</td>
<td>${pro.cutoff}</td>
<td><a href="/product?cmd=edit&id=${pro.id}">编辑</a> | <a
href="${pageContext.request.contextPath}/product?cmd=delete&id=${pro.id}">删除</a>
</td>
</tr>
</c:forEach>
<tr>
<td colspan="9" >
<a href="${pageContext.request.contextPath}/product?currentPage=1" >首页</a>
<a href="javascript:go(${page.prePage })" >上一页</a>
<c:forEach begin="${page.pageIndex.beginIndex }" end="${page.pageIndex.endIndex }" var="index" >
<c:if test="${page.currentPage == index }">
${index }
</c:if>
<c:if test="${page.currentPage != index }">
<a href="${pageContext.request.contextPath}/product?currentPage=${index }">${index }</a>
</c:if>
</c:forEach>
<a href="javascript:go(${page.nextPage })" >下一页</a>
<a href="${pageContext.request.contextPath}/product?currentPage=${page.totalPage }" >尾页</a>
总条数:${ page.totalCount}
当前页:${ page.currentPage}/${ page.totalPage}
<input type="number" name="currentPage" style="width:50px" min="1" max="${page.totalPage}" value="${ page.currentPage}" />
<input type="submit" value="go" />
每页显示<select name="pageSize" onchange="document.getElementById('form2').submit()" >
<option ${page.pageSize==5?'selected=selected':'' } >5</option>
<option ${page.pageSize==10?'selected=selected':'' }>10</option>
<option ${page.pageSize==15?'selected=selected':'' }>15</option>
</select>条
</td>
</tr>
</table>
</form>
</body>
</html>