web开发中的分页查询

在web开发中，分页查询是一种常用的功能

首先，需要对SQL分页进行一些了解

select * from 表名 limit ?,?;
参数1：开始索引位置，默认值为0。必须是正数
参数2：每页显示的条数pageSize

开始索引的计算公式为：index = （当前页-1）  * 每页显示条数；

在开始进行代码的编写前，需要编写一个PageBean 来封装分页所需要的信息

package com.vo;

import java.util.ArrayList;
import java.util.List;
//增加泛型，保证代码的可重复使用
public class PageBean<T> {
	//当前页
	private int currentPage;
	//当前页显示条数
	private int currentCount;
	//总条数
	private int totalCount;
	//总页数
	private int totalPage;
	//当前页商品列表
	private List<T> productList = new ArrayList<>();
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getCurrentCount() {
		return currentCount;
	}
	public void setCurrentCount(int currentCount) {
		this.currentCount = currentCount;
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}
	public int getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	public List<T> getProductList() {
		return productList;
	}
	public void setProductList(List<T> productList) {
		this.productList = productList;
	}
	
}

在ProductServlet的doget方法中获取前台传递的数据，并传递到service层进行业务处理

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String currentPageStr = request.getParameter("currentPage");.//获取前台传递来的参数
		if (currentPageStr==null) {	
			currentPageStr = "1"; //设置默认值为1，否则将会报错
		}
		//当前页
		int currentPage =Integer.parseInt(currentPageStr);
		//每页显示条数，本例中固定为每页显示12条
		int currentCount = 12;
	
		ProductListService service = new ProductListService();
		PageBean<Product> pageBean = null;
		try {
			pageBean = service.findPageBean(currentPage,currentCount);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		request.setAttribute("pageBean", pageBean);
		request.getRequestDispatcher("/product_list.jsp").forward(request, response);
		
	}

service层代码如下所示

       

	public PageBean<Product> findPageBean(int currentPage,int currentCount) throws SQLException {
		ProductListDao dao = new ProductListDao();
		PageBean<Product> pageBean = new PageBean<Product>();	
		//当前页private int currentPage;
		pageBean.setCurrentPage(currentPage);		
		//当前页显示条数private int currentCount;
		pageBean.setCurrentCount(currentCount);
		//总条数private int totalCount;
		int totalCount = dao.getTotalCount();
		pageBean.setTotalCount(totalCount);
		//总页数private int totalPage;

		//总页数计算公式  总页数 = 总条数 / 每页显示条数  并向上取整

		int totalPage = (int) Math.ceil(1.0*totalCount/currentCount);
		pageBean.setTotalPage(totalPage);
		//当前页商品列表private List<T> productList = new ArrayList<>();
		List<Product> productList = new ArrayList<>();

		//index 为数据的起始索引位置 
		int index  = (currentPage-1)* currentCount;

		productList = dao.findProductByPageBean(index,currentCount);

		pageBean.setProductList(productList);
		return pageBean;
	}

在service中封装好pageBean对象并将其返回web层中。

其中总条数、总页数、当前页商品列表需要在dao层中获取

	public int getTotalCount() throws SQLException {
		QueryRunner qRunner =new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from product";
		Long query = (Long)qRunner.query(sql, new ScalarHandler());//count(*) 的返回值类型为长整形，需要使用Long来接收
		return query.intValue();//使用内置方法将其转换为int型
	}

	public List<Product> findProductByPageBean(int index, int currentCount) throws SQLException {
		QueryRunner qRunner =new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from product limit ?,?";
		return qRunner.query(sql, new BeanListHandler<Product>(Product.class),index,currentCount);
	}

到此，分页的后台代码就实现了

前台的代码实现如下

		<c:forEach items="${pageBean.productList }" var="product">
		
			<div class="col-md-2">
				<a href="product_info.htm"> <img src="${pageContext.request.contextPath }/${product.pimage}"
					width="170" height="170" style="display: inline-block;">
				</a>
				<p>
					<a href="product_info.html" style='color: green'>${product.pname }</a>
				</p>
				<p>
					<font color="#FF0000">商城价：¥${product.shop_price }</font>
				</p>
			</div>
		</c:forEach>

使用jstl对后台传递的pageBean对象进行遍历，显示商品的具体信息

               分页

	<!--分页 -->
	<div style="width: 380px; margin: 0 auto; margin-top: 50px;">
		<ul class="pagination" style="text-align: center; margin-top: 10px;">
			<c:if test="${pageBean.currentPage==1 }">
				<li class="disabled">
					<a href="javascript:void(0);" aria-label="Previous">
						<span aria-hidden="true">«</span>
					</a>
				</li>
			</c:if>
			<c:if test="${pageBean.currentPage!=1 }">
				<li>
					<a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage-1 }" aria-label="Previous">
						<span aria-hidden="true">«</span>
					</a>
				</li>
			</c:if>
			<c:forEach begin="1" end="${pageBean.totalPage }" var="page">
				<c:if test="${pageBean.currentPage==page }">
					<li class="active"><a href="${pageContext.request.contextPath }/productList?currentPage=${page }">${page }</a></li>
				</c:if>
				<c:if test="${pageBean.currentPage!=page }">
					<li><a href="${pageContext.request.contextPath }/productList?currentPage=${page }">${page }</a></li>
				</c:if>
			</c:forEach>			
			<c:if test="${pageBean.currentPage==pageBean.totalPage }">
				<li class="disabled">
					<a href="javascript:void(0);" aria-label="Next"> 
						<span aria-hidden="true">»</span>
					</a>
				</li>
			</c:if>
			<c:if test="${pageBean.currentPage!=pageBean.totalPage }">
				<li>
					<a href="${pageContext.request.contextPath }/productList?currentPage=${pageBean.currentPage+1 }" aria-label="Next"> 
						<span aria-hidden="true">»</span>
					</a>
				</li>
			</c:if>
			
		</ul>
	</div>
	<!-- 分页结束 -->

   

     最终显示效果如下