分页技术的实现

 

关于分页的逻辑如图所示

---

 实现步骤如下

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;
	}

	@Override
	public 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
	}
	@Override
	public 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 {

	@Override
	public 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 block
			e.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 block
			e.printStackTrace();
		}*/
	}

	@Override
	public 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 block
			throw new RuntimeException();
		}
	
	}

}

4、Service/servlet的完成

接口及对应的实现

//分页查询数据（接口中的抽象方法）
		public void getAll(PageBean<Product> pBean);
		public int getTotalCount();


/*****分页处理（接口的实现）****/
	PageOperate pageOperate=new PageOperate();
	@Override
	public void getAll(PageBean<Product> pBean) {
		// TODO Auto-generated method stub
		try {
			pageOperate.getAllPage(pBean);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public int getTotalCount() {
		// TODO Auto-generated method stub
		return 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 }" onclick="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>

　　

　　

实现的效果如图