分页查询是java web开发中经常使用到的技术。在数据库中数据量非常大的情况下,不适合将所有的数据全部显示到一个页面中,同时为了节约程序以及数据库的资源,就需要对数据进行分页查询操作。
通过JDBC实现分页的方法比较多,而且不同的数据库机制其分页的方式也不同,这里我们介绍典型的两个分页方法。
1.通过ResultSet的光标实现分页
该分页方法可以在各种数据库之间通用,但是带来的缺点是占用了大量的资源,不适合在数据库大的情况下使用。
2.通过数据库机制进行分页
很多数据库都会提供这种分页机制,例如SQLServer中就提供了top关键字,mysql数据库中提供了limit关键字,用这些关键字都可以设置数据返回的记录数。
使用这种分页查询方式可以减少数据库的资源开销,提高程序效率,但是缺点是只适应于一种数据库。
通过JDBC实现分页的方法比较多,而且不同的数据库机制其分页的方式也不同,这里我们介绍典型的两个分页方法。
1.通过ResultSet的光标实现分页
该分页方法可以在各种数据库之间通用,但是带来的缺点是占用了大量的资源,不适合在数据库大的情况下使用。
2.通过数据库机制进行分页
很多数据库都会提供这种分页机制,例如SQLServer中就提供了top关键字,mysql数据库中提供了limit关键字,用这些关键字都可以设置数据返回的记录数。
使用这种分页查询方式可以减少数据库的资源开销,提高程序效率,但是缺点是只适应于一种数据库。
注:因为第一种不适合在数据量大的情况下使用,所以在实际开发中也不使用该方式来查询数据,只对第二种方式做介绍。
步骤说明:
1、代码请参考—— 第七篇JDBC操作数据库之批处理(删除)。2、在第七篇基础上继续实现功能——第八篇JDBC操作数据库之分页查询。
一、Book.java
1.添加
public static final int PAGE_SIZE = 2; //每一页显示2行数据
/**
* Created by Ray on 2018/3/11 0011.
**/
public class Book {
public static final int PAGE_SIZE = 2; //每一页显示2行数据
private int id; //编号
private String name; //名称
private double price; //价格
private int bookCount; //数量
private String author; //作者
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getBookCount() {
return bookCount;
}
public void setBookCount(int bookCount) {
this.bookCount = bookCount;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
}
二、BookFindDao.java
1.分页查询图书.
2.查询数据表总行数.
/**
* Created by Ray on 2018/3/12 0012.
**/
public class BookFindDao {
Dbconn dbconn = new Dbconn();
Connection conn = null;
PreparedStatement ps = null;
Statement st = null;
ResultSet rs = null;
Book book = null;
/**
* @Author: Ray
* @Date: 2018/3/12 0012
* @Description: 分页查询图书
* @Return: bookList
*/
public List find(int page){
//创建List
List bookList = new ArrayList();
try{
//获取数据库连接
conn = dbconn.getConnection();
//分页查询的sql语句
String sql = "select * from booktable order by id asc limit ?,?";
//获取PreparedStatement
ps = conn.prepareStatement(sql);
//对占位符进行赋值
ps.setInt(1,(page-1) * Book.PAGE_SIZE);
ps.setInt(2,Book.PAGE_SIZE);
//执行查询操作
rs = ps.executeQuery();
//光标向后移动,并判断是否有效
while(rs.next()){
//实例化Book
book = new Book();
book.setId(rs.getInt("id"));
book.setName(rs.getString("name"));
book.setPrice(rs.getDouble("price"));
book.setBookCount(rs.getInt("bookCount"));
book.setAuthor(rs.getString("author"));
//将book对象添加到集合中
bookList.add(book);
}
rs.close();
ps.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
return bookList;
}
/**
* @Author: Ray
* @Date: 2018/3/12 0012
* @Description: 查询数据表总行数
* @Return: 总行数
*/
public int findCount(){
//总行数
int count = 0;
try{
//获取数据库连接
conn = dbconn.getConnection();
//查询总行数的sql语句
String sql = "select count(*) from booktable";
//创建Statement
st = conn.createStatement();
//查询并获取ResultSet
rs = st.executeQuery(sql);
//光标向后移动,并判断是否有效
if(rs.next()){
//取出count(*)字段的值对总行数赋值
count = rs.getInt(1);
}
rs.close();
st.close();
conn.close();
}catch (Exception e){
e.printStackTrace();
}
//返回总行数
return count;
}
}
三、BookFind.java
1.检查当前页码
2.查询总页码
3.构建分页条
/**
* Created by Ray on 2018/3/12 0012.
**/
public class BookFind extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//当前页码
int currPage = 1;
//判断传递页码是否有效
if(request.getParameter("page") != null){
//对当前页码赋值
currPage = Integer.parseInt(request.getParameter("page"));
}
//实例化BookFindDao
BookFindDao bookFindDao = new BookFindDao();
//查询数据表总行数
List booklist = bookFindDao.find(currPage);
//request值传递
request.setAttribute("booklist",booklist);
//总页数
int pages;
//查询总行数
int count = bookFindDao.findCount();
//计算总页数
if(count % Book.PAGE_SIZE == 0){
//对总页数赋值
pages = count / Book.PAGE_SIZE;
}else{
//对总页数赋值
pages = count / Book.PAGE_SIZE + 1;
}
//实例化StringBuffer
StringBuffer sb = new StringBuffer();
//通过循环构建分页条
for(int i = 1; i <= pages; i++){
//判断是否为当前页
if (i == currPage){
//构建分页条
sb.append("[" + i + "]");
}else{
//构建分页条
sb.append("<a href='BookFind?page=" + i + "'>" + i + "</a>");
}
sb.append(" ");
}
//将分页条的字符串放置到request中
request.setAttribute("bar",sb.toString());
//转发到bookList
request.getRequestDispatcher("/bookFind.jsp").forward(request,response);
}
}
四、bookList.jsp
1.添加
<li><a href="<%=request.getContextPath()%>/BookFind">分页显示图书</a></li>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page import="com.entity.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>图书列表</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="css/bookList.css">
</head>
<body>
<form action="" method="post">
<table width="80%" align="center">
<div class="list">图书列表</div>
<ul>
<li><a href="bookAdd.jsp">新增图书</a> </li>
<li><a href="<%=request.getContextPath()%>/BookBatchAdd">批量新增图书</a></li>
<li><a href="<%=request.getContextPath()%>/BookBatchDelete">批量删除图书</a></li>
<li><a href="<%=request.getContextPath()%>/BookFind">分页显示图书</a></li>
</ul>
<tr>
<td>图书编号</td>
<td>图书名称</td>
<td>图书价格</td>
<td>图书数量</td>
<td>图书作者</td>
<td>图书修改</td>
<td>图书删除</td>
</tr>
<c:forEach var="bookitem" items="${booklist}">
<tr>
<td>${bookitem.id}</td>
<td>${bookitem.name}</td>
<td>${bookitem.price}</td>
<td>${bookitem.bookCount}</td>
<td>${bookitem.author}</td>
<td><a href="<%=request.getContextPath()%>/BookUpdate?id=${bookitem.id}">修改</a></td>
<td><a href="<%=request.getContextPath()%>/BookDelete?id=${bookitem.id}">删除</a></td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
五、bookFind.jsp
1.添加
<td align="center" colspan="7"><%=request.getAttribute("bar")%></td>
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>图书列表</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="css/bookList.css">
</head>
<body>
<form action="" method="post">
<table width="80%" align="center">
<div class="list">图书列表</div>
<ul>
<li><a href="bookAdd.jsp">新增图书</a> </li>
<li><a href="<%=request.getContextPath()%>/BookBatchAdd">批量新增图书</a></li>
<li><a href="<%=request.getContextPath()%>/BookBatchDelete">批量删除图书</a></li>
<li><a href="<%=request.getContextPath()%>/BookList">显示所有图书</a></li>
</ul>
<tr>
<td>图书编号</td>
<td>图书名称</td>
<td>图书价格</td>
<td>图书数量</td>
<td>图书作者</td>
<td>图书修改</td>
<td>图书删除</td>
</tr>
<c:forEach var="bookitem" items="${booklist}">
<tr>
<td>${bookitem.id}</td>
<td>${bookitem.name}</td>
<td>${bookitem.price}</td>
<td>${bookitem.bookCount}</td>
<td>${bookitem.author}</td>
<td><a href="<%=request.getContextPath()%>/BookUpdate?id=${bookitem.id}">修改</a></td>
<td><a href="<%=request.getContextPath()%>/BookDelete?id=${bookitem.id}">删除</a></td>
</tr>
</c:forEach>
<tr>
<td align="center" colspan="7"><%=request.getAttribute("bar")%></td>
</tr>
</table>
</form>
</body>
</html>
六、web.xml
这个很关键,不能出错,否则访问会出现404错误
<?xml version="1.0" encoding="UTF-8"?>
<web-app>
<servlet>
<servlet-name>BookList</servlet-name>
<servlet-class>com.control.BookList</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookList</servlet-name>
<url-pattern>/BookList</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookAdd</servlet-name>
<servlet-class>com.control.BookAdd</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookAdd</servlet-name>
<url-pattern>/BookAdd</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookUpdate</servlet-name>
<servlet-class>com.control.BookUpdate</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookUpdate</servlet-name>
<url-pattern>/BookUpdate</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookDoUpdate</servlet-name>
<servlet-class>com.control.BookDoUpdate</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookDoUpdate</servlet-name>
<url-pattern>/BookDoUpdate</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookDelete</servlet-name>
<servlet-class>com.control.BookDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookDelete</servlet-name>
<url-pattern>/BookDelete</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookDoDelete</servlet-name>
<servlet-class>com.control.BookDoDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookDoDelete</servlet-name>
<url-pattern>/BookDoDelete</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookBatchAdd</servlet-name>
<servlet-class>com.control.BookBatchAdd</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookBatchAdd</servlet-name>
<url-pattern>/BookBatchAdd</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookBatchDelete</servlet-name>
<servlet-class>com.control.BookBatchDelete</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookBatchDelete</servlet-name>
<url-pattern>/BookBatchDelete</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>BookFind</servlet-name>
<servlet-class>com.control.BookFind</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>BookFind</servlet-name>
<url-pattern>/BookFind</url-pattern>
</servlet-mapping>
</web-app>
七、页面效果
ok!