java 分页查询

超、凢脫俗 2022-05-15 22:40 209阅读 0赞

**一.概述**

在上一篇高级查询中,已经对查询对象进行了封装,在本篇文章中,主要来分析分页的封装和分页和高级查询的合并.

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>
    
    
    <link rel="stylesheet"
    	href="/bootstrap/bootstrap.min.css">
    
    
    <link rel="stylesheet"
    	href="/bootstrap/bootstrap-theme.min.css">
    
    
    <script src="/bootstrap/jquery.min.js"></script>
    
    
    <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>