首页 > 代码库 > MySQL分页查询

MySQL分页查询

2024-09-05 23:56:23   220人阅读

一、在Dao类中写两种方法：获得总页数和指定页的数据

获得总页数

public int getPageCount(int pageSize) throws Exception{
        try {
            conn=DBConnection.getConnection();
            String sql="select count(*) from car";
            stat=conn.prepareStatement(sql);
            rs=stat.executeQuery();
            rs.next();
            int rowsCount=rs.getInt(1);
            int pageCount=(int)Math.ceil(1.0*rowsCount/pageSize);//算出总共需要多少页
            return pageCount;
        }
        finally{
            conn.close();
        }

获得指定页的数据

public ArrayList<Car> getPageCar(int pageNo,int pageSize) throws Exception{//两个形参分别为当前页，每页有多少行
        ArrayList<Car> list=new ArrayList<Car>();
        try {
            conn=DBConnection.getConnection();            
            String sql="select * from car limit ?,?";
            stat=conn.prepareStatement(sql);
            stat.setInt(1, (pageNo-1)*pageSize);距离这一页的第一行数据，其前面有多少行数据
            stat.setInt(2, pageSize);//每页有多少行
            rs=stat.executeQuery();
            while(rs.next()){
                Car data=new Car();
                data.setCode(rs.getString(1));
                data.setName(rs.getString(2));
                data.setBrand(rs.getString(3));
                data.setTime(rs.getDate(4));
                data.setOil(rs.getDouble(5));
                data.setPowers(rs.getInt(6));
                data.setExhaust(rs.getInt(7));
                data.setPrice(rs.getDouble(8));
                data.setImage(rs.getString(8));
                list.add(data);                
            }
            
        } finally{
        conn.close();
        }
        return list;
    }

二、servlet处理数据

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //获取参数
        int pageNo=1;
        String s=request.getParameter("pgno");
        if(s!=null){
            pageNo=Integer.parseInt(s);
        }
        //处理数据
        try {
            int pageCount=new CarDao().getPageCount(PAGESIZE);//获得总页数
            ArrayList<Car> list=new CarDao().getPageCar(pageNo, PAGESIZE);//获得指定页数据
            int currentPage=pageNo;
            request.setAttribute("currentPage", currentPage);
            request.setAttribute("pageCount", pageCount);
            request.setAttribute("cars", list);
            int pagePrev=pageNo>1?pageNo-1:1;//上一页
            int pageNext=pageNo<pageCount?pageNo+1:pageCount;//下一页
            request.setAttribute("pageNow", pageNo);
            request.setAttribute("pagePrev", pagePrev);
            request.setAttribute("pageNext", pageNext);
            
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        
        //跳转
        request.getRequestDispatcher("home.jsp").forward(request, response);
    }

三、在jsp页面输出

<%@page import="com.itnba.maya.bean.Car"%>
<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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>
function aaa(){
    document.getElementById("f1").submit();
}
</script>
</head>
<body>
<h1>汽车分页</h1>
<c:forEach items="${cars }" var="c">
<div>
<span style="width:100px;display:inline-block;">${c.code }</span>
<span style="width:300px;display:inline-block;">${c.name }</span>
</div>
</c:forEach>
<!--下面是分页链接 -->
<a href="show?pgno=1">首页</a>
<a href="show?pgno=${pagePrev }">上一页</a>

<c:forEach begin="1" end="${pageCount }" var="i">
<a href="show?pgno=${i }">${i }</a> &nbsp;
</c:forEach>
<a href="show?pgno=${pageNext }">下一页</a>
<a href="show?pgno=${pageCount }">尾页</a>
<form id="f1" method="get" action="show">
<select name="pgno" onchange="aaa()">
<c:forEach begin="1" end="${pageCount }" var="i">
<c:choose>
    <c:when test="${pageNow == i }">
        <option value="${i }" selected="selected" >${i }</option>
    </c:when>
    <c:otherwise>
        <option value="${i }">${i }</option>
    </c:otherwise>
</c:choose>
</c:forEach>
</select>
</form>
</body>
</html>

运行结果：

技术分享

 

MySQL分页查询


联系
我们