关于分页的逻辑如图所示
实现步骤如下
1、相关环境准备
a) 引入jar文件及引入配置文件
i. 数据库驱动包
ii. C3P0连接池jar文件 及 配置文件
iii. DbUtis组件: QueryRunner qr = new QueryRuner(dataSouce);
b) 公用类: C3P0Utils.java
package gqx.shoppingcar.util;import javax.activation.DataSource;import org.apache.commons.dbutils.QueryRunner;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Util {//1、初始化C3P0连接池private static ComboPooledDataSource dataSource;static{dataSource=new ComboPooledDataSource();}public static QueryRunner getQueryRunner(){//创建queryRunner对象//创建QueryRunner对象的时候，如果传入了数据源对象//那么在使用QueryRunner对象的的时候，就不需要传入连接对象了//会自动从数据源中获取连接（所以不用我们自己来手动关闭连接）return new QueryRunner(dataSource);}}
对应的的配置文件
<c3p0-config><default-config><property name="jdbcUrl">jdbc:sqlserver://localhost:1433;DataBaseName=Test</property> <property name="driverClass">com.microsoft.sqlserver.jdbc.SQLServerDriver</property><property name="user">sa</property><property name="password">123456</property><property name="maxIdleTime">3000</property><property name="maxPoolSize">6</property><property name="initialPoolSize">3</property></default-config><named-config name="mysqlConfig"><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_demo</property><property name="driverClass">com.mysql.jdbc.Driver</property><property name="user">root</property><property name="password">root</property><property name="initialPoolSize">3</property><property name="maxPoolSize">6</property><property name="maxIdleTime">1000</property></named-config>
</c3p0-config>
2、先设计：PageBean.java
package gqx.shoppingcar.entity;import java.util.List;public class PageBean<T> {private int currentPage=1; //当前页，默认显示第一页private int pageCount=4; //每页显示的行数private int totalCount; //总记录数private int totalPage; //总页数=总记录数private List<T> pageDataList; //分页查到的数据//返回总页数public int getTotalPage() {if (totalCount%pageCount==0) {totalPage=totalCount/pageCount;}else {totalPage=totalCount/pageCount+1;}return totalPage;}public void setTotalPage(int totalPage) {this.totalPage = totalPage;}public int getCurrentPage() {return currentPage;}public void setCurrentPage(int currentPage) {this.currentPage = currentPage;}public int getPageCount() {return pageCount;}public void setPageCount(int pageCount) {this.pageCount = pageCount;}public int getTotalCount() {return totalCount;}public void setTotalCount(int totalCount) {this.totalCount = totalCount;}public List<T> getPageDataList() {return pageDataList;}public void setPageDataList(List<T> pageDataList) {this.pageDataList = pageDataList;}@Overridepublic String toString() {return "PageBean [currentPage=" + currentPage + ", pageCount="+ pageCount + ", totalCount=" + totalCount + ", totalPage="+ totalPage + ", pageDataList=" + pageDataList + "]";}}
显示产品的javaBean
package gqx.shoppingcar.entity;public class Product {private int ID;private String name;private float price;private int number;private String message;public int getID() {return ID;}public void setID(int iD) {ID = iD;}public String getName() {return name;}public void setName(String name) {this.name = name;}public float getPrice() {return price;}public void setPrice(float price) {this.price = price;}public int getNumber() {return number;}public void setNumber(int number) {this.number = number;}public String getMessage() {return message;}public void setMessage(String message) {this.message = message;}public Product(int iD, String name, float price, int number, String message) {super();ID = iD;this.name = name;this.price = price;this.number = number;this.message = message;}public Product() {super();// TODO Auto-generated constructor stub}@Overridepublic String toString() {return "Product [ID=" + ID + ", name=" + name + ", price=" + price+ ", number=" + number + ", message=" + message + "]";}}
3、Dao接口设计/实现：
接口：
package gqx.shoppingcar.dao;import gqx.shoppingcar.entity.PageBean;
import gqx.shoppingcar.entity.Product;public interface PageDao {/***页数处理***///分页查询数据public void getAllPage(PageBean<Product> pbBean);//查询总记录数public int getTotalCount();
}
实现
package gqx.shoppingcar.dao.impl;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;import gqx.shoppingcar.dao.PageDao;
import gqx.shoppingcar.entity.PageBean;
import gqx.shoppingcar.entity.Product;
import gqx.shoppingcar.util.C3P0Util;
import gqx.shoppingcar.util.JDBCUtil;public class PageOperate implements PageDao {@Overridepublic void getAllPage(PageBean pbBean) {//1、获取当前页数int currentPage=pbBean.getCurrentPage();//查询返回的行数int count=pbBean.getPageCount();//2、查询总记录数，设计到pb对象中int totalCount=this.getTotalCount();pbBean.setTotalCount(totalCount);//3、分页查询数据；当查询到的数据放到pb对象中String sql="SELECT TOP "+count+" * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber, * FROM product ) A WHERE RowNumber > "+count+"*("+currentPage+"-1)";//得到QueryRunner对象QueryRunner qr=C3P0Util.getQueryRunner();//根据当前页面，查询当前数据try {List<Product> pageData=qr.query(sql, new BeanListHandler<Product>(Product.class));//设置到pageBean中pbBean.setPageDataList(pageData);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}/*Connection connection=JDBCUtil.getConnection();PreparedStatement statement=null;ResultSet result=null;List<Product> list=new ArrayList<Product>();try {statement=connection.prepareStatement(sql);result=statement.executeQuery();while(result.next()){Product product =new Product();product.setID(result.getInt("ID"));product.setName(result.getString("name"));product.setPrice(result.getFloat("price"));product.setNumber(result.getInt("number"));product.setMessage(result.getString("message"));list.add(product); }pbBean.setPageDataList(list);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}*/}@Overridepublic int getTotalCount() {String sql="select count(*) from product";try {QueryRunner qr=C3P0Util.getQueryRunner();int count=qr.query(sql, new ScalarHandler<Integer>());return count;} catch (SQLException e) {// TODO Auto-generated catch blockthrow new RuntimeException();}}}
4、Service/servlet的完成
接口及对应的实现
//分页查询数据（接口中的抽象方法）public void getAll(PageBean<Product> pBean);public int getTotalCount();/*****分页处理（接口的实现）****/PageOperate pageOperate=new PageOperate();@Overridepublic void getAll(PageBean<Product> pBean) {// TODO Auto-generated method stubtry {pageOperate.getAllPage(pBean);} catch (Exception e) {throw new RuntimeException(e);}}@Overridepublic int getTotalCount() {// TODO Auto-generated method stubreturn 0;}
对应的servlet
package gqx.shoppingcar.servlet;import gqx.shoppingcar.entity.PageBean;
import gqx.shoppingcar.entity.Product;
import gqx.shoppingcar.service.ProductService;
import gqx.shoppingcar.service.impl.SerOperate;import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;import javax.enterprise.context.spi.Context;
import javax.jms.Session;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;public class IndexServlet extends HttpServlet {public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {HttpSession session=request.getSession();//判断是否是注册进来的Object object=request.getAttribute("status");if (object!=null) {request.getRequestDispatcher("success.jsp").forward(request, response);return;}ProductService operate=new SerOperate();String name=request.getParameter("username");if (name!=null) {name=new String(name.getBytes("iso-8859-1"),"utf-8");String password=request.getParameter("password");int result=operate.test(name, password);if (result>0) {List<Product> list=operate.allPro(result);session.setAttribute("userproducts", list);session.setAttribute("name", name);session.setAttribute("id", result);}}String currPage = request.getParameter("currentPage");System.out.println(currPage+"kkk");if (currPage == null || "".equals(currPage.trim())){currPage = "1"; // 第一次访问，设置当前页为1;}int currentPage = Integer.parseInt(currPage);//2. 创建PageBean对象，设置当前页参数； 传入service方法参数PageBean<Product> pageBean = new PageBean<Product>();System.out.println(currentPage);System.out.println(pageBean.getTotalPage()+"pageBean.getTotalPage()");if(currentPage<1){pageBean.setCurrentPage(pageBean.getTotalPage());}else {pageBean.setCurrentPage(currentPage);}operate.getAll(pageBean);List<Product> list=pageBean.getPageDataList();session.setAttribute("pageBean", pageBean);response.sendRedirect(request.getContextPath()+"/index.jsp");}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {this.doGet(request, response);}}
5、jsp页面
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="gqx.shoppingcar.entity.Product,gqx.shoppingcar.entity.PageBean"%>
<%@ page language="java" import="java.util.*,gqx.shoppingcar.service.impl.SerOperate" pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<% SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");String time =format.format(new Date());pageContext.setAttribute("time", time);%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<% SerOperate operate=new SerOperate();List<Product> list=operate.Shop(); pageContext.setAttribute("allproducts", list);
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head><base href="<%=basePath%>"><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><title>欢迎来到网上商城</title></head><style>td{text-align: center;}</style><body><center><h2>网上商城</h2><p>今天是：${time}</p><h4>欢迎您的到来<c:choose><c:when test="${!empty sessionScope.name}">：${sessionScope.name }，要不去<strong><a href="${pageContext.request.contextPath }/UserServlet?id=${sessionScope.id}">我的购物车</a></strong>瞧一瞧<a href="${pageContext.request.contextPath }/login.html">退出</a></c:when><c:otherwise>：亲，您还未登入，请先去<a href="${pageContext.request.contextPath }/login.html">登入</a></c:otherwise></c:choose></h4><hr><table border="1"><caption>商品列表</caption><thead><tr><th>商品编号</th><th>商品名称</th><th>商品价格</th><th>商品数量</th><th style="width:500px">商品信息</th><th>操作</th></tr></thead><c:if test="${empty sessionScope.pageBean}"><% PageBean<Product> pageBean = new PageBean<Product>();pageBean.setCurrentPage(1);operate.getAll(pageBean);out.print(pageBean.getTotalPage());session.setAttribute("pageBean",pageBean);%> </c:if><tbody><c:choose><c:when test="${not empty sessionScope.pageBean.pageDataList}"><c:forEach items="${sessionScope.pageBean.pageDataList}" var="product" varStatus="varStar"><tr><td>${product.ID }</td><td>${product.name }</td><td>${product.price }</td><td>${product.number }</td><td style="width:500px">${product.message }</td><td><c:if test="${!empty sessionScope.name}"><a href="${pageContext.request.contextPath }/BuyServlet?pid=${product.ID }" οnclick="tell()">购买</a></c:if><c:if test="${empty sessionScope.name}"><a href="javascript:remind()">购买</a></c:if></td></tr><tr> </tr></c:forEach></c:when><c:otherwise><tr><td colspan="3">对不起，没有你要找的数据</td></tr></c:otherwise></c:choose><tr><td colspan="6" align="center">当前${sessionScope.pageBean.currentPage }/${sessionScope.pageBean.totalPage }页 <a href="${pageContext.request.contextPath }/IndexServlet?currentPage=1">首页</a><a href="${pageContext.request.contextPath }/IndexServlet?currentPage=<c:choose><c:when test="${sessionScope.pageBean.currentPage==1}">${sessionScope.pageBean.totalPage}</c:when><c:otherwise>${sessionScope.pageBean.currentPage-1}</c:otherwise></c:choose>">上一页 </a><a href="${pageContext.request.contextPath }/IndexServlet?currentPage=<c:choose><c:when test="${sessionScope.pageBean.currentPage==sessionScope.pageBean.totalPage}">1</c:when><c:otherwise>${sessionScope.pageBean.currentPage+1}</c:otherwise></c:choose>">下一页 </a><a href="${pageContext.request.contextPath }/IndexServlet?currentPage=${sessionScope.pageBean.totalPage}">末页</a></td></tr></tbody></table></center><script type="text/javascript">function remind(){alert("亲，你还没有登入");}function tell(){alert("哈哈，已成功加入了购物车啦。")}</script></body>
</html>
实现的效果如图
