Java实现MySQL分页查询
一、在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>
</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>
运行结果：
声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
- 上一篇： java的web开发分页工具类
- 下一篇： vue中的event bus非父子组件通信
