分页查询功能
分析实现步骤
首先创建实体类BeanPage,设置总记录数、总页码数、每页的数据、当前页码、每页显示的记录数等参数。
其次创建Servlet,获取请求参数,调用service查询BeanPage,将beanPage存到request中,转发到jsp页面展示。 然后创建service查询BeanPage,先 创建空的PageBean对象, 设置当前页面属性和rows属性, 调用dao查询总记录数, 调用dao查询list集合, 计算总页码, 返回PageBean对象。再然后 创建UserDao,实现查询总记录数和查询list集合两个方法。最后 修改jsp页面,将request中的BeanPage数据放到页面中。实体类的创建
BeanPage.java
import java.util.List;/** * 分页对象 */public class PageBean { private int totalCount; // 总记录数 private int totalPage ; // 总页码 private Listlist ; // 每页的数据 private int currentPage ; //当前页码 private int rows;//每页显示的记录数 public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public List getList() { return list; } public void setList(Listlist) { this.list = list; } public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getRows() { return rows; } public void setRows(int rows) { this.rows = rows; }}
Servlet的创建
FindUserByPageServlet.java
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 java.io.IOException;import java.util.Map;@WebServlet("/findUserByPageServlet")public class FindUserByPageServlet extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); //1.获取参数 String currentPage = request.getParameter("currentPage");//当前页码 String rows = request.getParameter("rows");//每页显示条数 if(currentPage == null || "".equals(currentPage)){ currentPage = "1"; } if(rows == null || "".equals(rows)){ rows = "5"; } //获取条件查询参数 Map condition = request.getParameterMap(); //2.调用service查询 UserService service = new UserService(); PageBean pb = service.findUserByPage(currentPage,rows,condition); //3.将PageBean存入request request.setAttribute("pb",pb); request.setAttribute("condition",condition);//将查询条件存入request //4.转发到list.jsp request.getRequestDispatcher("/list.jsp").forward(request,response); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); }}
Service的创建
UserService.java
import java.util.List;import java.util.Map;public class UserService { private UserDao dao = new UserDao(); @Override public PageBean findUserByPage(String _currentPage, String _rows, Map condition) { int currentPage = Integer.parseInt(_currentPage); int rows = Integer.parseInt(_rows); if(currentPage <=0) { currentPage = 1; } //1.创建空的PageBean对象 PageBean pb = new PageBean(); //2.设置参数 pb.setCurrentPage(currentPage); pb.setRows(rows); //3.调用dao查询总记录数 int totalCount = dao.findTotalCount(condition); pb.setTotalCount(totalCount); //4.调用dao查询List集合 //计算开始的记录索引 int start = (currentPage - 1) * rows; List list = dao.findByPage(start,rows,condition); pb.setList(list); //5.计算总页码 int totalPage = (totalCount % rows) == 0 ? totalCount/rows : (totalCount/rows) + 1; pb.setTotalPage(totalPage); return pb; }}
UserDao的创建
UserDao.java
import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.Set;public class UserDao { private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource()); @Override public int findTotalCount(Map condition) { //1.定义模板初始化sql String sql = "select count(*) from user where 1 = 1 "; StringBuilder sb = new StringBuilder(sql); //2.遍历map Set keySet = condition.keySet(); //定义参数的集合 List params = new ArrayList(); for (String key : keySet) { //排除分页条件参数 if("currentPage".equals(key) || "rows".equals(key)){ continue; } //获取value String value = condition.get(key)[0]; //判断value是否有值 if(value != null && !"".equals(value)){ //有值 sb.append(" and "+key+" like ? "); params.add("%"+value+"%");//?条件的值 } } System.out.println(sb.toString()); System.out.println(params); return template.queryForObject(sb.toString(),Integer.class,params.toArray()); } @Override public ListfindByPage(int start, int rows, Map condition) { String sql = "select * from user where 1 = 1 "; StringBuilder sb = new StringBuilder(sql); //2.遍历map Set keySet = condition.keySet(); //定义参数的集合 List params = new ArrayList(); for (String key : keySet) { //排除分页条件参数 if("currentPage".equals(key) || "rows".equals(key)){ continue; } //获取value String value = condition.get(key)[0]; //判断value是否有值 if(value != null && !"".equals(value)){ //有值 sb.append(" and "+key+" like ? "); params.add("%"+value+"%");//?条件的值 } } //添加分页查询 sb.append(" limit ?,? "); //添加分页查询参数值 params.add(start); params.add(rows); sql = sb.toString(); System.out.println(sql); System.out.println(params); return template.query(sql,new BeanPropertyRowMapper(User.class),params.toArray()); }}
修改jsp页面
list.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><html lang="zh-CN"><head> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>用户信息管理系统title> <link href="css/bootstrap.min.css" rel="stylesheet"> <script src="js/jquery-2.1.0.min.js">script> <script src="js/bootstrap.min.js">script> <style type="text/css"> td, th { text-align: center; } style>head><body><div class="container"> <h3 style="text-align: center">用户信息列表h3> <div style="float: left;"> <form class="form-inline" action="${pageContext.request.contextPath}/findUserByPageServlet" method="post"> <div class="form-group"> <label for="exampleInputName2">姓名label> <input type="text" name="name" value="${condition.name[0]}" class="form-control" id="exampleInputName2" > div> <div class="form-group"> <label for="exampleInputName3">籍贯label> <input type="text" name="address" value="${condition.address[0]}" class="form-control" id="exampleInputName3" > div> <div class="form-group"> <label for="exampleInputEmail2">邮箱label> <input type="text" name="email" value="${condition.email[0]}" class="form-control" id="exampleInputEmail2" > div> <button type="submit" class="btn btn-default">查询button> form> div> <div style="float: right;margin: 5px;"> <a class="btn btn-primary" href="${pageContext.request.contextPath}/add.jsp">添加联系人a> <a class="btn btn-primary" href="javascript:void(0);" id="delSelected">删除选中a> div> <form id="form" action="${pageContext.request.contextPath}/delSelectedServlet" method="post"> <table border="1" class="table table-bordered table-hover"> <tr class="success"> <th><input type="checkbox" id="firstCb">th> <th>编号th> <th>姓名th> <th>性别th> <th>年龄th> <th>籍贯th> <th>QQth> <th>邮箱th> <th>操作th> tr> <c:forEach items="${pb.list}" var="user" varStatus="s"> <tr> <td><input type="checkbox" name="uid" value="${user.id}">td> <td>${s.count}td> <td>${user.name}td> <td>${user.gender}td> <td>${user.age}td> <td>${user.address}td> <td>${user.qq}td> <td>${user.email}td> <td> <a class="btn btn-default btn-sm" href="#">修改a> <a class="btn btn-default btn-sm" href="#">删除a> td> tr> c:forEach> table> form> <div> <nav aria-label="Page navigation"> <ul class="pagination"> <c:if test="${pb.currentPage == 1}"> <li class="disabled"> c:if> <c:if test="${pb.currentPage != 1}"> <li> c:if> <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage - 1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Previous"> <span aria-hidden="true">«span> a> li> <c:forEach begin="1" end="${pb.totalPage}" var="i" > <c:if test="${pb.currentPage == i}"> <li class="active"><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}a>li> c:if> <c:if test="${pb.currentPage != i}"> <li><a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${i}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}">${i}a>li> c:if> c:forEach> <li> <a href="${pageContext.request.contextPath}/findUserByPageServlet?currentPage=${pb.currentPage + 1}&rows=5&name=${condition.name[0]}&address=${condition.address[0]}&email=${condition.email[0]}" aria-label="Next"> <span aria-hidden="true">»span> a> li> <span style="font-size: 25px;margin-left: 5px;"> 共${pb.totalCount}条记录,共${pb.totalPage}页 span> ul> nav> div>div>body>html>
页面最终效果如图所示:
![68a37943369cb99ae2d9c9e271aaee77.png](https://i-blog.csdnimg.cn/blog_migrate/602b50ecc515c843e99c6bb03091d677.png)
![e6244a2c76366858016b35704cc7a777.png](https://i-blog.csdnimg.cn/blog_migrate/7b3dcc654b1e2bc46ca945176b53ec3b.png)
![2613f3a7bb759003dde04b6ffd27b149.png](https://i-blog.csdnimg.cn/blog_migrate/89a4a2c7ef9ad8a12c4ada60aa400a44.jpeg)
![e6244a2c76366858016b35704cc7a777.png](https://i-blog.csdnimg.cn/blog_migrate/7b3dcc654b1e2bc46ca945176b53ec3b.png)