基于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="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>