分页查询
-
好处：
1. 减轻服务器内存的开销
2. 提升用户体验 -
输入输出分析图
- 总记录数:
根据sql在数据库中得到 - 总页码数:
根据总记录数和每页显示条数计算得到 - 数据集合:
根据sql在数据库中得到 - 当前页码:
在前端传参得到 - 每页显示的条数:
在前端传参得到
- 总记录数:
-
使用StringJDBC插件与Druid连接池
-
后台分析图
-
复杂条件查询分析
-
关键代码
- PageBean类
package cn.itcast.domain;
import java.util.List;
/**
* 分页对象
*
* @author 嘿嘿嘿1212
*/
public class PageBean<T> {
/**
* 总记录数
*/
private int totalCount;
/**
* 总页数
*/
private int totalPage;
/**
* 每页的数据
*/
private List<T> list;
/**
* 当前页数
*/
private int currentPage;
/**
* 每页显示的记录数
*/
private int rows;
public PageBean() {
}
public PageBean(int totalCount, int totalPage, List<T> list, int currentPage, int rows) {
this.totalCount = totalCount;
this.totalPage = totalPage;
this.list = list;
this.currentPage = currentPage;
this.rows = 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<T> getList() {
return list;
}
public void setList(List<T> list) {
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;
}
@Override
public String toString() {
final StringBuffer sb = new StringBuffer("PageBean{");
sb.append("totalCount=").append(totalCount);
sb.append(", totalPage=").append(totalPage);
sb.append(", list=").append(list);
sb.append(", currentPage=").append(currentPage);
sb.append(", rows=").append(rows);
sb.append('}');
return sb.toString();
}
}
- User类
package cn.itcast.domain;
/**
* @author 嘿嘿嘿1212
*/
public class User {
/**
* 用户id
*/
private Integer id;
/**
* 用户名称
*/
private String name;
/**
* 用户性别
*/
private String sex;
/**
* 用户年龄
*/
private Integer age;
/**
* 用户籍贯
*/
private String address;
/**
* 用户QQ
*/
private String qq;
/**
* 邮箱
*/
private String email;
/**
* 用户账号
*/
private String username;
/**
* 用户密码
*/
private String password;
public User() {
}
public User(Integer id, String name, String sex, Integer age, String address, String qq, String email, String username, String password) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.address = address;
this.qq = qq;
this.email = email;
this.username = username;
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
User user = (User) o;
if (id != null ? !id.equals(user.id) : user.id != null) return false;
if (name != null ? !name.equals(user.name) : user.name != null) return false;
if (sex != null ? !sex.equals(user.sex) : user.sex != null) return false;
if (age != null ? !age.equals(user.age) : user.age != null) return false;
if (address != null ? !address.equals(user.address) : user.address != null) return false;
if (qq != null ? !qq.equals(user.qq) : user.qq != null) return false;
if (email != null ? !email.equals(user.email) : user.email != null) return false;
if (username != null ? !username.equals(user.username) : user.username != null) return false;
return password != null ? password.equals(user.password) : user.password == null;
}
@Override
public int hashCode() {
int result = id != null ? id.hashCode() : 0;
result = 31 * result + (name != null ? name.hashCode() : 0);
result = 31 * result + (sex != null ? sex.hashCode() : 0);
result = 31 * result + (age != null ? age.hashCode() : 0);
result = 31 * result + (address != null ? address.hashCode() : 0);
result = 31 * result + (qq != null ? qq.hashCode() : 0);
result = 31 * result + (email != null ? email.hashCode() : 0);
result = 31 * result + (username != null ? username.hashCode() : 0);
result = 31 * result + (password != null ? password.hashCode() : 0);
return result;
}
@Override
public String toString() {
final StringBuffer sb = new StringBuffer("User{");
sb.append("id=").append(id);
sb.append(", name='").append(name).append('\'');
sb.append(", sex='").append(sex).append('\'');
sb.append(", age=").append(age);
sb.append(", address='").append(address).append('\'');
sb.append(", qq='").append(qq).append('\'');
sb.append(", email='").append(email).append('\'');
sb.append(", username='").append(username).append('\'');
sb.append(", password='").append(password).append('\'');
sb.append('}');
return sb.toString();
}
}
- 前端页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<!-- 网页使用的语言 -->
<html lang="zh-CN">
<head>
<!-- 指定字符集 -->
<meta charset="utf-8">
<!-- 使用Edge最新的浏览器的渲染方式 -->
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<!-- viewport视口：网页可以根据设置的宽度自动进行适配，在浏览器的内部虚拟一个容器，容器的宽度与设备的宽度相同。
width: 默认宽度与设备的宽度相同
initial-scale: 初始的缩放比，为1:1 -->
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
<title>用户信息管理系统</title>
<!-- 1. 导入CSS的全局样式 -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- 2. jQuery导入，建议使用1.9以上的版本 -->
<script src="js/jquery-2.1.0.min.js"></script>
<!-- 3. 导入bootstrap的js文件 -->
<script src="js/bootstrap.min.js"></script>
<style type="text/css">
td, th {
text-align: center;
}
.form-top-left {
float: right;
margin: 5px;
}
.form-inline {
float: left;
}
/*.form-top-right{*/
/*float: right;*/
/*}*/
</style>
<script>
//单条删除提醒
function deleteUser(id) {
if (confirm("确定删除吗?")) {
location.href = " ${pageContext.request.contextPath}/deleteUserServlet?id=" + id;
}
}
$(function () {
//批量删除提醒
$("#delSelected").click(function () {
var flag = $("input[name='uid']:checked");
if (flag.length) {
if (confirm("确定删除所选条目吗?"))
$("#form").submit()
}
});
//全选于全不选
$("#firstCb").click(function () {
$("input[name='uid']").prop("checked", this.checked)
});
//补充全选
$("input[name='uid']").click(function () {
var flag = true;
$("input[name='uid']").each(function (index, elemt) {
if (!$(elemt).prop("checked")) {
flag = false;
}
});
$("#firstCb").prop("checked", flag)
})
})
</script>
</head>
<body>
<div class="container">
<h3 style="text-align: center">用户信息列表</h3>
<div>
<form class="form-inline" action="${pageContext.request.contextPath}/findUserByPageServlet" method="post">
<div class="form-group">
<label for="username">姓名</label>
<input type="text" class="form-control" value="${condition.name[0]}" name="name" id="username">
</div>
<div class="form-group">
<label for="birthplace">籍贯</label>
<input type="text" class="form-control" value="${condition.address[0]}" name="address" id="birthplace">
</div>
<div class="form-group">
<label for="exampleInputEmail2">Email</label>
<input type="email" class="form-control" value="${condition.email[0]}" name="email"
id="exampleInputEmail2">
</div>
<button type="submit" class="btn btn-default form-top-right">查询</button>
</form>
</div>
<div class="form-top-left">
<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 action="${pageContext.request.contextPath}/delSelectedServlet" method="post" id="form">
<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>QQ</th>
<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.sex}</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="${pageContext.request.contextPath}/findUserServlet?id=${user.id}">修改</a> <a
class="btn btn-default btn-sm"
href="javascript:deleteUser(${user.id})">删除</a></td>
</tr>
</c:forEach>
</table>
</form>
<div style="float: left">
<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>
<c:if test="${pb.currentPage==pb.totalPage}">
<li class="disabled">
</c:if>
<c:if test="${pb.currentPage!=pb.totalPage}">
<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="Next">
<span aria-hidden="true">»</span>
</a>
</li>
</ul>
</nav>
</div>
<div style="float: left;margin-top:27px">
<span style="font-size: 10px;margin-left: 5px;">
共${pb.totalCount}条数据,共${pb.totalPage}页
</span>
</div>
</div>
</body>
</html>
- 条件分页Servelt
package cn.itcast.web.servlet;
import cn.itcast.domain.PageBean;
import cn.itcast.domain.User;
import cn.itcast.service.UserService;
import cn.itcast.service.impl.UserServiceImpl;
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;
/**
* @author 嘿嘿嘿1212
*/
@WebServlet("/findUserByPageServlet")
public class FindUserByPageServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
//获取参数
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<String, String[]> condition = request.getParameterMap();
//调用service方法
UserService userService = new UserServiceImpl();
PageBean<User> pb = userService.findUserByPage(currentPage, rows, condition);
//将PageBean存入request
request.setAttribute("pb", pb);
request.setAttribute("condition",condition);
//转发到list.jsp
request.getRequestDispatcher("list.jsp").forward(request, response);
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}
}
- UserServiceImpl(未贴出UserService接口)
package cn.itcast.service.impl;
import cn.itcast.dao.UserDao;
import cn.itcast.dao.impl.UserDaoImpl;
import cn.itcast.domain.PageBean;
import cn.itcast.domain.User;
import cn.itcast.service.UserService;
import java.util.List;
import java.util.Map;
/**
* @author 嘿嘿嘿1212
*/
public class UserServiceImpl implements UserService {
private UserDao dao = new UserDaoImpl();
@Override
public List<User> findAll() {
//调用Dao
return dao.findAll();
}
@Override
public User findUserById(String id) {
return dao.findById(Integer.parseInt(id));
}
@Override
public User login(User user) {
return dao.findUserByUsernameAndPassword(user.getUsername(), user.getPassword());
}
@Override
public void addUser(User user) {
dao.addUser(user);
}
@Override
public void deleteUser(String id) {
dao.delete(Integer.parseInt(id));
}
@Override
public void updateUser(User user) {
dao.update(user);
}
@Override
public void delSelectedUser(String[] udis) {
for (String udi : udis) {
dao.delete(Integer.parseInt(udi));
}
}
@Override
public PageBean<User> findUserByPage(String currentPage, String rows, Map<String, String[]> condition) {
//数据类型转换
int newCurrentPage = Integer.parseInt(currentPage);
int newRows = Integer.parseInt(rows);
//创建PageBean对象
PageBean<User> pb = new PageBean<>();
//设置参数
pb.setCurrentPage(newCurrentPage);
pb.setRows(newRows);
//调用dao查询总记录数
int totalCount = dao.findTotalCount(condition);
pb.setTotalCount(totalCount);
//计算总页数
int totalPage = totalCount % newRows == 0 ? totalCount / newRows : (totalCount / newRows) + 1;
pb.setTotalPage(totalPage);
//判断是否到达首页
if (newCurrentPage <= 0) {
newCurrentPage = 1;
}
//判断是否到达末尾
if (newCurrentPage > totalPage) {
newCurrentPage = totalPage;
}
//调用dao查询List集合
int start = (newCurrentPage - 1) * newRows;
List<User> list = dao.findByPage(start, newRows, condition);
pb.setList(list);
return pb;
}
}
- DaoImpl类
package cn.itcast.dao.impl;
import cn.itcast.dao.UserDao;
import cn.itcast.domain.User;
import cn.itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* @author 嘿嘿嘿1212
*/
public class UserDaoImpl implements UserDao {
private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
@Override
public List<User> findAll() {
String sql = "select * from user";
List<User> users = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
return users;
}
@Override
public User findById(Integer id) {
String sql = "select * from user where id=?";
User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
return user;
}
@Override
public User findUserByUsernameAndPassword(String username, String password) {
try {
String sql = "select * from user where username= ? and password =?";
User user = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
return user;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
@Override
public void addUser(User user) {
//创建sql
String sql = "insert into user values(null,?,?,?,?,?,?,null,null)";
//执行语句
template.update(sql, user.getName(), user.getSex(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail());
}
@Override
public void delete(int id) {
//创建Sql
String sql = "delete from user where id=?";
template.update(sql, id);
}
@Override
public void update(User user) {
//创建sqL
String sql = "update user set name=?,sex=?,age=?,address=?,qq=?,email=? where id=?";
template.update(sql, user.getName(), user.getSex(), user.getAge(), user.getAddress(), user.getQq(), user.getEmail(), user.getId());
}
@Override
public int findTotalCount(Map<String, String[]> condition) {
//定义模板
String sql = "select count(*) from user where 1=1 ";
StringBuilder sb = new StringBuilder(sql);
//遍历map
Set<Map.Entry<String, String[]>> entries = condition.entrySet();
//定义参数集合
List<Object> params = new ArrayList<>();
paramsJoin(sb, entries, params);
System.out.println(sb.toString());
System.out.println(params);
return template.queryForObject(sb.toString(), Integer.class, params.toArray());
}
private void paramsJoin(StringBuilder sb, Set<Map.Entry<String, String[]>> entries, List<Object> params) {
for (Map.Entry<String, String[]> entry : entries) {
//排除分页条件
if ("currentPage".equals(entry.getKey()) || "rows".equals(entry.getKey())) {
continue;
}
String value = entry.getValue()[0];
if (value != null && !"".equals(value)) {
sb.append(" and " + entry.getKey() + " like ?");
//条件的值
params.add("%" + value + "%");
}
}
}
@Override
public List<User> findByPage(int start, int rows, Map<String, String[]> condition) {
String sql = "select * from user where 1=1 ";
StringBuilder sb = new StringBuilder(sql);
//遍历map
Set<Map.Entry<String, String[]>> entries = condition.entrySet();
//定义参数集合
List<Object> params = new ArrayList<>();
paramsJoin(sb, entries, params);
sb.append(" limit ?,? ");
params.add(start);
params.add(rows);
sql = sb.toString();
return template.query(sql, new BeanPropertyRowMapper<User>(User.class),params.toArray());
}
}
扫一扫
742
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
