数据分页
控制台版本的分页
封装pageresult对象
实行后台数据分页设计
实现前台数据分页原理
高级查询加数据分页
解决高级查询翻页后数据丢失的问题
详细代码：
前台代码：
<%@ 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 PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function go(pageno){
document.getElementById("currentpage").value=pageno;
document.forms[0].submit();
}
</script>
</head>
<body>
<h1>商品信息</h1>
<form action="/product?cmd=list" method="post">
商品名称：<input type="text" name="name" value="${go.productName }" /><br>
商品价格：<input type="nmuber" name="minsale" value="${go.minsale }"/>-<input type="nmuber" name="maxsale" value="${go.maxsale }"/><br>
<select name="yaoshi">
<option value="-1">所有分类</option>
<c:forEach items="${dirdao }" var="item">
<option value="${item.id }" ${item.id==go.dirId?"selected":"" }>${item.dirName }</option>
</c:forEach>
</select>
关键字查询：<input type="text" name="keyword" value="${go.keyword}" /><br>
<input type="submit" value="查询"/>
<br>
<a href="/product?cmd=edit">添加商品</a>
<table cellpadding="0" cellspacing="0" border="1" width="80%">
<tr style="background-color:orange;">
<th>货品编号</th>
<th>货品名称</th>
<th>货品品牌</th>
<th>货品分类</th>
<th>供应商</th>
<th>零售价</th>
<th>成本价</th>
<th>折扣</th>
<th>操作</th>
</tr>
<c:forEach items="${pageresult.data}" var="item" varStatus="vs">
<tr style="background-color:${vs.count%2==0?'pink':''};">
<td>${item.id}</td>
<td>${item.productName}</td>
<td>${item.brand}</td>
<td>
<c:choose>
<c:when test="${item.dir_id==1}">
鼠标
</c:when>
<c:when test="${item.dir_id==2}">
无线鼠标
</c:when>
<c:when test="${item.dir_id==3}">
有线鼠标
</c:when>
<c:when test="${item.dir_id==4}">
游戏鼠标
</c:when>
<c:when test="${item.dir_id==5}">
机械鼠标
</c:when>
</c:choose>
</td>
<td>${item.supplier}</td>
<td>${item.salePrice}</td>
<td>${item.costPrice}</td>
<td>${item.cutoff}</td>
<td style="align-content: center;"><a href="/product?cmd=delete&id=${item.id}">删除</a>||<a href="/product?cmd=edit&id=${item.id}">编辑</a></td>
</c:forEach>
</tr>
<tr>
<td colspan="9" align="center">
<a href="javascript:go(1);">首页</a>
<a href="javascript:go(${pageresult.lastpage });">上一页</a>
<c:forEach begin="${pageresult.beginIndex }" end="${pageresult.endIndex }" var="pagenumber">
<c:if test="${pagenumber!=pageresult.currentpage }">
<a href="javascript:go(${pagenumber });">${pagenumber }</a>
</c:if>
<c:if test="${pagenumber==pageresult.currentpage }">
<span style="color:red;font:italic bold;">
${pagenumber }
</span>
</c:if>
</c:forEach>
<a href="javascript:go(${pageresult.nextpage });">下一页</a>
<a href="javascript:go(${pageresult.endpage });">尾页</a>
当前第${pageresult.currentpage }页，一共${pageresult.endpage }页,共${pageresult.totalcount }数据
跳转到第<input name="currentpage" id="currentpage" type="number" min="1" max="${pageresult.endpage }" value="${pageresult.currentpage }"/>页
<input type="button" value="GO" onclick="go();"/>
每页
<select name="page" onchange="go();">
<c:forEach items="${pageresult.pagedata}" var="item">
<option ${item==pageresult.pagesize?"selected":"" }>${item }</option>
</c:forEach>
</select>
条数据
</td>
</tr>
</table>
</form>
</body>
</html>
分页类-PageIndex类
package product.productquery.page;
/**
* 页码的开始索引和接受索引
*/
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;
}
public Integer getBeginIndex() {
return beginIndex;
}
public Integer getEndIndex() {
return endIndex;
}
}
分页基本数据类
package product.productquery.page;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import lombok.Data;
import lombok.Getter;
@Getter
public class pageresult {
private List<?>data;
private Integer totalcount;
private Integer currentpage=1;
private Integer pagesize=5;
private Integer beginpage=1;
private Integer endpage;
private Integer lastpage;
private Integer nextpage;
private Integer beginIndex;
private Integer endIndex;
private List<Integer>pagedata=Arrays.asList(3,5,6,9,10);
public pageresult(List<?> data, Integer totalcount, Integer currentpage, Integer pagesize) {
this.data = data;
this.totalcount = totalcount;
this.currentpage = currentpage;
this.pagesize = pagesize;
endpage=this.totalcount%this.pagesize==0? this.totalcount/this.pagesize:this.totalcount/this.pagesize+1;
lastpage=currentpage-1>=1?currentpage-1:1;
nextpage=currentpage+1<=endpage?currentpage+1:endpage;
PageIndex index=PageIndex.getPageIndex(3, currentpage, endpage);
beginIndex=index.getBeginIndex();
endIndex=index.getEndIndex();
}
}
productquery类
package product.productquery;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
@Data
public class productquery extends queryobject{
private String productName;
private BigDecimal minsale;
private BigDecimal maxsale;
private Long dirId=-1L;
private String keyword;
public Boolean haslength(String value) {
return value!=null&&!"".equals(value);
}
public void custerize()
{
if(haslength(productName))
{
/*
connection.add(" productName like ? ");
para.add("%"+productName+"%");*/
super.addquery(" productName like ? ", "%"+productName+"%");
}
if(haslength(keyword))
{
/*
connection.add(" productName like ? ");
para.add("%"+productName+"%");*/
super.addquery(" (productName like ? OR brand like ?) ", "%"+keyword+"%","%"+keyword+"%");
}
if(minsale!=null)
{/*
connection.add(" salePrice >= ? ");
para.add(minsale);*/
super.addquery(" salePrice >= ? ", minsale);
}
if(maxsale!=null)
{
//connection.add(" salePrice <= ?");
//para.add(maxsale);
super.addquery(" salePrice <= ? ", maxsale);
}
if(dirId!=-1)
{
//connection.add(" dir_id= ?");
//para.add(dirId);
super.addquery(" dir_id= ?", dirId);
}
}
}
抽取类
package product.productquery;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import lombok.Getter;
import lombok.Setter;
public class queryobject {
private Integer currentpage=1;
private Integer pagesize=5;
private List<Object> para=new ArrayList<>();
private List<String> connection=new ArrayList<>();
public String getquerystring()
{
StringBuilder sql=new StringBuilder(100);
this.custerize();
if(connection.size()==0)
{
return "";
}
String query=StringUtils.join(connection," AND ");
return sql.append(" WHERE ").append(query).toString();
}
public void custerize() {
// TODO Auto-generated method stub
}
public List<Object>getpara(){
return para;
}
public void addquery(String connection,Object...param) {
this.connection.add(connection);
this.para.addAll(Arrays.asList(param));
}
}
servlet
package product.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.List;
import java.util.function.Supplier;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import product.DAO.ProductImpl;
import product.DAO.ProductdirImpl;
import product.DAO.Product.IProduct;
import product.DAO.Product.IProductdir;
import product.domain.Productach;
import product.productquery.productquery;
import product.productquery.page.pageresult;
@WebServlet("/product")
public class servlet extends HttpServlet{
private IProduct dao;
private IProductdir dirdao;
private static final long serialVersionUID = 1L;
@Override
public void init() throws ServletException {
// TODO Auto-generated method stub
dao=new ProductImpl();
dirdao=new ProductdirImpl();
}
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
req.setCharacterEncoding("UTF-8");
String cmd=req.getParameter("cmd");
switch (cmd) {
case "edit":
edit(req, resp);
break;
case "delete":
delete(req, resp);
break;
case "saveORupdate":
saveORupdate(req, resp);
break;
case "list":
list(req, resp);
break;
default:
list(req, resp);
break;
}
}
public void list(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
productquery go=new productquery();
this.parainit(req,go);
req.setAttribute("go", go);
//List<Productach> list=dao.search(go);
pageresult pageresult=dao.resudesign(go);
req.setAttribute("pageresult", pageresult);
req.setAttribute("dirdao", dirdao.list());
req.setAttribute("go", go);
req.getRequestDispatcher("/WEB-INF/view/product/product.jsp").forward(req, resp);
}
private void parainit(HttpServletRequest req,productquery go) {
try {
req.setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String name=req.getParameter("name");
String minsale=req.getParameter("minsale");
String maxsale=req.getParameter("maxsale");
String sele=req.getParameter("yaoshi");
String keyword=req.getParameter("keyword");
String currpage=req.getParameter("currentpage");
String psize=req.getParameter("page");
if(currpage!=null&&!"".equals(currpage)) {
go.setCurrentpage(Integer.valueOf(currpage));
}
if(psize!=null&&!"".equals(psize)) {
go.setPagesize(Integer.valueOf(psize));
}
if(haslength(name))
{
go.setProductName(name);
}
if(minsale!=null&&!"".equals(minsale))
{
go.setMinsale(new BigDecimal(minsale));
}
if(maxsale!=null&&!"".equals(maxsale))
{
go.setMaxsale(new BigDecimal(maxsale));
}
if(sele!=null&&!"".equals(sele))
{
go.setDirId(Long.valueOf(sele));
}
if(keyword!=null&&!"".equals(keyword))
{
go.setKeyword(keyword);
}
}
public Boolean haslength(String value) {
return value!=null&&!"".equals(value);
}
public void saveORupdate(HttpServletRequest req, HttpServletResponse resp) throws IOException {
// TODO Auto-generated method stub
String id=req.getParameter("num");
String productName=req.getParameter("name");
String brand=req.getParameter("brand");
String kind=req.getParameter("kind");
String supplier=req.getParameter("supply");
String costsale=req.getParameter("costsale");
String sale=req.getParameter("sale");
String cost=req.getParameter("cost");
ProductImpl dao=new ProductImpl();
Productach pro=new Productach(0,productName,Long.valueOf(kind),Double.valueOf(sale),
supplier,brand,Double.valueOf(costsale),
Double.valueOf(cost));
if(id!=null&&!"".equals(id))
{
dao.update(Long.valueOf(id), pro);
}else {
dao.insert(pro);
}
resp.sendRedirect("/product?cmd=list");
}
public void delete(HttpServletRequest req, HttpServletResponse resp) {
// TODO Auto-generated method stub
String id=req.getParameter("id");
dao.delete(Long.valueOf(id));
try {
resp.sendRedirect("/product?cmd=list");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void edit(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
String id=req.getParameter("id");
if(id!=null&&!"".equals(id))
{
Productach pro=dao.getone(Long.valueOf(id));
req.setAttribute("pro",pro);
}
req.getRequestDispatcher("/WEB-INF/view/product/saveORupdate.jsp").forward(req, resp);
}
}
接口
package product.DAO.Product;
import java.math.BigDecimal;
import java.util.List;
import product.domain.Productach;
import product.productquery.productquery;
import product.productquery.page.pageresult;
public interface IProduct {
void insert(Productach pro);
void delete(long id);
void update(long id,Productach pro);
Productach getone(long id);
List<Productach> list();
List<Productach> search(productquery go);
pageresult query(Integer currentpage,Integer pagesize);
pageresult resudesign(productquery go);
}
实现类
package product.DAO;
import java.math.BigDecimal;
import java.security.GuardedObject;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.druid.sql.dialect.oracle.ast.clause.ModelClause.ReturnRowsClause;
import product.DAO.Product.IProduct;
import product.domain.Productach;
import product.javabean.bean;
import product.javabean.beanlist;
import product.javabean.type;
import product.productquery.productquery;
import product.productquery.page.pageresult;
import product.template.template;
public class ProductImpl implements IProduct{
@Override
public void insert(Productach pro) {
// TODO Auto-generated method stub
String sql="INSERT INTO product(productName,dir_id,salePrice,supplier,brand,cutoff,costPrice)VALUES(?,?,?,?,?,?,?)";
template.DMLoperate(sql, pro.getProductName(),pro.getDir_id(),pro.getSalePrice(),pro.getSupplier(),pro.getBrand(),pro.getCutoff(),pro.getCostPrice());
}
@Override
public void delete(long id) {
// TODO Auto-generated method stub
String sql="DELETE FROM product where id=?";
template.DMLoperate(sql, id);
}
@Override
public void update(long id, Productach pro) {
// TODO Auto-generated method stub
String sql="UPDATE product set productName=?,dir_id=?,salePrice=?,supplier=?,brand=?,cutoff=?,costPrice=? where id=?";
template.DMLoperate(sql, pro.getProductName(),pro.getDir_id(),pro.getSalePrice(),pro.getSupplier(),pro.getBrand(),pro.getCutoff(),pro.getCostPrice(),id);
}
@Override
public Productach getone(long id) {
// TODO Auto-generated method stub
String sql="SELECT *FROM product where id=?";
return template.DQLoperator(sql, new bean<>(Productach.class), id);
}
@Override
public List<Productach> list() {
// TODO Auto-generated method stub
String sql="SELECT *FROM product";
return template.DQLoperator(sql, new beanlist<>(Productach.class));
}
@Override
public List<Productach> search(productquery go) {
// TODO Auto-generated method stub
String sql="SELECT *FROM product"+go.getquerystring();
return template.DQLoperator(sql, new beanlist<Productach>(Productach.class), go.getpara().toArray());
}
public Boolean haslength(String value) {
return value!=null&&!"".equals(value);
}
@Override
public pageresult query(Integer currentpage, Integer pagesize) {
// TODO Auto-generated method stub
String countsql="SELECT COUNT(*) FROM product";
Integer totalcount=template.DQLoperator(countsql, new type<Long>() {
@Override
public Long hanlder(ResultSet rs) throws Exception {
// TODO Auto-generated method stub
if(rs.next()){
return rs.getLong(1);
}
return 0L;
}
}).intValue();
String resultsql="SELECT *FROM product LIMIT ?,?";
Object []para= {(currentpage-1)*pagesize,pagesize};
List<Productach> pro=template.DQLoperator(resultsql, new beanlist<>(Productach.class), para);
return new pageresult(pro,totalcount,currentpage,pagesize);
}
@Override
public pageresult resudesign(productquery go) {
// TODO Auto-generated method stub
String countsql="SELECT COUNT(*) FROM product"+go.getquerystring();
Integer totalcount=template.DQLoperator(countsql, new type<Long>() {
@Override
public Long hanlder(ResultSet rs) throws Exception {
// TODO Auto-generated method stub
if(rs.next()) {
System.out.println(rs.getLong(1));
return rs.getLong(1);
}
return 0L;
}
}, go.getpara().toArray()).intValue();
if(totalcount==0){
return null;
}
String resultsql="SELECT *FROM product "+go.getquerystring()+" LIMIT ?,?";
go.getpara().add((go.getCurrentpage()-1)*go.getPagesize());
go.getpara().add(go.getPagesize());
List<Productach>data=template.DQLoperator(resultsql, new beanlist<>(Productach.class), go.getpara().toArray());
return new pageresult(data, totalcount, go.getCurrentpage(), go.getPagesize());
}
}