分页查询实现
关于分页的逻辑如图所示
实现步骤如下
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>
实现的效果如图
扫一扫
5289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
