分页

基于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>&nbsp;&nbsp;&nbsp;&nbsp;<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>

 

上一篇:在移动端通过vue-pdf实现预览pdf


下一篇:sql 2005 row_number分页