Java项目之查询后分页
一、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="layui/css/layui.css"> <script src="js/jquery.min.js"></script> <style> body{ 100%; } /* 添加按钮样式 */ #updateList { margin: 0 auto; 300px; height: 250px; border: 1px solid #F00 } </style> </head> <body> <!-- 员工信息版块 --> <div> <form > action="${pageContext.request.contextPath}/ConditionPersonServlet" method="post"> <!--搜索栏 --> 公司职位： <select name="ppid" > <option value="">请选择</option> <!-- 需要注意的是{}里面不能有空格 --> <c:forEach items="${PositionList}" var="pl"> <option value="${pl.ppid}">${pl.pposition}</option> </c:forEach> </select> 团队： <select name="pteam" > <option value="">请选择</option> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> </select> 部门： <select name="pdid" > <option value="">请选择</option> <c:forEach items="${DepartmentList}" var="dl"> <option value="${dl.pdid}">${dl.pdepartment}</option> </c:forEach> </select> <input type="submit" value="搜索" class="layui-btn layui-btn-normal"> <!-- 信息表 --> <table class="layui-table"> <colgroup> <col width="150"> <col width="200"> <col> </colgroup> <thead> <tr> <th>员工编号</th> <th>员工姓名</th> <th>公司职位</th> <th>所属团队</th> <th>所属部门</th> <th></th> </tr> </thead> <tbody> <!-- varStatus="vs" 特有的属性，循环变量 varStatus="vs" --> <c:forEach items="${PageBean.list}" var="pro"> <tr> <td width=10%>${pro.pid}</td> <td width=15%>${pro.pname}</td> <td width=15%>${pro.pposition}</td> <td width=10%>${pro.pteam}</td> <td width=20%>${pro.pdepartment}</td> <td width=30%> <!-- PersonList是当前登录用户的信息集合 --> <c:if test="${pro.pid==1}"> <a href="${pageContext.request.contextPath}/PersonListByIdServlet?pid=${pro.pid}" id="updatebtn" class="layui-btn " style="display: none">修改</a> <a href="javascript:void(0)" onClick="del('${pro.pid}')" class="layui-btn layui-btn-danger" style="display: none">辞退</a> </c:if> <c:if test="${pro.pid!=1}"> <a href="${pageContext.request.contextPath}/PersonListByIdServlet?pid=${pro.pid}" id="updatebtn" class="layui-btn ">修改</a> <a href="javascript:void(0)" onClick="del('${pro.pid}')" class="layui-btn layui-btn-danger" style="margin-left:50px">辞退</a> </c:if> </td> </tr> </c:forEach> </tbody> </table> <%-- 构建分页导航 --%> <nav aria-label="Page navigation"> <ul class="pagination"> 共有${PageBean.totalCount} 个员工，共${PageBean.totalpage } 页，当前为${PageBean.currentPage} 页 <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=1">首页</a> <!-- 如果当前页为第一页时，就没有上一页这个超链接显示 --> <c:if test="${PageBean.currentPage ==1}"> <c:forEach begin="1" end="${PageBean.totalpage}" step="1" var="i"> <c:if test="${PageBean.currentPage == i}"> ${i} </c:if> <c:if test="${PageBean.currentPage != i}"> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${i}">${i}</a> </c:if> </c:forEach> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${PageBean.currentPage+1}">下一页</a> </c:if> <!-- 如果当前页不是第一页也不是最后一页，则有上一页和下一页这个超链接显示 --> <c:if test="${PageBean.currentPage > 1 && PageBean.currentPage < PageBean.totalpage}"> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${PageBean.currentPage-1}">上一页</a> <c:forEach begin="1" end="${PageBean.totalpage}" step="1" var="i"> <c:if test="${PageBean.currentPage == i}"> ${i} </c:if> <c:if test="${PageBean.currentPage != i}"> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${i}">${i}</a> </c:if> </c:forEach> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${PageBean.currentPage+1}">下一页</a> </c:if> <!-- 如果当前页是最后一页，则只有上一页这个超链接显示，下一页没有 --> <c:if test="${PageBean.currentPage == PageBean.totalpage}"> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${PageBean.currentPage-1}">上一页</a> <c:forEach begin="1" end="${PageBean.totalpage}" step="1" var="i"> <c:if test="${PageBean.currentPage == i}"> ${i} </c:if> <c:if test="${PageBean.currentPage != i}"> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${i}">${i}</a> </c:if> </c:forEach> </c:if> <a href="${pageContext.request.contextPath}/ConditionPersonServlet?currentPage=${PageBean.totalpage}">尾页</a> </ul> </div> </body> </html> <script type="text/javascript"> /* 点击删除按钮 */ function del(pid) { var isdel = confirm("您确认要辞退该员工吗？（辞退即所有此人的信息均无）"); if (isdel) { /* 除了form表单，其他的都是get提交，都需要? 拼接参数 */ window.location.href = "${pageContext.request.contextPath}/DeleteServlet?pid=" + pid; } } /* 回显---找属性值为Pdepartment等的元素使其被选中*/ $(function() { $("#pposition option[value='${Condition.ppid}']") .prop("selected", true); }) $(function() { $("#pteam option[value='${Condition.pteam}']").prop("selected", true); }) $(function() { $("#pdepartment option[value='${Condition.pdid}']").prop("selected", true); }) </script>
二、javaBean类：
1、创建分页类
package domain; //分页用类 import java.util.ArrayList; import java.util.List; //为了能多个地方调用这个，需要用泛型 public class PageBean<T> { //封装当前页 private Integer currentPage; //总页数 private Integer totalpage; //每页显示条数 private Integer currentCount; //总条数 private Integer totalCount; //每页显示的数据 private List<T> list = new ArrayList<T>(); public Integer getCurrentPage() { return currentPage; } public void setCurrentPage(Integer currentPage) { this.currentPage = currentPage; } public Integer getTotalpage() { return totalpage; } public void setTotalpage(Integer totalpage) { this.totalpage = totalpage; } public Integer getCurrentCount() { return currentCount; } public void setCurrentCount(Integer currentCount) { this.currentCount = currentCount; } public Integer getTotalCount() { return totalCount; } public void setTotalCount(Integer totalCount) { this.totalCount = totalCount; } public List<T> getList() { return list; } public void setList(List<T> list) { this.list = list; } @Override public String toString() { return "PageBean [currentPage=" + currentPage + ", totalpage=" + totalpage + ", currentCount=" + currentCount + ", totalCount=" + totalCount + ", list=" + list + "]"; } }
2、创建查询所用的条件类
package domain; import java.io.Serializable; //将搜索条件创建一个JavaBean---都是String类型的(这个跟原来的类中的类型无关）是需要判断非空的 public class Condition implements Serializable{ private String ppid; private String pteam; private String pdid; public String getPpid() { return ppid; } public void setPpid(String ppid) { this.ppid = ppid; } public String getPteam() { return pteam; } public void setPteam(String pteam) { this.pteam = pteam; } public String getPdid() { return pdid; } public void setPdid(String pdid) { this.pdid = pdid; } @Override public String toString() { return "Condition [pp>; } }
三、Dao层：
// 根据条件在员工信息表中搜索 public List<Message> getMessageByCondition(Condition condition, int index, Integer currentCount) throws SQLException { QueryRunner qr = new QueryRunner(MyDBUtils.getDataSource()); String sql = "select * from person join position ON person.ppid=position.ppid JOIN department ON person.pdid=department.pdid where 1=1"; // 需要将结果存在集合中（因数组长度不可变） DISTINCT去重 ArrayList<Object> arr = new ArrayList<Object>(); // trim()是去掉首尾空格 if (condition.getPpid() != null && condition.getPpid() != "") { // sql += " and pposition like ?"; && condition.getPteam() != "" // arr.add("%" + condition.getPposition() + "%"); sql += " and person.Ppid=?"; arr.add(condition.getPpid()); } if (condition.getPdid() != null && condition.getPdid() != "") { sql += " and person.Pdid=?"; arr.add(condition.getPdid()); } if (condition.getPteam() != null && condition.getPteam() != "") { sql += " and pteam=?"; arr.add(condition.getPteam()); } sql += " limit ?,?"; arr.add(index); arr.add(currentCount); List<Message> list = qr.query(sql, new BeanListHandler<Message>(Message.class), arr.toArray()); return list; }
2、Service层：
// 根据condition查询员工信息 public PageBean<Message> getMessageByCondition(Condition condition, Integer currentPage, Integer currentCount) { // 创建PageBean PageBean<Message> pageBean = new PageBean<Message>(); // 设置当前页 pageBean.setCurrentPage(currentPage); // 设置每页显示的条数 pageBean.setCurrentCount(currentCount); // 设置总条数 int totalCount = 0; try { totalCount = personMessageDao.getTotalCount(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } pageBean.setTotalCount(totalCount); // 计算总页数=总条数/每页显示的条数 //先整数*1.0变成小数，得到有小数点的-->向上取整，得到double类型-->再转为int Integer totalPage = (int) Math.ceil(totalCount * 1.0 / currentCount); // 设置总页数 pageBean.setTotalpage(totalPage); // 设置每页显示的数据 // select *fromproduct limit (当前页-1)*每页显示的条数 ， 每页显示的条数 int index = (currentPage - 1) * currentCount; // 定义 List<Message> list = null; try { list = personMessageDao.getMessageByCondition(condition, index, currentCount); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } pageBean.setList(list); return pageBean; }
四、Web层：
package web; import java.io.IOException; import java.lang.reflect.InvocationTargetException; import java.util.List; import java.util.Map; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.beanutils.BeanUtils; import dao.PersonListDao; import domain.Condition; import domain.Department; import domain.Message; import domain.PageBean; import domain.Person; import domain.Position; import service.PersonListService; import service.PersonMessageService; //当职位在总经理之下则进入此页面---员工信息里的分页和查询 public class PersonListServlet extends HttpServlet { private PersonListService personListService = new PersonListService(); private PersonMessageService personMessageService = new PersonMessageService(); public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // post解决乱码 request.setCharacterEncoding("utf-8"); // 获得所有条件参数所在的Map Map<String, String[]> map = request.getParameterMap(); // 创建Condition对象 Condition condition = new Condition(); // 用BeanUtils进行封装 try { BeanUtils.populate(condition, map); } catch (IllegalAccessException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (InvocationTargetException e) { // TODO Auto-generated catch block e.printStackTrace(); } // 获取当前页 String currentPageStr = request.getParameter("currentPage"); // 需要设置默认--当点击进来的时候显示第一页 if (currentPageStr == null) { currentPageStr = "1"; } // 将字符串转为Integer Integer currentPage = Integer.parseInt(currentPageStr); // 设置每页显示的条数 Integer currentCount = 8; // 调用service层方法 // 获得所有的职位的list-根据职位表把搜索的信息整合到主表中 List<Position> listp = personMessageService.getPosition(); // 获得所有的部门的list-根据职位表把搜索的信息整合到主表中 List<Department> listd = personMessageService.getDepartment(); // 获取员工信息的列表 PageBean<Message> list = personMessageService.getMessageByCondition(condition, currentPage, currentCount); // 向域中存值 request.getSession().setAttribute("PageBean", list); request.getSession().setAttribute("PositionList", listp); request.getSession().setAttribute("DepartmentList", listd); // 回显---将选择的选项进行显示在页面内 request.getSession().setAttribute("Condition", condition); request.getRequestDispatcher("PersonList.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }