基于mysql的分页查询
1关键字----limit m,n m起始位置（从0开始） n条数
2m和当前页currentPage，pageSize的公式
M=(currentPage-1)*pageSize
N=pageSize
分页开发步骤
1、分页辅助类
package crm.zy.utils;
import java.util.List;
public class LimitUtil {//分页辅助类
//属性
//页面尺寸----->自己规定的
private Integer pageSize=3;
//总条数------->从数据库查询的（SELECT COUNT(*) from t_customer）
private Integer totalCount;
//总页数------>总页数int c=(a%b==0?(a/b):(a/b)+1);
private Integer pageCount;
//当前页 ------->页面传入的参数
private Integer currentPage;
//查询出来的数据集合 ?不确定：增强
private List<?> data;
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalCount() {
return totalCount;
}
public void setTotalCount(Integer totalCount) {
this.totalCount = totalCount;
}
//------------------------------------
public Integer getPageCount() {
//总页数------>总页数int c=(a%b==0?(a/b):(a/b)+1);
this.pageCount=(totalCount%pageSize==0)?(totalCount/pageSize):((totalCount/pageSize)+1);
return pageCount;
}
//------------------------------------
public void setPageCount(Integer pageCount) {
this.pageCount = pageCount;
}
public Integer getCurrentPage() {
return currentPage;
}
public void setCurrentPage(Integer currentPage) {
this.currentPage = currentPage;
}
public List<?> getData() {
return data;
}
public void setData(List<?> data) {
this.data = data;
}
}
2 、Dao层
//分页
@Override
//查询总条数
public int getTotalCount() {
// TODO Auto-generated method stub
String sql="SELECT COUNT(*) from t_customer";
Connection connection=null;
PreparedStatement ps = JDBCutil.getPreparedStatement(con, url, user, pwd1, sql);
Customer customer=null;
int num=0;
try {
ResultSet rs = ps.executeQuery();
while(rs.next()){
num=rs.getInt(1);
}
JDBCutil.closeAll(rs, connection, ps);
return num;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
//分页查询客户信息
@Override
public List<Customer> getCustomer(int m, int n) {
String sql="SELECT t_customer.cid, t_customer.custName,t_dict.dname,t_customer.custSource,t_customer.custPhone,t_customer.custMobile,t_dict.did"
+ " FROM t_customer INNER JOIN t_dict ON t_customer.custLevel=t_dict.did"
+" LIMIT ?,?";
Connection connection=null;
PreparedStatement ps = JDBCutil.getPreparedStatement(con, url, user, pwd1, sql);
Customer customer=null;
ArrayList<Customer> list = new ArrayList();
try {
ps.setInt(1, m);
ps.setInt(2, n);
ResultSet rs = ps.executeQuery();
while(rs.next()){
customer=new Customer(rs.getInt(1),rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6),rs.getInt(7));
list.add(customer);
}
JDBCutil.closeAll(rs, connection, ps);
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
3、Service层
@Override
//分页
public LimitUtil getCustomerInfo(int currentPage) {
//得到m，n
LimitUtil limitUtil = new LimitUtil();
limitUtil.setCurrentPage(currentPage);
int m=(currentPage-1)*limitUtil.getPageSize();
int n=limitUtil.getPageSize();
List<Customer> customer = cd.getCustomer(m, n);
//把limitUtil中未赋值的属性赋值
limitUtil.setTotalCount(cd.getTotalCount());
limitUtil.setData(customer);//list类型
return limitUtil;//进过以上赋值，limitUtil拥有页面所需要的一些信息，把它返回
}
4、Servlet
package com.zy.controller;
import java.io.IOException;
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 com.zy.service.impl.CustomerServiceImpl;
import crm.zy.utils.LimitUtil;
/**
* Servlet implementation class PageLimitServlet
*/
@WebServlet("/PageLimitServlet")
public class PageLimitServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//接收参数
CustomerServiceImpl cs = new CustomerServiceImpl();
String parameter = request.getParameter("currentPage");
if(parameter!=null){
int currentPage = Integer.parseInt(parameter);
LimitUtil customerInfo = cs.getCustomerInfo(currentPage);
//存域
request.setAttribute("pageBean", customerInfo);
}
request.getRequestDispatcher("/WEB-INF/customer/limitPage.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
5、Jsp
<%@ 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>
<html>
<head>
<meta charset="UTF-8">
<title>分页客户列表</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<link rel="stylesheet" href="${pageContext.request.contextPath}/css/begtable.css" />
<link rel="stylesheet" href="${pageContext.request.contextPath}/plugins/layui/css/layui.css" media="all" />
<link rel="stylesheet" href="${pageContext.request.contextPath}/plugins/font-awesome/css/font-awesome.min.css">
</head>
<body>
<div style="width:100%; height: 80%; border: 1px solid #009688;">
<div class="beg-table-box">
<div class="beg-table-body">
<table class="beg-table beg-table-bordered beg-table-striped beg-table-hovered">
<tr>
<th>客户名称</th>
<th>客户级别</th>
<th>客户来源</th>
<th>固定电话</th>
<th>移动电话</th>
<th>操作</th>
</tr>
<c:forEach items="${pageBean.data}" var="customer">
<tr>
<td>${customer.custName}</td>
<td>${customer.dname}</td>
<td>${customer.custSource}</td>
<td>${customer.custPhone}</td>
<td>${customer.custMobile}</td><!-- 绝对路径请求 值为/crm -->
<td><a href="${pageContext.request.contextPath}/ToUpdateServlet?id=${customer.cid}">修改</a> <a href="DeleteCustServlet?id=${customer.cid}&control=deleteCust" onclick="javascript:return confirm('请慎重考虑是否要删除该数据！！！！')">删除</a></td>
</tr>
</c:forEach>
</table>
</div>
<div class="beg-table-paged">
共[<B>${pageBean.totalCount}</B>]条记录，[<B>${pageBean.pageCount}</B>]页，当前第[<B>${pageBean.currentPage}</B>]页
<c:if test="${pageBean.currentPage!=1 }">
[<B><a href="PageLimitServlet?currentPage=${pageBean.currentPage-1}">前一页</a></B>]
</c:if>
<c:if test="${pageBean.currentPage!=pageBean.pageCount}">
[<B><a href="PageLimitServlet?currentPage=${pageBean.currentPage+1}">后一页</a></B>]
</c:if>
</div>
</div>
</div>
</body>
</html>