文章目录
一、为什么要分页
- 1.现代web系统中,拥有大量的数据,如果部分也,这些数据将难以处理
- 2.分页有2个作用,方便页面的展示,减轻服务器和数据库的压力
二、如何分页
- 1.伪分页,又叫内存级分页,查询全量数据,但是只展示一部分
- 2.真分页,又称为物理分页,或者叫做数据库分页,查询和展示的都是数据库中部分数据
三、Mysql数据库分页语句
语法:关键字 limit 起始位置(offset) 查询数量(pageSize)
-- 第一页
select * from stu limit 0,5;
-- 第二页
select * from stu limit 5,5;
-- 第三页
select * from stu limit 10,5;
-- 第n页
-- select * from stu limit (currentSize-1)*pageSize,pageSize;
四、Dao层部分代码
package org.lanqiao.dao.impl;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.lanqiao.bean.Student;
import org.lanqiao.dao.IStudentDao;
import org.lanqiao.util.DBUtil;
public class StudentDaoImpl implements IStudentDao {
@Override
public List<Student> getStusByPage(int currentPage, int pageSize) {
//获取数据库连接
Connection conn=DBUtil.getConn();
//准备sql语句
String sql="select * from student limit ?,?";
//准备参数
Object[] objs= {(currentPage-1)*pageSize,pageSize};
//执行通用查询
ResultSet rs=DBUtil.executeQuery(sql, objs);
//创建学生对象集合
List<Student> students=new ArrayList<>();
try {
while(rs.next()) {
int sid=rs.getInt("ID");
String name=rs.getString("name");
String pass=rs.getString("password");
String grade=rs.getString("grade");
int age=rs.getInt("age");
int teaId=rs.getInt("teacher_ID");
Student student=new Student(sid, name, pass, age, grade, teaId);
students.add(student);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return students;
}
}
五、service层实现
直接调用Dao层的代码
六、分页工具类Page.java
6.1分页有关概念
- currentPage 当前页
- pageSize 每页显示的条数
- offset 偏移量
- totalCount 数据总条数
- totalPage 数据的总页数
6.2 获取数据的总条数
Mysql语法
-- 获取学生数据总条数
select count(ID) from student ;
先引入Page.java
package org.lanqiao.util;
public class Page {
private int currentPage;//当前页
private int pageSize; //每页显示的条数
private int totalCount; //总条数
private int pageCount; //总页数
private int offSet; //偏移量
public Page(int currentPage, int pageSize, int totalCount) {
super();
this.currentPage = currentPage;
this.pageSize = pageSize;
this.totalCount = totalCount;
}
public Page() {
super();
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getPageCount() { //获得总页数
if(totalCount%pageSize==0) {
return totalCount/pageSize;
}else {
return totalCount/pageSize+1;
}
}
public int getOffset() {
return (currentPage-1)*pageSize;
}
@Override
public String toString() {
return "Page [currentPage=" + currentPage + ", pageSize=" + pageSize + ", totalCount=" + totalCount + "]";
}
}
对应的dao层代码
/**
* 查询学生总数
*/
public int getStuCount() {
//获取数据库连接
Connection conn=DBUtil.getConn();
//准备sql语句
String sql="select count(ID) from student ";
ResultSet rs=DBUtil.executeQuery(sql, null);
int count=-1;
try {
if(rs.next()) {
count=rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
book.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 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></title>
<link rel="stylesheet" href="css/pintuer.css">
<link rel="stylesheet" href="css/admin.css">
<script src="js/jquery.js"></script>
<script src="js/pintuer.js"></script>
</head>
<body>
<form method="post" action="">
<div class="panel admin-panel">
<div class="panel-head"><strong class="icon-reorder"> 学生管理</strong></div>
<div class="padding border-bottom">
<ul class="search">
<li>
<button type="button" class="button border-green" id="checkall"><span class="icon-check"></span> 全选</button>
<button type="submit" class="button border-red"><span class="icon-trash-o"></span> 批量删除</button>
</li>
</ul>
</div>
<table class="table table-hover text-center">
<tr>
<th width="120">序号</th>
<th>ID</th>
<th>姓名</th>
<th>年级</th>
<th>年龄</th>
<th>老师ID</th>
<th>操作</th>
</tr>
<c:forEach items="${studentList }" var="stu" varStatus="status">
<tr>
<td><input type="checkbox" name="id[]" value="1" />
${status.index+1 }</td>
<td>${stu.ID}</td>
<td>${stu.name }</td>
<td>${stu.grade }</td>
<td>${stu.age }</td>
<td>${stu.teacher_ID }</td>
<td>
<div class="button-group"> <a class="button border-red" href="javascript:void(0)" onclick="return del(${stu.ID })"><span class="icon-trash-o"></span> 删除</a> </div>
<div class="button-group"> <a class="button border-green" href="stuManagerServlet?flag=get&id=${stu.ID}" onclick="updateStu.jsp?id=${stu.ID }"><span class="icon-trash-o"></span> 修改</a> </div>
</td>
</tr>
</c:forEach>
<tr>
<td colspan="8"><div class="pagelist">
<a href="stuManagerServlet?flag=stusByPage¤tPage=1">首页</a>
<c:if test="${page.currentPage!=1 }">
<a href="stuManagerServlet?flag=stusByPage¤tPage=${page.currentPage-1 }">上一页</a>
</c:if>
<!--
<a class="current" href="stuManagerServlet?flag=stusByPage¤tPage=${1 }">1</a>
<a class="current" href="stuManagerServlet?flag=stusByPage¤tPage=${2 }">2</a>
<a class="current" href="stuManagerServlet?flag=stusByPage¤tPage=${3 }">3</a>
-->
<c:set var="begin" value="1" scope="page"/>
<c:set var="end" value="${page.pageCount }" scope="page"/>
<c:if test="${page.currentPage-10>0 }">
<c:set var="begin" value="page.currentPage-10" scope="page"/>
</c:if>
<c:if test="${page.currentPage+10<page.pageCount }">
<c:set var="end" value="page.currentPage+10" scope="page"/>
</c:if>
<c:forEach var="i" begin="${begin }" end="${end }" >
<c:if test="${page.currentPage==i }">
${i }
</c:if>
<c:if test="${page.currentPage!=i }">
<a href="StuManageServlet?flag=stusByPage¤tPage=${i}">${i }</a>
</c:if>
</c:forEach>
<input type="text" id="currentPage" name="currentPage"size="2"/>
<input type="button" value="go" onclick="jump()"/>
<c:if test="${page.currentPage!=page.pageCount }">
<a href="stuManagerServlet?flag=stusByPage¤tPage=${page.currentPage+1 }">下一页</a>
</c:if>
<a href="stuManagerServlet?flag=stusByPage¤tPage=${page.pageCount }">尾页</a> </div></td>
</tr>
</table>
</div>
</form>
<script type="text/javascript">
function jump(){
var currentPage=$("#currentPage").val();
location.href="StuManageServlet?flag=stusByPage¤tPage="+currentPage;
}
function del(id){
//alert(id);
if(confirm("您确定要删除吗?")){
location.href="stuManagerServlet?flag=del&id="+id; //跳转到某个地址
}
}
$("#checkall").click(function(){
$("input[name='id[]']").each(function(){
if (this.checked) {
this.checked = false;
}
else {
this.checked = true;
}
});
})
function DelSelect(){
var Checkbox=false;
$("input[name='id[]']").each(function(){
if (this.checked==true) {
Checkbox=true;
}
});
if (Checkbox){
var t=confirm("您确认要删除选中的内容吗?");
if (t==false) return false;
}
else{
alert("请选择您要删除的内容!");
return false;
}
}
</script>
</body>
</html>