目录
1 分页查询
1.1 分页核心
设计一个用于封装当前页所有分页相关的数据的对象，叫分页对象PageBean
| /** * 分页对象。用于封装当前页的分页相关的所有数据 */ public class PageBean { private List<Employee> data;//当前页的数据 private Integer firstPage;//首页 private Integer prePage;//上一页 private Integer nextPage;//下一页 private Integer totalPage;//末页、总页数 private Integer currentPage;//当前页 private Integer totalCount;//总记录数 private Integer pageSize;//每页显示的记录数 } |
1.2 分页的实现步骤
1）编写分页对象和实体对象
| /** * 分页对象。用于封装当前页的分页相关的所有数据 */ public class PageBean { private List<Employee> data;//当前页的数据 private Integer firstPage;//首页 private Integer prePage;//上一页 private Integer nextPage;//下一页 private Integer totalPage;//末页、总页数 private Integer currentPage;//当前页 private Integer totalCount;//总记录数 private Integer pageSize;//每页显示的记录数 public List<Employee> getData() { return data; } public void setData(List<Employee> data) { this.data = data; } public Integer getFirstPage() { return 1; } public void setFirstPage(Integer firstPage) { this.firstPage = firstPage; } /** * 计算上一页 * @return */ public Integer getPrePage() { return this.getCurrentPage()==this.getFirstPage() ? 1 : this.getCurrentPage()-1; } public void setPrePage(Integer prePage) { this.prePage = prePage; } /** * 计算下一页 * @return */ public Integer getNextPage() { return this.getCurrentPage()==this.getTotalPage()? this.getTotalPage() : this.getCurrentPage()+1; } public void setNextPage(Integer nextPage) { this.nextPage = nextPage; } public Integer getTotalPage() { return this.getTotalCount()%this.getPageSize()==0 ? this.getTotalCount()/this.getPageSize() :this.getTotalCount()/this.getPageSize()+1; } public void setTotalPage(Integer totalPage) { this.totalPage = totalPage; } public Integer getCurrentPage() { return currentPage; } public void setCurrentPage(Integer currentPage) { this.currentPage = currentPage; } public Integer getTotalCount() { return totalCount; } public void setTotalCount(Integer totalCount) { this.totalCount = totalCount; } public Integer getPageSize() { return pageSize; } public void setPageSize(Integer pageSize) { this.pageSize = pageSize; } } |
| /** * 员工对象 */ public class Employee { private int id; private String name; private String gender; private int age; private String title; private String phone; private String email; 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 String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Employee(int id, String name, String gender, int age, String title, String phone, String email) { super(); this.id = id; this.name = name; this.gender = gender; this.age = age; this.title = title; this.phone = phone; this.email = email; } public Employee() { super(); // TODO Auto-generated constructor stub } @Override public String toString() { return "Employee [age=" + age + ", email=" + email + ", gender=" + gender + ", id=" + id + ", name=" + name + ", phone=" + phone + ", title=" + title + "]"; } } |
2）编写DAO层代码（查询总记录数和查询当前页数据）
| /** * 员工的DAO类 */ public class EmpDao { /** * 提供一个查询当前页员工的方法 */ public List<Employee> queryCurrentData(Integer currentPage,Integer pageSize){ try { //1.创建QueryRunner对象 QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource()); //2.执行查询sql操作 //计算查询的起始行 int startNo = (currentPage-1)*pageSize; List<Employee> list = (List<Employee>)qr.query("SELECT * FROM employee LIMIT ?,?", new BeanListHandler(Employee.class), new Object[]{startNo,pageSize}); return list; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } }
/** * 提供查询总记录数的方法 * @param args */ public Integer queryTotalCount(){ try { //1.创建QueryRunner QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource()); //2.执行sql查询 Long count = (Long)qr.query("SELECT COUNT(*) FROM employee", new ScalarHandler(1)); return count.intValue(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } |
3）编写Service层代码（封装PageBean对象）
| /** * 员工的业务类 */ public class EmpService { /** * 提供用于封装PageBean对象方法（处理业务逻辑） */ public PageBean queryPageBean(Integer currentPage,Integer pageSize){ //封装PageBean分页对象数据 PageBean pageBean = new PageBean();
//设置当前页 pageBean.setCurrentPage(currentPage);
//设置每页显示的记录数 pageBean.setPageSize(pageSize);
EmpDao empDao = new EmpDao(); /** * 从数据库中查询出总记录数 */ int totalCount = empDao.queryTotalCount(); //设置总记录数 pageBean.setTotalCount(totalCount);
//设置当前页的数据 /** * 从数据库中查询出当前页的员工数据 */ List<Employee> list = empDao.queryCurrentData(pageBean.getCurrentPage(), pageBean.getPageSize()); pageBean.setData(list); return pageBean; } } |
4）编写Servlet代码（接收用户输入）
| public class PageServlet extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { /***********一、获取用户输入**************/ //设置当前页(获取用户的输入) String currentPageStr = request.getParameter("currentPage"); //如果用户没有输入，就是默认第1页 if(currentPageStr==null || currentPageStr.equals("")){ currentPageStr = "1"; }
//设置每页显示的记录数(获取用户的输入) String pageSizeStr = request.getParameter("pageSize"); if(pageSizeStr==null || pageSizeStr.equals("")){ pageSizeStr = "5"; }
/***************二、调用业务方法，获取PageBean对象***********************/ EmpService empService = new EmpService(); PageBean pageBean = empService.queryPageBean(Integer.parseInt(currentPageStr), Integer.parseInt(pageSizeStr));
/****************三、得到业务数据，跳转视图*********************/ //把PageBean数据发送到jsp页面中显示 request.setAttribute("pageBean", pageBean); //转发 request.getRequestDispatcher("/list.jsp").forward(request, response); } } |
5）编写jsp页面代码（显示分页效果）
| <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>分页显示效果</title> </head> <body> <table border="1" align="center" width="700px"> <tr> <th>编号</th> <th>姓名</th> <th>性别</th> <th>年龄</th> <th>职位</th> <th>电话</th> <th>邮箱</th> </tr> <c:forEach items="${requestScope.pageBean.data}" var="emp"> <tr> <td>${emp.id }</td> <td>${emp.name }</td> <td>${emp.gender }</td> <td>${emp.age}</td> <td>${emp.title }</td> <td>${emp.phone }</td> <td>${emp.email }</td> </tr> </c:forEach> <tr> <td colspan="7" align="center"> <%-- 1）如果当前页是首页，则不显示“首页”和“上一页”的连接 2）如果当前页是末页，则不显示“末页”和“下一页”的连接 --%> <c:choose> <c:when test="${pageBean.currentPage==pageBean.firstPage}"> 首页 上一页 </c:when> <c:otherwise> <a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.firstPage }&pageSize=${pageBean.pageSize}">首页</a> <a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.prePage }&pageSize=${pageBean.pageSize}">上一页</a> </c:otherwise> </c:choose> <c:choose> <c:when test="${pageBean.currentPage==pageBean.totalPage}"> 下一页 末页 </c:when> <c:otherwise> <a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.nextPage}&pageSize=${pageBean.pageSize}">下一页</a> <a href="${pageContext.request.contextPath }/PageServlet?currentPage=${pageBean.totalPage }&pageSize=${pageBean.pageSize}">末页</a> </c:otherwise> </c:choose> 当前第${pageBean.currentPage }页/共${pageBean.totalPage }页， 共${pageBean.totalCount }条 每页显示 <input type="text" name="pageSize" id="pageSize" size="2" value="${pageBean.pageSize }" onblur="changePageSize()"/> 条 </td> </tr> </table> <script type="text/javascript"> //改变每页显示记录数的方法 function changePageSize(){ //获取用户输入的记录数 var pageSize = document.getElementById("pageSize").value; //判断是否输入的数值 var reg = /^[1-9][0-9]?$/; if(!reg.test(pageSize)){ alert("请输入数组类型！"); return; } //把记录数发送到后台 var url = "${pageContext.request.contextPath}/PageServlet?pageSize="+pageSize; window.location.href=url; } </script> </body> </html> |
2 条件查询
2.1 条件查询的核心
根据用户的查询条件组装sql语句：
| //2.组装sql StringBuffer sql = new StringBuffer("select * from department where 1=1 "); if(query!=null){ //部门名称不为空时 if(query.getDeptName()!=null && !query.getDeptName().equals("")){ sql.append(" and deptName like '%"+query.getDeptName()+"%'"); } //部门负责人不为空时 if(query.getPrincipal()!=null && !query.getPrincipal().equals("")){ sql.append(" and principal like '%"+query.getPrincipal()+"%'"); } //部门职能不为空时 if(query.getFunctional()!=null && !query.getFunctional().equals("")){ sql.append(" and functional like '%"+query.getFunctional()+"%'"); } } |
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
